Received: from localhost (unknown [200.46.204.191]) by postgresql.org (Postfix) with ESMTP id 988809F9A76 for ; Wed, 26 Sep 2007 15:00:51 -0300 (ADT) Received: from postgresql.org ([200.46.204.71]) by localhost (mx1.hub.org [200.46.204.191]) (amavisd-maia, port 10024) with ESMTP id 40664-08 for ; Wed, 26 Sep 2007 15:00:47 -0300 (ADT) X-Greylist: domain auto-whitelisted by SQLgrey-1.7.5 Received: from ug-out-1314.google.com (ug-out-1314.google.com [66.249.92.174]) by postgresql.org (Postfix) with ESMTP id 4212C9F995E for ; Wed, 26 Sep 2007 15:00:47 -0300 (ADT) Received: by ug-out-1314.google.com with SMTP id j3so1256738ugf for ; Wed, 26 Sep 2007 11:00:45 -0700 (PDT) DKIM-Signature: v=1; a=rsa-sha256; c=relaxed/relaxed; d=gmail.com; s=beta; h=domainkey-signature:received:received:message-id:date:from:to:subject:mime-version:content-type; bh=vP01DkK6l71eMQwFXo3NlPXBPTubs4TxCD4oi2YISno=; b=kOWn9sYAKX5bVI5VjgskxOZcSHnTw44643WhpzS95xvjwt0l07HJNYJzF2VXYsaaY1D4TgLBtz9XOBE5mnSzGLV8iVT524X9c/+VKlEd82y9ZM+7JZnT4LAl3X47tosu2iQaEtNReEeU27WBS0DjD/JNnIjOPIWyhhYzczOxf64= DomainKey-Signature: a=rsa-sha1; c=nofws; d=gmail.com; s=beta; h=received:message-id:date:from:to:subject:mime-version:content-type; b=BATw5BvB2N+Tx+wzsHkT2HD/hmzYxCoew9bBe8zTe6eZGo8dE/ycgDp7OODqvASEaFEulhm7pppO8RG08TWoq3Ve5hfjDsAR7shKcpABRNMOS6XoxdRoOKsbW9aEQVOY6/rb3DJ4hEkxhInVMjwlaLoQ29eG+DGPO7+uzK9jlXQ= Received: by 10.66.237.9 with SMTP id k9mr2550084ugh.1190829642902; Wed, 26 Sep 2007 11:00:42 -0700 (PDT) Received: by 10.67.98.4 with HTTP; Wed, 26 Sep 2007 11:00:42 -0700 (PDT) Message-ID: Date: Wed, 26 Sep 2007 14:00:42 -0400 From: "Radhika S" To: pgsql-performance@postgresql.org Subject: Difference in query plan when using = or > in where clause MIME-Version: 1.0 Content-Type: multipart/alternative; boundary="----=_Part_7871_33128043.1190829642864" X-Virus-Scanned: Maia Mailguard 1.0.1 X-Archive-Number: 200709/452 X-Sequence-Number: 26997 ------=_Part_7871_33128043.1190829642864 Content-Type: text/plain; charset=ISO-8859-1 Content-Transfer-Encoding: 7bit Content-Disposition: inline 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 ------=_Part_7871_33128043.1190829642864 Content-Type: text/html; charset=ISO-8859-1 Content-Transfer-Encoding: 7bit Content-Disposition: inline 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.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 ------=_Part_7871_33128043.1190829642864--