Re: Patrick's Evil Query

Good timing. I was just about to send an update:
I created a function to clean up the CASE statement hell. The CASE that
Josh refers to handled only 1 condition and it was a nasty looking thing.
The process now runs in about 90 secs.
I also tried creating a function that would remove some of the CASE
statements I had earlier in the query, but this bumped the time back up to
about 3 mins.
So after fixing the query, here's my EXPLAIN ANALYZE:

mdc_oz=# set sort_mem = 64000;
SET VARIABLE
mdc_oz=# explain analyze Select * from testb_v where gmmid=6;
NOTICE: QUERY PLAN:

Merge Join (cost=100613.94..102306.47 rows=9420 width=464) (actual
time=47381.84..96282.41 rows=58634 loops=1)
-> Index Scan using dvn_dept_vend_idx on tbluniquedvn dvn_v
(cost=0.00..1600.29 rows=22695 width=37) (actual time=10.36..562.66
rows=22300 loops=1)
-> Sort (cost=100613.94..100613.94 rows=9420 width=427) (actual
time=47315.97..47390.72 rows=58634 loops=1)
-> Hash Join (cost=46672.76..99992.18 rows=9420 width=427)
(actual time=2706.94..46481.35 rows=58634 loops=1)
-> Hash Join (cost=31.76..53092.15 rows=9420 width=403)
(actual time=102.26..37009.35 rows=58634 loops=1)
-> Seq Scan on salessummary s (cost=0.00..51711.51
rows=164151 width=346) (actual time=97.11..31564.12 rows=164151 loops=1)
-> Hash (cost=31.64..31.64 rows=49 width=57) (actual
time=4.46..4.46 rows=0 loops=1)
-> Seq Scan on kst k (cost=0.00..31.64 rows=49
width=57) (actual time=2.05..4.27 rows=68 loops=1)
-> Hash (cost=20081.25..20081.25 rows=409525 width=24)
(actual time=2603.64..2603.64 rows=0 loops=1)
-> Seq Scan on mdc_upc u (cost=0.00..20081.25
rows=409525 width=24) (actual time=39.25..2337.98 rows=106248 loops=1)
Total runtime: 96387.38 msec

I'll now try this query on the Mac OS X box I setup this morning that has v
3.x
Thanks for the help. I really appreciate it.

Patrick Hatcher
Macys.Com
Legacy Integration Developer
415-422-1610 office
HatcherPT - AIM

Josh Berkus
<josh(at)agliodbs(dot)c To: sfpug(at)postgresql(dot)org
om> cc:
Sent by: Subject: [sfpug] Patrick's Evil Query
sfpug-owner(at)post
gresql.org


01/14/2003 01:09
PM
Please respond
to josh

Folks,

For those of you who were present at last night's meeting, we decided to
tackle an "evil" query presented us by Patrick, which was taking 3.5
minutes
to execute.

For David & Matt, who had to leave before we solved it, Stephan and I found

the problem. At the output stage of the query, Patrick was executing a
60-line CASE statement with type conversions which accounted for a full
minute-and-a-half of the excution time for the query. Replaceing the CASE

statement with a function should improve the situation.

--
-Josh Berkus
Aglio Database Solutions
San Francisco

---------------------------(end of broadcast)---------------------------
TIP 6: Have you searched our list archives?

http://archives.postgresql.org