Re: Single table forcing sequential scans on query plans

Cristian Gafton <gafton(at)rpath(dot)com> writes:
> I have a weird query execution plan problem I am trying to debug on
> Postgresql 8.2.6. I have a query that joins against a temporary table that
> has very few rows.

Is it possible that the temp table ever has exactly zero rows?

> My questions are:
> - what would make the analyze operation "fail" in the eyes of the planner?
> - why joining to a single unanalyzed table disables any and all indexes from
> the other tables references in the query?

That's entirely the wrong way to think about it. The planner is
choosing a good plan based on its estimates of table sizes, which
are wildly different in the two cases:

> -> Seq Scan on tmpinstanceid (cost=0.00..1.02 rows=2 width=8) (actual time=0.005..0.007 rows=2 loops=1)

> -> Seq Scan on tmpinstanceid (cost=0.00..29.40 rows=1940 width=8)

If there actually were nearly 2000 rows in the temp table, that
nested-loops plan would take about a thousand times longer than
it does, and you'd not be nearly so pleased with it. The
merge-and-hash-joins plan looks quite sane to me for that table size.

The larger estimate is coming from some heuristics that are applied
when the table size recorded in pg_class.relpages & reltuples is
exactly zero. It's intentionally not small, to keep us from choosing
a plan with brittle performance behavior when we are looking at a
table that's never been vacuumed or analyzed.

The only idea I have for how the planner could "ignore" a previous
analyze result is if the analyze found the table to be of zero size.
Then the heuristic would still be applied because relpages == 0.

regards, tom lane