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

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