Re: Single table forcing sequential scans on query plans

On Sun, 16 Mar 2008, Tom Lane wrote:

> > 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?

Ah, that is indeed a possibility. If I am to understand correctly, there is
no way to represent the difference between an un-analyzed table and a
zero-sized analyzed table as far as the query planner is concerned? Looks
like I'll have to do a "select count(*)" before running query to avoid
entering this trap. (That feels a bit suboptimal since the conary repository
code does extensive work with/through temporary tables, and this could very
well end up not being the only section affected...)

> 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)

In this particular case it would be nice if there would be a differentiation
between "estimate size 0" and "estimate size unknown".

> 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.

For now I will try to run with the assumption that the massive sequential
scans are caused by joing an empty table in the query and try to work my way
around it - unless there is some trick to tell the planner that this is a
query that would be much better optimized away instead of causing a massive
IO storm.

Thanks,

Cristian
--
Cristian Gafton
rPath, Inc.