PostgreSQL code analysis, query optimization, canonicalize_qual, postgresql

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>