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

03.02.11 17:31, Robert Haas написав(ла):
>
>> 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.
> That would actually be a pessimization for many real world cases.  Consider:
>
> COPY
> COPY
> COPY
> COPY
> COPY
> COPY
> COPY
> COPY
> COPY
> COPY
> COPY
> COPY
> COPY
> SELECT
If all the copies are ~ same in size and large this will make it:

COPY
ANALYZE
COPY
ANALYZE
COPY
COPY
ANALYZE
COPY
COPY
COPY
COPY
ANALYZE
COPY
COPY
COPY
COPY
COPY
SELECT

instead of

COPY
COPY
COPY
COPY
COPY
COPY
COPY
COPY
COPY
COPY
COPY
COPY
COPY
ANALYZE (manual, if one is clever enough)
SELECT

So, yes this will add 3 more analyze, but
1) Analyze is pretty cheap comparing to large data loading. I'd say this
would add few percent of burden. And NOT doing analyze manually before
select can raise select costs orders of magnitude.
2) How often in real world a single table is loaded in many COPY
statements? (I don't say it's not often, I really don't know). At least
for restore it is not the case, is not it?
3) default thresholds are things to discuss. You can make x=90 or x=200
(latter will make it run only for massive load/insert operations). You
can even make it disabled by default for people to test. Or enable by
default for temp tables only (and have two sets of thresholds)
4) As most other settings, this threshold can be changed on up to
per-query basis.

P.S. I would also like to have index analyze as part of any create index
process.

Best regards, Vitalii Tymchyshyn