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

On Thu, Feb 03, 2011 at 02:11:58AM -0800, david@lang.hm wrote:
> 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
>

+1 for in-flight ANALYZE. This would be great for bulk loads of
real tables as well as temp tables.

Cheers,
Ken