fix: replace OR-based query with UNION ALL in GraphQL address.transactions to fix full table scan by Kewe63 · Pull Request #14537 · blockscout/blockscout

Kewe63 · 2026-07-04T12:35:33Z

Summary

The GraphQL address.transactions query was triggering full table scans on the transactions table because OR conditions across three address columns prevented PostgreSQL from using per-column indexes. This caused timeouts exceeding 120 seconds even for addresses with only 25 transactions.

Motivation

The GraphQL address.transactions query was triggering full table scans on the transactions table because OR conditions across three address columns prevented PostgreSQL from using per-column indexes. This caused timeouts exceeding 120 seconds even for addresses with only 25 transactions.

Changes

File	Change
`apps/explorer/lib/explorer/graphql.ex`	OR → UNION ALL + deferred join
`apps/explorer/test/explorer/graphql_test.exs`	3 new regression tests

apps/explorer/lib/explorer/graphql.ex

Fixed address_to_transactions_query/2 to use a UNION ALL + deferred join pattern instead of OR conditions, allowing PostgreSQL to use per-column indexes on from_address_hash, to_address_hash, and created_contract_address_hash independently.
Query time drops from >120s to <250ms across all tested address types.

apps/explorer/test/explorer/graphql_test.exs — 3 new regression tests:

orders by ascending block and index — verifies :asc sort order
does not return duplicate transactions when address appears in multiple roles — address appearing as both from and to must not be returned twice
returns transactions matching any of the three address roles — verifies all three roles (from_address_hash, to_address_hash, created_contract_address_hash) are correctly covered

Changelog

Bug Fixes

Fixed address_to_transactions_query/2 in Explorer.GraphQL to use a UNION ALL + deferred join pattern instead of OR conditions, allowing PostgreSQL to use per-column indexes on from_address_hash, to_address_hash, and created_contract_address_hash independently. Query time drops from >120s to <250ms across all tested address types.

Enhancements: None.

Incompatible Changes: None.

Upgrading

No database reset or re-index required.

Checklist

Risk & Impact

Low. Drop-in query rewrite — external API surface, response shape, and pagination behavior are unchanged. The UNION ALL + deferred join is a well-known PostgreSQL optimization for OR-on-indexed-columns and requires no schema migration.

Type: 🐛 Bug fix
Closes: #14157

Summary by CodeRabbit

Bug Fixes
- Improved address page transaction lookups to consistently match transactions where the address appears as sender, recipient, or contract creator.
- Removed duplicate transactions when an address matches multiple roles within the same transaction.
- Ensured results are deterministically ordered by ascending block number, then ascending transaction index.
Chores
- Updated backend storage configuration to use a persistent Docker named volume for /app/dets/ storage.

…_query GraphQL address.transactions was triggering full table scans on the transactions table because OR conditions across three address columns (from_address_hash, to_address_hash, created_contract_address_hash) prevented PostgreSQL from using per-column indexes. Replace with a UNION ALL + deferred join pattern so each branch hits its own index independently. Smart contract addresses query only to_address_hash and created_contract_address_hash; EOAs query all three. Fixes blockscout#14157

…L fix Covers: - ascending order - no duplicate transactions when address appears in multiple roles - all three address roles (from, to, created_contract) returned correctly Fixes blockscout#14157

coderabbitai · 2026-07-04T12:35:52Z

Walkthrough

Explorer.GraphQL.address_to_transactions_query/2 now builds role-specific UNION ALL branches with deduplication and ordering, with tests covering ordering, duplicate suppression, and role matching. The backend compose service also switches /app/dets/ to a named Docker volume.

Changes

Address Transactions Query Rewrite

Layer / File(s)	Summary
Query construction `apps/explorer/lib/explorer/graphql.ex`	Adds `subquery` and `union_all` imports and rewrites `address_to_transactions_query/2` to branch on smart contract versus EOA address roles, then joins a deduplicating subquery and orders by block number and index.
Query behavior tests `apps/explorer/test/explorer/graphql_test.exs`	Adds tests for ascending block/index ordering, duplicate suppression when an address appears in multiple roles in one transaction, and matching across `from_address`, `to_address`, and `created_contract_address`.

Backend DETS Volume Configuration

Layer / File(s)	Summary
DETS volume mount `docker-compose/services/backend.yml`	Replaces the backend `/app/dets/` bind mount with a named Docker volume and declares the volume at the top level.

Estimated code review effort: 3 (Moderate) | ~20 minutes

Suggested reviewers: vbaranov

Poem

A rabbit dug through tables deep,
Where OR clauses made Postgres weep,
Now UNION branches, swift and neat,
Join and dedupe, no more defeat,
Hop, hop — the query's fast asleep... resolved! 🐇

🚥 Pre-merge checks | ✅ 4 | ❌ 1

❌ Failed checks (1 warning)

Check name	Status	Explanation	Resolution
Out of Scope Changes check	⚠️ Warning	The docker-compose backend volume change is unrelated to the GraphQL timeout fix and appears out of scope for issue `#14157`.	Remove or split the docker-compose volume change into a separate PR unless it is required for the GraphQL fix.

✅ Passed checks (4 passed)

Check name	Status	Explanation
Linked Issues check	✅ Passed	The query rewrite and regression tests match issue `#14157`'s UNION ALL, deferred join, deduplication, and result-preservation requirements.
Docstring Coverage	✅ Passed	No functions found in the changed files to evaluate docstring coverage. Skipping docstring coverage check.
Description Check	✅ Passed	Check skipped - CodeRabbit’s high-level summary is enabled.
Title check	✅ Passed	The title accurately summarizes the main change: replacing the OR-based GraphQL address.transactions query with a UNION ALL approach to avoid full table scans.

Thanks for using CodeRabbit! It's free for OSS, and your support helps us grow. If you like it, consider giving us a shout-out.

❤️ Share

_{Comment @coderabbitai help to get the list of available commands.}

coderabbitai

Actionable comments posted: 1

🧹 Nitpick comments (2)

apps/explorer/lib/explorer/graphql.ex (2)
29-37: 📐 Maintainability & Code Quality | 🔵 Trivial | 💤 Low value

@doc no longer matches actual matching behavior for smart-contract addresses.

The doc still states matching on to_address_hash, from_address_hash, or created_contract_address_hash unconditionally, but for smart-contract addresses from_address_hash is now excluded. Worth updating for accuracy.
🤖 Prompt for AI Agents
Verify each finding against current code. Fix only still-valid issues, skip the
rest with a brief reason, keep changes minimal, and validate.

In `@apps/explorer/lib/explorer/graphql.ex` around lines 29 - 37, Update the `@doc`
on the transaction query helper in explorer/graphql.ex so it matches the current
smart-contract behavior: the description should no longer say
`from_address_hash` is always included for all addresses. Adjust the wording in
the doc for the function that builds the UNION ALL query to reflect that
smart-contract addresses exclude the `from_address_hash` match while still
covering `to_address_hash` and `created_contract_address_hash` as applicable.
40-47: 🚀 Performance & Scalability | 🔵 Trivial | ⚡ Quick win

Extra hash_to_address call defeats part of the perf goal.

Chain.hash_to_address/1 (default options) preloads full associations just to answer two boolean checks. This is called on every invocation of address_to_transactions_query/2, adding an extra query + joins to a code path that this PR is specifically trying to make faster. Consider a lighter-weight existence/contract-code check (e.g., a targeted select for contract_code only) instead of the fully-preloaded address.
🤖 Prompt for AI Agents
Verify each finding against current code. Fix only still-valid issues, skip the
rest with a brief reason, keep changes minimal, and validate.

In `@apps/explorer/lib/explorer/graphql.ex` around lines 40 - 47, The
`address_to_transactions_query/2` path in `Explorer.GraphQL` still pays for a
full `Chain.hash_to_address/1` preload before the `smart_contract?` and
`eoa_with_code?` checks, which adds unnecessary query overhead. Replace that
lookup with a lighter-weight existence/contract-code check that only fetches the
minimal fields needed (for example, a targeted query for `contract_code` or a
boolean existence check), and keep the `is_smart_contract` decision logic in the
same `Explorer.GraphQL` flow so the performance gain is preserved.

🤖 Prompt for all review comments with AI agents

Verify each finding against current code. Fix only still-valid issues, skip the
rest with a brief reason, keep changes minimal, and validate.

Inline comments:
In `@apps/explorer/test/explorer/graphql_test.exs`:
- Around line 92-157: Add test coverage for the smart-contract UNION ALL path in
GraphQL.address_to_transactions_query by using an address fixture with contract
code so is_smart_contract is true. Extend the graphql_test.exs cases or add a
new one that exercises the smart-contract branch in GraphQL
address_to_transactions_query and verifies it still returns transactions matched
via to_address_hash and created_contract_address_hash while excluding the
from_address_hash-only path.

---

Nitpick comments:
In `@apps/explorer/lib/explorer/graphql.ex`:
- Around line 29-37: Update the `@doc` on the transaction query helper in
explorer/graphql.ex so it matches the current smart-contract behavior: the
description should no longer say `from_address_hash` is always included for all
addresses. Adjust the wording in the doc for the function that builds the UNION
ALL query to reflect that smart-contract addresses exclude the
`from_address_hash` match while still covering `to_address_hash` and
`created_contract_address_hash` as applicable.
- Around line 40-47: The `address_to_transactions_query/2` path in
`Explorer.GraphQL` still pays for a full `Chain.hash_to_address/1` preload
before the `smart_contract?` and `eoa_with_code?` checks, which adds unnecessary
query overhead. Replace that lookup with a lighter-weight
existence/contract-code check that only fetches the minimal fields needed (for
example, a targeted query for `contract_code` or a boolean existence check), and
keep the `is_smart_contract` decision logic in the same `Explorer.GraphQL` flow
so the performance gain is preserved.

🪄 Autofix (Beta)

Fix all unresolved CodeRabbit comments on this PR:

Push a commit to this branch (recommended)
Create a new PR with the fixes

ℹ️ Review info

⚙️ Run configuration

Configuration used: Repository UI

Review profile: CHILL

Plan: Pro

Run ID: 477b81e9-41ab-4ee7-8fb3-dd7e956781fb

📥 Commits

Reviewing files that changed from the base of the PR and between 731015d and 97bb1c5.

📒 Files selected for processing (2)

apps/explorer/lib/explorer/graphql.ex
apps/explorer/test/explorer/graphql_test.exs

When ./dets/ is bind-mounted from a host directory owned by root:root, the container's non-root user (blockscout, UID 10001) cannot write to it, causing an eacces error on DETS queue_storage open and crashing the indexer. Replace the bind-mount with a Docker named volume (backend-dets) so Docker manages ownership automatically and the app user always has write access. The logs directory keeps its bind-mount since users need direct host access to log files. Fixes blockscout#13583

coderabbitai

🧹 Nitpick comments (1)

docker-compose/services/backend.yml (1)
17-20: 📐 Maintainability & Code Quality | 🔵 Trivial | 💤 Low value

Note migration impact for existing deployments.

Switching from the bind mount to a named volume improves portability/permissions, but existing deployments with data in the host ./dets directory won't automatically carry that state into the new backend-dets volume — it starts empty. Since this backs the NFT media handler queue state, worst case is a requeue rather than data loss, but worth calling out in upgrade notes.
🤖 Prompt for AI Agents
Verify each finding against current code. Fix only still-valid issues, skip the
rest with a brief reason, keep changes minimal, and validate.

In `@docker-compose/services/backend.yml` around lines 17 - 20, Call out the
storage change in the backend service so existing deployments know the new
backend-dets named volume will not inherit data from the old ./dets bind mount.
Update the upgrade/migration notes for the docker-compose backend volume change
to mention that the queue state starts empty on first run and may require a
requeue, referencing the backend-dets volume entry and the NFT media handler
queue state.

🤖 Prompt for all review comments with AI agents

Verify each finding against current code. Fix only still-valid issues, skip the
rest with a brief reason, keep changes minimal, and validate.

Nitpick comments:
In `@docker-compose/services/backend.yml`:
- Around line 17-20: Call out the storage change in the backend service so
existing deployments know the new backend-dets named volume will not inherit
data from the old ./dets bind mount. Update the upgrade/migration notes for the
docker-compose backend volume change to mention that the queue state starts
empty on first run and may require a requeue, referencing the backend-dets
volume entry and the NFT media handler queue state.

ℹ️ Review info

⚙️ Run configuration

Configuration used: Repository UI

Review profile: CHILL

Plan: Pro

Run ID: 68265b32-5509-4bba-9021-8c61617aec3b

📥 Commits

Reviewing files that changed from the base of the PR and between 97bb1c5 and dfcf78c.

📒 Files selected for processing (1)

docker-compose/services/backend.yml

Kewe63 added 2 commits July 4, 2026 15:18

test: add regression tests for address_to_transactions_query UNION AL…

97bb1c5

…L fix Covers: - ascending order - no duplicate transactions when address appears in multiple roles - all three address roles (from, to, created_contract) returned correctly Fixes blockscout#14157

coderabbitai Bot reviewed Jul 4, 2026

View reviewed changes

Comment thread apps/explorer/test/explorer/graphql_test.exs

coderabbitai Bot reviewed Jul 4, 2026

View reviewed changes

revert: remove dets fix from graphql branch

c9e9102

vbaranov changed the base branch from master to dev July 7, 2026 14:38

Navigation Menu

Uh oh!

fix: replace OR-based query with UNION ALL in GraphQL address.transactions to fix full table scan - #14537

fix: replace OR-based query with UNION ALL in GraphQL address.transactions to fix full table scan#14537
Kewe63 wants to merge 4 commits into
blockscout:devfrom
Kewe63:fix/graphql-address-transactions-union-all

Kewe63 commented Jul 4, 2026 •

edited by coderabbitai Bot

Loading

Uh oh!

coderabbitai Bot commented Jul 4, 2026 •

edited

Loading

❌ Failed checks (1 warning)

Uh oh!

coderabbitai Bot left a comment

Uh oh!

Uh oh!

coderabbitai Bot left a comment

Uh oh!

Reviewers

Assignees

Labels

Projects

Milestone

Development

Uh oh!

1 participant

Uh oh!

Conversation

Kewe63 commented Jul 4, 2026 • edited by coderabbitai Bot Loading Uh oh! There was an error while loading. Please reload this page.

Uh oh!

Summary

Motivation

Changes

Changelog

Upgrading

Checklist

Risk & Impact

Summary by CodeRabbit

Uh oh!

coderabbitai Bot commented Jul 4, 2026 • edited Loading Uh oh! There was an error while loading. Please reload this page.

Uh oh!

Walkthrough

Changes

❌ Failed checks (1 warning)

Uh oh!

coderabbitai Bot left a comment

Choose a reason for hiding this comment

Uh oh!

Uh oh!

coderabbitai Bot left a comment

Choose a reason for hiding this comment

Uh oh!

Reviewers

Assignees

Labels

Projects

Milestone

Development

Uh oh!

1 participant

Kewe63 commented Jul 4, 2026 •

edited by coderabbitai Bot

Loading

coderabbitai Bot commented Jul 4, 2026 •

edited

Loading