Re: BUG #19332: Sudden 330x performance degradation of SELECT amid INSERTs

> so I think if the index did contain the 74962 pages when the query was
planned, then the costs should have known about it.
Yes, here we are facing a corner case when data is generated in a long
transaction on just cleaned-up DB => when autovacuum kicks in,
autoanalyze wipes old stats and have no data in tables to properly assess
relations cardinality.

> And for me, I've not really seen enough evidence that there's any bug
here.
So the question is whether the query planner should take into account the
WHERE clause that hints that it is enough to join just a single row instead
of the whole table.

ср, 10 дек. 2025 г. в 06:46, David Rowley <dgrowleyml(at)gmail(dot)com>:

> On Thu, 4 Dec 2025 at 23:20, Sergey Naumov <sknaumov(at)gmail(dot)com> wrote:
> > I've collected slow and fast query plans and it looks like when data is
> cleaned up, PostgreSQL doesn't know what table is big and what is small,
> and when data generation is in one big transaction, data from this
> uncommitted transaction already affects SELECT queries, but VACUUM doesn't
> see this uncommitted data to adjust stats => query planner could come up
> with suboptimal query plan.
> >
> > But still, the query itself has a hint as to what table has to be
> filtered first - there is a WHERE clause to keep just one line from this
> table. But query planner decides to join another (very big) table first =>
> performance degrades by orders of magnitude.
> >
> > For me It looks like a flaw in query planner logic, that, having no data
> about tables content, ignores the WHERE clause that hints what table has to
> be processed first => not sure whether it should be treated as performance
> issue or bug.
>
> I've studied this for a bit and pretty much feel like I've got one
> hand tied behind my back due to the format you've submitted the
> EXPLAIN in. I don't really understand the visual format as details I'm
> used to seeing are not there, and the JSON "raw" format isn't much
> better for humans to read, as it doesn't fit on a page.
>
> From what I see, it looks like the slow version opts to build the hash
> table for the hash join from an Index Scan on an index on test_run,
> but that index is bloated.
>
> For the fast plan, the bloated index isn't scanned because that same
> index is on the inner side of a nested loop and the outer side didn't
> find any ways, resulting in the inner side never being executed. From
> looking at get_relation_info(), I see we do fetch the actual size of
> the index with "info->pages =
> RelationGetNumberOfBlocks(indexRelation);", and from looking at
> genericcostestimate(), we should estimate the number of pages with
> "numIndexPages = ceil(numIndexTuples * index->pages /
> index->tuples);", so I think if the index did contain the 74962 pages
> when the query was planned, then the costs should have known about it.
>
> Are you using prepared statements here? (I suspect not, since the UUID
> is in the EXPLAIN output, but you could have hardcoded that)
>
> > Query plans are attached as PEV2 standalone HTML pages.
>
> I imagine some people might like this format, but I can bearly read
> it. You'll probably have more luck around here with EXPLAIN (ANALYZE,
> BUFFERS, FORMAT TEXT)
>
> And for me, I've not really seen enough evidence that there's any bug
> here. If you think there is, then you might need to work a bit harder
> and provide a script that we can reproduce this with ourselves.
>
> David
>