Query Tuning Parameters

Pivotal Query Optimizer Configuration Parameters

  • optimizer
  • optimizer_analyze_root_partition
  • optimizer_control

Query Plan Operator Control Parameters

The following parameters control the types of plan operations the legacy query optimizer can use. Enable or disable plan operations to force the legacy query optimizer to choose a different plan. This is useful for testing and comparing query performance using different plan types.

enable_bitmapscan

enable_groupagg

enable_hashagg

enable_hashjoin

enable_indexscan

enable_mergejoin

enable_nestloop

enable_seqscan

enable_sort

enable_tidscan

gp_enable_adaptive_nestloop

gp_enable_agg_distinct

gp_enable_agg_distinct_pruning

gp_enable_direct_dispatch

gp_enable_fallback_plan

gp_enable_fast_sri

gp_enable_groupext_distinct_ gather

gp_enable_groupext_distinct_ pruning

gp_enable_multiphase_agg

gp_enable_predicate_ propagation

gp_enable_preunique

gp_enable_sequential_window_ plans

gp_enable_sort_distinct

gp_enable_sort_limit

Legacy Query Optimizer Costing Parameters

Warning: Pivotal recommends that you do not adjust these query costing parameters. They are tuned to reflect Greenplum Database hardware configurations and typical workloads. All of these parameters are related. Changing one without changing the others can have adverse effects on performance.

cpu_index_tuple_cost

cpu_operator_cost

cpu_tuple_cost

cursor_tuple_fraction

effective_cache_size

gp_motion_cost_per_row

gp_segments_for_planner

random_page_cost

seq_page_cost

Database Statistics Sampling Parameters

These parameters adjust the amount of data sampled by an ANALYZE operation. Adjusting these parameters affects statistics collection system-wide. You can configure statistics collection on particular tables and columns by using the ALTER TABLE SET STATISTICS clause.

  • default_statistics_target
  • gp_analyze_relative_error

Sort Operator Configuration Parameters

  • gp_enable_sort_distinct
  • gp_enable_sort_limit

Aggregate Operator Configuration Parameters

gp_enable_agg_distinct

gp_enable_agg_distinct_pruning

gp_enable_multiphase_agg

gp_enable_preunique

gp_enable_groupext_distinct_ gather

gp_enable_groupext_distinct_ pruning

gp_workfile_compress_algorithm

Join Operator Configuration Parameters

join_collapse_limit

gp_adjust_selectivity_for_outerjoins

gp_hashjoin_tuples_per_bucket

gp_statistics_use_fkeys

gp_workfile_compress_algorithm

Other Legacy Query Optimizer Configuration Parameters

  • from_collapse_limit
  • gp_enable_predicate_propagation
  • gp_max_plan_size
  • gp_statistics_pullup_from_child_partition