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

Editor to share with you the example analysis of query optimization in PostgreSQL. I hope you will get something after reading this article. Let's discuss it together.

I. General explanation

The following is an overall description of the optimizer-related functions and data structures in the README file in the PG source directory (/ src/backend/optimizer):

Optimizer Functions-The primary entry point is planner () .planner () / / the optimizer main entry function set up for recursive handling of subqueries// configures the processor for the subquery (recursive)-subquery_planner () / / calls the (child) query optimization function pull up sublinks and subqueries from rangetable, if if possible// can Pull up sublinks and subqueries canonicalize qual// expression normalization 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.// simplified WHERE statement simplify constant expressions// simplified constant expression process sublinks// processing sublink convert Vars of outer query levels into Params// converts Vars variables of external queries to Params-- grouping_planner () / / preprocess target list for non-SELECT queries// preprocesses the projection columns handle UNION/INTERSECT/EXCEPT, GROUP BY, HAVING, aggregates of non-SELECT statements / / handle set operations / aggregate functions / sort, such as ORDER BY, DISTINCT LIMIT--query_planner () / / make list of base relations used in query// constructs the base table linked table split up the qual into restrictions (aqui1) and joins (breadc) / / split expression for constraints and joins find qual clauses that enable merge and hash joins// to find expressions that can make Merge and Hash connections effective-make_one_rel () / / set_base_rel_pathlists () / / sets the base table path Radial list find seqscan and all index paths for each base relation// traverses each base table Looking for sequential scans and all possible index scan paths find selectivity of columns used in joins// finds selective make_rel_from_joinlist () / / constructs Relation hand off join subproblems to a plugin, GEQO from the join linked list of columns used in the join Or standard_join_search () / /-standard_join_search () / / Standard connection search function call join_search_one_level () for each level of join tree needed// each join tree calls join_search_one_level join_search_one_level (): For each joinrel of the prior level, do make_rels_by_clause_joins () / / for each joinrel above Execute 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// goes back to the standard_join_search function and, if necessary, collects the relevant paths and uses the set_cheapest function to get the least expensive path each newly constructed joinrel, then apply set_cheapest () to extract the cheapest path for it. Loop back if this was not the top join level.//, if not the top-level connection, Loop Back at grouping_planner:// back to grouping_planner function do grouping (GROUP BY) and aggregation// processing grouping and aggregation do window functions// processing window function make unique (DISTINCT) / handling uniqueness do sorting (ORDER BY) / processing sorting do limit (LIMIT/OFFSET) / processing LimitBack at planner (): / / back to planner function convert finished Path tree into a Plan tree// converts the final path tree to the plan Tree do final cleanup after planning// closing work Optimizer Data Structures-PlannerGlobal-global information for a single planner invocation// global optimization information PlannerInfo-information for planning a particular Query (optimization information of a we make// Planner a separate PlannerInfo node for each sub-Query) RelOptInfo-a relation or joined relations// optimization information of a certain Relation (including connections) RestrictInfo-WHERE clauses Like "x = 3" or "y = z" / / restrictions (note the same structure is used for restriction and join clauses) Path-every way to generate a RelOptInfo (sequential,index Joins) / / construct the path of the relationship (note: the intermediate result is also one of the relationships) SeqScan-represents a sequential scan plan IndexPath-index scan BitmapHeapPath-top of a bitmapped index scan TidPath-scan by CTID SubqueryScanPath-scan a subquery-in-FROM ForeignPath-scan a foreign table, foreign join or foreign upper-relation CustomPath-for custom scan providers AppendPath-append multiple subpaths together MergeAppendPath-merge multiple subpaths Preserving their common sort order ResultPath-a childless Result plan node (used for FROM-less SELECT) MaterialPath-a Material plan node UniquePath-remove duplicate rows (either by hashing or sorting) GatherPath-collect the results of parallel workers GatherMergePath-collect parallel results Preserving their common sort order ProjectionPath-a Result plan node with child (used for projection) ProjectSetPath-a ProjectSet plan node applied to some sub-path SortPath-a Sort plan node applied to some sub-path GroupPath-a Group plan node applied to some sub-path UpperUniquePath-a Unique plan node applied to some sub-path AggPath-an Agg plan node applied to some sub-path GroupingSetsPath-an Agg plan node used to implement GROUPING SETS MinMaxAggPath-a Result plan node with subplans performing MIN/MAX WindowAggPath-a WindowAgg plan node applied to some sub -path SetOpPath-a SetOp plan node applied to some sub-path RecursiveUnionPath-a RecursiveUnion plan node applied to two sub-paths LockRowsPath-a LockRows plan node applied to some sub-path ModifyTablePath-a ModifyTable plan node applied to some sub-path (s) LimitPath-a Limit plan node applied to some sub-path NestPath-nested-loop joins MergePath-merge joins HashPath-hash joins EquivalenceClass-a data structure representing a set of values known equal// equivalent class PathKey-a data structure representing The sort ordering of a path// sort key has finished reading this article. I believe you have a certain understanding of "sample Analysis of query Optimization in PostgreSQL". If you want to know more about it, you are welcome to follow the industry information channel. Thank you for reading!

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.