As a novice of open source database, recently a comparison of the PG and MySQL query plan.
Through the PG source code directory of Src\backend\executor\readme file, plus some simple debugging, can be the implementation mechanism of PG to produce a preliminary impression;
MySQL's code readability is much worse than PG, and may take some time to get to know first.
Originally wanted to write a comparison of the implementation mechanism of the article, now can only talk about the experience of PG, deficiencies and errors please correct me.
- PG is the academy's Open source database represents the product, its optimization based on relational algebra, the implementation of the operator looks very cordial. The implementation plan for PG is straightforward compared to the flat plan for MySQL.
- The execution plan of the PG is static read-only, which is convenient for reusing the plan. When a plan is executed, there is a state operation descriptor (corresponding to plan Trees and state Trees) that contains the read-only schedule pointer + the information required to execute. Similar expressions include expr and exprstate.
- the execution plan for the PG is the operator tree, which joins the merge Join,hash join as a two-element operator and the other as a unary operator. A special case is related to the semi join filter, the subquery generates a Subplan mount to the filter condition, if the filter condition has n correlated subquery then will get n subplan under the scan, This is similar to Oracle (Note: 12c related query processing will be more unnest, the sub-query added to the group to construct).
- the execution of an operator is state-based, and the operator uses tuples to pass data between them. Control is up and down, and the data is transmitted from the bottom up. The operator handles the data in tuples, and there is no batch optimization at this time.
- an operator may involve expression evaluation (interface Execmakefunctionresultnosets), such as projection, filtering. Depending on the type of expression such as Integer comparison, integral type addition, there will be different exprestate correspondence. exprstate contains the function pointers for integer additions or comparisons, and also contains operands. for example, Int_col1 + int_col2, first load the two operands of exprstate: Get the values from the nth field in the tuple and place them in the exprstate parameters. Next, using the Exprstate function pointer, combine two parameters for the addition operation and return the result.
Summarize:
The code logic of the executor of PG is very clear, but the processing in a tuple can make the CPU resources underutilized;
The expression reuse optimization now sees only the 9.6 introduced aggregation op reuse;
Preliminary study of PostgreSQL execution mechanism
