Hi
I am working on a real time
application.
I have a table which has 10000 rows and 31 columns.It is updated
every 5 secs.But only a max of 5000 rows may be updated.And Updates effect only
about 10 columns.
The postgresql.conf file is given below.I donot know
what values the parameters should take in order for the queries on this table to
work effieciently.
Please help me out
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 = true
max_connections = 100
#
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 = 1000 # min 16, at least max_connections*2,
8KB each
#sort_mem = 1024 # 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 = 7 # 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 = 1000 #
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 = 0 # range 0-2; 0=stdout; 1=both;
2=syslog
#syslog_facility = 'LOCAL0'
#syslog_ident = 'postgres'
# -
When to Log -
#client_min_messages = notice # Values, in order of
decreasing detail:
# debug5, debug4, debug3, debug2, debug1,
# log, info,
notice, warning, error
#log_min_messages = notice # Values, in order of
decreasing detail:
# debug5, debug4, debug3, debug2, debug1,
# info,
notice, warning, error, log, fatal,
# panic
#log_error_verbosity =
default # terse, default, or verbose messages
#log_min_error_statement =
panic # Values in order of increasing severity:
# debug5, debug4, debug3,
debug2, debug1,
# info, notice, warning, error,
panic(off)
#log_min_duration_statement = -1 # 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 = false
#log_connections =
false
#log_duration = false
#log_pid = false
#log_statement =
false
#log_timestamp = false
#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 = 'en_US.UTF-8' # locale for system error message
string
s
lc_monetary = 'en_US.UTF-8' # locale for monetary
formatting
lc_numeric = 'en_US.UTF-8' # locale for number
formatting
lc_time = 'en_US.UTF-8' # locale for time formatting
# -
Other Defaults -
#explain_pretty_print = true
#dynamic_library_path =
'$libdir'
max_expr_depth = 100000 # 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
bash-2.05b$
Thanks
Madhukar