Re: Postgresql 7.4.8 inconsistent index usage

On 7/8/05, Michael Fuhr <mike@fuhr.org> wrote:
> 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/
>

Sure:

=== Defaults: ===
SCANS=# explain analyze 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) (actual
time=0.090..137.883 rows=41199 loops=1)
  Index Cond: (scan_id = 56)
 Total runtime: 180.431 ms
(3 rows)

SCANS=# explain analyze select * from nessus_results where scan_id = 55;
                                                        QUERY PLAN

-----------------------------------------------------------------------------------------------------------------------------
 Seq Scan on nessus_results  (cost=0.00..127170.34 rows=42640
width=169) (actual time=1612.537..2425.909 rows=41507 loops=1)
  Filter: (scan_id = 55)
 Total runtime: 2469.605 ms
(3 rows)

=== enable_seqscan off, enable_indexscan on ===

SCANS=# SET enable_seqscan = off;
SET
SCANS=# explain analyze 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) (actual
time=0.086..138.420 rows=41199 loops=1)
  Index Cond: (scan_id = 56)
 Total runtime: 181.712 ms
(3 rows)

SCANS=# explain analyze select * from nessus_results where scan_id = 55;

QUERY PLAN

--------------------------------------------------------------------------------------------------------------------------------------------------------
 Index Scan using nessus_results_scan_id on nessus_results
(cost=0.00..129136.46 rows=42640 width=169) (actual
time=0.066..139.351 rows=41507 loops=1)
  Index Cond: (scan_id = 55)
 Total runtime: 182.934 ms
(3 rows)

SCANS=#

=== enable_seqscan on, enable_indexscan off ===

SCANS=# set enable_seqscan =on;
SET
SCANS=# set enable_indexscan =off;
SET
SCANS=# explain analyze select * from nessus_results where scan_id = 56;
                                                        QUERY PLAN

-----------------------------------------------------------------------------------------------------------------------------
 Seq Scan on nessus_results  (cost=0.00..127170.34 rows=41813
width=169) (actual time=1640.184..2422.106 rows=41199 loops=1)
  Filter: (scan_id = 56)
 Total runtime: 2464.834 ms
(3 rows)

SCANS=# explain analyze select * from nessus_results where scan_id = 55;
                                                        QUERY PLAN

-----------------------------------------------------------------------------------------------------------------------------
 Seq Scan on nessus_results  (cost=0.00..127170.34 rows=42640
width=169) (actual time=1612.734..2425.494 rows=41507 loops=1)
  Filter: (scan_id = 55)
 Total runtime: 2469.415 ms
(3 rows)

Clearly it needs to use the index =)