Re: ported application having performance issues - Mailing list pgsql-performance

pgsql performance gurus,

I truly appreciate the suggestions provided.

I have tried each one separately to determine the
best fit.  I have included results for each suggestion.
I have also included my entire postgresql.conf file so
you can see our base configuration.
Each result is based on an in-session variable setting,
so it only affected the current session.

(1) Increase the default_statistics_target,
run vacuum, analyze on each table for each setting

The default setting is 10.

I tried the following settings, with the corresponding
results:

default_statistics_target =   10     time approximately 4500ms
default_statistics_target =  100     time approximately 3900ms
default_statistics_target =  500     time approximately 3900ms
default_statistics_target = 1000     time approximately 3900ms

So, this option does not quite get us there.

(2) Increase sort_mem value

The current setting for sort_mem is 2048.

sort_mem =  2048     time approximately 4500ms
sort_mem =  8192     time approximately 2750ms
sort_mem = 16384     time approximately 2650ms
sort_mem =  1024     time approximately 1000ms

Interesting to note...
When I set sort_mem to 1024, the plan started the join
with the lead_requests table and used the contacts index.
None of the above attempts used this.

(3) Decrease random_page_cost, increase effective_cache_size

The default setting for random_page_cost is 4.
Our setting for effective_cache_size is 2048.

random_page_cost = 4, effective_cache_size = 2048   time approximately 4500ms
random_page_cost = 3, effective_cache_size = 2048   time approximately 1050ms
random_page_cost = 3, effective_cache_size = 4096   time approximately 1025ms

The decrease of random_page_cost to 3 caused the plan
to work properly, using the lead_requests table as a
join starting point and using the contacts index.

*****

It appears we learned the following:

(a) For some reason, setting the sort_mem smaller than
our current setting caused things to work correctly.
(b) Lowering random_page_cost causes things to work
correctly.

This brings up the following questions:

 (i) What is the ideal configuration for this query
to work?
(ii) Will this ideal configuration work for all our
other queries, or is this specific to this query only?
(iii) Should I try additional variable changes, or
lower/raise the variables I have already changed even
more?

Thanks again for the suggestions provided.  And,
thanks in advance for any additional thoughts or
suggestions.

JohnM

::::::::::::::
postgresql.conf
::::::::::::::
-----
# -----------------------------
# 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".


#---------------------------------------------------------------------------
# CONNECTIONS AND AUTHENTICATION
#---------------------------------------------------------------------------

# - Connection Settings -

tcpip_socket = false
max_connections = 128
        # note: increasing max_connections costs about 500 bytes of shared
        # memory per connection slot, in addition to costs from shared_buffers
        # and max_locks_per_transaction.
#superuser_reserved_connections = 2
#port = 5432
#unix_socket_directory = ''
#unix_socket_group = ''
#unix_socket_permissions = 0777 # octal
#virtual_host = ''              # what interface to listen on; defaults to any
#rendezvous_name = ''           # defaults to the computer name

# - Security & Authentication -

#authentication_timeout = 60    # 1-600, in seconds
#ssl = false
#password_encryption = true
#krb_server_keyfile = ''
#db_user_namespace = false


#---------------------------------------------------------------------------
# RESOURCE USAGE (except WAL)
#---------------------------------------------------------------------------

# - Memory -

shared_buffers = 4096           # min 16, at least max_connections*2, 8KB each
sort_mem = 2048                 # min 64, size in KB
#vacuum_mem = 8192              # min 1024, size in KB

# - Free Space Map -

#max_fsm_pages = 20000          # min max_fsm_relations*16, 6 bytes each
#max_fsm_relations = 1000       # min 100, ~50 bytes each

# - Kernel Resource Usage -

#max_files_per_process = 1000   # min 25
#preload_libraries = ''


#---------------------------------------------------------------------------
# WRITE AHEAD LOG
#---------------------------------------------------------------------------

# - Settings -

#fsync = true                   # turns forced synchronization on or off
#wal_sync_method = fsync        # the default varies across platforms:
                                # fsync, fdatasync, open_sync, or open_datasync
#wal_buffers = 8                # min 4, 8KB each

# - Checkpoints -

#checkpoint_segments = 3        # in logfile segments, min 1, 16MB each
#checkpoint_timeout = 300       # range 30-3600, in seconds
#checkpoint_warning = 30        # 0 is off, in seconds
#commit_delay = 0               # range 0-100000, in microseconds
#commit_siblings = 5            # range 1-1000


#---------------------------------------------------------------------------
# QUERY TUNING
#---------------------------------------------------------------------------

# - Planner Method Enabling -

#enable_hashagg = true
#enable_hashjoin = true
#enable_indexscan = true
#enable_mergejoin = true
#enable_nestloop = true
#enable_seqscan = true
#enable_sort = true
#enable_tidscan = true

# - Planner Cost Constants -

effective_cache_size = 2048     # 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)

# - Genetic Query Optimizer -

#geqo = true
#geqo_threshold = 11
#geqo_effort = 1
#geqo_generations = 0
#geqo_pool_size = 0             # default based on tables in statement,
                                # range 128-1024
#geqo_selection_bias = 2.0      # range 1.5-2.0

# - Other Planner Options -

#default_statistics_target = 10 # range 1-1000
#from_collapse_limit = 8
#join_collapse_limit = 8        # 1 disables collapsing of explicit JOINs


#---------------------------------------------------------------------------
# ERROR REPORTING AND LOGGING
#---------------------------------------------------------------------------

# - Syslog -

syslog = 1                      # range 0-2; 0=stdout; 1=both; 2=syslog
syslog_facility = 'LOCAL0'
syslog_ident = 'postgres'

# - When to Log -

client_min_messages = log       # Values, in order of decreasing detail:
                                #   debug5, debug4, debug3, debug2, debug1,
                                #   log, info, notice, warning, error

log_min_messages = info         # Values, in order of decreasing detail:
                                #   debug5, debug4, debug3, debug2, debug1,
                                #   info, notice, warning, error, log, fatal,
                                #   panic

log_error_verbosity = verbose   # terse, default, or verbose messages

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

log_min_duration_statement = 250        # Log all statements whose
                                        # execution time exceeds the value, in
                                        # milliseconds.  Zero prints all
                                        # queries.  Minus-one disables.

silent_mode = false             # DO NOT USE without Syslog!

# - What to Log -

#debug_print_parse = false
#debug_print_rewritten = false
#debug_print_plan = false
debug_pretty_print = true
log_connections = true
#log_duration = true
#log_pid = false
#log_statement = true
log_timestamp = true
#log_hostname = false
#log_source_port = false


#---------------------------------------------------------------------------
# RUNTIME STATISTICS
#---------------------------------------------------------------------------

# - Statistics Monitoring -

#log_parser_stats = false
#log_planner_stats = false
#log_executor_stats = false
#log_statement_stats = false

# - Query/Index Statistics Collector -

#stats_start_collector = true
#stats_command_string = false
#stats_block_level = false
#stats_row_level = false
#stats_reset_on_server_start = true


#---------------------------------------------------------------------------
# CLIENT CONNECTION DEFAULTS
#---------------------------------------------------------------------------

# - Statement Behavior -

#search_path = '$user,public'   # schema names
#check_function_bodies = true
#default_transaction_isolation = 'read committed'
#default_transaction_read_only = false
#statement_timeout = 0          # 0 is disabled, in milliseconds

# - Locale and Formatting -

#datestyle = 'iso, mdy'
#timezone = unknown             # actually, defaults to TZ environment setting
#australian_timezones = false
#extra_float_digits = 0         # min -15, max 2
#client_encoding = sql_ascii    # actually, defaults to database encoding

# These settings are initialized by initdb -- they may be changed
lc_messages = 'C'               # locale for system error message strings
lc_monetary = 'C'               # locale for monetary formatting
lc_numeric = 'C'                # locale for number formatting
lc_time = 'C'                   # locale for time formatting

# - Other Defaults -

#explain_pretty_print = true
#dynamic_library_path = '$libdir'
#max_expr_depth = 10000         # min 10


#---------------------------------------------------------------------------
# LOCK MANAGEMENT
#---------------------------------------------------------------------------

#deadlock_timeout = 1000        # in milliseconds
#max_locks_per_transaction = 64 # min 10, ~260*max_connections bytes each


#---------------------------------------------------------------------------
# VERSION/PLATFORM COMPATIBILITY
#---------------------------------------------------------------------------

# - Previous Postgres Versions -

#add_missing_from = true
#regex_flavor = advanced        # advanced, extended, or basic
#sql_inheritance = true

# - Other Platforms & Clients -

#transform_null_equals = false
-----

--
John Mendenhall
john@surfutopia.net
surf utopia
internet services