Sample verbose analyze:
VACUUM VERBOSE ANALYZE hello_rda_or_key;
INFO: vacuuming "craft.hello_rda_or_key"
INFO: index "hello242_1105" now contains 740813 row versions in 2477 pages
DETAIL: 0 index pages have been deleted, 0 are currently reusable.
CPU 0.42s/0.13u sec elapsed 4.76 sec.
INFO: "hello_rda_or_key": found 0 removable, 740813 nonremovable row versions in 12778 pages
DETAIL: 440813 dead row versions cannot be removed yet.
There were 0 unused item pointers.
0 pages are entirely empty.
CPU 0.78s/0.66u sec elapsed 6.41 sec.
INFO: analyzing "craft.hello_rda_or_key"
INFO: "hello_rda_or_key": 12778 pages, 3000 rows sampled, 39388 estimated total rows
VACUUM
Here is my postgresql.conf file:
# ----------------------------- # 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 = 100
max_connections = 600
# 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
port = 5333
#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 shared_buffers = 1200 # min 16, at least max_connections*2, 8KB each #sort_mem = 1024 # min 64, size in KB sort_mem = 40960 # min 64, size in KB #vacuum_mem = 8192 # min 1024, size in KB vacuum_mem = 81920 # min 1024, size in KB
# - Free Space Map -
#max_fsm_pages = 20000 # min max_fsm_relations*16, 6 bytes each max_fsm_pages = 50000 # min max_fsm_relations*16, 6 bytes each #max_fsm_relations = 1000 # min 100, ~50 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
fsync = false # 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 = 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 = 0 # Log all statements whose
# execution time exceeds the value, in
# milliseconds. Zero 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 #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
At 2:58 pm -0700 2003/10/18, Josh Berkus wrote:
Gan,
Is the index bloating issue still not resolved in 7.4beta4 ?
No, it should be. Please post your max_fsm_pages setting, and the output of a sample VACUUM VERBOSE ANALYZE. You probably don't have your FSM set right.
-- -Josh Berkus Aglio Database Solutions San Francisco
---------------------------(end of broadcast)--------------------------- TIP 7: don't forget to increase your free space map settings
-- +--------------------------------------------------------+ | Seum-Lim GAN email : [EMAIL PROTECTED] | | Lucent Technologies | | 2000 N. Naperville Road, 6B-403F tel : (630)-713-6665 | | Naperville, IL 60566, USA. fax : (630)-713-7272 | | web : http://inuweb.ih.lucent.com/~slgan | +--------------------------------------------------------+
---------------------------(end of broadcast)---------------------------
TIP 9: the planner will ignore your desire to choose an index scan if your
joining column's datatypes do not match
