Skip site navigation (1) Skip section navigation (2)

Re: Seqscan/Indexscan still a known issue?

From: Russell Smith <mr-russ(at)pws(dot)com(dot)au>
To: Carlos Moreno <moreno_pg(at)mochima(dot)com>
Cc: pgsql-performance(at)postgresql(dot)org
Subject: Re: Seqscan/Indexscan still a known issue?
Date: 2007-01-27 07:35:23
Message-ID: 45BB00BB.4070009@pws.com.au (view raw or flat)
Thread:
Lists: pgsql-performance
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


In response to

Responses

pgsql-performance by date

Next:From: Guido NeitzerDate: 2007-01-27 07:38:02
Subject: Re: Seqscan/Indexscan still a known issue?
Previous:From: Dennis BjorklundDate: 2007-01-27 07:26:13
Subject: Re: Seqscan/Indexscan still a known issue?

Privacy Policy | About PostgreSQL
Copyright © 1996-2014 The PostgreSQL Global Development Group