"Surabhi Ahuja " <surabhi.ahuja@iiitb.ac.in> writes:
> why is it not searching the indexes(index scan)? and how can i make such a
> query much faster?
130 values out of 3,000 is 4.3% of the table. If you have narrow records with,
say, 20 per page that would mean reading most of the pages out of the table.
Add in that it has to read in the index pages and it could be doing more i/o
than a simple sequential scan. Now consider that random access is about four
times slower than reading sequentially and you could be far behind.
set enable_indexscan = off;
explain analyze select ...
set enable_indexscan = on;
Note that it can be quite hard to get reliable timing information for such a
small table since the second time you run the query all the data will be in
RAM already.
If you find indexes are faster you might try lowering random_page_cost,
possibly as low as 1.2-2.0 but beware of using a single query to tune such
parameters. And beware tuning them on non-production conditions. When the data
grows and less of it fits in RAM you may have to raise it again.
--
greg
