Re: Postgresql 7.4.8 inconsistent index usage

On Fri, Jul 08, 2005 at 11:11:46AM -0400, Stephen Bowman wrote:
>
> SCANS=# explain select * from nessus_results where scan_id = 55;
> QUERY PLAN
> -------------------------------------------------------------------------
> Seq Scan on nessus_results (cost=0.00..127170.34 rows=42640 width=169)
> Filter: (scan_id = 55)
> (2 rows)
>
> SCANS=# explain select * from nessus_results where scan_id = 56;
> QUERY PLAN
> --------------------------------------------------------------------------------------------------------
> Index Scan using nessus_results_scan_id on nessus_results (cost=0.00..126632.83 rows=41813 width=169)
> Index Cond: (scan_id = 56)
> (2 rows)

It looks like you're right at the edge of where the planner thinks
a sequential scan would be faster than an index scan. The planner
estimates that scan_id = 55 will produce more rows than scan_id = 56
(42640 vs. 41813), which is probably just enough to make the estimated
cost for an index scan higher than for a sequential scan. Could
you post the EXPLAIN ANALYZE output for these queries so we can see
how realistic the estimates are? It might also be useful to see
them both with (enable_seqscan = on, enable_indexscan = off) and
then with (enable_seqscan = off, enable_indexscan = on).

Some people lower random_page_cost from the default of 4 to reduce
the estimated cost of an index scan. Beware of tweaking cost
estimate settings based on one particular query, though.

--
Michael Fuhr
http://www.fuhr.org/~mfuhr/