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)
#
# PostgreSQL configuration file
# -----------------------------
#
# This file consists of lines of the form:
#
#   name = value
#
# (The '=' is optional.) White space may be used. Comments are introduced
# with '#' anywhere on a line. The complete list of option names and
# allowed values can be found in the PostgreSQL documentation. The
# commented-out settings shown in this file represent the default values.
#
# Any option can also be given as a command line switch to the
# postmaster, e.g. 'postmaster -c log_connections=on'. Some options
# can be changed at run-time with the 'SET' SQL command.
#
# This file is read on postmaster startup and when the postmaster
# receives a SIGHUP. If you edit the file on a running system, you have 
# to SIGHUP the postmaster for the changes to take effect, or use 
# "pg_ctl reload".


#========================================================================


#
#       Connection Parameters
#
tcpip_socket = true
#ssl = false

max_connections =100 
superuser_reserved_connections =10 

port = 5432 
#hostname_lookup = false
#show_source_port = false

#unix_socket_directory = ''
#unix_socket_group = ''
#unix_socket_permissions = 0777 # octal

#virtual_host = ''

#krb_server_keyfile = ''


#
#       Shared Memory Size
#
#shared_buffers = 64            # min max_connections*2 or 16, 8KB each
##2005-01-26 1600 VBG:  Changed shared_buffers from 64 to 1000
shared_buffers = 10000            # min max_connections*2 or 16, 8KB each

#max_fsm_relations = 1000       # min 10, fsm is free space map, ~40 bytes
#max_fsm_pages = 10000          # min 1000, fsm is free space map, ~6 bytes
#max_locks_per_transaction = 64 # min 10
#wal_buffers = 8                # min 4, typically 8KB each

#
#       Non-shared Memory Sizes
#
sort_mem = 4096         # min 64, size in KB
#vacuum_mem = 8192              # min 1024, size in KB


#
#       Write-ahead log (WAL)
#
#checkpoint_segments = 3        # in logfile segments, min 1, 16MB each
#checkpoint_timeout = 300       # range 30-3600, in seconds
#
#commit_delay = 0               # range 0-100000, in microseconds
#commit_siblings = 5            # range 1-1000
#
#fsync = true
#wal_sync_method = fsync        # the default varies across platforms:
#                               # fsync, fdatasync, open_sync, or open_datasync
#wal_debug = 0                  # range 0-16


#
#       Optimizer Parameters
#
#enable_seqscan = true
#enable_indexscan = true
#enable_tidscan = true
#enable_sort = true
#enable_nestloop = true
#enable_mergejoin = true
#enable_hashjoin = true

effective_cache_size = 100000   # typically 8KB each
#random_page_cost = 4           # units are one sequential page fetch cost
#cpu_tuple_cost = 0.01          # (same)
#cpu_index_tuple_cost = 0.001   # (same)
#cpu_operator_cost = 0.0025     # (same)

#default_statistics_target = 10 # range 1-1000

#
#       GEQO Optimizer Parameters
#
#geqo = true
#geqo_selection_bias = 2.0      # range 1.5-2.0
#geqo_threshold = 11
#geqo_pool_size = 0             # default based on tables in statement, 
                                # range 128-1024
#geqo_effort = 1
#geqo_generations = 0
#geqo_random_seed = -1          # auto-compute seed


#
#       Message display
#
#server_min_messages = notice   # Values, in order of decreasing detail:
                                #   debug5, debug4, debug3, debug2, debug1,
                                #   info, notice, warning, error, log, fatal,
                                #   panic
#client_min_messages = notice   # Values, in order of decreasing detail:
                                #   debug5, debug4, debug3, debug2, debug1,
                                #   log, info, notice, warning, error
#silent_mode = false

#log_connections = false
#log_pid = false
#log_statement = false
#log_duration = false
log_timestamp = true 

#log_min_error_statement = panic # Values in order of increasing severity:
                                 #   debug5, debug4, debug3, debug2, debug1,
                                 #   info, notice, warning, error, panic(off)

#debug_print_parse = false
#debug_print_rewritten = false
#debug_print_plan = false
#debug_pretty_print = false

#explain_pretty_print = true

# requires USE_ASSERT_CHECKING
#debug_assertions = true


#
#       Syslog
#
#syslog = 0                     # range 0-2
#syslog_facility = 'LOCAL0'
#syslog_ident = 'postgres'


#
#       Statistics
#
#show_parser_stats = false
#show_planner_stats = false
#show_executor_stats = false
#show_statement_stats = false

# requires BTREE_BUILD_STATS
#show_btree_build_stats = false


#
#       Access statistics collection
#
#stats_start_collector = true
#stats_reset_on_server_start = true
#stats_command_string = false
#stats_row_level = false
#stats_block_level = false


#
#       Lock Tracing
#
#trace_notify = false

# requires LOCK_DEBUG
#trace_locks = false
#trace_userlocks = false
#trace_lwlocks = false
#debug_deadlocks = false
#trace_lock_oidmin = 16384
#trace_lock_table = 0


#
#       Misc
#
#autocommit = true
#dynamic_library_path = '$libdir'
#search_path = '$user,public'
#datestyle = 'iso, us'
#timezone = unknown             # actually, defaults to TZ environment setting
#australian_timezones = false
#client_encoding = sql_ascii    # actually, defaults to database encoding
#authentication_timeout = 60    # 1-600, in seconds
#deadlock_timeout = 1000        # in milliseconds
#default_transaction_isolation = 'read committed'
#max_expr_depth = 10000         # min 10
#max_files_per_process = 1000   # min 25
#password_encryption = true
#sql_inheritance = true
#transform_null_equals = false
#statement_timeout = 0          # 0 is disabled, in milliseconds
#db_user_namespace = false
 


#
#       Locale settings
#
# (initialized by initdb -- may be changed)
LC_MESSAGES = 'C'
LC_MONETARY = 'C'
LC_NUMERIC = 'C'
LC_TIME = 'C'

Attachment: tables.sql
Description: Binary data

---------------------------(end of broadcast)---------------------------
TIP 3: if posting/reading through Usenet, please send an appropriate
      subscribe-nomail command to [EMAIL PROTECTED] so that your
      message can get through to the mailing list cleanly

Reply via email to