Skip to content

get_store_table_health's index read joins millions of estimated catalog rows on a store with many chunks #9224

Closed

Description

@erikdarlingdata

Claude posting for Erik Darling.

What

Build 3.13.0-nightly.20261121.714. get_store_table_health takes several seconds on a store with many chunks, and the Viewer's Store Health tab waits on the same read. Its slowest statement is the index read, DarlingStoreTableHealthReader.BuildIndexSql over PgIndexBloatCollector.QueryText (WITH est AS MATERIALIZED ...). Several SELECT s.rel_oid ... statements and one WITH targets statement in the same call also take seconds each.

The index read touches only catalogs (pg_class, pg_index, pg_attribute, pg_statistic), but it does about a million buffer hits a call. Its plan estimates millions of rows where the store has tens of thousands of indexes: the members CTE, the ix set and the final sort are each estimated in the millions. The estimates come from the joins over timescaledb_information.chunks and the partition tree. pg_attribute is hashed whole. The per-relation size calls do not show in a plan, and they run for every chunk and index the read keeps.

Part of #8570.

Proposed

  • Find where the work goes, on a store with thousands of chunks: the join that multiplies rows, the whole-table pg_attribute hash, and how many size calls run.
  • Rework the reads so each one joins the real number of rows. For example, take chunk membership from a narrower source, or materialize the small sets first, and filter pg_attribute to the indexes being read. Call the size functions once per relation the answer keeps.
  • Do the same for the rel_oid and targets statements if they share the cause.
  • The answer stays the same rows with the same values.

Tests

  • On a seeded store with many chunks, the reworked statements return the same rows as before.
  • The tool's call stays under its budget on that store. Assert a count the plan or the statement statistics give (rows joined, or size calls made), not a timing.

Activity

  1. added
    in-progressActively being worked by a local session or its agents (PR open or in flight)
    on Nov 21, 2026
  2. erikdarlingdata commented on Nov 21, 2026

    @erikdarlingdata
    OwnerAuthor

    Claude posting for Erik Darling.

    #9231 is merged to dev. This issue stays open until the table health reads are timed again on a large store, after the nightly build that carries the change. The test store shows the plan fix, but only a large store shows the real time of the call.

  3. added
    waitingOwned, nothing in flight: waits on a named event. The last comment names it.
    on Nov 21, 2026
  4. erikdarlingdata commented on Nov 21, 2026

    @erikdarlingdata
    OwnerAuthor

    Claude posting for Erik Darling.

    Fixed on dev by #9231.

Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment

Metadata

Metadata

Assignees

No one assigned

    Labels

    in-progressActively being worked by a local session or its agents (PR open or in flight)waitingOwned, nothing in flight: waits on a named event. The last comment names it.

    Projects

    No projects

      Milestone

      No milestone

      Relationships

      None yet

      Development

      No branches or pull requests

      Issue actions