Skip site navigation (1) Skip section navigation (2)

DBT-3 Query 2 EXPLAIN ANALYZE differences

From: Mark Wong <markw(at)osdl(dot)org>
To: pgsql-hackers(at)postgresql(dot)org
Subject: DBT-3 Query 2 EXPLAIN ANALYZE differences
Date: 2004-10-26 20:52:06
Message-ID: 20041026135206.A9775@osdl.org (view raw or flat)
Thread:
Lists: pgsql-hackers
I was doing some testing with DBT-3 on our 8-way STP systems and
noticed a significant difference in the execution of Query 2 using
8.0beta3.

Here is the query template we're using:
select s_acctbal, s_name, n_name, p_partkey, p_mfgr, s_address, s_phone,
       s_comment
from part, supplier, partsupp, nation, region
where p_partkey = ps_partkey
  and s_suppkey = ps_suppkey
  and p_size = :1
  and p_type like '%:2'
  and s_nationkey = n_nationkey
  and n_regionkey = r_regionkey
  and r_name = ':3'
  and ps_supplycost = ( select min(ps_supplycost)
                        from partsupp, supplier, nation, region
                        where p_partkey = ps_partkey
                          and s_suppkey = ps_suppkey
                          and s_nationkey = n_nationkey
                          and n_regionkey = r_regionkey
                          and r_name = ':3'
                      )
order by s_acctbal desc, n_name, s_name, p_partkey

This first run executes it pretty fast:
	http://khack.osdl.org/stp/298338/  1555.414 ms

This second run executes it relatively slow:
	http://khack.osdl.org/stp/298340/  42532.855 ms

The plans are different and I suspect thats where the differences lie.
For brevity (and readability) I won't copy the plans here but I'll
provide the links.  Search for 'PERF1.POWER.Q2' in the file, it's the
second query executed and you'll notice the differences under the
SubPlan:

The first run:
	http://khack.osdl.org/stp/298338/results/run/power_query.result

The second run:
	http://khack.osdl.org/stp/298340/results/run/power_query.result

I know Jenny has previously presented a problem that was solved by
doing a setseed(0), but I noticed the kit doesn't do that anymore.
Anyone know if this might be the same or related issues?

Thanks,
Mark

pgsql-hackers by date

Next:From: Hannu KrosingDate: 2004-10-26 20:53:44
Subject: Re: plans for bitmap indexes?
Previous:From: Hannu KrosingDate: 2004-10-26 20:44:10
Subject: Re:

Privacy Policy | About PostgreSQL
Copyright © 1996-2014 The PostgreSQL Global Development Group