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

Re: Patrick's Evil Query

From: "Patrick Hatcher" <PHatcher(at)macys(dot)com>
To: sfpug(at)postgresql(dot)org, sfpug-owner(at)postgresql(dot)org
Subject: Re: Patrick's Evil Query
Date: 2003-01-20 23:55:19
Message-ID: OF465BB6AD.F67616EB-ON88256CB4.0082B323@fds.com (view raw or flat)
Thread:
Lists: sfpug
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







Responses

sfpug by date

Next:From: Stephan SzaboDate: 2003-01-21 00:17:02
Subject: Re: Patrick's Evil Query
Previous:From: David FetterDate: 2003-01-20 22:36:04
Subject: Re: Reporting: Oracle-like features?

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