On Thu, Oct 21, 2010 at 3:20 AM, Mathieu De Zutter <mathieu@dezutter.org> wrote:
On Thu, Oct 21, 2010 at 3:47 AM, DM <dm.aeqa@gmail.com> wrote: > I was hoping the optimizer would do a join using index scan. > > Could some one please explain me why its not doing an index scan rather than > sequential scan .
A index scan would be probably slower here because you're asking for a lot of rows. A lot of rows means a lot of I/O, and an index scan is more I/O intensive (since it has to read the index too). If you limit the result (by being more selective in your where clause, just like you do in the first two queries), postgres will most likely switch to index scan.
You can see for yourself if index-scan would be faster in your case by running the following command before "explain (analyze)":
set enable_seqscan = off;
BTW, try to use explain analyze instead of explain, that way you'll see the actual timings too instead of just the planner estimates.
