Message-ID: <45BB00BB.4070009@pws.com.au>
Date: Sat, 27 Jan 2007 18:35:23 +1100
From: Russell Smith <mr-russ@pws.com.au>
User-Agent: Thunderbird 1.5.0.9 (Windows/20061207)
MIME-Version: 1.0
To: Carlos Moreno <moreno_pg@mochima.com>
CC: pgsql-performance@postgresql.org
Subject: Re: Seqscan/Indexscan still a known issue?
References: <45BAB682.6060709@mochima.com>
In-Reply-To: <45BAB682.6060709@mochima.com>
Content-Type: text/plain; charset=ISO-8859-1; format=flowed
Content-Transfer-Encoding: 7bit

Carlos Moreno wrote:
>
> Hi,
>
> I find various references in the list to this issue of queries
> being too slow because the planner miscalculates things and
> decides to go for a sequenctial scan when an index is available
> and would lead to better performance.
>
> Is this still an issue with the latest version?   I'm doing some
> tests right now, but I have version 7.4  (and not sure when I will
> be able to spend the effort to move our system to 8.2).
>
> When I force it via  "set enable_seqscan to off", the index scan
> takes about 0.1 msec  (as reported by explain analyze), whereas
> with the default, it chooses a seq. scan, for a total execution
> time around 10 msec!!  (yes: 100 times slower!).  The table has
> 20 thousand records, and the WHERE part of the query uses one
> field that is part of the primary key  (as in, the primary key
> is the combination of field1,field2, and the query involves a
> where field1=1 and some_other_field=2).  I don't think I'm doing
> something "wrong", and I find no reason not to expect the query
> planner to choose an index scan.
>
> For the time being, I'm using an explicit "enable_seqscan off"
> in the client code, before executing the select.  But I wonder:
> Is this still an issue, or has it been solved in the latest
> version?
Please supply explain analyze for the query in both the index and 
sequence scan operation.  We may be able to tell you why it's choosing 
the wrong options.  Guess 1 would be that your primary key is int8, but 
can't be certain that is what's causing the problem.

Regards

Russell Smith
>
> Thanks,
>
> Carlos