Skip site navigation (1) Skip section navigation (2)

Difference in query plan when using = or > in where clause

From: "Radhika S" <radhika(dot)sambamurti(at)gmail(dot)com>
To: pgsql-performance(at)postgresql(dot)org
Subject: Difference in query plan when using = or > in where clause
Date: 2007-09-26 18:00:42
Message-ID: fe27bfd40709261100s1303c35bidd526354b57f62c6@mail.gmail.com (view raw or flat)
Thread:
Lists: pgsql-performance
Hi,

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.47rows=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.73rows=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.55rows=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

Responses

pgsql-performance by date

Next:From: Ow Mun HengDate: 2007-09-27 06:59:13
Subject: Re: REPOST: Nested loops row estimates always too high
Previous:From: pgdbaDate: 2007-09-26 16:56:33
Subject: Re: Incorrect row estimates in plan?

Privacy Policy | About PostgreSQL
Copyright © 1996-2014 The PostgreSQL Global Development Group