Cristian Gafton <gafton@rpath.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.007rows=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