From: pierre@desertmoon.com
To: pgsql-sql@postgreSQL.org
Subject: 6.4.x vs. 6.5 oddity
Date: Mon, 8 Mar 1999 12:27:57 -0700 (MST)
Message-ID: <19990308192757.20501.qmail@desertmoon.com> (raw)
All,
I have started playing with 6.5 and duped my DB onto a
spare box. I grabbed one of the most intensive queries that
runs under the current system and ran it on 6.5. It took
FOREVER. I then ran explain on both 6.4 and 6.5 and here is
the output for both...can anyone explain what the issue is here?
Or is it just that 6.5 is still in development?
(All tables are vacuumed)
-=pierre
V6.5
-------------------------------
explain select distinct(p.prod_id), p.prod_name, v.version, d.dev_name from
prod p, dev d, pkey k, version v
, pos o, pcat c where
v.version_id = p.version_id and d.dev_id = p.dev_id and
p.prod_id = k.prod_id and
c.cat_id = 8 and c.prod_id = k.prod_id and
o.os_id = 4 and
o.prod_id = k.prod_id and
k.keyword like 'photoshop%'
order by p.prod_name;
NOTICE: QUERY PLAN:
Unique (cost=822846.44 size=0 width=0)
-> Sort (cost=822846.44 size=0 width=0)
-> Nested Loop (cost=822846.44 size=1 width=68)
-> Nested Loop (cost=768643.19 size=27050 width=64)
-> Nested Loop (cost=768641.12 size=1 width=48)
-> Nested Loop (cost=3724.64 size=373130 width=32)
-> Nested Loop (cost=3460.39 size=1 width=28)
-> Index Scan using pkey_keyword_idx on pkey k (cost=3421.44 size=19 width=4)
-> Index Scan using prod_id_idx on prod p (cost=2.05 size=86557 width=24)
-> Index Scan using pcat_dcat_id_idx on pcat c (cost=264.24 size=4465 width=4)
-> Index Scan using version_id_idx on version v (cost=2.05 size=88843 width=16)
-> Index Scan using dev_id_idx on dev d (cost=2.05 size=27050 width=16)
-> Index Scan using pos_prod_id_idx on pos o (cost=2.00 size=13006 width=4)
EXPLAIN
EOF
V6.4
-------------------------------
explain select distinct(p.prod_id), p.prod_name, v.version, d.dev_name from
prod p, dev d, pkey k, version v
, pos o, pcat c where
v.version_id = p.version_id and d.dev_id = p.dev_id and
p.prod_id = k.prod_id and
c.cat_id = 8 and c.prod_id = k.prod_id and
o.os_id = 4 and
o.prod_id = k.prod_id and
k.keyword like 'photoshop%'
order by p.prod_name;
NOTICE: QUERY PLAN:
Unique (cost=3540.75 size=0 width=0)
-> Sort (cost=3540.75 size=0 width=0)
-> Nested Loop (cost=3540.75 size=105 width=68)
-> Nested Loop (cost=3534.60 size=3 width=52)
-> Nested Loop (cost=3530.50 size=2 width=36)
-> Nested Loop (cost=3528.45 size=1 width=12)
-> Nested Loop (cost=3524.44 size=2 width=8)
-> Index Scan using pkey_keyword_idx on pkey k (cost=3486.39 size=19 width=4)
-> Index Scan using pcat_prod_id_idx on pcat c (cost=2.00 size=4457 width=4)
-> Index Scan using pos_prod_id_idx on pos o (cost=2.01 size=13023 width=4)
-> Index Scan using prod_id_idx on prod p (cost=2.05 size=86666 width=24)
-> Index Scan using version_id_idx on version v (cost=2.05 size=89165 width=16)
-> Index Scan using dev_id_idx on dev d (cost=2.05 size=27135 width=16)
NOTICE: QUERY PLAN:
EXPLAIN
EOF
Reply instructions:
You may reply publicly to this message via plain-text email
using any one of the following methods:
* Reply to all the recipients from your mail client; the link fills in
the headers below: reply via email
Some clients, webmail especially, drop In-Reply-To and References from
a link, and the reply then starts a new thread: use the mbox below.
To: pgsql-sql@postgresql.org
Cc: pierre@desertmoon.com
Subject: Re: 6.4.x vs. 6.5 oddity
In-Reply-To: <19990308192757.20501.qmail@desertmoon.com>
References: <19990308192757.20501.qmail@desertmoon.com>
* Save the following mbox file, import it into your mail client,
and reply-to-all from there: mbox
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