Query speed anomalies

From: Camm Maguire <camm(at)enhanced(dot)com>
To: pgsql-general(at)postgresql(dot)org
Subject: Query speed anomalies
Date: 2001-03-08 23:01:00
Message-ID: 547l1zdfwz.fsf@intech19.enhanced.com
Views: Raw Message | Whole Thread | Download mbox | Resend email
Thread:
Lists: 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.0 else r2.split end)-r1.price+(case when r1.dividend isnull then 0.0 else r1.dividend end))/r1.price as ret,r2.mret from rprices 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.0 else r2.split end)-r1.price+(case when r1.dividend isnull then 0.0 else r1.dividend end))/r1.price as ret,r2.mret from rprices 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(at)enhanced(dot)com
==========================================================================
"The earth is but one country, and mankind its citizens." -- Baha'u'llah

In response to

Browse pgsql-general by date

  From Date Subject
Next Message Rob Arnold 2001-03-08 23:12:01 Re: Optimisation of IN condition
Previous Message Peter Eisentraut 2001-03-08 22:16:04 Re: compilation error