pg.ddx.io pgsql-sql@postgresql.org mailing list archive
help / color / mirror / Atom feed6.4.x vs. 6.5 oddity
6+ messages / 3 participants
[nested] [flat]
* 6.4.x vs. 6.5 oddity
@ 1999-03-08 19:27 pierre@desertmoon.com
0 siblings, 3 replies; 6+ messages in thread
From: pierre@desertmoon.com @ 1999-03-08 19:27 UTC (permalink / raw)
To: 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)
-=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
^ permalink raw reply [nested|flat] 6+ messages in thread
* Re: [SQL] 6.4.x vs. 6.5 oddity
@ 1999-03-08 19:29 Bruce Momjian <maillist@candle.pha.pa.us>
parent: pierre@desertmoon.com
2 siblings, 0 replies; 6+ messages in thread
From: Bruce Momjian @ 1999-03-08 19:29 UTC (permalink / raw)
To: pierre@desertmoon.com; +Cc: 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)
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
^ permalink raw reply [nested|flat] 6+ messages in thread
* Re: [SQL] 6.4.x vs. 6.5 oddity
@ 1999-03-08 19:33 Bruce Momjian <maillist@candle.pha.pa.us>
parent: pierre@desertmoon.com
2 siblings, 1 reply; 6+ messages in thread
From: Bruce Momjian @ 1999-03-08 19:33 UTC (permalink / raw)
To: pierre@desertmoon.com; +Cc: 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.
--
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
^ permalink raw reply [nested|flat] 6+ messages in thread
* Re: [SQL] 6.4.x vs. 6.5 oddity
@ 1999-03-08 19:53 Ross J. Reedstrom <reedstrm@wallace.ece.rice.edu>
parent: pierre@desertmoon.com
2 siblings, 0 replies; 6+ messages in thread
From: reedstrm@wallace.ece.rice.edu @ 1999-03-08 19:53 UTC (permalink / raw)
To: pierre@desertmoon.com; +Cc: pgsql-sql
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
^ permalink raw reply [nested|flat] 6+ messages in thread
* Re: [SQL] 6.4.x vs. 6.5 oddity
@ 1999-03-08 20:22 Bruce Momjian <maillist@candle.pha.pa.us>
parent: pierre@desertmoon.com
0 siblings, 0 replies; 6+ messages in thread
From: Bruce Momjian @ 1999-03-08 20:22 UTC (permalink / raw)
To: pierre@desertmoon.com; +Cc: 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:
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
^ permalink raw reply [nested|flat] 6+ messages in thread
* Re: [SQL] 6.4.x vs. 6.5 oddity
@ 1999-03-08 20:22 pierre@desertmoon.com
parent: Bruce Momjian <maillist@candle.pha.pa.us>
0 siblings, 1 reply; 6+ messages in thread
From: pierre@desertmoon.com @ 1999-03-08 20:22 UTC (permalink / raw)
To: maillist@candle.pha.pa.us; +Cc: 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
^ permalink raw reply [nested|flat] 6+ messages in thread
end of thread, other threads:[~1999-03-08 20:22 UTC | newest]
Thread overview: 6+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
1999-03-08 19:27 6.4.x vs. 6.5 oddity pierre@desertmoon.com
1999-03-08 19:29 ` Bruce Momjian <maillist@candle.pha.pa.us>
1999-03-08 19:33 ` Bruce Momjian <maillist@candle.pha.pa.us>
1999-03-08 20:22 ` pierre@desertmoon.com
1999-03-08 20:22 ` Bruce Momjian <maillist@candle.pha.pa.us>
1999-03-08 19:53 ` Ross J. Reedstrom <reedstrm@wallace.ece.rice.edu>
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox