Thread: 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)
-=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
> 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
> 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
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
>
> > 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
> > > > > 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