Instances where enable_seqscan = false is good

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)