|PostgreSQL Reference Manual - Volume 3 - Server Administration Guide|
by The PostgreSQL Global Development Group
Paperback (6"x9"), 204 pages
RRP £13.95 ($24.95)
Sales of this book support the PostgreSQL project! Get a printed copy>>>
4.6.4 Other Planner Options
Sets the default statistics target for table columns that have
not had a column-specific target set via
ALTER TABLE SET STATISTICS. Larger values increase the time needed to do
ANALYZE, but may improve the quality of the planner's estimates. The default is 10. For more information on the use of statistics by the PostgreSQL query planner, refer to Volume 1: Statistics Used by the Planner.
Enables or disables the query planner's use of table constraints to
optimize queries. The default is
off. When this parameter is
on, the planner compares query conditions with table
CHECKconstraints, 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
SELECTwill not scan
child1000at all. This can improve performance when inheritance is used to build partitioned tables. Currently,
constraint_exclusionis disabled by default because it risks incorrect results if query plans are cached--if a table constraint is changed or dropped, the previously generated plan might now be wrong, and there is no built-in mechanism to force re-planning. (This deficiency will probably be addressed in a future PostgreSQL release.) Another reason for keeping it off is that the constraint checks are relatively expensive, and in many circumstances will yield no savings. It is recommended to turn this on only if you are actually using partitioned tables designed to take advantage of the feature. Refer to Volume 1: Partitioning for more information on using constraint exclusion and partitioning.
The planner will merge sub-queries into upper queries if the
FROMlist would have no more than this many items. Smaller values reduce planning time but may yield inferior query plans. The default is eight. It is usually wise to keep this less than
geqo_threshold. For more information see Volume 1: Controlling the Planner with Explicit JOIN Clauses.
The planner will rewrite explicit
FULL JOINs) into lists of
FROMitems whenever a list of no more than this many items would result. Smaller values reduce planning time but may 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. The query planner does not always choose the optimal join order; advanced users may elect to temporarily set this variable to 1, and then specify the join order they desire explicitly. For more information see Volume 1: Controlling the Planner with Explicit JOIN Clauses.
|ISBN 0954612043||PostgreSQL Reference Manual - Volume 3 - Server Administration Guide||See the print edition|