Re: [SQL] 6.4.x vs. 6.5 oddity - Mailing list pgsql-sql

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