pg.ddx.io pgsql-performance@postgresql.org mailing list archive
help / color / mirror / Atom feedDifference in query plan when using = or > in where clause
2+ messages / 2 participants
[nested] [flat]
* Difference in query plan when using = or > in where clause
@ 2007-09-26 18:00 Radhika S <radhika.sambamurti@gmail.com>
0 siblings, 1 reply; 2+ messages in thread
From: Radhika S @ 2007-09-26 18:00 UTC (permalink / raw)
To: 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
^ permalink raw reply [nested|flat] 2+ messages in thread
* Re: Difference in query plan when using = or > in where clause
@ 2007-09-27 12:39 Heikki Linnakangas <heikki@enterprisedb.com>
parent: Radhika S <radhika.sambamurti@gmail.com>
0 siblings, 0 replies; 2+ messages in thread
From: Heikki Linnakangas @ 2007-09-27 12:39 UTC (permalink / raw)
To: Radhika S <radhika.sambamurti@gmail.com>; +Cc: pgsql-performance
Radhika S wrote:
> 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:
> ...
> But when my query is:
> Select count(*) from View_A WHERE tradedate BETWEEN '20070801' and
> '20070901';
> The query plan is:
> ...
In short, the planner estimates that "tradedate BETWEEN '20070801' and
'20070901'" matches more rows than "tradatedate = '20070801'"
--
Heikki Linnakangas
EnterpriseDB http://www.enterprisedb.com
^ permalink raw reply [nested|flat] 2+ messages in thread
end of thread, other threads:[~2007-09-27 12:39 UTC | newest]
Thread overview: 2+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2007-09-26 18:00 Difference in query plan when using = or > in where clause Radhika S <radhika.sambamurti@gmail.com>
2007-09-27 12:39 ` Heikki Linnakangas <heikki@enterprisedb.com>
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox