Search Postgresql Archives

Re: Postgresql 7.4.8 inconsistent index usage

[Date Prev][Date Next][Thread Prev][Thread Next][Date Index][Thread Index]

 



On 7/8/05, Michael Fuhr <mike@xxxxxxxx> 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 =)

---------------------------(end of broadcast)---------------------------
TIP 6: explain analyze is your friend


[Date Prev][Date Next][Thread Prev][Thread Next][Date Index][Thread Index]
[Index of Archives]     [Postgresql Jobs]     [Postgresql Admin]     [Postgresql Performance]     [Linux Clusters]     [PHP Home]     [PHP on Windows]     [Kernel Newbies]     [PHP Classes]     [PHP Books]     [PHP Databases]     [Postgresql & PHP]     [Yosemite]
  Powered by Linux