Horton

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

Vitalii Tymchyshyn <tivv00@gmail.com>

View thread
From: Vitalii Tymchyshyn <tivv00@gmail.com>
To: Robert Haas <robertmhaas@gmail.com>
Cc: Jon Nelson <jnelson+pgsql@jamponi.net>, Mladen Gogala <mladen.gogala@vmsinfo.com>, Andrew Dunstan <andrew@dunslane.net>, Bruce Momjian <bruce@momjian.us>, Tom Lane <tgl@sss.pgh.pa.us>, "david@lang.hm" <david@lang.hm>, Craig Ringer <craig@postnewspapers.com.au>, "pgsql-performance@postgresql.org" <pgsql-performance@postgresql.org>
Date: 2011-02-03T15:43:47Z
Lists: pgsql-hackers, pgsql-performance 
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