From: Mladen Gogala <mladen.gogala@vmsinfo.com>
To: pgsql-performance@postgresql.org <pgsql-performance@postgresql.org>
Subject: [Fwd: Re: [HACKERS] Slow count(*) again...]
Date: Wed, 02 Feb 2011 14:31:43 -0500
Message-ID: <4D49B11F.8090108@vmsinfo.com> (raw)
I mistakenly replied to sender only.
Jon Nelson wrote:
> However, sometimes using an index results in a HORRIBLE HORRIBLE plan.
> I recently encountered the issue myself, and plopping an ANALYZE
> $tablename in there, since I was using a temporary table anyway, make
> all the difference. The planner switched from an index-based query to
> a sequential scan, and a sequential scan was (is) vastly more
> efficient in this particular case.
>
That can be fixed by modifying the query. One can write the query in
such a way that optimizer cannot use an index.
> Personally, I'd get rid of autovacuum/autoanalyze support on temporary
> tables (they typically have short lives and are often accessed
> immediately after creation preventing the auto* stuff from being
> useful anyway), *AND* every time I ask I'm always told "make sure
> ANALYZE the table before you use it".
>
>
I consider that requirement very bad. I hate it when I have to do things
like this:
try {
$tmprows=array();
$db->StartTrans();
foreach ($result["matches"] as $doc => $docinfo) {
$tmp=$result["matches"][$doc]["attrs"]["created"];
$tmprows[]=array(date($FMT,$tmp),$doc);
}
$db->Execute($TMPINS,$tmprows);
$db->CommitTrans();
// Why the heck is this needed?
$db->Execute("analyze tempids");
$tmprows=array();
if ($result["total_found"]>$result["total"]) {
print "Total results:" . $result["total_found"] . "<br>";
print "Returned results:" . $result["total"] . "<br>";
}
$result=array();
$rs = $db->Execute($IGEN, array($beg, $end));
show($fmt,$rs);
}
catch(Exception $e) {
The "analyze tempids" line makes my code ugly and slows it down.
--
Mladen Gogala
Sr. Oracle DBA
1500 Broadway
New York, NY 10036
(212) 329-5251
http://www.vmsinfo.com
The Leader in Integrated Media Intelligence Solutions
--
Mladen Gogala
Sr. Oracle DBA
1500 Broadway
New York, NY 10036
(212) 329-5251
http://www.vmsinfo.com
The Leader in Integrated Media Intelligence Solutions
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: mladen.gogala@vmsinfo.com
Subject: Re: [Fwd: Re: [HACKERS] Slow count(*) again...]
In-Reply-To: <4D49B11F.8090108@vmsinfo.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