Re: Single table forcing sequential scans on query plans

Cristian Gafton <gafton(at)rpath(dot)com> writes:
> On Sun, 16 Mar 2008, Tom Lane wrote:
>> 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?

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.

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
assumption that autovacuum will kick in and update the stats, leading
to invalidation of any existing plans that assume the table is small.
I don't feel very comfortable about that though --- throwing a few
hundred tuples into a table might not be enough to draw autovacuum's
attention, but it could surely be enough to create a performance
disaster for nestloop plans.

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

regards, tom lane