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