Message-ID: <4D49B11F.8090108@vmsinfo.com>
Date: Wed, 02 Feb 2011 14:31:43 -0500
From: Mladen Gogala <mladen.gogala@vmsinfo.com>
Organization: VMS
User-Agent: Thunderbird 2.0.0.24 (X11/20101027)
MIME-Version: 1.0
To: "pgsql-performance@postgresql.org" <pgsql-performance@postgresql.org>
Subject: [Fwd: Re: [HACKERS] Slow count(*) again...]
Content-Type: text/plain; charset=utf-8; format=flowed
Content-Transfer-Encoding: 7bit

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