Same query, executed twice, once using seqscan enabled and the other
with it disabled. Difference is nearly night and day.
How can I persuade PG to use the index w/o resorting to setting seqscan
= false
(actually, I don't know what are the pro or cons - I read posts from the
archives far back as 2005 but that was dealing with inconsistencies in
the text string eg: warwa(s/z/etc..) which caused it to pick seq scans.)
PPl in IRC suggested setting default_statistics = 100 but I didn't find
that useful nor helpful. Also, tables has been vacuum and analysed.
Using Seq-scans
---------------
QUERY PLAN
--------------------------------------------------------------------------------------------------
Limit (cost=4430.53..50173.70 rows=1000 width=47) (actual
time=21832.092..43771.536 rows=228 loops=1)
-> Hash Join (cost=4430.53..260866.77 rows=5606 width=47) (actual
time=21832.088..43770.927 rows=228 loops=1)
Hash Cond: ((trz.number)::text = (drv.number)::text)
-> Seq Scan on zone trz (cost=0.00..233254.27 rows=6148222
width=39) (actual time=22.807..31891.591 rows=6181910 loops=1)
Filter: ((zn_2 <> 0::numeric) AND (zn_2 IS NOT NULL))
-> Hash (cost=4414.39..4414.39 rows=1291 width=24) (actual
time=171.911..171.911 rows=12591 loops=1)
-> Bitmap Heap Scan on drv (cost=30.44..4414.39
rows=1291 width=24) (actual time=62.980..142.594 rows=12591 loops=1)
Recheck Cond: ((code)::text = 'NRN15'::text)
-> Bitmap Index Scan on idx_drv (cost=0.00..30.11
rows=1291 width=0) (actual time=62.199..62.199 rows=12649 loops=1)
Index Cond: ((code)::text = 'NRN15'::text)
Total runtime: 43772.045 ms
(11 rows)
set enable_seqscan = false;
QUERY PLAN
-----------------------------------------------------------------------------------------------------------
--
Limit (cost=0.00..69314.54 rows=1000 width=47) (actual
time=122.920..553.538 rows=228 loops=1)
-> Nested Loop (cost=0.00..388646.63 rows=5607 width=47) (actual
time=122.915..552.956 rows=228 loops=1)
-> Index Scan using idx_drv on drv (cost=0.00..5077.64
rows=1293 width=24) (actual time=38.164..110.933 rows=12591 loops=1)
Index Cond: ((code)::text = 'NRN15'::text)
-> Index Scan using idx_trz_sn on zone trz (cost=0.00..295.10
rows=120 width=39) (actual time=0.021..0.021 rows=0 loops=12591)
Index Cond: ((drv.number)::text = (trz.number)::text)
Filter: ((zn_2 <> 0::numeric) AND (zn_2 IS NOT NULL))
Total runtime: 553.964 ms
(8 rows)
Using Seq-scans
---------------
QUERY PLAN
----------------------------------------------------------------------------------------------------------------------------------------------------
Limit (cost=4430.53..50173.70 rows=1000 width=47) (actual time=21832.092..43771.536 rows=228 loops=1)
-> Hash Join (cost=4430.53..260866.77 rows=5606 width=47) (actual time=21832.088..43770.927 rows=228 loops=1)
Hash Cond: ((trz.number)::text = (drv.number)::text)
-> Seq Scan on zone trz (cost=0.00..233254.27 rows=6148222 width=39) (actual time=22.807..31891.591 rows=6181910 loops=1)
Filter: ((zn_2 <> 0::numeric) AND (zn_2 IS NOT NULL))
-> Hash (cost=4414.39..4414.39 rows=1291 width=24) (actual time=171.911..171.911 rows=12591 loops=1)
-> Bitmap Heap Scan on drv (cost=30.44..4414.39 rows=1291 width=24) (actual time=62.980..142.594 rows=12591 loops=1)
Recheck Cond: ((code)::text = 'NRN15'::text)
-> Bitmap Index Scan on idx_drv (cost=0.00..30.11 rows=1291 width=0) (actual time=62.199..62.199 rows=12649 loops=1)
Index Cond: ((code)::text = 'NRN15'::text)
Total runtime: 43772.045 ms
(11 rows)
set enable_seqscan = false;
QUERY PLAN
---------------------------------------------------------------------------------------------------------------------------------------------------------
Limit (cost=0.00..69314.54 rows=1000 width=47) (actual time=122.920..553.538 rows=228 loops=1)
-> Nested Loop (cost=0.00..388646.63 rows=5607 width=47) (actual time=122.915..552.956 rows=228 loops=1)
-> Index Scan using idx_drv on drv (cost=0.00..5077.64 rows=1293 width=24) (actual time=38.164..110.933 rows=12591 loops=1)
Index Cond: ((code)::text = 'NRN15'::text)
-> Index Scan using idx_trz_sn on zone trz (cost=0.00..295.10 rows=120 width=39) (actual time=0.021..0.021 rows=0 loops=12591)
Index Cond: ((drv.number)::text = (trz.number)::text)
Filter: ((zn_2 <> 0::numeric) AND (zn_2 IS NOT NULL))
Total runtime: 553.964 ms
(8 rows)
---------------------------(end of broadcast)---------------------------
TIP 9: In versions below 8.0, the planner will ignore your desire to
choose an index scan if your joining column's datatypes do not
match
