pg.ddx.io  pgsql-sql@postgresql.org mailing list archive  
help / color / mirror / Atom feed
From: pierre@desertmoon.com
To: maillist@candle.pha.pa.us
Cc: pgsql-sql@postgresql.org
Subject: Re: [SQL] 6.4.x vs. 6.5 oddity
Date: Mon, 8 Mar 1999 13:22:53 -0700 (MST)
Message-ID: <19990308202253.21864.qmail@desertmoon.com> (raw)
In-Reply-To: <199903081933.OAA07992@candle.pha.pa.us>

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



view thread (6+ messages)

Message-ID: <19990308202253.21864.qmail@desertmoon.com>
Permalink:  ../19990308202253.21864.qmail@desertmoon.com/
Also on:    postgresql.org/message-id/19990308202253.21864.qmail@desertmoon.com

 · 

reply

Reply instructions:

You may reply publicly to this message via plain-text email
using any one of the following methods:

* Reply to all the recipients from your mail client; the link fills in
  the headers below: reply via email
  Some clients, webmail especially, drop In-Reply-To and References from
  a link, and the reply then starts a new thread: use the mbox below.

  To: pgsql-sql@postgresql.org
  Cc: pierre@desertmoon.com, maillist@candle.pha.pa.us
  Subject: Re: [SQL] 6.4.x vs. 6.5 oddity
  In-Reply-To: <19990308202253.21864.qmail@desertmoon.com>
  References: <199903081933.OAA07992@candle.pha.pa.us> <19990308202253.21864.qmail@desertmoon.com>

* Save the following mbox file, import it into your mail client,
  and reply-to-all from there: mbox

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