Probably the most well known way to look at settings is by using the SHOW ALL command in psql. This commands basically does what it says; it shows all the postgresql variables as a resultset onto your screen. It works ok, but I’ve found when doing tuning it really gives me a lot of extraneous information that I don’t need, and doesn’t give me some of the information I do need. So instead I look at [http://www.postgresql.org/docs/current/static/view-pg-settings.html pg_settings].
By using pg_settings, you can query on specific information relevant to your current session. Want to see all of the values you’re using that aren’t defaults, try this:
bjt=# select name, setting, source, category from pg_settings where source <> 'default';
name | setting | source | category
--------------------------------+-------------------------------------+--------------------+------------------------------------------------------
autovacuum | on | configuration file | Auto Vacuum
checkpoint_segments | 8 | configuration file | Write-Ahead Log / Checkpoints
checkpoint_timeout | 600 | configuration file | Write-Ahead Log / Checkpoints
config_file | /var/lib/pgsql/data/postgresql.conf | override | File Locations
data_directory | /var/lib/pgsql/data | override | File Locations
effective_cache_size | 90000 | configuration file | Query Tuning / Planner Cost Constants
enable_nestloop | on | session | Query Tuning / Planner Method Configuration
fsync | on | configuration file | Write-Ahead Log / Settings
geqo_effort | 5 | session | Query Tuning / Genetic Query Optimizer
hba_file | /var/lib/pgsql/data/pg_hba.conf | override | File Locations
ident_file | /var/lib/pgsql/data/pg_ident.conf | override | File Locations
join_collapse_limit | 25 | session | Query Tuning / Other Planner Options
lc_collate | C | override | Client Connection Defaults / Locale and Formatting
lc_ctype | C | override | Client Connection Defaults / Locale and Formatting
lc_messages | C | configuration file | Client Connection Defaults / Locale and Formatting
lc_monetary | C | configuration file | Client Connection Defaults / Locale and Formatting
lc_numeric | C | configuration file | Client Connection Defaults / Locale and Formatting
lc_time | C | configuration file | Client Connection Defaults / Locale and Formatting
listen_addresses | * | configuration file | Connections and Authentication / Connection Settings
log_destination | syslog | configuration file | Reporting and Logging / Where to Log
log_directory | pg_log | configuration file | Reporting and Logging / Where to Log
log_line_prefix | :%h: | configuration file | Reporting and Logging / What to Log
log_min_duration_statement | 5000 | configuration file | Reporting and Logging / When to Log
log_min_error_statement | error | configuration file | Reporting and Logging / When to Log
log_rotation_age | 1440 | configuration file | Reporting and Logging / Where to Log
log_rotation_size | 0 | configuration file | Reporting and Logging / Where to Log
log_statement | ddl | configuration file | Reporting and Logging / What to Log
log_truncate_on_rotation | on | configuration file | Reporting and Logging / Where to Log
maintenance_work_mem | 32768 | configuration file | Resource Usage / Memory
max_connections | 1120 | configuration file | Connections and Authentication / Connection Settings
max_fsm_pages | 200000 | configuration file | Resource Usage / Free Space Map
max_prepared_transactions | 0 | configuration file | Resource Usage
max_stack_depth | 8192 | configuration file | Resource Usage / Memory
port | 5432 | command line | Connections and Authentication / Connection Settings
random_page_cost | 3.5 | session | Query Tuning / Planner Cost Constants
redirect_stderr | off | configuration file | Reporting and Logging / Where to Log
server_encoding | SQL_ASCII | override | Client Connection Defaults / Locale and Formatting
shared_buffers | 50000 | configuration file | Resource Usage / Memory
statement_timeout | 2700000 | configuration file | Client Connection Defaults / Statement Behavior
stats_command_string | on | configuration file | Statistics / Query and Index Statistics Collector
stats_row_level | on | configuration file | Statistics / Query and Index Statistics Collector
stats_start_collector | on | configuration file | Statistics / Query and Index Statistics Collector
superuser_reserved_connections | 20 | configuration file | Connections and Authentication / Connection Settings
temp_buffers | 3200 | configuration file | Resource Usage / Memory
TimeZone | EST5EDT | command line | Client Connection Defaults / Locale and Formatting
transaction_isolation | read committed | override | Client Connection Defaults / Statement Behavior
transaction_read_only | off | override | Client Connection Defaults / Statement Behavior
wal_buffers | 1024 | configuration file | Write-Ahead Log / Settings
work_mem | 1024 | configuration file | Resource Usage / Memory
(49 rows)
of course, even this might be too verbose, but the beauty is that you can make these queries as simple or complex as you’d like, for example if you’re doing performance tuning, you might want something fancy like:
bjt=# select name, setting, source, context from pg_settings where category ~ 'Tun|Memory' order
bjt-# by source='session' desc, source;
name | setting | source | context
---------------------------+---------+--------------------+------------
enable_nestloop | on | session | user
geqo_effort | 5 | session | user
join_collapse_limit | 25 | session | user
random_page_cost | 3.5 | session | user
effective_cache_size | 90000 | configuration file | user
maintenance_work_mem | 32768 | configuration file | user
max_stack_depth | 8192 | configuration file | superuser
shared_buffers | 50000 | configuration file | postmaster
temp_buffers | 3200 | configuration file | user
work_mem | 1024 | configuration file | user
constraint_exclusion | off | default | user
cpu_index_tuple_cost | 0.001 | default | user
cpu_operator_cost | 0.0025 | default | user
cpu_tuple_cost | 0.01 | default | user
default_statistics_target | 10 | default | user
enable_bitmapscan | on | default | user
enable_hashagg | on | default | user
enable_hashjoin | on | default | user
enable_indexscan | on | default | user
enable_mergejoin | on | default | user
enable_seqscan | on | default | user
enable_sort | on | default | user
enable_tidscan | on | default | user
from_collapse_limit | 8 | default | user
geqo | on | default | user
geqo_generations | 0 | default | user
geqo_pool_size | 0 | default | user
geqo_selection_bias | 2 | default | user
geqo_threshold | 12 | default | user
(29 rows)
That certainly gives you a quick overview of your current setting within your session when your doing performance tuning. And also don’t forget you can also create views of these types of queries if you want:
bjt=# create view "dba.tune" as select name, setting, source, context from pg_settings where
bjt-# category ~ 'Tun|Memory' order by source='session' desc, source;
CREATE VIEW
… and make use of the info as you need…
bjt=# select * from "dba.tune" where source <> 'default';
name | setting | source | context
----------------------+---------+--------------------+------------
enable_nestloop | on | session | user
geqo_effort | 5 | session | user
join_collapse_limit | 25 | session | user
random_page_cost | 3.5 | session | user
effective_cache_size | 90000 | configuration file | user
maintenance_work_mem | 32768 | configuration file | user
max_stack_depth | 8192 | configuration file | superuser
shared_buffers | 50000 | configuration file | postmaster
temp_buffers | 3200 | configuration file | user
work_mem | 1024 | configuration file | user
(10 rows)