IntersectMBO / IntersectMBO/cardano-db-sync

`consumed_by_tx_id` on `ma_tx_out`

Open
#1,913 6 comments 0 reactions 1 assignee Claimed by @sgillespie View on GitHub
Dominant language
Haskell
Stars
318
Forks
168
PR merge metrics
No merged PRs in 30d

Description

As far as I'm aware, it's currently not possible to get all unspent multi asset transaction outputs for a specific multi asset without doing one of:

- Scanning all rows of `ma_tx_out` for a given multi asset joined with `tx_out` filtered on `consumed_by_tx_id`
- Scanning all `consumed_by_tx_id IS NULL` on `tx_out` joined with `ma_tx_out` filtered for a given multi asset
- Creating a materialized view

The former ends up being far more performant, but still leads to slow queries for multi assets with many transaction outputs. Here's an example for the `/assets/:asset/addresses` query from Blockfrost:

```sql
SELECT
txo.address AS "address",
SUM(quantity) AS "quantity"
FROM ma_tx_out mto
JOIN multi_asset ma ON (mto.ident = ma.id)
JOIN tx_out txo ON (txo.id = mto.tx_out_id)
JOIN tx ON (tx.id = txo.tx_id)
WHERE txo.consumed_by_tx_id IS NULL
AND (encode(ma.policy, 'hex') || encode(ma.name, 'hex')) = $1
-- don't count utxos that are part of transaction that failed script validation at stage 2
AND tx.valid_contract = 'true'
GROUP BY txo.address
ORDER BY MIN(tx.id) ASC
LIMIT 100;
```

Would it make sense to include a `consumed_by_tx_id` field on `ma_tx_out` alongside the existing field on `tx_out` for this case?

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.