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
--
Sent via pgsql-performance mailing list (pgsql-performance@xxxxxxxxxxxxxx)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-performance
