PostgreSQL code analysis, query optimization, canonicalize_qual, postgresql

Source: Internet
Author: User

PostgreSQL code analysis, query optimization, canonicalize_qual, postgresql


Here, we have finished sorting out the part of the regular expression. The reading order is as follows:


I. PostgreSQL code analysis, query optimization, canonicalize_qual

Ii. PostgreSQL code analysis, query optimization, pull_ands () and pull_ors ()

Iii. PostgreSQL code analysis and query optimization, process_duplicate_ors





/ *
 * canonicalize_qual
 * Convert a qualification expression to the most useful form.
 *
 * The name of this routine is a holdover from a time when it would try to
 * force the expression into canonical AND-of-ORs or OR-of-ANDs form.
 * Eventually, we recognized that that had more theoretical purity than
 * actual usefulness, and so now the transformation doesn't involve any
 * notion of reaching a canonical form.
 *
 * NOTE: we assume the input has already been through eval_const_expressions
 * and therefore possesses AND / OR flatness. Formerly this function included
 * its own flattening logic, but that requires a useless extra pass over the
 * tree.
 *
 * Returns the modified qualification.
 * /
/ *
 * Standardize the conditions in the WHERE statement or the ON statement, and merge and decompose the AND and OR operations from the perspective of the set.
 * /
Expr *
canonicalize_qual (Expr * qual)
{
Expr * newqual;

/ * Quick exit for empty qual * /
if (qual == NULL)
return NULL;

/ *
* Pull up redundant subclauses in OR-of-AND trees. We do this only
* within the top-level AND / OR structure; there's no point in looking
* deeper.
* /
/ *
* Find duplicate OR operations, that is, simplifying conditional statements.
* Assuming WHERE conditions are:
* (A = 1 AND B = 1) OR (A = 1 AND C = 1)
* Can be simplified to:
* A = 1 AND (B = 1 OR C = 1)
*
* In addition, the function does flatten (flatten, or flatten) the tree-like AND or OR statement.
* These two tasks are mainly reflected in the two functions of pull_ands () and pull_ors ().
* /
newqual = find_duplicate_ors (qual);

return newqual;
}


/ *
 * find_duplicate_ors
 * Given a qualification tree with the NOTs pushed down, search for
 * OR clauses to which the inverse OR distributive law might apply.
 * Only the top-level AND / OR structure is searched.
 *
 * Returns the modified qualification. AND / OR flatness is preserved.
 * /
static Expr *
find_duplicate_ors (Expr * qual)
{
/ *
* "Branch one: or_clause"
*
* If the main operation in the WHERE expression is OR, for example, the following form:
* A = 1 OR B = 1 OR (C = 1 AND D = 1)
* Then the parameter qual pointer actually points to a BoolExpr structure.
typedef struct BoolExpr
{
Expr xpr; = slightly
BoolExprType boolop; = OR_EXPR, OR operation on the following 3
List * args; = 3, A = 1 and B = 1 and (C = 1 AND D = 1)
} BoolExpr;
*
* Here args is a list, and the types of the three elements are as follows:
* The first is a comparison operation, the type is OpExpr
* The second is a comparison operation, the type is OpExpr
* The third is an AND operation, which is a BoolExpr of type AND_EXPR, which will be processed when recursively called
*
* Zhang's blog: http://blog.csdn.net/shujiezhang
* /
if (or_clause ((Node *) qual))
{
List * orlist = NIL;
ListCell * temp;

/ * Recurse * /
/ *
* Recursively process the three conditions in BoolExpr.
* Here we need to focus on the BoolExpr of type AND_EXPR in the above example, because in the recursive call, he will
* Trigger the next branch (branch two).
* /
foreach (temp, ((BoolExpr *) qual)-> args)
orlist = lappend (orlist, find_duplicate_ors (lfirst (temp)));

/ *
* Don't need pull_ors () since this routine will never introduce an OR
* where there wasn't one before. *** The reason was not understood. ***
* /
/ * For details, see "PostgreSQL Code Analysis, Query Optimization, process_duplicate_ors" blog post * /
return process_duplicate_ors (orlist);
}
/ *
* "Branch two: and_clause"
*
* There are two main operations performed here:
* 1) If there is an OR type substatement in the substatement, then find_duplicate_ors is called recursively,
* May also be able to extract public items.
* 2) Flatten the AND operation.
* /
else if (and_clause ((Node *) qual))
{
List * andlist = NIL;
ListCell * temp;

/ * Recurse * /
/ *
* A series of ORs exist in the substatement.
* For example:
* A = 1 AND ((B = 1 AND C = 1) OR (B = 1 AND D = 1))
* The qual pointer here points to a BoolExpr structure of type AND_EXPR, then
*
typedef struct BoolExpr
{
Expr xpr; = slightly
BoolExprType boolop; = AND_EXPR, that is, AND operation is performed on the following 2
List * args; = 2 "A = 1" and "((B = 1 AND C = 1) OR (B = 1 AND D = 1))"
} BoolExpr;
*
* For "((B = 1 AND C = 1) OR (B = 1 AND D = 1))", in the recursive call,
* Will enter "branch one: or_clause", and then convert to:
* B = 1 AND (C = 1 OR D = 1)
*
* Zhang's blog: http://blog.csdn.net/shujiezhang
* /
foreach (temp, ((BoolExpr *) qual)-> args)
andlist = lappend (andlist, find_duplicate_ors (lfirst (temp)));

/ * Flatten any ANDs introduced just below here * /
/ *
* Flatten.
*
* Because the main statement is AND type, and the child statement is also AND type, you can directly pull the child statement to the parent node.
*
* /
andlist = pull_ands (andlist);
/ * The AND list can't get shorter, so result is always an AND * /
return make_andclause (andlist);
}
else
return qual;
} 


PostgreSQL code analysis and query optimization.

Big understand blog: http://blog.csdn.net/shujiezhang

Related blog posts: PostgreSQL code analysis, query optimization, process_duplicate_ors



Source code analysis of PostgreSQL Query Processing

When updating the first data

1 + 1 = 2

The column 2 already exists, so an error occurs during update.

Multiple records of Columns with unique restrictions during one update cannot be used in this way.

PostgreSQL: How to query parameters set based on the user (role)

The following example shows Method 1: Query through pg_user View -- 1.1 set the log_statement parameter S = # alter role francs set log_statement = "all "; alter role -- 1.2 verify ipvs = # select * From pg_user where usename = 'francs '; -[RECORD 1] Using usename | francsusesysid | 24920 usecreatedb | fusesuper | fusecatupd | fuserepl | fpasswd | ******* valuntil | useconfig | {log_statement = all} -- 1.3 set the user's maintenance_work_mem parameter postg Res = # alter role francs set maintenance_work_mem = "1 GB"; alter role--1.4 verify postgres again = # select * From pg_user where usename = 'francs '; -[RECORD 1] Using usename | francsusesysid | 24920 usecreatedb | fusesuper | fusecatupd | fuserepl | fpasswd | ******* valuntil | useconfig | {log_statement = all, maintenance_work_mem = 1 GB} Remarks: The above is queried through pg_user.useconfig. Method 2: query using the pg_db_role_setting catalog base Table -- 2.1 pg_db_role_setting Table structure Table "tables" Column | Type | Modifiers ------------- + -------- + ----------- setdatabase | oid | not null setrole | oid | not null setconfig | text [] | Indexes: "pg_db_role_setting_databaseid_rol_index" UNIQUE, btree (setdatabase, setrole), tablespace "pg_global" Remarks: we can see that pg_db_role_setting records the database and user level. -- 2.2 verify ipvs = # select oid, rolname from pg_authid where rolname = 'francs '; oid | rolname ------- + --------- 24920 | francs (1 row) S = # select * From pg_db_role_setting where setrole = 24920; setdatabase | setrole | setconfig -----------...... remaining full text>

Related Article

Contact Us

The content source of this page is from Internet, which doesn't represent Alibaba Cloud's opinion; products and services mentioned on that page don't have any relationship with Alibaba Cloud. If the content of the page makes you feel confusing, please write us an email, we will handle the problem within 5 days after receiving your email.

If you find any instances of plagiarism from the community, please send an email to: info-contact@alibabacloud.com and provide relevant evidence. A staff member will contact you within 5 working days.

A Free Trial That Lets You Build Big!

Start building with 50+ products and up to 12 months usage for Elastic Compute Service

  • Sales Support

    1 on 1 presale consultation

  • After-Sales Support

    24/7 Technical Support 6 Free Tickets per Quarter Faster Response

  • Alibaba Cloud offers highly flexible support services tailored to meet your exact needs.