Re: [SQL] 6.4.x vs. 6.5 oddity
От | pierre@desertmoon.com |
---|---|
Тема | Re: [SQL] 6.4.x vs. 6.5 oddity |
Дата | |
Msg-id | 19990308202253.21864.qmail@desertmoon.com обсуждение исходный текст |
Ответ на | Re: [SQL] 6.4.x vs. 6.5 oddity (Bruce Momjian <maillist@candle.pha.pa.us>) |
Ответы |
Re: [SQL] 6.4.x vs. 6.5 oddity
(Bruce Momjian <maillist@candle.pha.pa.us>)
|
Список | 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
В списке pgsql-sql по дате отправления:
Предыдущее
От: reedstrm@wallace.ece.rice.edu (Ross J. Reedstrom)Дата:
Сообщение: Re: [SQL] 6.4.x vs. 6.5 oddity