> Please provide some specifics. It's been a very long time since the
> planner was completely unaware of the size of such a table. Lack of
> stats is certainly a handicap, but I'm not convinced it should result
> in horrible plans. Maybe a more appropriate answer to this type of
> issue is to tweak some of the default selectivity numbers.
Sure. See attached output. This is from 8.2.9, but the behavior on
HEAD is similar. The first query executed before and then again after
the ANALYZE is OK, but the second, which involves an additional join
condition, is 6X slower prior to the ANALYZE.
I don't see how you're going to fix this problem by tweaking the
selectivity estimates. If it were possible to generate good query
plans without selectivity estimates derived from the actual table
contents, we wouldn't need ANALYZE in the first place.
>> And maybe also do the same thing if the table has grown significantly
>> (not sure what the threshold should be) since the last ANALYZE.
>
> Autovacuum already does this type of thing.
It's asynchronous, though. Frequently, you want to load a bunch of
data into a table and then immediately execute a query against it, or
possibly several queries. It's pretty annoying to have to write logic
that says - ok, if the number of rows that we just inserted was really
big relative to what was already in the table, then do an ANALYZE on
the table before issuing the SELECT, otherwise skip it.
I would be happy enough if we could recognize CREATE TABLE ... insert
a bunch of data ... SELECT as a case where we need to force a
synchronous ANALYZE - because in my experience you almost always do.
Recognizing the case where the table has grown a lot since the last
ANALYZE is probably harder, and a bit less important, but would surely
be nice if it could be done.
...Robert
