DBT-3 Query 2 EXPLAIN ANALYZE differences
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
Mark,
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:
Yeah, the difference seems to be an arbitrary choice of which table to join on
first. The error is only +10 ms, but when it's being executed 3000
times ...
Will delve into this when I can re-format it, and try to figure out why the
planner is choosing what it is.
--
--Josh
Josh Berkus
Aglio Database Solutions
San Francisco
Import Notes
Reply to msg id not found: 20041026224912.8DCDB3A44C5@svr1.postgresql.orgReference msg id not found: 20041026224912.8DCDB3A44C5@svr1.postgresql.org | Resolved by subject fallback