Tuning, configuration for 7.3.5 on a Sun E4500 - Mailing list pgsql-performance

Env:  Sun E4500 with 8 gig of RAM in total.  Database is stored
locally (not on a network storage devise).  A copy of the
postgresql.conf file is attached.

When running queries we are experiencing much bigger result times than
anticipated.

Attached is a copy of our postgresql.conf file and of our the table
definitions and row counts.

Below is an example of SQL and the explain plans.

Any help/pointers/tips/etc.  for getting this speed up would be great!!

Cheers


SELECT C.component_id, I.cli,
                        BL.ncos_value, BL.description,
                        SG.switch_group_code, SG.servcom_name,
                        S.description AS status,
                        RC.description AS process_status,
                        OT.description AS order_type,
                        P.party_name,
                        RDCR.consumer_ref AS consumer_ref,
                        C.raised_dtm AS created_dtm,
                        (SELECT dtm FROM orders.communication WHERE
component_id = C.component_id ORDER BY dtm DESC LIMIT 1) AS status_dtm
                         FROM  (SELECT * FROM parties.party WHERE
party_id = 143 AND is_active = true) P
                         JOIN orders.commercial_order CO ON
CO.party_id = P.party_id
                         JOIN (SELECT raised_dtm, component_id,
last_supplier_status, component_type_id, current_status_id_fr,
commercial_order_id FROM orders.component WHERE raised_dtm BETWEEN
'2003-01-01 00:00:00'::timestamp AND '2005-01-01 23:59:59'::timestamp
AND component_type_id IN (3, 2, 1)) C ON C.commercial_order_id =
CO.commercial_order_id
                         JOIN (SELECT * FROM orders.ida WHERE cli IS
NOT NULL  ) I ON C.component_id = I.component_id
                         --Get the consumer reference if there is one
                          LEFT JOIN parties.consumer_ref  RDCR ON
CO.consumer_ref = RDCR.consumer_ref_id
                         --May or may not have barring level or ncos
dependant on the order type
                         LEFT JOIN line_configs.ida_barring_level BL
ON I.ida_barring_level_id = BL.ida_barring_level_id
                         LEFT JOIN line_configs.switch_group SG ON
I.switchgroup_id = SG.switch_group_id
                         --Get the order type
                         JOIN business_rules.component_type CT ON
C.component_type_id = CT.component_type_id
                         JOIN business_rules.order_type OT ON
OT.order_type_id = CT.order_type_id
                         --Get the status
                         LEFT JOIN orders.status S ON S.status_id =
C.current_status_id_fr
                         --Get the process status
                         LEFT JOIN orders.response_code RC ON
RC.response_code_id = C.last_supplier_status


QUERY PLAN

-------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------

 Hash Join  (cost=18.02..16067.46 rows=1158 width=277) (actual
time=639100.57..957020.42 rows=34638 loops=1)
   Hash Cond: ("outer".last_supplier_status = "inner".response_code_id)
   ->  Hash Join  (cost=9.29..16038.49 rows=1158 width=218) (actual
time=639084.27..937250.67 rows=34638 loops=1)
         Hash Cond: ("outer".current_status_id_fr = "inner".status_id)
         ->  Hash Join  (cost=8.17..16017.14 rows=1158 width=197)
(actual time=639083.19..931508.95 rows=34638 loops=1)
               Hash Cond: ("outer".order_type_id = "inner".order_type_id)
               ->  Hash Join  (cost=6.99..15995.69 rows=1158
width=180) (actual time=639082.01..926146.92 rows=34638 loops=1)
                     Hash Cond: ("outer".component_type_id =
"inner".component_type_id)
                     ->  Hash Join  (cost=5.47..15973.91 rows=1158
width=172) (actual time=639080.29..921574.75 rows=34638 loops=1)
                           Hash Cond: ("outer".switchgroup_id =
"inner".switch_group_id)
                           ->  Hash Join  (cost=1.49..15949.66
rows=1158 width=147) (actual time=639074.90..917437.55 rows=34638
loops=1)
                                 Hash Cond:
("outer".ida_barring_level_id = "inner".ida_barring_level_id)
                                 ->  Merge Join  (cost=0.00..15927.90
rows=1158 width=112) (actual time=639073.24..914042.15 rows=34638
loops=1)
                                       Merge Cond:
("outer".consumer_ref = "inner".consumer_ref_id)
                                       ->  Nested Loop
(cost=0.00..2630554.06 rows=1158 width=91) (actual
time=639072.57..909395.62 rows=34638 loops=1)
                                             ->  Nested Loop
(cost=0.00..2626789.68 rows=1244 width=66) (actual
time=639053.64..902100.16 rows=34638 loops=1)
                                                   ->  Nested Loop
(cost=0.00..2599576.29 rows=7041 width=38) (actual
time=2073.94..891860.92 rows=46376 loops=1)
                                                         Join Filter:
("outer".party_id = "inner".party_id)
                                                         ->  Index
Scan using commercial_order_consumer_ref_ix on commercial_order co
(cost=0.00..19499.42 rows=725250 width=12) (actual time=8.62..30310.16
rows=725250 loops=1)
                                                         ->  Seq Scan
on party  (cost=0.00..3.54 rows=1 width=26) (actual time=0.62..1.16
rows=1 loops=725250)
                                                               Filter:
((party_id = 143) AND (is_active = true))
                                                   ->  Index Scan
using component_commercial_order_id_ix on component  (cost=0.00..3.85
rows=1 width=28) (actual time=0.17..0.18 rows=1 loops=46376)
                                                         Index Cond:
(component.commercial_order_id = "outer".commercial_order_id)
                                                         Filter:
((raised_dtm >= '2003-01-01 00:00:00'::timestamp without time zone)
AND (raised_dtm <= '2005-01-01 23:59:59'::timestamp without time zone)
AND ((component_type_id = 3) OR (component_type_id = 2) OR
(component_type_id = 1)))
                                             ->  Index Scan using
ida_pkey on ida  (cost=0.00..3.01 rows=1 width=25) (actual
time=0.12..0.14 rows=1 loops=34638)
                                                   Index Cond:
("outer".component_id = ida.component_id)
                                                   Filter: (cli IS NOT NULL)
                                       ->  Index Scan using
consumer_ref_pk on consumer_ref rdcr  (cost=0.00..24.31 rows=937
width=21) (actual time=0.48..0.48 rows=1 loops=1)
                                 ->  Hash  (cost=1.39..1.39 rows=39
width=35) (actual time=1.07..1.07 rows=0 loops=1)
                                       ->  Seq Scan on
ida_barring_level bl  (cost=0.00..1.39 rows=39 width=35) (actual
time=0.07..0.76 rows=39 loops=1)
                           ->  Hash  (cost=3.59..3.59 rows=159
width=25) (actual time=4.54..4.54 rows=0 loops=1)
                                 ->  Seq Scan on switch_group sg
(cost=0.00..3.59 rows=159 width=25) (actual time=0.09..3.13 rows=159
loops=1)
                     ->  Hash  (cost=1.41..1.41 rows=41 width=8)
(actual time=0.90..0.90 rows=0 loops=1)
                           ->  Seq Scan on component_type ct
(cost=0.00..1.41 rows=41 width=8) (actual time=0.08..0.64 rows=41
loops=1)
               ->  Hash  (cost=1.15..1.15 rows=15 width=17) (actual
time=0.43..0.43 rows=0 loops=1)
                     ->  Seq Scan on order_type ot  (cost=0.00..1.15
rows=15 width=17) (actual time=0.08..0.31 rows=15 loops=1)
         ->  Hash  (cost=1.09..1.09 rows=9 width=21) (actual
time=0.29..0.29 rows=0 loops=1)
               ->  Seq Scan on status s  (cost=0.00..1.09 rows=9
width=21) (actual time=0.08..0.22 rows=9 loops=1)
   ->  Hash  (cost=7.99..7.99 rows=299 width=59) (actual
time=8.69..8.69 rows=0 loops=1)
         ->  Seq Scan on response_code rc  (cost=0.00..7.99 rows=299
width=59) (actual time=0.16..5.94 rows=299 loops=1)
   SubPlan
     ->  Limit  (cost=21.23..21.23 rows=1 width=8) (actual
time=0.45..0.46 rows=1 loops=34638)
           ->  Sort  (cost=21.23..21.27 rows=16 width=8) (actual
time=0.44..0.44 rows=1 loops=34638)
                 Sort Key: dtm
                 ->  Index Scan using communication_component_id_ix on
communication  (cost=0.00..20.90 rows=16 width=8) (actual
time=0.12..0.14 rows=1 loops=34638)
                       Index Cond: (component_id = $0)
 Total runtime: 957091.40 msec
(47 rows)



SELECT raised_dtm, component_id, last_supplier_status,
component_type_id, current_status_id_fr, commercial_order_id FROM
orders.component WHERE raised_dtm BETWEEN '2003-01-01
00:00:00'::timestamp AND '2005-01-01 23:59:59'::timestamp  AND
component_type_id IN (3, 2, 1)

 QUERY PLAN

-----------------------------------------------------------------------------------------------------------------------------------------------------------
 Index Scan using component_raised_dtm_ix on component
(cost=0.00..17442.38 rows=128571 width=28) (actual time=1.04..20781.05
rows=307735 loops=1)
   Index Cond: ((raised_dtm >= '2003-01-01 00:00:00'::timestamp
without time zone) AND (raised_dtm <= '2005-01-01 23:59:59'::timestamp
without time zone))
   Filter: ((component_type_id = 3) OR (component_type_id = 2) OR
(component_type_id = 1))
 Total runtime: 21399.79 msec
(4 rows)


SELECT * FROM orders.ida WHERE cli IS NOT NULL;
                                                  QUERY PLAN
--------------------------------------------------------------------------------------------------------------
 Seq Scan on ida  (cost=0.00..12420.24 rows=677424 width=25) (actual
time=0.15..16782.27 rows=677415 loops=1)
   Filter: (cli IS NOT NULL)
 Total runtime: 17885.80 msec
(3 rows)