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'
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
