On Sat, 20 Aug 2005, John Mendenhall wrote:

> I need to improve the performance for the following
> query.

I have run the same query in the same database under
different schemas.  Each schema is pretty much the same
tables and indices.  One has an extra backup table and
an extra index which are not used in either of the explain
analyze plans.

The first schema is a development schema, which I used
to performance tune the server so everything was great.

Here are the current results of the sql run in the development
environment:

-----
LOG:  duration: 852.275 ms  statement: explain analyze
SELECT
 c.id AS contact_id,
 sr.id AS sales_rep_id,
 p.id AS partner_id,
 coalesce(LTRIM(RTRIM(c.company)), LTRIM(RTRIM(c.firstname || ' ' || 
c.lastname))) AS contact_company,
 co.name AS contact_country,
 c.master_key_token
FROM
 sales_reps sr
 JOIN partners p ON (sr.id = p.sales_rep_id)
 JOIN contacts c ON (p.id = c.partner_id)
 JOIN countries co ON (LOWER(c.country) = LOWER(co.code))
 JOIN partner_classification pc ON (p.classification_id = pc.id AND 
pc.classification != 'Sales Rep')
WHERE
 c.lead_deleted IS NULL
 AND EXISTS
 (
  SELECT
   lr.id
  FROM
   lead_requests lr,
   lead_request_status lrs
  WHERE
   c.id = lr.contact_id AND
   lr.status_id = lrs.id  AND
   lrs.is_closed = 0
 )
ORDER BY
 contact_company, contact_id
                                                                                
                   QUERY PLAN                   
                                                                                
 
--------------------------------------------------------------------------------------------------------------------------------
---------------------------------------------------------------------------------
 Sort  (cost=18238.25..18238.27 rows=11 width=102) (actual 
time=823.721..823.915 rows=247 loops=1)
   Sort Key: COALESCE(ltrim(rtrim((c.company)::text)), 
ltrim(rtrim((((c.firstname)::text || ' '::text) || (c.lastname)::text)))), c.id
   ->  Hash Join  (cost=18230.34..18238.06 rows=11 width=102) (actual 
time=808.042..818.427 rows=247 loops=1)
         Hash Cond: (lower(("outer".code)::text) = 
lower(("inner".country)::text))
         ->  Seq Scan on countries co  (cost=0.00..4.42 rows=242 width=19) 
(actual time=0.032..1.208 rows=242 loops=1)
         ->  Hash  (cost=18230.31..18230.31 rows=9 width=95) (actual 
time=807.554..807.554 rows=0 loops=1)
               ->  Merge Join  (cost=18229.98..18230.31 rows=9 width=95) 
(actual time=794.413..804.855 rows=247 loops=1)
                     Merge Cond: ("outer".sales_rep_id = "inner".id)
                     ->  Sort  (cost=18227.56..18227.59 rows=9 width=95) 
(actual time=793.132..793.502 rows=250 loops=1)
                           Sort Key: p.sales_rep_id
                           ->  Merge Join  (cost=18227.26..18227.42 rows=9 
width=95) (actual time=782.832..789.205 rows=250 loops=1)
                                 Merge Cond: ("outer".id = 
"inner".classification_id)
                                 ->  Sort  (cost=1.05..1.05 rows=2 width=10) 
(actual time=0.189..0.194 rows=2 loops=1)
                                       Sort Key: pc.id
                                       ->  Seq Scan on partner_classification 
pc  (cost=0.00..1.04 rows=2 width=10) (actual time=0.089..0.127 rows=2 loops=1)
                                             Filter: ((classification)::text <> 
'Sales Rep'::text)
                                 ->  Sort  (cost=18226.21..18226.24 rows=13 
width=105) (actual time=782.525..782.818 rows=251 loops=1)
                                       Sort Key: p.classification_id
                                       ->  Merge Join  (cost=0.00..18225.97 
rows=13 width=105) (actual time=54.135..776.299 rows=449 loops=1)
                                             Merge Cond: ("outer".id = 
"inner".partner_id)
                                             ->  Index Scan using partners_pkey 
on partners p  (cost=0.00..30.80 rows=395 width=30) (actual time=0.073..6.873 
rows=395 loops=1)
                                             ->  Index Scan using 
contacts_partner_id_idx on contacts c  (cost=0.00..130157.20 rows=93 width=85) 
(actual time=0.366..739.783 rows=453 loops=1)
                                                   Filter: ((lead_deleted IS 
NULL) AND (subplan))
                                                   SubPlan
                                                     ->  Nested Loop  
(cost=0.00..6.75 rows=2 width=10) (actual time=0.103..0.103 rows=0 loops=5576)
                                                           Join Filter: 
("outer".status_id = "inner".id)
                                                           ->  Index Scan using 
lead_requests_contact_id_idx on lead_requests lr (cost=0.00..4.23 rows=2 
width=20) (actual time=0.075..0.075 rows=0 loops=5576)
                                                                 Index Cond: 
($0 = contact_id)
                                                           ->  Seq Scan on 
lead_request_status lrs  (cost=0.00..1.16 rows=8 width=10) (actual 
time=0.028..0.098 rows=4 loops=522)
                                                                 Filter: 
(is_closed = 0::numeric)
                     ->  Sort  (cost=2.42..2.52 rows=39 width=10) (actual 
time=1.183..1.569 rows=268 loops=1)
                           Sort Key: sr.id
                           ->  Seq Scan on sales_reps sr  (cost=0.00..1.39 
rows=39 width=10) (actual time=0.056..0.353 rows=39 loops=1)
 Total runtime: 826.425 ms
(34 rows)
-----

Here is the current run in the production environment,
which I need to figure out how to get to the performance
level of the development environment:

-----
LOG:  duration: 6447.934 ms  statement: explain analyze
SELECT
 c.id AS contact_id,
 sr.id AS sales_rep_id,
 p.id AS partner_id,
 coalesce(LTRIM(RTRIM(c.company)), LTRIM(RTRIM(c.firstname || ' ' || 
c.lastname))) AS contact_company,
 co.name AS contact_country,
 c.master_key_token
FROM
 sales_reps sr
 JOIN partners p ON (sr.id = p.sales_rep_id)
 JOIN contacts c ON (p.id = c.partner_id)
 JOIN countries co ON (LOWER(c.country) = LOWER(co.code))
 JOIN partner_classification pc ON (p.classification_id = pc.id AND 
pc.classification != 'Sales Rep')
WHERE
 c.lead_deleted IS NULL
 AND EXISTS
 (
  SELECT
   lr.id
  FROM
   lead_requests lr,
   lead_request_status lrs
  WHERE
   c.id = lr.contact_id AND
   lr.status_id = lrs.id  AND
   lrs.is_closed = 0
 )
ORDER BY
 contact_company, contact_id
                                                                                
        QUERY PLAN                              
                                                          
--------------------------------------------------------------------------------------------------------------------------------
----------------------------------------------------------
 Sort  (cost=40838.98..40849.08 rows=4042 width=102) (actual 
time=6418.732..6419.536 rows=1071 loops=1)
   Sort Key: COALESCE(ltrim(rtrim((c.company)::text)), 
ltrim(rtrim((((c.firstname)::text || ' '::text) || (c.lastname)::text)))), c.id
   ->  Merge Join  (cost=40442.25..40596.85 rows=4042 width=102) (actual 
time=6357.161..6389.616 rows=1071 loops=1)
         Merge Cond: ("outer"."?column3?" = "inner"."?column9?")
         ->  Sort  (cost=14.00..14.61 rows=242 width=19) (actual 
time=9.753..10.018 rows=240 loops=1)
               Sort Key: lower((co.code)::text)
               ->  Seq Scan on countries co  (cost=0.00..4.42 rows=242 
width=19) (actual time=0.126..3.950 rows=242 loops=1)
         ->  Sort  (cost=40428.24..40436.59 rows=3340 width=95) (actual 
time=6347.154..6348.429 rows=1071 loops=1)
               Sort Key: lower((c.country)::text)
               ->  Merge Join  (cost=75.65..40232.76 rows=3340 width=95) 
(actual time=60.308..6331.266 rows=1071 loops=1)
                     Merge Cond: ("outer".partner_id = "inner".id)
                     ->  Index Scan using contacts_partner_id_idx on contacts c 
 (cost=0.00..161018.18 rows=20120 width=85) (actual time=2.769..6188.886 
rows=1548 loops=1)
                           Filter: ((lead_deleted IS NULL) AND (subplan))
                           SubPlan
                             ->  Nested Loop  (cost=1.16..6.57 rows=2 width=10) 
(actual time=0.129..0.129 rows=0 loops=40262)
                                   Join Filter: ("outer".status_id = "inner".id)
                                   ->  Index Scan using 
lead_requests_contact_id_idx on lead_requests lr  (cost=0.00..4.86 rows=3 
width=20) (actual time=0.086..0.092 rows=0 loops=40262)
                                         Index Cond: ($0 = contact_id)
                                   ->  Materialize  (cost=1.16..1.24 rows=8 
width=10) (actual time=0.002..0.013 rows=6 loops=12593)
                                         ->  Seq Scan on lead_request_status 
lrs  (cost=0.00..1.16 rows=8 width=10) (actual time=0.078..0.243 rows=7 loops=1)
                                               Filter: (is_closed = 0::numeric)
                     ->  Sort  (cost=75.65..76.37 rows=290 width=20) (actual 
time=57.243..59.574 rows=1334 loops=1)
                           Sort Key: p.id
                           ->  Merge Join  (cost=59.24..63.79 rows=290 
width=20) (actual time=33.975..42.215 rows=395 loops=1)
                                 Merge Cond: ("outer".id = "inner".sales_rep_id)
                                 ->  Sort  (cost=2.42..2.52 rows=39 width=10) 
(actual time=1.206..1.285 rows=39 loops=1)
                                       Sort Key: sr.id
                                       ->  Seq Scan on sales_reps sr  
(cost=0.00..1.39 rows=39 width=10) (actual time=0.028..0.365 rows=39 loops=1)
                                 ->  Sort  (cost=56.82..57.55 rows=290 
width=20) (actual time=32.566..33.254 rows=395 loops=1)
                                       Sort Key: p.sales_rep_id
                                       ->  Nested Loop  (cost=24.35..44.96 
rows=290 width=20) (actual time=0.158..25.227 rows=395 loops=1)
                                             Join Filter: 
("inner".classification_id = "outer".id)
                                             ->  Seq Scan on 
partner_classification pc  (cost=0.00..1.04 rows=2 width=10) (actual 
time=0.050..0.096 rows=2 loops=1)
                                                   Filter: 
((classification)::text <> 'Sales Rep'::text)
                                             ->  Materialize  
(cost=24.35..28.70 rows=435 width=30) (actual time=0.028..6.617 rows=435 
loops=2)
                                                   ->  Seq Scan on partners p  
(cost=0.00..24.35 rows=435 width=30) (actual time=0.042..9.941 rows=435 loops=1)
 Total runtime: 6423.683 ms
(37 rows)
-----

The SQL is exactly the same.

The issue is the query plan is different, and thus,
not up to the performance we need.

We have 256meg in the machine.  Would it help if
we threw some more memory in?

Please let me know if you have *any* pointers as to
the reason for the difference.

Thank you very much in advance for any pointers or
suggestions.

JohnM

-- 
John Mendenhall
[EMAIL PROTECTED]
surf utopia
internet services

---------------------------(end of broadcast)---------------------------
TIP 2: Don't 'kill -9' the postmaster

Reply via email to