Viewing Your Current Session's Settings

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)