Re: [SQL] 6.4.x vs. 6.5 oddity

>
> > 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