Configuration Parameters

Descriptions of the Greenplum Database server configuration parameters listed alphabetically.

• add_missing_from
• application_name
• array_nulls
• authentication_timeout
• backslash_quote
• block_size
• bonjour_name
• check_function_bodies
• client_encoding
• client_min_messages
• cpu_index_tuple_cost
• cpu_operator_cost
• cpu_tuple_cost
• cursor_tuple_fraction
• custom_variable_classes
• data_checksums
• DateStyle
• db_user_namespace
• deadlock_timeout
• debug_assertions
• debug_pretty_print
• debug_print_parse
• debug_print_plan
• debug_print_prelim_plan
• debug_print_rewritten
• debug_print_slice_table
• default_statistics_target
• default_tablespace
• default_transaction_isolation
• default_transaction_read_only
• dtx_phase2_retry_count
• dynamic_library_path
• effective_cache_size
• enable_bitmapscan
• enable_groupagg
• enable_hashagg
• enable_hashjoin
• enable_indexscan
• enable_mergejoin
• enable_nestloop
• enable_seqscan
• enable_sort
• enable_tidscan
• escape_string_warning
• explain_pretty_print
• extra_float_digits
• filerep_mirrorvalidation_during_resync
• from_collapse_limit
• gp_adjust_selectivity_for_outerjoins
• gp_analyze_relative_error
• gp_appendonly_compaction
• gp_appendonly_compaction_threshold
• gp_autostats_mode
• gp_autostats_mode_in_functions
• gp_autostats_on_change_threshold
• gp_backup_directIO
• gp_backup_directIO_read_chunk_mb
• gp_cached_segworkers_threshold
• gp_command_count
• gp_connection_send_timeout
• gp_connections_per_thread
• gp_content
• gp_create_table_random_default_distribution
• gp_dbid
• gp_debug_linger
• gp_default_storage_options
• gp_dynamic_partition_pruning
• gp_email_from
• gp_email_smtp_password
• gp_email_smtp_server
• gp_email_smtp_userid
• gp_email_to
• gp_enable_agg_distinct
• gp_enable_agg_distinct_pruning
• gp_enable_direct_dispatch
• gp_enable_exchange_default_partition
• gp_enable_fallback_plan
• gp_enable_fast_sri
• gp_enable_gpperfmon
• gp_enable_groupext_distinct_gather
• gp_enable_groupext_distinct_pruning
• gp_enable_multiphase_agg
• gp_enable_predicate_propagation
• gp_enable_preunique
• gp_enable_relsize_collection
• gp_enable_segment_copy_checking
• gp_enable_sequential_window_plans (Beta)
• gp_enable_sort_distinct
• gp_enable_sort_limit
• gp_external_enable_exec
• gp_external_max_segs
• gp_external_enable_filter_pushdown
• gp_filerep_tcp_keepalives_count
• gp_filerep_tcp_keepalives_idle
• gp_filerep_tcp_keepalives_interval
• gp_fts_probe_interval
• gp_fts_probe_retries
• gp_fts_probe_threadcount
• gp_fts_probe_timeout
• gp_gpperfmon_send_interval
• gpperfmon_log_alert_level
• gp_hadoop_home
• gp_hadoop_target_version
• gp_hashjoin_tuples_per_bucket
• gp_idf_deduplicate
• gp_ignore_error_table
• gp_initial_bad_row_limit
• gp_interconnect_debug_retry_interval
• gp_interconnect_fc_method
• gp_interconnect_hash_multiplier
• gp_interconnect_queue_depth
• gp_interconnect_setup_timeout
• gp_interconnect_snd_queue_depth
• gp_interconnect_type
• gp_log_interconnect
• gp_log_format
• gp_log_fts
• gp_log_interconnect
• gp_log_gang
• gp_max_csv_line_length
• gp_max_databases
• gp_max_filespaces
• gp_max_local_distributed_cache
• gp_max_packet_size
• gp_max_plan_size
• gp_max_slices
• gp_max_tablespaces
• gp_motion_cost_per_row
• gp_num_contents_in_cluster
• gp_recursive_cte_prototype
• gp_reject_percent_threshold
• gp_reraise_signal
• gp_resgroup_memory_policy
• gp_resource_group_bypass
• gp_resource_group_cpu_limit
• gp_resource_group_memory_limit
• gp_resource_manager
• gp_resqueue_memory_policy
• gp_resqueue_priority
• gp_resqueue_priority_cpucores_per_segment
• gp_resqueue_priority_sweeper_interval
• gp_role
• gp_safefswritesize
• gp_segment_connect_timeout
• gp_segments_for_planner
• gp_server_version
• gp_server_version_num
• gp_session_id
• gp_set_proc_affinity
• gp_set_read_only
• gp_snmp_community
• gp_snmp_monitor_address
• gp_snmp_use_inform_or_trap
• gp_statistics_pullup_from_child_partition
• gp_statistics_use_fkeys
• gp_vmem_idle_resource_timeout
• gp_vmem_protect_limit
• gp_vmem_protect_segworker_cache_limit
• gp_workfile_checksumming
• gp_workfile_compress_algorithm
• gp_workfile_limit_files_per_query
• gp_workfile_limit_per_query
• gp_workfile_limit_per_segment
• gpperfmon_port
• ignore_checksum_failure
• integer_datetimes
• IntervalStyle
• join_collapse_limit
• keep_wal_segments
• krb_caseins_users
• krb_server_keyfile
• krb_srvname
• lc_collate
• lc_ctype
• lc_messages
• lc_monetary
• lc_numeric
• lc_time
• listen_addresses
• local_preload_libraries
• log_autostats
• log_connections
• log_disconnections
• log_dispatch_stats
• log_duration
• log_error_verbosity
• log_executor_stats
• log_hostname
• log_min_duration_statement
• log_min_error_statement
• log_min_messages
• log_parser_stats
• log_planner_stats
• log_rotation_age
• log_rotation_size
• log_statement
• log_statement_stats
• log_temp_files
• log_timezone
• log_truncate_on_rotation
• max_appendonly_tables
• max_connections
• max_files_per_process
• max_fsm_pages
• max_fsm_relations
• max_function_args
• max_identifier_length
• max_index_keys
• max_locks_per_transaction
• max_prepared_transactions
• max_resource_portals_per_transaction
• max_resource_queues
• max_stack_depth
• max_statement_mem
• memory_spill_ratio
• optimizer
• optimizer_array_expansion_threshold
• optimizer_analyze_root_partition
• optimizer_control
• optimizer_cost_model
• optimizer_cte_inlining_bound
• optimizer_dpe_stats
• optimizer_enable_associativity
• optimizer_enable_dml
• optimizer_enable_master_only_queries
• optimizer_force_agg_skew_avoidance
• optimizer_force_multistage_agg
• optimizer_force_three_stage_scalar_dqa
• optimizer_join_arity_for_associativity_commutativity
• optimizer_join_order
• optimizer_join_order_threshold
• optimizer_mdcache_size
• optimizer_metadata_caching
• optimizer_minidump
• optimizer_nestloop_factor
• optimizer_parallel_union
• optimizer_penalize_skew
• optimizer_print_missing_stats
• optimizer_print_optimization_stats
• optimizer_sort_factor
• optimizer_use_gpdb_allocators
• optimizer_xform_bind_threshold
• password_hash_algorithm
• pgcrypto.fips
• pgstat_track_activity_query_size
• pljava_classpath
• pljava_classpath_insecure
• pljava_statement_cache_size
• pljava_release_lingering_savepoints
• pljava_vmoptions
• port
• random_page_cost
• readable_external_table_timeout
• repl_catchup_within_range
• replication_timeout
• regex_flavor
• resource_cleanup_gangs_on_wait
• resource_select_only
• runaway_detector_activation_percent
• search_path
• seq_page_cost
• server_encoding
• server_version
• server_version_num
• shared_buffers
• shared_preload_libraries
• ssl
• ssl_ciphers
• standard_conforming_strings
• statement_mem
• statement_timeout
• stats_queue_level
• superuser_reserved_connections
• tcp_keepalives_count
• tcp_keepalives_idle
• tcp_keepalives_interval
• temp_buffers
• TimeZone
• timezone_abbreviations
• track_activities
• track_activity_query_size
• track_counts
• transaction_isolation
• transaction_read_only
• transform_null_equals
• unix_socket_directory
• unix_socket_group
• unix_socket_permissions
• update_process_title
• vacuum_cost_delay
• vacuum_cost_limit
• vacuum_cost_page_dirty
• vacuum_cost_page_hit
• vacuum_cost_page_miss
• vacuum_freeze_min_age
• validate_previous_free_tid
• verify_gpfdists_cert
• vmem_process_interrupt
• wal_receiver_status_interval
• writable_external_table_bufsize
• xid_stop_limit
• xid_warn_limit
• xmlbinary
• xmloption

add_missing_from

Automatically adds missing table references to FROM clauses. Present for compatibility with releases of PostgreSQL prior to 8.1, where this behavior was allowed by default.

application_name 0

Sets the application name for a client session. For example, if connecting via psql, this will be set to psql. Setting an application name allows it to be reported in log messages and statistics views.

array_nulls

This controls whether the array input parser recognizes unquoted NULL as specifying a null array element. By default, this is on, allowing array values containing null values to be entered. Greenplum Database versions before 3.0 did not support null values in arrays, and therefore would treat NULL as specifying a normal array element with the string value ‘NULL’.

authentication_timeout

Maximum time to complete client authentication. This prevents hung clients from occupying a connection indefinitely.

backslash_quote

This controls whether a quote mark can be represented by \‘ in a string literal. The preferred, SQL-standard way to represent a quote mark is by doubling it (‘’) but PostgreSQL has historically also accepted \‘. However, use of \‘ creates security risks because in some client character set encodings, there are multibyte characters in which the last byte is numerically equivalent to ASCII .

block_size

Reports the size of a disk block.

bonjour_name

Specifies the Bonjour broadcast name. By default, the computer name is used, specified as an empty string. This option is ignored if the server was not compiled with Bonjour support.

check_function_bodies

When set to off, deactivates validation of the function body string during CREATE FUNCTION. Deactivating validation is occasionally useful to avoid problems such as forward references when restoring function definitions from a dump.

client_encoding

Sets the client-side encoding (character set). The default is to use the same as the database encoding. See Supported Character Sets in the PostgreSQL documentation.

client_min_messages

Controls which message levels are sent to the client. Each level includes all the levels that follow it. The later the level, the fewer messages are sent.

cpu_index_tuple_cost

For the legacy query optimizer (planner), sets the estimate of the cost of processing each index row during an index scan. This is measured as a fraction of the cost of a sequential page fetch.

cpu_operator_cost

For the legacy query optimizer (planner), sets the estimate of the cost of processing each operator in a WHERE clause. This is measured as a fraction of the cost of a sequential page fetch.

cpu_tuple_cost

For the legacy query optimizer (planner), Sets the estimate of the cost of processing each row during a query. This is measured as a fraction of the cost of a sequential page fetch.

cursor_tuple_fraction

Tells the legacy query optimizer (planner) how many rows are expected to be fetched in a cursor query, thereby allowing the legacy optimizer to use this information to optimize the query plan. The default of 1 means all rows will be fetched.

custom_variable_classes

Specifies one or several class names to be used for custom variables. A custom variable is a variable not normally known to the server but used by some add-on modules. Such variables must have names consisting of a class name, a dot, and a variable name.

data_checksums

Reports whether checksums are enabled for heap data storage in the database system. Checksums for heap data are activated or deactivated when the database system is initialized and cannot be changed.

Heap data pages store heap tables, catalog tables, indexes, and database metadata. Append-optimized storage has built-in checksum support that is unrelated to this parameter.

Greenplum Database uses checksums to prevent loading data corrupted in the file system into memory managed by database processes. When heap data checksums are enabled, Greenplum Database computes and stores checksums on heap data pages when they are written to disk. When a page is retrieved from disk, the checksum is verified. If the verification fails, an error is generated and the page is not permitted to load into managed memory.

If the ignore_checksum_failure configuration parameter has been set to on, a failed checksum verification generates a warning, but the page is allowed to be loaded into managed memory. If the page is then updated, it is flushed to disk and replicated to the mirror. This can cause data corruption to propagate to the mirror and prevent a complete recovery. Because of the potential for data loss, the ignore_checksum_failure parameter should only be enabled when needed to recover data. See ignore_checksum_failure for more information.

DateStyle

Sets the display format for date and time values, as well as the rules for interpreting ambiguous date input values. This variable contains two independent components: the output format specification and the input/output specification for year/month/day ordering.

db_user_namespace

This enables per-database user names. If on, you should create users as username@dbname. To create ordinary global users, simply append @ when specifying the user name in the client.

deadlock_timeout

The time to wait on a lock before checking to see if there is a deadlock condition. On a heavily loaded server you might want to raise this value. Ideally the setting should exceed your typical transaction time, so as to improve the odds that a lock will be released before the waiter decides to check for deadlock.

debug_assertions

Turns on various assertion checks.

debug_pretty_print

Indents debug output to produce a more readable but much longer output format. client_min_messages or log_min_messages must be DEBUG1 or lower.

debug_print_parse

For each executed query, prints the resulting parse tree. client_min_messages or log_min_messages must be DEBUG1 or lower.

debug_print_plan

For each executed query, prints the Greenplum parallel query execution plan. client_min_messages or log_min_messages must be DEBUG1 or lower.

debug_print_prelim_plan

For each executed query, prints the preliminary query plan. client_min_messages or log_min_messages must be DEBUG1 or lower.

debug_print_rewritten

For each executed query, prints the query rewriter output. client_min_messages or log_min_messages must be DEBUG1 or lower.

debug_print_slice_table

For each executed query, prints the Greenplum query slice plan. client_min_messages or log_min_messages must be DEBUG1 or lower.

default_statistics_target

Sets the default statistics sampling target (the number of values that are stored in the list of common values) for table columns that have not had a column-specific target set via ALTER TABLE SET STATISTICS. Larger values may improve the quality of the legacy query optimizer (planner) estimates.

default_tablespace

The default tablespace in which to create objects (tables and indexes) when a CREATE command does not explicitly specify a tablespace.

default_transaction_isolation

Controls the default isolation level of each new transaction.

default_transaction_read_only

Controls the default read-only status of each new transaction. A read-only SQL transaction cannot alter non-temporary tables.

dtx_phase2_retry_count

The maximum number of retries attempted by Greenplum Database during the second phase of a two phase commit. When one or more segments cannot successfully complete the commit phase, the master retries the commit a maximum of dtx_phase2_retry_count times. If the commit continues to fail on the last retry attempt, the master generates a PANIC.

When the network is unstable, the master may be unable to connect to one or more segments; increasing the number of two phase commit retries may improve high availability of Greenplum when the master encounters transient network issues.

dynamic_library_path

If a dynamically loadable module needs to be opened and the file name specified in the CREATE FUNCTION or LOAD command does not have a directory component (i.e. the name does not contain a slash), the system will search this path for the required file. The compiled-in PostgreSQL package library directory is substituted for $libdir. This is where the modules provided by the standard PostgreSQL distribution are installed.

effective_cache_size

Sets the assumption about the effective size of the disk cache that is available to a single query for the legacy query optimizer (planner). This is factored into estimates of the cost of using an index; a higher value makes it more likely index scans will be used, a lower value makes it more likely sequential scans will be used. This parameter has no effect on the size of shared memory allocated by a Greenplum server instance, nor does it reserve kernel disk cache; it is used only for estimation purposes.

Set this parameter to a number of block_size blocks (default 32K) with no units; for example, 8192 for 256MB. You can also directly specify the size of the effective cache; for example, '1GB' specifies a size of 32768 blocks. The gpconfig utility and SHOW command display the effective cache size value in units such as ‘GB’, ‘MB’, or ‘kB’.

enable_bitmapscan

Activates or deactivates the use of bitmap-scan plan types by the legacy query optimizer (planner). Note that this is different than a Bitmap Index Scan. A Bitmap Scan means that indexes will be dynamically converted to bitmaps in memory when appropriate, giving faster index performance on complex queries against very large tables. It is used when there are multiple predicates on different indexed columns. Each bitmap per column can be compared to create a final list of selected tuples.

enable_groupagg

Activates or deactivates the use of group aggregation plan types by the legacy query optimizer (planner).

enable_hashagg

Activates or deactivates the use of hash aggregation plan types by the legacy query optimizer (planner).

enable_hashjoin

Activates or deactivates the use of hash-join plan types by the legacy query optimizer (planner).

enable_indexscan

Activates or deactivates the use of index-scan plan types by the legacy query optimizer (planner).

enable_mergejoin

Activates or deactivates the use of merge-join plan types by the legacy query optimizer (planner). Merge join is based on the idea of sorting the left- and right-hand tables into order and then scanning them in parallel. So, both data types must be capable of being fully ordered, and the join operator must be one that can only succeed for pairs of values that fall at the ‘same place’ in the sort order. In practice this means that the join operator must behave like equality.

enable_nestloop

Activates or deactivates the use of nested-loop join plans by the legacy query optimizer (planner). It’s not possible to suppress nested-loop joins entirely, but turning this variable off discourages the legacy optimizer from using one if there are other methods available.

enable_seqscan

Activates or deactivates the use of sequential scan plan types by the legacy query optimizer (planner). It’s not possible to suppress sequential scans entirely, but turning this variable off discourages the legacy optimizer from using one if there are other methods available.

enable_sort

Activates or deactivates the use of explicit sort steps by the legacy query optimizer (planner). It’s not possible to suppress explicit sorts entirely, but turning this variable off discourages the legacy optimizer from using one if there are other methods available.

enable_tidscan

Activates or deactivates the use of tuple identifier (TID) scan plan types by the legacy query optimizer (planner).

escape_string_warning

When on, a warning is issued if a backslash () appears in an ordinary string literal (‘…’ syntax). Escape string syntax (E’…’) should be used for escapes, because in future versions, ordinary strings will have the SQL standard-conforming behavior of treating backslashes literally.

explain_pretty_print

Determines whether EXPLAIN VERBOSE uses the indented or non-indented format for displaying detailed query-tree dumps.

extra_float_digits

Adjusts the number of digits displayed for floating-point values, including float4, float8, and geometric data types. The parameter value is added to the standard number of digits. The value can be set as high as 3, to include partially-significant digits; this is especially useful for dumping float data that needs to be restored exactly. Or it can be set negative to suppress unwanted digits.

filerep_mirrorvalidation_during_resync

The default setting false improves Greenplum Database performance during incremental resynchronization of segment mirrors. Setting the value to true enables checking for the existence of files for all relations on the segment mirror during incremental resynchronization. Checking for files degrades performance of the incremental resynchronization process but provides a minimal check of database objects.

from_collapse_limit

The legacy query optimizer (planner) will merge sub-queries into upper queries if the resulting FROM list would have no more than this many items. Smaller values reduce planning time but may yield inferior query plans.

gp_adjust_selectivity_for_outerjoins

Enables the selectivity of NULL tests over outer joins.

gp_analyze_relative_error

Sets the estimated acceptable error in the cardinality of the table “ a value of 0.5 is supposed to be equivalent to an acceptable error of 50% (this is the default value used in PostgreSQL). If the statistics collected during ANALYZE are not producing good estimates of cardinality for a particular table attribute, decreasing the relative error fraction (accepting less error) tells the system to sample more rows.

gp_appendonly_compaction

Enables compacting segment files during VACUUM commands. When deactivated, VACUUM only truncates the segment files to the EOF value, as is the current behavior. The administrator may want to deactivate compaction in high I/O load situations or low space situations.

gp_appendonly_compaction_threshold

Specifies the threshold ratio (as a percentage) of hidden rows to total rows that triggers compaction of the segment file when VACUUM is run without the FULL option (a lazy vacuum). If the ratio of hidden rows in a segment file on a segment is less than this threshold, the segment file is not compacted, and a log message is issued.

gp_autostats_mode

Specifies the mode for triggering automatic statistics collection with ANALYZE. The on_no_stats option triggers statistics collection for CREATE TABLE AS SELECT, INSERT, or COPY operations on any table that has no existing statistics.

The on_change option triggers statistics collection only when the number of rows affected exceeds the threshold defined by gp_autostats_on_change_threshold. Operations that can trigger automatic statistics collection with on_change are:

CREATE TABLE AS SELECT

UPDATE

DELETE

INSERT

COPY

Default is on_no_stats.

Note: For partitioned tables, automatic statistics collection is not triggered if data is inserted from the top-level parent table of a partitioned table.

Automatic statistics collection is triggered if data is inserted directly in a leaf table (where the data is stored) of the partitioned table. Statistics are collected only on the leaf table.

gp_autostats_mode_in_functions

Specifies the mode for triggering automatic statistics collection with ANALYZE for statements in procedural language functions. The none option deactivates statistics collection. The on_no_stats option triggers statistics collection for CREATE TABLE AS SELECT, INSERT, or COPY operations that are executed in functions on any table that has no existing statistics.

The on_change option triggers statistics collection only when the number of rows affected exceeds the threshold defined by gp_autostats_on_change_threshold. Operations in functions that can trigger automatic statistics collection with on_change are:

CREATE TABLE AS SELECT

UPDATE

DELETE

INSERT

COPY

gp_autostats_on_change_threshold

Specifies the threshold for automatic statistics collection when gp_autostats_mode is set to on_change. When a triggering table operation affects a number of rows exceeding this threshold, ANALYZEis added and statistics are collected for the table.

gp_backup_directIO

Direct I/O allows Greenplum Database to bypass the buffering of memory within the file system cache for backup. When Direct I/O is used for a file, data is transferred directly from the disk to the application buffer, without the use of the file buffer cache.

Direct I/O is supported only on Red Hat Enterprise Linux, CentOS, and SUSE.

gp_backup_directIO_read_chunk_mb

Sets the chunk size in MB when Direct I/O is enabled with gp_backup_directIO. The default chunk size is 20MB.

The default value is the optimal setting. Decreasing it will increase the backup time and increasing it will result in little change to backup time.

gp_cached_segworkers_threshold

When a user starts a session with Greenplum Database and issues a query, the system creates groups or ‘gangs’ of worker processes on each segment to do the work. After the work is done, the segment worker processes are destroyed except for a cached number which is set by this parameter. A lower setting conserves system resources on the segment hosts, but a higher setting may improve performance for power-users that want to issue many complex queries in a row.

gp_command_count

Shows how many commands the master has received from the client. Note that a single SQLcommand might actually involve more than one command internally, so the counter may increment by more than one for a single query. This counter also is shared by all of the segment processes working on the command.

gp_connection_send_timeout

Timeout for sending data to unresponsive Greenplum Database user clients during query processing. A value of 0 deactivates the timeout, Greenplum Database waits indefinitely for a client. When the timeout is reached, the query is cancelled with this message:

1. Could not send data to client: Connection timed out.

gp_connections_per_thread

Controls the number of asynchronous threads (worker threads) that a Greenplum Database query dispatcher (QD) generates when dispatching work to query executor processes on segment instances when processing SQL queries. The value sets the number of primary segment instances that a worker thread connects to when processing a query. For example, when the value is 2 and there are 64 segment instances, a QD generates 32 worker threads to dispatch a query plan work. Each thread is assigned to two segments.

For the default value, 0, a query dispatcher generates two types of threads: a main thread that manages the dispatch of query plan work, and an interconnect thread. The main thread also acts as a worker thread.

For a value greater than 0, a QD generates three types of threads: a main thread, one or more worker threads, and an interconnect thread. When the value is equal to or greater than the number of segment instances, a QD generates three threads: a main thread, a single worker thread, and an interconnect thread.

The value does not need to be changed from the default unless there are known throughput performance issues.

This parameter is master only and changing it requires a server restart.

gp_content

The local content id if a segment.

gp_create_table_random_default_distribution

Controls table creation when a Greenplum Database table is created with a CREATE TABLE or CREATE TABLE AS command that does not contain a DISTRIBUTED BY clause.

For CREATE TABLE, if the value of the parameter is off (the default), and the table creation command does not contain a DISTRIBUTED BY clause, Greenplum Database chooses the table distribution key based on the command. If the LIKE or INHERITS clause is specified in table creation command, the created table uses the same distribution key as the source or parent table.

If the value of the parameter is set to on, Greenplum Database follows these rules to create a table when the DISTRIBUTED BY clause is not specified:

• If PRIMARY KEY or UNIQUE columns are not specified, the distribution of the table is random (DISTRIBUTED RANDOMLY). Table distribution is random even if the table creation command contains the LIKE or INHERITS clause.
• If PRIMARY KEY or UNIQUE columns are specified, a DISTRIBUTED BY clause must also be specified. If a DISTRIBUTED BY clause is not specified as part of the table creation command, the command fails.

For a CREATE TABLE AS command that does not contain a distribution clause:

• If the legacy

（本文超出站点单文档体积上限，已截断）