Tuning, configuration for 7.3.5 on a Sun E4500

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)