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: (view raw or whole thread)
Lists: sfpug
I thought I would ask this question here before sending to the Performance
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?


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

                    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;
mdc_oz=# explain analyze Select * from testb_v where gmmid=6;

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
Thanks for the help.  I really appreciate it.

Patrick Hatcher
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                                  
                    01/14/2003 01:09                                                                                  
                    Please respond                                                                                    
                    to josh                                                                                           


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
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?


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-2015 The PostgreSQL Global Development Group