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
