IntersectMBO / IntersectMBO/cardano-db-sync

Slow queries (lack of indicies)

Open
#1,410 8 comments 0 reactions 0 assignees View on GitHub
bug
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

Open the contributing guide

Assessment

This issue has not been assessed yet.

Get new issues in your inbox

A short digest of beginner-friendly GitHub issues.