From: Vitalii Tymchyshyn <tivv00@gmail.com>
To: Robert Haas <robertmhaas@gmail.com>
Cc: Jon Nelson <jnelson+pgsql@jamponi.net>
Cc: Mladen Gogala <mladen.gogala@vmsinfo.com>
Cc: Andrew Dunstan <andrew@dunslane.net>
Cc: Bruce Momjian <bruce@momjian.us>
Cc: Tom Lane <tgl@sss.pgh.pa.us>
Cc: david@lang.hm <david@lang.hm>
Cc: Craig Ringer <craig@postnewspapers.com.au>
Cc: pgsql-performance@postgresql.org <pgsql-performance@postgresql.org>
Subject: Re: [HACKERS] Slow count(*) again...
Date: Thu, 03 Feb 2011 17:43:47 +0200
Message-ID: <4D4ACD33.903@gmail.com> (raw)
In-Reply-To: <AANLkTim0AbX71fPTOQa++q9PAGn7SL2ziYpRnb_JMyNR@mail.gmail.com>
References: <201102012247.p11Ml6u02682@momjian.us>
<4D48914B.4050403@dunslane.net>
<4D489AD1.1010105@vmsinfo.com>
<AANLkTiknZi97dDwm7sHqy0EL7tpNEP9sY5uPV6AN+q=u@mail.gmail.com>
<4D499E55.4020107@vmsinfo.com>
<AANLkTimo2J-BagObs_V1bGaHOO9qL+kTF6s6-wZ=M3KW@mail.gmail.com>
<AANLkTin7A_Q=Yc9Wf_5=POp2mVqmaiwqUDsFMLG68H45@mail.gmail.com>
<4D4A7B69.2020704@gmail.com>
<AANLkTim0AbX71fPTOQa++q9PAGn7SL2ziYpRnb_JMyNR@mail.gmail.com>
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
Reply instructions:
You may reply publicly to this message via plain-text email
using any one of the following methods:
* Reply to all the recipients using the --to and --cc options:
reply via email
To: pgsql-performance@postgresql.org
Cc: tivv00@gmail.com, robertmhaas@gmail.com, jnelson+pgsql@jamponi.net, mladen.gogala@vmsinfo.com, andrew@dunslane.net, bruce@momjian.us, tgl@sss.pgh.pa.us, david@lang.hm, craig@postnewspapers.com.au
Subject: Re: [HACKERS] Slow count(*) again...
In-Reply-To: <4D4ACD33.903@gmail.com>
* Save the following mbox file, import it into your mail client,
and reply-to-all from there: mbox
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox