Open Fell-x27 opened 1 year ago
This is expected behaviour. db-sync doesn't create and cannot create all possible indexes. You can add your own based on your queries.
You can add your own based on your queries.
But the queries mentioned above are not specific to my own queries. They are internal queries executed by db-sync. The purpose of posting this issue is to highlight the need for those indices within db-sync itself, as they are essential for optimizing its performance.
Oh I see then it's a different story indeed. I believe these queries are executed during rollback. Do you see them often?
Oh I see then it's a different story indeed. I believe these queries are executed during rollback. Do you see them often?
Often. Very often. As I can see, they are executing right after block insertion queries.
But, of course, they have disappeared after indices creation.
There is one more takes more than 5 sec:
SELECT "reference_tx_in"."id"
FROM "reference_tx_in"
WHERE "reference_tx_in"."tx_in_id" >= 69610494
ORDER BY "reference_tx_in"."id" ASC
LIMIT 1
With the same problem and solution.
-------63 MB
create index reference_tx_in_tx_in_id
on reference_tx_in (tx_in_id);
I'm pretty sure these queries happen only during rollback. Rollbacks are not that frequent and spending a few seconds for them should be fine.
OS Your OS: Ubuntu 22.04
Versions The
db-sync
version: 13.1.0.2 PostgreSQL version: 14Build/Install Method The method you use to build or install
cardano-db-sync
: binariesRun method The method you used to run
cardano-db-sync
(eg Nix/Docker/systemd/none): systemdProblem Report I have discovered some slow queries in my logs after updating to version 13.1.0.2. These internal queries executed by db-sync are consistently taking about 5-7 seconds to complete:
Of course, it runs slow for every tx_id, not just for the specific value of 67772287 :)
This issue is occurring because there are no indices on the columns used in these queries.
To address this problem, you can create the following indices:
By creating these indices, the query execution time can be reduced significantly from 5-7 seconds to approximately 0.2 milliseconds.