From: Russell Smith <mr-russ@pws.com.au>
To: Carlos Moreno <moreno_pg@mochima.com>
Cc: pgsql-performance@postgresql.org
Subject: Re: Seqscan/Indexscan still a known issue?
Date: Sat, 27 Jan 2007 18:35:23 +1100
Message-ID: <45BB00BB.4070009@pws.com.au> (raw)
In-Reply-To: <45BAB682.6060709@mochima.com>
References: <45BAB682.6060709@mochima.com>
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
Reply instructions:
You may reply publicly to this message via plain-text email
using any one of the following methods:
* Reply to all the recipients using the --to and --cc options:
reply via email
To: pgsql-performance@postgresql.org
Cc: mr-russ@pws.com.au, moreno_pg@mochima.com
Subject: Re: Seqscan/Indexscan still a known issue?
In-Reply-To: <45BB00BB.4070009@pws.com.au>
* Save the following mbox file, import it into your mail client,
and reply-to-all from there: mbox
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox