count_unconsolidated() seq-scans memory_units on every consolidation job (inner LIMIT has no ORDER BY)

Open Beginner friendly
  • Is something wrong?
#6,415 0 comments 0 reactions 0 assignees View on GitHub

Maintainers usually reply within 1 day

Nobody has claimed this yet.

Assessment

Difficulty
2/5
Estimated time
1-3 hours
Newbie friendliness
83/100
Issue type
Bug
Clarity
Clearly specified
Activity status
Active
Tech stack
postgresql, python
Domain
databases, performance

Research direction

Read hindsight-api-slim/hindsight_api/engine/memories/pg/reads.py around count_unconsolidated() and compare its query with find_unconsolidated(). The issue includes SQL to reproduce the planner behavior; verify the change with the provided EXPLAIN (ANALYZE, COSTS OFF) queries. Done when the bounded count uses the expected index scan while returning the same capped count.

Written by the indexing model from the issue text.

Description

core
Bug Description

count_unconsolidated() seq-scans the whole memory_units table on every consolidation job, even when nothing is pending. The consolidator calls it at the start of each job for progress logging (consolidator.py#L1929).

Cause: the bounded count in pg/reads.py#L444-L449 has a LIMIT but no ORDER BY:

SELECT COUNT(*) AS c FROM (
    SELECT 1 FROM memory_units
    WHERE bank_id = $1 AND consolidated_at IS NULL AND consolidation_failed_at IS NULL
      AND fact_type = ANY($2)
    LIMIT $3
) sub

The planner overestimates the pending rows (about 81k estimated, 0 actual), so "seq scan until LIMIT rows are found" looks cheap. With nothing pending, the scan reads the whole table. find_unconsolidated() has the same predicates plus ORDER BY created_at ASC (pg/reads.py#L402). That ordering matches idx_memory_units_unconsolidated (bank_id, created_at), so find_unconsolidated() gets an index scan.

Suggested fix: add ORDER BY created_at to the inner query of count_unconsolidated(). The count is unchanged, because any LIMIT rows give the same capped count.

Steps to Reproduce

On a bank with about 354k memory_units rows (PostgreSQL 18, embedded pg0, Hindsight 1.2.0), with nothing pending and no extra indexes beyond the shipped ones:

PREPARE cur(text, text[], int) AS
SELECT COUNT(*) AS c FROM (SELECT 1 FROM memory_units
  WHERE bank_id = $1 AND consolidated_at IS NULL AND consolidation_failed_at IS NULL
    AND fact_type = ANY($2) LIMIT $3) sub;

PREPARE fix(text, text[], int) AS
SELECT COUNT(*) AS c FROM (SELECT 1 FROM memory_units
  WHERE bank_id = $1 AND consolidated_at IS NULL AND consolidation_failed_at IS NULL
    AND fact_type = ANY($2) ORDER BY created_at LIMIT $3) sub;

SET plan_cache_mode = force_custom_plan;
EXPLAIN (ANALYZE, COSTS OFF) EXECUTE cur('<bank>', '{experience,world}', 1000);
EXPLAIN (ANALYZE, COSTS OFF) EXECUTE fix('<bank>', '{experience,world}', 1000);
Expected Behavior

An index scan on idx_memory_units_unconsolidated, as find_unconsolidated() gets.

Actual Behavior
Query Plan Execution
current (cur) Seq Scan on memory_units 227 ms
with ORDER BY created_at (fix) Index Scan using idx_memory_units_unconsolidated 0.1 ms

With force_generic_plan, both shapes fall back to a seq scan (the fix shape adds a sort). Postgres only picks a generic plan when it is not more expensive than the custom plans, so in practice fix stays on the index plan.

Version

1.2.0 (same code on main at eb963cb5a929d92c081371df64c2d5c2fe3351af)

Dominant language
Python
Stars
45.2k
Forks
5.9k
Avg merge
3h 44m
Merged PRs (30d)
491

Getting set up

This project ships no dev container, Dockerfile or contributing guide, so setting up is up to you: start from its README, and see our first-contribution guide for the general steps.

First steps

  1. Read the whole issue, then the project's contributing guide.
  2. Comment on the issue to say you are picking it up — it saves two people doing the same work.
  3. Fork the repository and make your change on a branch.
  4. Open a pull request that references the issue number.

More from vectorize-io/hindsight

All issues in vectorize-io/hindsight

Similar issues

More Python issues

Get new issues in your inbox

A short digest of beginner-friendly GitHub issues.