Viewing Server Configuration Parameter Settings
The SQL command SHOW allows you to see the current server configuration parameter settings. For example, to see the settings for all parameters:
$ psql -c 'SHOW ALL;'
For Example
[20:46 sachi@sachi ~] > psql -c 'SHOW ALL;'
Password:
1 name | setting | descr 1 iption
2 -------------------------------------------+----------------+----------------------------------------------------------------------------------------
2 ------------------------------------------------------------------------------------------
3 add_missing_from | off | Automatically adds missing table references to FROM clauses.
4 application_name | | Sets the application name to be reported in statistics and logs.
5 array_nulls | on | Enable input of NULL elements in arrays.
6 authentication_timeout | 1min | Sets the maximum time in seconds to complete client authentication.
7 backslash_quote | safe_encoding | Sets whether "\'" is allowed in string literals.
8 block_size | 32768 | Shows size of a disk block
9 bonjour_name | | Sets the Bonjour broadcast service name.
10 check_function_bodies | on | Check function bodies during CREATE FUNCTION.
11 client_encoding | UTF8 | Sets the client's character set encoding.
12 client_min_messages | notice | Sets the message levels that are sent to the client.
13 cpu_index_tuple_cost | 0.005 | Sets the planner's estimate of the cost of processing each index entry during an index 13 scan.
14 cpu_operator_cost | 0.0025 | Sets the planner's estimate of the cost of processing each operator or function call.
15 cpu_tuple_cost | 0.01 | Sets the planner's estimate of the cost of processing each tuple (row).
16 cursor_tuple_fraction | 1 | Sets the planner's estimate of the fraction of a cursor's rows that will be retrieved.
17 custom_variable_classes | | Sets the list of known custom variable classes.
18 DateStyle | ISO, MDY | Sets the display format for date and time values.
19 db_user_namespace | off | Enables per-database user names.
20 deadlock_timeout | 1s | The time in milliseconds to wait on lock before checking for deadlock.
21 debug_assertions | off | Turns on various assertion checks.
22 debug_pretty_print | off | Indents parse and plan tree displays.
23 debug_print_parse | off | Prints the parse tree to the server log.
24 debug_print_plan | off | Prints the execution plan to server log.
25 debug_print_prelim_plan | off | Prints the preliminary execution plan to server log.
26 debug_print_rewritten | off | Prints the parse tree after rewriting to server log.
27 debug_print_slice_table | off | Prints the slice table to server log.
28 default_statistics_target | 25 | Sets the default statistics target.
29 default_tablespace | | Sets the default tablespace to create tables and indexes in.
30 default_transaction_isolation | read committed | Sets the transaction isolation level of each new transaction.
31 default_transaction_read_only | off | Sets the default read-only status of new transactions.
32 effective_cache_size | 512MB | Sets the planner's assumption about size of the disk cache.
33 enable_bitmapscan | on | Enables the planner's use of bitmap-scan plans.
34 enable_groupagg | on | Enables the planner's use of grouping aggregation plans.
35 enable_hashagg | on | Enables the planner's use of hashed aggregation plans.
36 enable_hashjoin | on | Enables the planner's use of hash join plans.
37 enable_indexscan | on | Enables the planner's use of index-scan plans.
38 enable_mergejoin | off | Enables the planner's use of merge join plans.
39 enable_nestloop | off | Enables the planner's use of nested-loop join plans.
40 enable_seqscan | on | Enables the planner's use of sequential-scan plans.
41 enable_sort | on | Enables the planner's use of explicit sort steps.
42 enable_tidscan | on | Enables the planner's use of TID scan plans.
43 escape_string_warning | on | Warn about backslash escapes in ordinary string literals.
44 explain_pretty_print | on | Uses the indented output format for EXPLAIN VERBOSE.
45 extra_float_digits | 0 | Sets the number of digits displayed for floating-point values.
46 from_collapse_limit | 20 | Sets the FROM-list size beyond which subqueries are not collapsed.
47 gp_adjust_selectivity_for_outerjoins | on | Adjust selectivity of null tests over outer joins.
48 gp_analyze_relative_error | 0.25 | target relative error fraction for row sampling during analyze
49 gp_autostats_mode | ON_NO_STATS | Sets the autostats mode.
50 gp_autostats_on_change_threshold | 2147483647 | Threshold for number of tuples added to table by CTAS or Insert-to to trigger autostats 50 in on_change mode. See gp_autostats_mode.
51 gp_backup_directIO | off | Enable direct IO dump
52 gp_backup_directIO_read_chunk_mb | 20 | Size of read Chunk buffer in directIO dump (in MB)
53 gp_cached_segworkers_threshold | 5 | Sets the maximum number of segment workers to cache between statements.
54 gp_command_count | 1 | Shows the number of commands received from the client in this session.
55 gp_connections_per_thread | 64 | Sets the number of client connections handled in each thread.
56 gp_contentid | -1 | The contentid used by this server.
57 gp_dbid | 1 | The dbid used by this server.
58 gp_debug_linger | 0 | Number of seconds for QD/QE process to linger upon fatal internal error.
59 gp_dynamic_partition_pruning | on | This guc enables plans that can dynamically eliminate scanning of partitions.
60 gp_enable_adaptive_nestloop | on | Enables the planner's use of the Adaptive Join Choice operator.
61 gp_enable_agg_distinct | on | Enable 2-phase aggregation to compute a single distinct-qualified aggregate.
62 gp_enable_agg_distinct_pruning | on | Enable 3-phase aggregation and join to compute distinct-qualified aggregates.
63 gp_enable_direct_dispatch | on | Enable dispatch for single-row-insert targetted mirror-pairs.
64 gp_enable_fallback_plan | on | Plan types which are not enabled may be used when a query would be infeasible without t 64 hem.
65 gp_enable_fast_sri | on | Enable single-slice single-row inserts.
66 gp_enable_gpperfmon | on | Enable gpperfmon monitoring.
67 gp_enable_groupext_distinct_gather | on | Enable gathering data to a single node to compute distinct-qualified aggregates on grou 67 ping extention queries.
68 gp_enable_groupext_distinct_pruning | on | Enable 3-phase aggregation and join to compute distinct-qualified aggregates on groupin 68 g extention queries.
69 gp_enable_multiphase_agg | on | Enables the planner's use of two- or three-stage parallel aggregation plans.
70 gp_enable_predicate_propagation | on | When two expressions are equivalent (such as with equijoined keys) then the planner app 70 lies predicates on one expression to the other expression.
71 gp_enable_preunique | on | Enable 2-phase duplicate removal.
72 gp_enable_sequential_window_plans | on | Experimental feature: Enable non-parallel window plans.
73 gp_enable_sort_distinct | on | Enable duplicate removal to be performed while sorting.
74 gp_enable_sort_limit | on | Enable LIMIT operation to be performed while sorting.
75 gp_external_enable_exec | on | Enable selecting from an external table with an EXECUTE clause.
76 gp_external_grant_privileges | off | Enable non superusers to create http or gpfdist external tables.
77 gp_external_max_segs | 64 | Maximum number of segments that connect to a single gpfdist URL.
78 gp_filerep_tcp_keepalives_count | 2 | Maximum number of TCP keepalive retransmits for FileRep connection.
79 gp_filerep_tcp_keepalives_idle | 1min | Seconds between issuing TCP keepalives for FileRep connection.
80 gp_filerep_tcp_keepalives_interval | 30s | Seconds between TCP keepalive retransmits for FileRep connection.
81 gp_fts_probe_interval | 1min | A complete probe of all segments starts each time a timer with this period expires.
82 gp_fts_probe_threadcount | 16 | Use this number of threads for probing the segments.
83 gp_fts_probe_timeout | 20s | Maximum time (in seconds) allowed for FTS to complete probing a segment.
84 gp_gpperfmon_send_interval | 1 | Interval in seconds between sending messages to gpperfmon.
85 gp_hadoop_home | | The location where Hadoop is installed in each segment.
86 gp_hadoop_target_version | gphd-1.1 | The distro/version of Hadoop that external table is connecting to.
87 gp_hashjoin_tuples_per_bucket | 5 | Target density of hashtable used by Hashjoin during execution
88 gp_idf_deduplicate | auto | Sets the mode to control inverse distribution function's de-duplicate strategy.
89 gp_interconnect_hash_multiplier | 2 | Sets the number of hash buckets used by the UDP interconnect to track connections (the 89 number of buckets is given by the product of the segment count and the hash multipliers).
90 gp_interconnect_queue_depth | 4 | Sets the maximum size of the receive queue for each connection in the UDP interconnect
91 gp_interconnect_setup_timeout | 2h | Timeout (in seconds) on interconnect setup that occurs at query start
92 gp_interconnect_type | UDP | Sets the protocol used for inter-node communication.
93 gp_log_format | csv | Sets the format for log files.
94 gp_max_csv_line_length | 1048576 | Maximum allowed length of a csv input data row in bytes
95 gp_max_databases | 16 | Sets the maximum number of databases.
96 gp_max_filespaces | 8 | Sets the maximum number of filespaces.
97 gp_max_local_distributed_cache | 1024 | Sets the number of local-distributed transactions to cache for optimizing visibility pr 97 ocessing by backends.
98 gp_max_packet_size | 8192 | Sets the max packet size for the Interconnect.
99 gp_max_tablespaces | 16 | Sets the maximum number of tablespaces.
100 gp_motion_cost_per_row | 0 | Sets the planner's estimate of the cost of moving a row between worker processes.
101 gp_num_contents_in_cluster | 2 | Sets the number of segments in the cluster.
102 gp_reject_percent_threshold | 300 | Reject limit in percent starts calculating after this number of rows processed
103 gp_reraise_signal | on | Do we attempt to dump core when a serious problem occurs.
104 gp_resqueue_memory_policy | eager_free | Sets the policy for memory allocation of queries.
105 gp_resqueue_priority | on | Enables priority scheduling.
106 gp_resqueue_priority_cpucores_per_segment | 4 | Number of processing units associated with a segment.
107 gp_resqueue_priority_sweeper_interval | 1000 | Frequency (in ms) at which sweeper process re-evaluates CPU shares.
108 gp_role | dispatch | Sets the role for the session.
109 gp_safefswritesize | 0 | Minimum FS safe write size.
110 gp_segment_connect_timeout | 10min | Maximum time (in seconds) allowed for a new worker process to start or a mirror to resp 110 ond.
111 gp_segments_for_planner | 0 | If >0, number of segment dbs for the planner to assume in its cost and size estimates.
112 gp_session_id | 170069 | Global ID used to uniquely identify a particular session in an Greenplum Database array
113 gp_set_proc_affinity | off | On postmaster startup, attempt to bind postmaster to a processor
114 gp_standby_dbid | 0 | Sets DBID of standby master.
115 gp_statistics_pullup_from_child_partition | on | This guc enables the planner to utilize statistics from partitions in planning queries 115 on the parent.
116 gp_statistics_use_fkeys | on | This guc enables the planner to utilize statistics derived from foreign key relationshi 116 ps.
117 gp_subtrans_warn_limit | 16777216 | Sets the warning limit on number of subtransactions in a transaction.
118 gp_vmem_idle_resource_timeout | 18s | Sets the time a session can be idle (in milliseconds) before we release gangs on the se 118 gment DBs to free resources.
119 gp_vmem_protect_limit | 8192 | Virtual memory limit (in MB) of Greenplum memory protection.
120 gp_vmem_protect_segworker_cache_limit | 500 | Max virtual memory limit (in MB) for a segworker to be cachable.
121 gp_workfile_checksumming | on | Enable checksumming on the executor work files in order to catch possible faulty writes 121 caused by your disk drivers.
122 gp_workfile_compress_algorithm | none | Specify the compression algorithm that work files in the query executor use.
123 gpperfmon_port | 8888 | Sets the port number of gpperfmon.
124 integer_datetimes | on | Datetimes are integer based.
125 IntervalStyle | postgres | Sets the display format for interval values.
126 join_collapse_limit | 20 | Sets the FROM-list size beyond which JOIN constructs are not flattened.
127 krb_caseins_users | off | Sets whether Kerberos user names should be treated as case-insensitive.
128 krb_srvname | postgres | Sets the name of the Kerberos service.
129 lc_collate | en_US.utf8 | Shows the collation order locale.
130 lc_ctype | en_US.utf8 | Shows the character classification and case conversion locale.
131 lc_messages | en_US.utf8 | Sets the language in which messages are displayed.
132 lc_monetary | en_US.utf8 | Sets the locale for formatting monetary amounts.
133 lc_numeric | en_US.utf8 | Sets the locale for formatting numbers.
134 lc_time | en_US.utf8 | Sets the locale for formatting date and time values.
135 listen_addresses | * | Sets the host name or IP address(es) to listen to.
136 local_preload_libraries | | Lists shared libraries to preload into each backend.
137 log_autostats | off | Logs details of auto-stats issued ANALYZEs.
138 log_connections | on | Logs each successful connection.
139 log_disconnections | on | Logs end of a session, including duration.
140 log_dispatch_stats | off | Writes dispatcher performance statistics to the server log.
141 log_duration | off | Logs the duration of each completed SQL statement.
142 log_error_verbosity | default | Sets the verbosity of logged messages.
143 log_executor_stats | off | Writes executor performance statistics to the server log.
144 log_hostname | off | Logs the host name in the connection logs.
145 log_min_duration_statement | -1 | Sets the minimum execution time in milliseconds above which statements will be logged.
146 log_min_error_statement | error | Causes all statements generating error at or above this level to be logged.
147 log_min_messages | warning | Sets the message levels that are logged.
148 log_parser_stats | off | Writes parser performance statistics to the server log.
149 log_planner_stats | off | Writes planner performance statistics to the server log.
150 log_rotation_age | 1d | Automatic log file rotation will occur after N minutes
151 log_rotation_size | 0 | Automatic log file rotation will occur after N kilobytes
152 log_statement | all | Sets the type of statements logged.
153 log_statement_stats | off | Writes cumulative performance statistics to the server log.
154 log_timezone | US/Eastern | Sets the time zone to use in log messages.
155 log_truncate_on_rotation | off | Truncate existing log files of same name during log rotation.
156 maintenance_work_mem | 64MB | Sets the maximum memory to be used for maintenance operations.
157 max_appendonly_tables | 10000 | Maximum number of different (unrelated) append only tables that can participate in writ 157 ing data concurrently.
158 max_connections | 250 | Sets the maximum number of concurrent connections.
159 max_files_per_process | 1000 | Sets the maximum number of simultaneously open files for each server process.
160 max_fsm_pages | 200000 | Sets the maximum number of disk pages for which free space is tracked.
161 max_fsm_relations | 1000 | Sets the maximum number of tables and indexes for which free space is tracked.
162 max_function_args | 100 | Shows the maximum number of function arguments.
163 max_identifier_length | 63 | Shows the maximum identifier length
164 max_index_keys | 32 | Shows the maximum number of index keys.
165 max_locks_per_transaction | 128 | Sets the maximum number of locks per transaction.
166 max_prepared_transactions | 250 | Sets the maximum number of simultaneously prepared transactions.
167 max_resource_portals_per_transaction | 64 | Maximum number of resource queues.
168 max_resource_queues | 9 | Maximum number of resource queues.
169 max_stack_depth | 2MB | Sets the maximum stack depth, in kilobytes.
170 max_statement_mem | 2000MB | Sets the maximum value for statement_mem setting.
171 max_work_mem | 1000MB | Sets the maximum value for work_mem setting.
172 password_encryption | on | Encrypt passwords.
173 pljava_classpath | | classpath used by the the JVM
174 port | 5432 | Sets the TCP port the server listens on.
175 random_page_cost | 100 | Sets the planner's estimate of the cost of a nonsequentially fetched disk page.
176 regex_flavor | advanced | Sets the regular expression "flavor".
177 resource_cleanup_gangs_on_wait | on | Enable idle gang cleanup before resource lockwait.
178 resource_select_only | off | Enable resource locking of SELECT only.
179 search_path | "$user",public | Sets the schema search order for names that are not schema-qualified.
180 seq_page_cost | 1 | Sets the planner's estimate of the cost of a sequentially fetched disk page.
181 server_encoding | UTF8 | Sets the server (database) character set encoding.
182 server_version | 8.2.15 | Shows the server version.
183 server_version_num | 80215 | Shows the server version as an integer.
184 shared_buffers | 125MB | Sets the number of shared memory buffers used by the server.
185 ssl | off | Enables SSL connections.
186 standard_conforming_strings | off | '...' strings treat backslashes literally.
187 statement_mem | 125MB | Sets the memory to be reserved for a statement.
188 statement_timeout | 0 | Sets the maximum allowed duration (in milliseconds) of any statement.
189 stats_queue_level | off | Collects resource queue-level statistics on database activity.
190 superuser_reserved_connections | 3 | Sets the number of connection slots reserved for superusers.
191 tcp_keepalives_count | 0 | Maximum number of TCP keepalive retransmits.
192 tcp_keepalives_idle | 0 | Seconds between issuing TCP keepalives.
193 tcp_keepalives_interval | 0 | Seconds between TCP keepalive retransmits.
194 temp_buffers | 1024 | Sets the maximum number of temporary buffers used by each session.
195 TimeZone | US/Eastern | Sets the time zone for displaying and interpreting time stamps.
196 timezone_abbreviations | Default | Selects a file of time zone abbreviations.
197 track_activities | on | Collects information about executing commands.
198 track_counts | off | Collects statistics on database activity.
199 transaction_isolation | read committed | Sets the current transaction's isolation level.
200 transaction_read_only | off | Sets the current transaction's read-only status.
201 transform_null_equals | off | Treats "expr=NULL" as "expr IS NULL".
202 unix_socket_group | | Sets the owning group of the Unix-domain socket.
203 unix_socket_permissions | 511 | Sets the access permissions of the Unix-domain socket.
204 update_process_title | on | Updates the process title to show the active SQL command.
205 vacuum_cost_delay | 0 | Vacuum cost delay in milliseconds.
206 vacuum_cost_limit | 200 | Vacuum cost amount available before napping.
207 vacuum_cost_page_dirty | 20 | Vacuum cost for a page dirtied by vacuum.
208 vacuum_cost_page_hit | 1 | Vacuum cost for a page found in the buffer cache.
209 vacuum_cost_page_miss | 10 | Vacuum cost for a page not found in the buffer cache.
210 vacuum_freeze_min_age | 100000000 | Minimum age at which VACUUM should freeze a table row.
211 work_mem | 32MB | Sets the maximum memory to be used for query workspaces.
212 (209 rows)
SHOW lists the settings for the master instance only. To see the value of a particular parameter across the entire system (master and all segments), use the gpconfig
utility. For example:
$ gpconfig --show max_connections