From: pierre@desertmoon.com
To: maillist@candle.pha.pa.us
Cc: pgsql-sql@postgresql.org
Subject: Re: [SQL] 6.4.x vs. 6.5 oddity
Date: Mon, 8 Mar 1999 13:22:53 -0700 (MST)
Message-ID: <19990308202253.21864.qmail@desertmoon.com> (raw)
In-Reply-To: <199903081933.OAA07992@candle.pha.pa.us>
>
> > 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)
> >
>
> Also, during the recent weeks, GEQO was being enabled for >= 6 tables.
> Try SET GEQO TO 'off'. The current CVS has the GEQO setting at 11.
>
Ahh..tat fixed it. I already had the db vacuum analyze(d) and setting
GEQO to off gave me an EXPLAIN that was close to the one from 6.4
here it is:
set geqo to 'off';
SET VARIABLE
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=3851.97 size=0 width=0)
-> Sort (cost=3851.97 size=0 width=0)
-> Nested Loop (cost=3851.97 size=105 width=68)
-> Nested Loop (cost=3845.82 size=3 width=52)
-> Nested Loop (cost=3841.72 size=2 width=36)
-> Nested Loop (cost=3839.67 size=1 width=12)
-> Hash Join (cost=3835.66 size=2 width=8)
-> Index Scan using pcat_dcat_id_idx on pcat c (cost=264.24 size=4465 width=4)
-> Hash (cost=0.00 size=0 width=0)
-> Index Scan using pkey_keyword_idx on pkey k (cost=3421.44 size=19 width=4)
-> Index Scan using pos_prod_id_idx on pos o (cost=2.00 size=13006 width=4)
-> Index Scan using prod_id_idx on prod p (cost=2.05 size=86557 width=24)
-> 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)
EXPLAIN
I do have one question...this particular query is taking about 20 seconds
to return...I've got postmaster setup with -i -B 512 -S -o -F, I had read
on this list that 6.5 was supposed to have a bit of a speed increase. Of
course this could be that I've only got 32MB in this test machine..?
Thanks!
-=pierre
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, maillist@candle.pha.pa.us
Subject: Re: [SQL] 6.4.x vs. 6.5 oddity
In-Reply-To: <19990308202253.21864.qmail@desertmoon.com>
References: <199903081933.OAA07992@candle.pha.pa.us> <19990308202253.21864.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