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 |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
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
From | Date | Subject | |
---|---|---|---|
Next Message | Guido Neitzer | 2007-01-27 07:38:02 | Re: Seqscan/Indexscan still a known issue? |
Previous Message | Dennis Bjorklund | 2007-01-27 07:26:13 | Re: Seqscan/Indexscan still a known issue? |