[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