Query speed anomalies - Mailing list pgsql-general

Greetings!  Why is the first query below so much longer than the
second?  The only difference is the replacement of dates.asof =
r1.asof with dates.asof = 'constant', even though
r1.asof='same-constant' is a condition earlier in the where clause.

Here are the details:

rdb1=# explain select r1.asof,r1.id,r1.price,r2.price,r1.dividend,r2.split,(r2.price*(case when r2.split isnull then
1.0else r2.split end)-r1.price+(case when r1.dividend isnull then 0.0 else r1.dividend end))/r1.price as ret,r2.mret
fromrprices r1,rprices r2,dates where r1.asof = '20010201' and r2.asof = dates.nasof and dates.asof = r1.asof and r1.id
=r2.id and r1.price != 0.0 order by r1.id; 
NOTICE:  QUERY PLAN:

Sort  (cost=29853.31..29853.31 rows=11 width=56)
  ->  Nested Loop  (cost=36.15..29853.12 rows=11 width=56)
        ->  Hash Join  (cost=36.15..1163.55 rows=260 width=28)
              ->  Index Scan using rprices_asof_key on rprices r1  (cost=0.00..1100.76 rows=298 width=20)
              ->  Hash  (cost=18.78..18.78 rows=1178 width=8)
                    ->  Seq Scan on dates  (cost=0.00..18.78 rows=1178 width=8)
        ->  Index Scan using rprices_id_key on rprices r2  (cost=0.00..109.76 rows=29 width=28)

EXPLAIN
rdb1=# explain select r1.asof,r1.id,r1.price,r2.price,r1.dividend,r2.split,(r2.price*(case when r2.split isnull then
1.0else r2.split end)-r1.price+(case when r1.dividend isnull then 0.0 else r1.dividend end))/r1.price as ret,r2.mret
fromrprices r1,rprices r2,dates where r1.asof = '20010201' and r2.asof = dates.nasof and dates.asof = '20010201' and
r1.id= r2.id and r1.price != 0.0 order by r1.id; 
NOTICE:  QUERY PLAN:

Sort  (cost=2235.40..2235.40 rows=13 width=52)
  ->  Hash Join  (cost=1101.51..2235.17 rows=13 width=52)
        ->  Nested Loop  (cost=0.00..1105.76 rows=605 width=32)
              ->  Index Scan using dates_pkey on dates  (cost=0.00..2.01 rows=1 width=4)
              ->  Index Scan using rprices_asof_key on rprices r2  (cost=0.00..1100.02 rows=298 width=28)
        ->  Hash  (cost=1100.76..1100.76 rows=298 width=20)
              ->  Index Scan using rprices_asof_key on rprices r1  (cost=0.00..1100.76 rows=298 width=20)

EXPLAIN
rdb1=# \d rprices
        Table "rprices"
 Attribute |  Type   | Modifier
-----------+---------+----------
 asof      | date    | not null
 id        | integer | not null
 rinfo     | integer | not null
 price     | float8  | not null
 ret       | float8  | not null
 dividend  | float4  |
 split     | float4  |
 volume    | integer |
 idcsplit  | float8  |
 idcdiv    | float8  |
 mret      | float8  |
Indices: rprices_asof_key,
         rprices_id_key

rdb1=# \d rprices_asof_key
Index "rprices_asof_key"
 Attribute |  Type
-----------+---------
 asof      | date
 id        | integer
unique btree

rdb1=# \d rprices_id_key
Index "rprices_id_key"
 Attribute |  Type
-----------+---------
 id        | integer
btree

rdb1=# \d dates
        Table "dates"
 Attribute | Type | Modifier
-----------+------+----------
 asof      | date | not null
 nasof     | date |
 pasof     | date |
Index: dates_pkey

rdb1=# \d dates_pkey
Index "dates_pkey"
 Attribute | Type
-----------+------
 asof      | date
unique btree (primary key)



Thanks!
--
Camm Maguire                             camm@enhanced.com
==========================================================================
"The earth is but one country, and mankind its citizens."  --  Baha'u'llah