Re: Single table forcing sequential scans on query plans

On Sun, 16 Mar 2008, Tom Lane wrote:

> > 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?
>
> While thinking about your report I was considering having VACUUM and
> ANALYZE always set relpages to at least 1. Then seeing relpages=0
> would indeed indicate a never-analyzed table, whereas relpages=1
> when physical table size is zero could be taken to indicate that
> we should trust the table to be really empty. I'm not sure though
> whether this sort of convention would confuse any existing code.

If having a discrepancy between relpages and table size is a concern,
could relpages be a negative value to mark a non-analyzed table?

> Another possibility (though not a back-patchable solution) is that
> we could just dispense with the heuristic size estimate and trust a
> zero-sized table to stay zero-sized. This would be relying on the

I think improving the estimator would get us further, since in most cases it
seems to get it relatively right.

> Could you confirm that your problem cases are actually caused by this
> effect and not something else?

Yes, confirmed. The runaway queries all are joining against an empty
temporary table.

Thanks,

Cristian
--
Cristian Gafton
rPath, Inc.