Part-DB
Performance Issues using Postgres
Describe the bug I just started using this project today and noticed that performance starts to suffer massivly with only 10 parts (that where imported via LCSC) the initial loading when opening the first category took more than 5 seconds.
To Reproduce Steps to reproduce the behavior: Setup a server, using postgres as a database, import a few parts and see performance degradate when opening the parts page.
Expected behavior No 5+ second load time with 10 parts
Screenshots Enableing logging for slow queries resulted in this: https://haste.turulix.de/nuyikazodu.sql Also here is the full https://haste.turulix.de/gahudomaqa.sql
Server Side
- Part-DB Version: 1.17.1
- PHP Version: 8.3.21
- Database Server: PostgreSQL 17.0
Desktop (please complete the following information):
- OS: Windows
- Browser: Brave
- Version: 1.79.119
Additional context I did some digging around and managed to find some indexes that really help speed up this search, i managed to reduce the loading time from ~5seconds to 500ms by adding:
CREATE INDEX IF NOT EXISTS idx_attachments_element_id_class_name ON attachments (element_id, class_name);
-- For the JOIN on parameters (p0 -> p12)
-- Critical for filtering by element_id and type
CREATE INDEX IF NOT EXISTS idx_parameters_element_id_type ON parameters (element_id, type);
-- For the various foreign key JOINs from the 'parts' table
CREATE INDEX IF NOT EXISTS idx_parts_category_id ON parts (id_category);
CREATE INDEX IF NOT EXISTS idx_parts_footprint_id ON parts (id_footprint);
CREATE INDEX IF NOT EXISTS idx_parts_manufacturer_id ON parts (id_manufacturer);
-- For the JOIN between part_lots and parts/storelocations
CREATE INDEX IF NOT EXISTS idx_part_lots_part_id ON part_lots (id_part);
CREATE INDEX IF NOT EXISTS idx_part_lots_store_location_id ON part_lots (id_store_location);
-- For the JOIN between orderdetails and parts/suppliers
CREATE INDEX IF NOT EXISTS idx_orderdetails_part_id ON orderdetails (part_id);
CREATE INDEX IF NOT EXISTS idx_orderdetails_supplier_id ON orderdetails (id_supplier);
i'm not sure which indexes are strictly nessesary for this. But it definitly helped a lot with performance. Maybe someone smarter than me can find some more :)
Thats weird, postgresql should get the same indices as the other database types, as it is generated from doctrine attributes. So thats weird why the problems should only occur on postgres. And with 10 entries even a completly indexless query should be much faster.
Are you sure that this is not caused by some misconfiguration of postgres or PHP? How do you connect each other?
I mean, i just know it isn't a postgres issue since all other applications of mine using that db are doing fine (its also on pretty decent hardware so it likely isn't an issue with that either). Its even running with a work_mem of 64MB to allow in memory sorting on large tables.
I just set up a second db to try to run the migration again since i remembered seeing something that looked off during the initial migration:
docker exec --user=www-data partdb php bin/console doctrine:migrations:migrate
WARNING! You are about to execute a migration in database "partdb_db_temp" that could result in schema changes and data loss. Are you sure you wish to continue? (yes/no) [yes]:
> [notice] Migrating up to DoctrineMigrations\Version20250222165240
[warning] Migration not needed for Postgres. Skipping...
[warning] Migration DoctrineMigrations\Version1 was executed but did not result in any SQL statements.
[warning] Migration not needed for Postgres. Skipping...
[warning] Migration DoctrineMigrations\Version20190902140506 was executed but did not result in any SQL statements.
[warning] Migration not needed for Postgres. Skipping...
[warning] Migration DoctrineMigrations\Version20190913141126 was executed but did not result in any SQL statements.
[warning] Migration not needed for Postgres. Skipping...
[warning] Migration DoctrineMigrations\Version20190924113252 was executed but did not result in any SQL statements.
[warning] Migration not needed for Postgres. Skipping...
[warning] Migration DoctrineMigrations\Version20191214153125 was executed but did not result in any SQL statements.
[warning] Migration not needed for Postgres. Skipping...
[warning] Migration DoctrineMigrations\Version20200126191823 was executed but did not result in any SQL statements.
[warning] Migration not needed for Postgres. Skipping...
[warning] Migration DoctrineMigrations\Version20200311204104 was executed but did not result in any SQL statements.
[warning] Migration not needed for Postgres. Skipping...
[warning] Migration DoctrineMigrations\Version20200409130946 was executed but did not result in any SQL statements.
[warning] Migration not needed for Postgres. Skipping...
[warning] Migration DoctrineMigrations\Version20200502161750 was executed but did not result in any SQL statements.
[warning] Migration not needed for Postgres. Skipping...
[warning] Migration DoctrineMigrations\Version20220925162725 was executed but did not result in any SQL statements.
[warning] Migration not needed for Postgres. Skipping...
[warning] Migration DoctrineMigrations\Version20221003212851 was executed but did not result in any SQL statements.
[warning] Migration not needed for Postgres. Skipping...
[warning] Migration DoctrineMigrations\Version20221114193325 was executed but did not result in any SQL statements.
[warning] Migration not needed for Postgres. Skipping...
[warning] Migration DoctrineMigrations\Version20221204004815 was executed but did not result in any SQL statements.
[warning] Migration not needed for Postgres. Skipping...
[warning] Migration DoctrineMigrations\Version20221216224745 was executed but did not result in any SQL statements.
[warning] Migration not needed for Postgres. Skipping...
[warning] Migration DoctrineMigrations\Version20230108165410 was executed but did not result in any SQL statements.
[warning] Migration not needed for Postgres. Skipping...
[warning] Migration DoctrineMigrations\Version20230219225340 was executed but did not result in any SQL statements.
[warning] Migration not needed for Postgres. Skipping...
[warning] Migration DoctrineMigrations\Version20230220221024 was executed but did not result in any SQL statements.
[warning] Migration not needed for Postgres. Skipping...
[warning] Migration DoctrineMigrations\Version20230402170923 was executed but did not result in any SQL statements.
[warning] Migration not needed for Postgres. Skipping...
[warning] Migration DoctrineMigrations\Version20230408170059 was executed but did not result in any SQL statements.
[warning] Migration not needed for Postgres. Skipping...
[warning] Migration DoctrineMigrations\Version20230408213957 was executed but did not result in any SQL statements.
[warning] Migration not needed for Postgres. Skipping...
[warning] Migration DoctrineMigrations\Version20230417211732 was executed but did not result in any SQL statements.
[warning] Migration not needed for Postgres. Skipping...
[warning] Migration DoctrineMigrations\Version20230528000149 was executed but did not result in any SQL statements.
[warning] Migration not needed for Postgres. Skipping...
[warning] Migration DoctrineMigrations\Version20230716184033 was executed but did not result in any SQL statements.
[warning] Migration not needed for Postgres. Skipping...
[warning] Migration DoctrineMigrations\Version20230730131708 was executed but did not result in any SQL statements.
[warning] Migration not needed for Postgres. Skipping...
[warning] Migration DoctrineMigrations\Version20230816213201 was executed but did not result in any SQL statements.
[warning] Migration not needed for Postgres. Skipping...
[warning] Migration DoctrineMigrations\Version20231114223101 was executed but did not result in any SQL statements.
[warning] Migration not needed for Postgres. Skipping...
[warning] Migration DoctrineMigrations\Version20231130180903 was executed but did not result in any SQL statements.
[warning] Migration not needed for Postgres. Skipping...
[warning] Migration DoctrineMigrations\Version20240427222442 was executed but did not result in any SQL statements.
[warning]
[warning] The initial password for the "admin" user is: xyz
[warning]
[warning] Migration DoctrineMigrations\Version20240728145604 was executed but did not result in any SQL statements.
[notice] finished in 482.2ms, used 85M memory, 32 migrations executed, 191 sql queries
[OK] Successfully migrated to version: DoctrineMigrations\Version20250222165240
I don't know if this is intended or related