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

Pointers needed on optimizing slow SQL statements

From: Janine Sisk <janine(at)furfly(dot)net>
To: pgsql-performance(at)postgresql(dot)org
Subject: Pointers needed on optimizing slow SQL statements
Date: 2009-06-03 20:54:07
Message-ID: E43FC549-D1AE-4606-A5BA-8574F306EE52@furfly.net (view raw or flat)
Thread:
Lists: pgsql-performance
I've been Googling for SQL tuning help for Postgres but the pickings  
have been rather slim.  Maybe I'm using the wrong search terms.  I'm  
trying to improve the performance of the following query and would be  
grateful for any hints, either directly on the problem at hand, or to  
resources I can read to find out more about how to do this.  In the  
past I have fixed most problems by adding indexes to get rid of  
sequential scans, but in this case it appears to be the hash join and  
the nested loops that are taking up all the time and I don't really  
know what to do about that.  In Google I found mostly references from  
people wanting to use a hash join to *fix* a performance problem, not  
deal with it creating one...

My Postgres version is 8.3.3, on Linux.

Thanks in advance,

janine

iso=# explain analyze select  a.item_id,
iso-#
iso-#                              
content_item__get_best_revision(a.item_id) as revision_id,
iso-#                              
content_item__get_latest_revision(a.item_id) as last_revision_id,
iso-#                              
content_revision__get_number(a.article_id) as revision_no,
iso-#                             (select count(*) from cr_revisions  
where item_id=a.item_id) as revision_count,
iso-#
iso-#                             -- Language support
iso-#                             b.lang_id,
iso-#                             b.lang_key,
iso-#                             (case when b.lang_key = 'big5' then  
'#D7D7D7' else '#ffffff' end) as tr_bgcolor,
iso-#                             coalesce(dg21_item_langs__rel_lang  
(b.lang_id,'gb2312'),'0') as gb_item_id,
iso-#                             coalesce(dg21_item_langs__rel_lang  
(b.lang_id,'iso-8859-1'),'0') as eng_item_id,
iso-#
iso-#                             -- user defined data
iso-#                             a.article_id,
iso-#                             a.region_id,
iso-#                             a.author,
iso-#                             a.archive_status,
iso-#                             a.article_status,
iso-#                             case when a.archive_status='t'
iso-#                                  then '<font color=#808080>never  
expire</font>'
iso-#                                  else to_char(a.archive_date,  
'YYYY年MM月DD日')
iso-#                             end as archive_date,
iso-#
iso-#                             -- Standard data
iso-#                             a.article_title,
iso-#                             a.article_desc,
iso-#                             a.creation_user,
iso-#                             a.creation_ip,
iso-#                             a.modifying_user,
iso-#
iso-#                             -- Pretty format data
iso-#                             a.item_creator,
iso-#
iso-#                             -- Other data
iso-#                             a.live_revision,
iso-#                             to_char(a.publish_date, 'YYYY年MM月 
DD日') as publish_date,
iso-#                             to_char(a.creation_date, 'DD/MM/YYYY  
HH:MI AM') as creation_date,
iso-#
iso-#                             case when article_status='approved'
iso-#                                  then 'admin content, auto  
approved'
iso-#                                  when article_status='unapproved'
iso-#                                  then (select approval_text
iso(#                                        from   dg21_approval
iso(#                                        where   
revision_id=a.article_id
iso(#                                        and     
approval_status='f' order by approval_date desc limit 1)
iso-#                                  else  ''
iso-#                             end as approval_text
iso-#
iso-#                     from    dg21_article_items a,  
dg21_item_langs b
iso-#                     where   a.item_id = b.item_id
iso-#
iso-#                     order by b.lang_id desc, a.item_id
iso-#                     limit 21 offset 0;

                                                                                        QUERY 
  PLAN
-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
  Limit  (cost=3516.97..3516.98 rows=1 width=1245) (actual  
time=195948.132..195948.250 rows=21 loops=1)
    ->  Sort  (cost=3516.97..3516.98 rows=1 width=1245) (actual  
time=195948.122..195948.165 rows=21 loops=1)
          Sort Key: b.lang_id, ci.item_id
          Sort Method:  top-N heapsort  Memory: 24kB
          ->  Nested Loop  (cost=719.67..3516.96 rows=1 width=1245)  
(actual time=346.687..195852.741 rows=4159 loops=1)
                ->  Nested Loop  (cost=719.67..3199.40 rows=1  
width=413) (actual time=311.422..119467.334 rows=4159 loops=1)
                      ->  Nested Loop  (cost=719.67..3198.86 rows=1  
width=400) (actual time=292.951..1811.051 rows=4159 loops=1)
                            ->  Hash Join  (cost=719.67..3197.98  
rows=1 width=352) (actual time=292.832..777.290 rows=4159 loops=1)
                                  Hash Cond: (cr.item_id = ci.item_id)
                                  Join Filter: ((ci.live_revision =  
cr.revision_id) OR ((ci.live_revision IS NULL) AND (cr.revision_id =  
content_item__get_latest_revision(ci.item_id))))
                                  ->  Hash Join  (cost=154.38..1265.24  
rows=4950 width=348) (actual time=74.789..375.580 rows=4950 loops=1)
                                        Hash Cond: (cr.revision_id =  
ox.article_id)
                                        ->  Seq Scan on cr_revisions  
cr  (cost=0.00..913.73 rows=16873 width=321) (actual  
time=0.058..71.539 rows=16873 loops=1)
                                        ->  Hash  (cost=92.50..92.50  
rows=4950 width=27) (actual time=74.607..74.607 rows=4950 loops=1)
                                              ->  Seq Scan on  
dg21_articles ox  (cost=0.00..92.50 rows=4950 width=27) (actual  
time=0.071..18.604 rows=4950 loops=1)
                                  ->  Hash  (cost=384.02..384.02  
rows=14502 width=8) (actual time=217.789..217.789 rows=14502 loops=1)
                                        ->  Seq Scan on cr_items ci   
(cost=0.00..384.02 rows=14502 width=8) (actual time=0.051..137.988  
rows=14502 loops=1)
                            ->  Index Scan using acs_objects_pk on  
acs_objects ao  (cost=0.00..0.88 rows=1 width=56) (actual  
time=0.223..0.229 rows=1 loops=4159)
                                  Index Cond: (ao.object_id =  
cr.revision_id)
                      ->  Index Scan using persons_pk on persons ps   
(cost=0.00..0.27 rows=1 width=17) (actual time=0.017..0.023 rows=1  
loops=4159)
                            Index Cond: (ps.person_id =  
ao.creation_user)
                ->  Index Scan using dg21_item_langs_id_key on  
dg21_item_langs b  (cost=0.00..8.27 rows=1 width=15) (actual  
time=0.526..0.537 rows=1 loops=4159)
                      Index Cond: (b.item_id = ci.item_id)
                SubPlan
                  ->  Limit  (cost=297.21..297.22 rows=1 width=29)  
(never executed)
                        ->  Sort  (cost=297.21..297.22 rows=1  
width=29) (never executed)
                              Sort Key: dg21_approval.approval_date
                              ->  Seq Scan on dg21_approval   
(cost=0.00..297.20 rows=1 width=29) (never executed)
                                    Filter: ((revision_id = $2) AND  
((approval_status)::text = 'f'::text))
                  ->  Aggregate  (cost=10.77..10.78 rows=1 width=0)  
(actual time=0.051..0.053 rows=1 loops=4159)
                        ->  Index Scan using cr_revisions_item_id_idx  
on cr_revisions  (cost=0.00..10.77 rows=2 width=0) (actual  
time=0.019..0.024 rows=1 loops=4159)
                              Index Cond: (item_id = $0)
  Total runtime: 195949.928 ms
(33 rows)

---
Janine Sisk
President/CEO of furfly, LLC
503-693-6407





Responses

pgsql-performance by date

Next:From: Scott CareyDate: 2009-06-03 21:09:45
Subject: Re: Scalability in postgres
Previous:From: Erik AronestyDate: 2009-06-03 19:30:30
Subject: Re: degenerate performance on one server of 3

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