IntersectMBO / IntersectMBO/cardano-db-sync
Slow queries (lack of indicies)
- Dominant language
- Haskell
- Stars
- 318
- Forks
- 168
- PR merge metrics
- No merged PRs in 30d
Description
**OS**
Your OS: Ubuntu 22.04
**Versions**
The `db-sync` version: 13.1.0.2
PostgreSQL version: 14
**Build/Install Method**
The method you use to build or install `cardano-db-sync`: binaries
**Run method**
The method you used to run `cardano-db-sync` (eg Nix/Docker/systemd/none): systemd
**Problem 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:
```
SELECT "collateral_tx_in"."id"
FROM "collateral_tx_in"
WHERE "collateral_tx_in"."tx_in_id" >= 67772287
ORDER BY "collateral_tx_in"."id" ASC
LIMIT 1;
SELECT "collateral_tx_out"."id"
FROM "collateral_tx_out"
WHERE "collateral_tx_out"."tx_id" >= 67772287
ORDER BY "collateral_tx_out"."id" ASC
LIMIT 1;
SELECT "redeemer"."id"
FROM "redeemer"
WHERE "redeemer"."tx_id" >= 67772287
ORDER BY "redeemer"."id" ASC
LIMIT 1;
```
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:
```
-------Takes ~231MB
create index idx_collateral_tx_in_tx_in_id
on collateral_tx_in (tx_in_id);
-------Takes ~31MB
create index collateral_tx_out_tx_id
on collateral_tx_out (tx_id);
-------Takes ~282MB
create index redeemer_tx_id
on redeemer (tx_id);
```
By creating these indices, the query execution time can be reduced significantly from 5-7 seconds to approximately 0.2 milliseconds.
Contributor guide
Assessment
This issue has not been assessed yet.