Pointers needed on optimizing slow SQL statements

[Date Prev][Date Next][Thread Prev][Thread Next][Date Index][Thread Index]

 



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





--
Sent via pgsql-performance mailing list (pgsql-performance@xxxxxxxxxxxxxx)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-performance


[Postgresql General]     [Postgresql PHP]     [PHP Users]     [PHP Home]     [PHP on Windows]     [Kernel Newbies]     [PHP Classes]     [PHP Books]     [PHP Databases]     [Yosemite]

  Powered by Linux