Pointers needed on optimizing slow SQL statements - Mailing list 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