I am curious as to why this occurs. Why does an = change the query plan so drastically?
When my query is:
Select count(*) from View_A WHERE tradedate = '20070801'; The query plan is as below: I see that the scan on the alloctbl is being indexed on k_alloctbl_blockid_status
-> Bitmap Index Scan on idx_tradeblocktbl_tradeate (cost=0.00..50.47 rows=1444 width=0) (actual time=0.040..0.040 rows=106 loops=1) Index Cond: ((tradedate >= '2007-08-01'::date) AND (tradedate <= '2007-09-24'::date)) -> Bitmap Heap Scan on alloctbl a (cost=4.59..270.73 rows=70 width=16) (actual time=0.010..0.011 rows=1 loops=7) Recheck Cond: ( tr.recid = a.blockid) -> Bitmap Index Scan on k_alloctbl_blockid_status (cost=0.00..4.59 rows=70 width=0) (actual time=0.007..0.007 rows=1 loops=7) Index Cond: (tr.recid = a.blockid) Total runtime: 1.453 ms
But when my query is: Select count(*) from View_A WHERE tradedate BETWEEN '20070801' and '20070901'; The query plan is:
- -> Bitmap Heap Scan on tradeblocktbl tr (cost=50.47..2849.67 rows=1444 width=80) (actual time=0.095..0.218 rows=104 loops=1) Recheck Cond: ((tradedate >= '2007-08-01'::date) AND (tradedate <= '2007-09-24'::date)) -> Bitmap Index Scan on idx_tradeblocktbl_tradeate (cost=0.00..50.47 rows=1444 width=0) (actual time=0.050..0.050 rows=106 loops=1) Index Cond: ((tradedate >= '2007-08-01'::date) AND (tradedate <= '2007-09-24'::date)) -> Sort (cost=99007.79..100479.68 rows=588755 width=16) (actual time=2660.009..3150.887 rows=588755 loops=1) Sort Key: a.blockid -> Seq Scan on alloctbl a (cost=0.00..20442.55 rows=588755 width=16) (actual time=0.026..764.833 rows=588755 loops=1)
Total runtime: 3590.715 ms
Thank you. Radhika
-- It is all a matter of perspective. You choose your view by choosing where to stand. --Larry Wall