pg.ddx.io  pgsql-performance@postgresql.org mailing list archive  
help / color / mirror / Atom feed
Tuning, configuration for 7.3.5 on a Sun E4500
4+ messages / 2 participants
[nested] [flat]

* Tuning, configuration for 7.3.5 on a Sun E4500
@ 2005-03-07 14:46 tsarevich@gmail.com
  2005-03-07 17:31 ` Re: Tuning, configuration for 7.3.5 on a Sun E4500 Josh Berkus <josh@agliodbs.com>
  0 siblings, 1 reply; 4+ messages in thread

From: tsarevich@gmail.com @ 2005-03-07 14:46 UTC (permalink / raw)
  To: 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)

Attachments:

postgresql.conf (text/plain, 5.2K) download # # 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 (application/octet-stream, 12.4K) download ^ permalink raw reply [nested|flat] 4+ messages in thread

* Re: Tuning, configuration for 7.3.5 on a Sun E4500
  2005-03-07 14:46 Tuning, configuration for 7.3.5 on a Sun E4500 tsarevich@gmail.com
@ 2005-03-07 17:31 ` Josh Berkus <josh@agliodbs.com>
  2005-03-08 07:47   ` Re: Tuning, configuration for 7.3.5 on a Sun E4500 tsarevich@gmail.com
  0 siblings, 1 reply; 4+ messages in thread

From: Josh Berkus @ 2005-03-07 17:31 UTC (permalink / raw)
  To: tsarevich@gmail.com; +Cc: pgsql-performance

Tsarevich,

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

Looks like you haven't run ANALYZE on the database anytime recently.  Try that 
and re-run.

-- 
Josh Berkus
Aglio Database Solutions
San Francisco



^ permalink  raw  reply  [nested|flat] 4+ messages in thread

* Re: Tuning, configuration for 7.3.5 on a Sun E4500
  2005-03-07 14:46 Tuning, configuration for 7.3.5 on a Sun E4500 tsarevich@gmail.com
  2005-03-07 17:31 ` Re: Tuning, configuration for 7.3.5 on a Sun E4500 Josh Berkus <josh@agliodbs.com>
@ 2005-03-08 07:47   ` tsarevich@gmail.com
  2005-03-08 17:10     ` Re: Tuning, configuration for 7.3.5 on a Sun E4500 Josh Berkus <josh@agliodbs.com>
  0 siblings, 1 reply; 4+ messages in thread

From: tsarevich@gmail.com @ 2005-03-08 07:47 UTC (permalink / raw)
  To: Josh Berkus <josh@agliodbs.com>; +Cc: pgsql-performance

Analyze has been run on the database quite frequently during the
course of us trying to figure out this performance issue.  It is also
a task that is crontabbed nightly.


On Mon, 7 Mar 2005 09:31:06 -0800, Josh Berkus <josh@agliodbs.com> wrote:
> Tsarevich,
> 
> > 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.
> 
> Looks like you haven't run ANALYZE on the database anytime recently.  Try that
> and re-run.
> 
> --
> Josh Berkus
> Aglio Database Solutions
> San Francisco
>



^ permalink  raw  reply  [nested|flat] 4+ messages in thread

* Re: Tuning, configuration for 7.3.5 on a Sun E4500
  2005-03-07 14:46 Tuning, configuration for 7.3.5 on a Sun E4500 tsarevich@gmail.com
  2005-03-07 17:31 ` Re: Tuning, configuration for 7.3.5 on a Sun E4500 Josh Berkus <josh@agliodbs.com>
  2005-03-08 07:47   ` Re: Tuning, configuration for 7.3.5 on a Sun E4500 tsarevich@gmail.com
@ 2005-03-08 17:10     ` Josh Berkus <josh@agliodbs.com>
  0 siblings, 0 replies; 4+ messages in thread

From: Josh Berkus @ 2005-03-08 17:10 UTC (permalink / raw)
  To: tsarevich@gmail.com; +Cc: pgsql-performance

Tsarevich,

> Analyze has been run on the database quite frequently during the
> course of us trying to figure out this performance issue.  It is also
> a task that is crontabbed nightly.

Hmmm.  Then you probably need to up the STATISTICS levels on the target 
column, because PG is mis-estimating the number of rows returned 
significantly.   That's done by:

ALTER TABLE {table} ALTER COLUMN {column} SET STATISTICS {number}

Generally, I find that if mis-estimation occurs, you need to raise statistics 
to at least 250.

Here's where I see the estimation issues with your EXPLAIN:

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

So it looks like you need to raise the stats on communication.component_id and 
component.commercial_order_id,raised_dtm,component_type_id.   You also may 
want to consider a multi-column index on the last set.

BTW, if you have any kind of data update traffic at all, ANALYZE once a day is 
not adequate.

-- 
Josh Berkus
Aglio Database Solutions
San Francisco



^ permalink  raw  reply  [nested|flat] 4+ messages in thread


end of thread, other threads:[~2005-03-08 17:10 UTC | newest]

Thread overview: 4+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2005-03-07 14:46 Tuning, configuration for 7.3.5 on a Sun E4500 tsarevich@gmail.com
2005-03-07 17:31 ` Josh Berkus <josh@agliodbs.com>
2005-03-08 07:47   ` tsarevich@gmail.com
2005-03-08 17:10     ` Josh Berkus <josh@agliodbs.com>

This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox