- publishing free software manuals
The PostgreSQL 9.0 Reference Manual - Volume 3 - Server Administration Guide
by The PostgreSQL Global Development Group
Paperback (6"x9"), 274 pages
ISBN 9781906966072
RRP £9.95 ($14.95)

Sales of this book support the PostgreSQL project! Get a printed copy>>>

4.6.4 Other Planner Options

default_statistics_target (integer)
Sets the default statistics target for table columns without a column-specific target set via ALTER TABLE SET STATISTICS. Larger values increase the time needed to do ANALYZE, but might improve the quality of the planner's estimates. The default is 100. For more information on the use of statistics by the PostgreSQL query planner, refer to Volume 1A: 12.2 Statistics Used by the Planner.
constraint_exclusion (enum)
Controls the query planner's use of table constraints to optimize queries. The allowed values of constraint_exclusion are on (examine constraints for all tables), off (never examine constraints), and partition (examine constraints only for inheritance child tables and UNION ALL subqueries). partition is the default setting. It is often used with inheritance and partitioned tables to improve performance. When this parameter allows it for a particular table, the planner compares query conditions with the table's CHECK constraints, and omits scanning tables for which the conditions contradict the constraints. For example:
CREATE TABLE parent(key integer, ...);
CREATE TABLE child1000(check (key between 1000 and 1999)) 
  INHERITS(parent);
CREATE TABLE child2000(check (key between 2000 and 2999)) 
  INHERITS(parent);
...
SELECT * FROM parent WHERE key = 2400;
With constraint exclusion enabled, this SELECT will not scan child1000 at all, improving performance. Currently, constraint exclusion is enabled by default only for cases that are often used to implement table partitioning. Turning it on for all tables imposes extra planning overhead that is quite noticeable on simple queries, and most often will yield no benefit for simple queries. If you have no partitioned tables you might prefer to turn it off entirely. Refer to Volume 1A: 3.9.4 Partitioning and Constraint Exclusion for more information on using constraint exclusion and partitioning.
cursor_tuple_fraction (floating point)
Sets the planner's estimate of the fraction of a cursor's rows that will be retrieved. The default is 0.1. Smaller values of this setting bias the planner towards using “fast start” plans for cursors, which will retrieve the first few rows quickly while perhaps taking a long time to fetch all rows. Larger values put more emphasis on the total estimated time. At the maximum setting of 1.0, cursors are planned exactly like regular queries, considering only the total estimated time and not how soon the first rows might be delivered.
from_collapse_limit (integer)
The 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 might yield inferior query plans. The default is eight. For more information see Volume 1A: 12.3 Controlling the Planner with Explicit JOIN Clauses. Setting this value to geqo_threshold or more may trigger use of the GEQO planner, resulting in nondeterministic plans. See section 4.6.3 Genetic Query Optimizer.
join_collapse_limit (integer)
The planner will rewrite explicit JOIN constructs (except FULL JOINs) into lists of FROM items whenever a list of no more than this many items would result. Smaller values reduce planning time but might yield inferior query plans. By default, this variable is set the same as from_collapse_limit, which is appropriate for most uses. Setting it to 1 prevents any reordering of explicit JOINs. Thus, the explicit join order specified in the query will be the actual order in which the relations are joined. Because the query planner does not always choose the optimal join order, advanced users can elect to temporarily set this variable to 1, and then specify the join order they desire explicitly. For more information see Volume 1A: 12.3 Controlling the Planner with Explicit JOIN Clauses. Setting this value to geqo_threshold or more may trigger use of the GEQO planner, resulting in nondeterministic plans. See section 4.6.3 Genetic Query Optimizer.
ISBN 9781906966072The PostgreSQL 9.0 Reference Manual - Volume 3 - Server Administration GuideSee the print edition