Обсуждение: 6.4.x vs. 6.5 oddity

Поиск
Список
Период
Сортировка

6.4.x vs. 6.5 oddity

От
pierre@desertmoon.com
Дата:
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

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

От
Bruce Momjian
Дата:
> 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)

VACUUM ANALYZE?


--
  Bruce Momjian                        |  http://www.op.net/~candle
  maillist@candle.pha.pa.us            |  (610) 853-3000
  +  If your life is a hard drive,     |  830 Blythe Avenue
  +  Christ can be your backup.        |  Drexel Hill, Pennsylvania 19026

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

От
Bruce Momjian
Дата:
> 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.

--
  Bruce Momjian                        |  http://www.op.net/~candle
  maillist@candle.pha.pa.us            |  (610) 853-3000
  +  If your life is a hard drive,     |  830 Blythe Avenue
  +  Christ can be your backup.        |  Drexel Hill, Pennsylvania 19026

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

От
reedstrm@wallace.ece.rice.edu (Ross J. Reedstrom)
Дата:
Pierre -
Are you sure the databases are identical? Including indicies? The only
significant difference I can see is the choice of index by the 6.5 plan
for the scan on pcat:

> V6.5
>  Index Scan using pcat_dcat_id_idx on pcat c  (cost=264.24 size=4465 width=4)
>
> V6.4
>  Index Scan using pcat_prod_id_idx on pcat c  (cost=2.00 size=4457 width=4)

Strangely enough, the estimated total cost difference is about the same ratio.


--
Ross J. Reedstrom, Ph.D., <reedstrm@rice.edu>
NSBRI Research Scientist/Programmer
Computer and Information Technology Institute
Rice University, 6100 S. Main St.,  Houston, TX 77005

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

От
pierre@desertmoon.com
Дата:
>
> > 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

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

От
Bruce Momjian
Дата:
> >
> > > 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:

Good.

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

Should be much faster for joining many tables like this case.

--
  Bruce Momjian                        |  http://www.op.net/~candle
  maillist@candle.pha.pa.us            |  (610) 853-3000
  +  If your life is a hard drive,     |  830 Blythe Avenue
  +  Christ can be your backup.        |  Drexel Hill, Pennsylvania 19026