Re: [HACKERS] Slow count(*) again...

On Thu, 3 Feb 2011, Vitalii Tymchyshyn wrote:

> 02.02.11 20:32, Robert Haas ???????(??):
>>
>> Yeah.  Any kind of bulk load into an empty table can be a problem,
>> even if it's not temporary.  When you load a bunch of data and then
>> immediately plan a query against it, autoanalyze hasn't had a chance
>> to do its thing yet, so sometimes you get a lousy plan.
>
> May be introducing something like 'AutoAnalyze' threshold will help? I mean
> that any insert/update/delete statement that changes more then x% of table
> (and no less then y records) must do analyze right after it was finished.
> Defaults like x=50 y=10000 should be quite good as for me.

If I am understanding things correctly, a full Analyze is going over all
the data in the table to figure out patterns.

If this is the case, wouldn't it make sense in the situation where you are
loading an entire table from scratch to run the Analyze as you are
processing the data? If you don't want to slow down the main thread that's
inserting the data, you could copy the data to a second thread and do the
analysis while it's still in RAM rather than having to read it off of disk
afterwords.

this doesn't make sense for updates to existing databases, but the use
case of loading a bunch of data and then querying it right away isn't
_that_ uncommon.

David Lang