Re: Patrick's Evil Query

I thought I would ask this question here before sending to the Performance
list:
So I run my Evil query using a condition of gmmid = 6. It takes about 90
secs to run. However, if I run this and add a LIMIT 100 clause, the query
runs in less than 5 secs.
I don't understand this. It seems that the bulk of the time is spent
returning data and not actually running the query. Can this be so?

Thanks

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

Patrick
Hatcher To: josh(at)agliodbs(dot)com
cc: sfpug(at)postgresql(dot)org, sfpug-owner(at)postgresql(dot)org
01/15/2003 Subject: Re: [sfpug] Patrick's Evil Query(Document link: Patrick
02:50 PM Hatcher)

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