Shulou(Shulou.com)05/31 Report--

Editor to share with you how to optimize the function of Review PG's Optimizer mechanism in PostgreSQL, I believe most people do not know much about it, so share this article for your reference. I hope you will gain a lot after reading this article. Let's learn about it together.

1. Optimizer Functions

Optimizer Functions- query optimization function

The primary entry point is planner ().

Planner () / / main entrance

Set up for recursive handling of subqueries

-subquery_planner () / / planner- > subquery_planner

Pull up sublinks and subqueries from rangetable, if possible

Canonicalize qual

Attempt to simplify WHERE clause to the most useful form; this includes

Flattening nested AND/ORs and detecting clauses that are duplicated in

Different branches of an OR.

Simplify constant expressions

Process sublinks

Convert Vars of outer query levels into Params

-grouping_planner () / / planner- > subquery_planner- > grouping_planner

Preprocess target list for non-SELECT queries

Handle UNION/INTERSECT/EXCEPT, GROUP BY, HAVING, aggregates

ORDER BY, DISTINCT, LIMIT

-query_planner () / / subquery_planner- > grouping_planner- > query_planner

Make list of base relations used in query

Split up the qual into restrictions (axi1) and joins (bauxc)

Find qual clauses that enable merge and hash joins

-make_one_rel () /... grouping_planner- > query_planner- > make_one_rel

Set_base_rel_pathlists () / / generate an access path for each RelOptInfo

Find seqscan and all index paths for each base relation

Find selectivity of columns used in joins

Make_rel_from_joinlist () / / construct connection paths using genetic algorithms or dynamic programming algorithms

Hand off join subproblems to a plugin, GEQO, or standard_join_search ()

-standard_join_search () / / this is a dynamic programming algorithm

Call join_search_one_level () for each level of join tree needed

Join_search_one_level ():

For each joinrel of the prior level, do make_rels_by_clause_joins ()

If it has join clauses, or make_rels_by_clauseless_joins () if not.

Also generate "bushy plan" joins between joinrels of lower levels.

Back at standard_join_search (), generate gather paths if needed for

Each newly constructed joinrel, then apply set_cheapest () to extract

The cheapest path for it.

Loop back if this wasn't the top join level.

Back at grouping_planner:

Do grouping (GROUP BY) and aggregation// handles grouping / aggregation / unique filtering / sorting / controlling the number of output tuples at the highest level

Do window functions

Make unique (DISTINCT)

Do sorting (ORDER BY)

Do limit (LIMIT/OFFSET)

Back at planner ():

Convert finished Path tree into a Plan tree

Do final cleanup after planning

II. Optimizer Data Structures

Optimizer Data Structures

Data structure

PlannerGlobal-global information for a single planner invocation

PlannerInfo-information for planning a particular Query (we make

A separate PlannerInfo node for each sub-Query)

RelOptInfo-a relation or joined relations

RestrictInfo-WHERE clauses, like "x = 3" or "y = z"

(note the same structure is used for restriction and

Join clauses)

Path-every way to generate a RelOptInfo (sequential,index,joins)

SeqScan-represents a sequential scan plan / / Sequential scan

IndexPath-index scan / / Index scan

BitmapHeapPath-top of a bitmapped index scan / / Bitmap index scan

TidPath-scan by CTID / / CTID scan

Subquery scanning in the SubqueryScanPath-scan a subquery-in-FROM / / FROM clause

ForeignPath-scan a foreign table, foreign join or foreign upper-relation / / FDW

CustomPath-for custom scan providers / / customized scan

AppendPath-append multiple subpaths together / / multiple subpaths APPEND, commonly used in collection operations

MergeAppendPath-merge multiple subpaths, preserving their common sort order / / APPEND keeping order

ResultPath-a childless Result plan node (used for FROM-less SELECT) / / result path (e.g. SELECT 2x2)

MaterialPath-a Material plan node / / materialization path

UniquePath-remove duplicate rows (either by hashing or sorting) / / remove duplicate path

GatherPath-collect the results of parallel workers / / parallel

GatherMergePath-collect parallel results, preserving their common sort order / / parallel, keep order

ProjectionPath-a Result plan node with child (used for projection) / / projection

ProjectSetPath-a ProjectSet plan node applied to some sub-path / / projection (applied to subpaths)

SortPath-a Sort plan node applied to some sub-path / / sort

GroupPath-a Group plan node applied to some sub-path / / grouping

UpperUniquePath-a Unique plan node applied to some sub-path / / Unique Plan applied to the subpath

AggPath-an Agg plan node applied to some sub-path / / applies to the aggregation of subpaths

GroupingSetsPath-an Agg plan node used to implement GROUPING SETS / / grouping set

MinMaxAggPath-a Result plan node with subplans performing MIN/MAX / / maximum and minimum

WindowAggPath-a WindowAgg plan node applied to some sub-path / / window function applied to subpaths

SetOpPath-a SetOp plan node applied to some sub-path / / set operations applied to subpaths

RecursiveUnionPath-a RecursiveUnion plan node applied to two sub-paths / / Recursive UNION

LockRowsPath-a LockRows plan node applied to some sub-path / / LockRows applied to the subpath

ModifyTablePath-a ModifyTable plan node applied to some sub-path (s) / / data table updates applied to subpaths (such as INSERT/UPDATE operations, etc.)

LimitPath-a Limit plan node applied to some sub-path// applied to LIMIT of subpath

NestPath-nested-loop joins// nested loop connection

MergePath-merge joins//Merge Join

HashPath-hash joins//Hash Join

EquivalenceClass-a data structure representing a set of values known equal

PathKey-a data structure representing the sort ordering of a path

The optimizer spends a good deal of its time worrying about the ordering

Of the tuples returned by a path. The reason this is useful is that by

Knowing the sort ordering of a path, we may be able to use that path as

The left or right input of a mergejoin and avoid an explicit sort step.

Nestloops and hash joins don't really care what the order of their inputs

Is, but mergejoin needs suitably ordered inputs. Therefore, all paths

Generated during the optimization process are marked with their sort order

(to the extent that it is known) for possible use by a higher-level merge.

The optimizer spends a lot of time sorting tuples in order to avoid special sorting steps in Merge Join.

It is also possible to avoid an explicit sort step to implement a user's

ORDER BY clause if the final path has the right ordering already, so the

Sort ordering is of interest even at the top level. Grouping_planner () will

Look for the cheapest path with a sort order matching the desired order

Then compare its cost to the cost of using the cheapest-overall path and

Doing an explicit sort on that.

When we are generating paths for a particular RelOptInfo, we discard a path

If it is more expensive than another known path that has the same or better

Sort order. We will never discard a path that is the only known way to

Achieve a given sort order (without an explicit sort, that is). In this

Way, the next level up will have the maximum freedom to build mergejoins

Without sorting, since it can pick from any of the paths retained for its

Inputs.

These are all the contents of the article "how to optimize the function of Review PG's Optimizer mechanism in PostgreSQL". Thank you for reading! I believe we all have a certain understanding, hope to share the content to help you, if you want to learn more knowledge, welcome to follow the industry information channel!

Welcome to subscribe "Shulou Technology Information " to get latest news, interesting things and hot topics in the IT industry, and controls the hottest and latest Internet news, technology news and IT industry trends.