Cristian Gafton <[EMAIL PROTECTED]> 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 -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers