From: Kenneth Marshall <ktm@rice.edu>
To: david@lang.hm
Cc: Vitalii Tymchyshyn <tivv00@gmail.com>
Cc: 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: Craig Ringer <craig@postnewspapers.com.au>
Cc: pgsql-performance@postgresql.org <pgsql-performance@postgresql.org>
Subject: Re: [HACKERS] Slow count(*) again...
Date: Thu, 3 Feb 2011 07:41:42 -0600
Message-ID: <20110203134142.GO24931@aart.is.rice.edu> (raw)
In-Reply-To: <alpine.DEB.2.00.1102030208440.8162@asgard.lang.hm>
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>
<alpine.DEB.2.00.1102030208440.8162@asgard.lang.hm>
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
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: ktm@rice.edu, david@lang.hm, 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, craig@postnewspapers.com.au
Subject: Re: [HACKERS] Slow count(*) again...
In-Reply-To: <20110203134142.GO24931@aart.is.rice.edu>
* 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