PostgreSQL code analysis, query optimization, process_duplicate_ors, postgresql

PostgreSQL code analysis, query optimization, process_duplicate_ors, postgresql

PostgreSQL code analysis and query optimization.

/** Process_duplicate_ors * Given a list of exprs which are ORed together, try to apply * the inverse OR distributive law. ** Returns the resulting expression (cocould be an AND clause, an OR * clause, or maybe even a single subexpression ). * // ** suppose we have four tables, TEST_A, TEST_ B, TEST_C, and TEST_D. Each table has A column, which is A, B, C, D, * That Is, TEST_A has A column A, TEST_ B has A column B, and so on. ** This function handles this situation. For an option, SELECT * FROM TEST_A, TEST_ B, TEST_C, TEST_D * WHERE (A = 1 and B = 1) OR (A = 1 and c = 1) OR (A = 1 and d = 1); ** WHERE condition in the statement: * (A = 1 and B = 1) OR (A = 1 and c = 1) OR (A = 1 and d = 1) * can be rewritten as: * (A = 1) AND (B = 1 OR C = 1 OR D = 1) * this is the main function of this function. ** The parameter of this function is a list. For the above WHERE condition, the structure of orlist is as follows: * an element in orlist is BoolExpr of OR_EXPR type. The structure of BoolExpr is as follows: typedef struct BoolExpr {Expr xpr; = slightly BoolExprType boolop; = OR_EXPRList * args; = three conditions in OR, that is, (A = 1 and B = 1) OR (A = 1 and c = 1) OR (A = 1 and d = 1) bool plusFlag; = omitted} BoolExpr; ** The following describes the specific implementation of the function, the general steps are as follows: * 1) analyze the public items in each OR, 2) extract the public items, AND 3) Merge the remaining items into AND. */Static Expr * process_duplicate_ors (List * orlist) {List * reference = NIL; intnum_subkrases = 0; List * winners; List * neworlist; ListCell * temp; if (orlist = NIL) return NULL;/* probably can't happen * // * if there is only one ...., forget it. */if (list_length (orlist) = 1)/* single-expression OR (can this * happen ?) */Return linitial (orlist);/** Choose the shortest AND clause as the reference list --- obviously, any * subclause not in this clause isn't in all the clures. if we find a * clause that's not an AND, we can treat it as a one-element AND clause, * which necessarily wins as shortest. * // ** find the shortest path ". * In the WHERE statement: * (A = 1 and B = 1) OR (A = 1 AND C = 1) OR (A = 1 AND D = 1) * The OR operation concatenates three substatements and finds the shortest one. Because if there are public items, the shortest one must also contain * Public items, and the shortest one is found, you can reduce the number of comparisons in subsequent operations. * In the preceding WHERE statement, the length of the three sub-statements is the same. The execution procedure is as follows: (A = 1 and B = 1), AND * is the first one. */Foreach (temp, orlist) {Expr * clause = (Expr *) lfirst (temp); if (and_clause (Node *) clause )) {List * subkrases = (BoolExpr *) clause)-> args; intnclses = list_length (subclses);/** determine the length of the sub-statement here, for example, for A sub-statement (A = 1 and B = 1), * It is actually two sub-statements connected with AND, then its length is 2. ** Records the shortest sub-statements using nclses. If there is a shorter (nclses <num_subkrases), * replace it with the shortest. */If (reference = NIL | nclses <num_subclses) {reference = subclses; num_subclses = nclses ;}} else {/** there is another situation, it is possible that the clause is not an AND statement, which does not seem to comply with the rules. * If we regard it as a whole, this is the shortest element. ******************************** If the code is executed here, there are only two cases: * one is... WHERE (A = 1 and B = 1) OR (A = 1 AND C = 1) OR (A = 1 ). * One is... WHERE (A = 1 or c = 1) and B = 1) OR (A = 1 OR C = 1 ). * for both cases, you can simplify them as follows: * The first case is simplified to A = 1 * the second case is simplified to (A = 1 or c = 1) ** the third case is to be supplemented... */reference = list_make1 (clause); break;}/** Just in case, eliminate any duplicates in the reference list. * // * find the shortest result and save it to List */reference = list_union (NIL, reference ); /** Check each element of the reference list to see if it's in all the OR * clses. build a new li St of winning clses. * // ** "Search for public items ". ** NOTE: At this time, the advantage of "finding the shortest" will be reflected, and the number of outer loops will be less. ** If the WHERE statement is: * (A = 1 and B = 1) OR (A = 1 AND C = 1) OR (A = 1 AND D = 1) * It must be (A = 1 and B = 1) found in "finding the shortest ). * The outer layer has two loops... (foreach (temp, reference). The variables of the two loops are * A = 1 and B = 1 respectively. * The inner layer has three cycles... (foreach (temp2, orlist), the three-cycle variables are respectively * (A = 1 and B = 1) AND (A = 1 AND C = 1) AND (A = 1 and d = 1) ** example: * If the outer loop is executed for the first time, that is, the public item of A = 1 is searched. If the inner loop is also executed for the first time, * In (A = 1 and B = 1), check whether the public item A = 1 exists. The public item list_member exists ), * judge the second clause of the inner loop in sequence... ** in the preceding example, the operations of these loops are respectively: * for the first time in the outer layer: * determines whether A = 1 is in (A = 1 and B = 1, judge whether the next * judge whether A = 1 is in (A = 1 and c = 1), AND then judge the next * judge whether A = 1 is in (A = 1 and d = 1 ), in, A = 1 is A public item, record (winners = lappend ...) * Second outer layer: * determines whether B = 1 is in (A = 1 And B = 1), In, determine the next * judge whether B = 1 is in (A = 1 AND C = 1), no, jump out of the loop, no need to judge the next. * Judge whether B = 1 is in (A = 1 AND D = 1), not executed, because the previous item does not include public items, it is impossible to extract it. */Winners = NIL; foreach (temp, reference) {Expr * refclause = (Expr *) lfirst (temp); boolwin = true; ListCell * temp2; foreach (temp2, orlist) {Expr * clause = (Expr *) lfirst (temp2); if (and_clause (Node *) clause) {if (! List_member (BoolExpr *) clause)-> args, refclause) {win = false; break ;}} else {if (! Equal (refclause, clause) {win = false; break ;}} if (win) winners = lappend (winners, refclause);}/** If no winners, we can't transform the OR */if (winners = NIL) return make_orclause (orlist);/** Generate new OR list consisting of the remaining sub-clures. ** If any clause degenerates to empty, then we have a situation like (A * and B) OR (A), which can be forced CED to just A --- that is, the * addi Tional conditions in other arms of the OR are irrelevant. ** Note that because we use list_difference, any multiple occurrences of a * winning clause in an AND sub-clause will be removed automatically. * // ** "extract public items ". * Use list_difference to delete public items. The implementation details are not described in detail. */Neworlist = NIL; foreach (temp, orlist) {Expr * clause = (Expr *) lfirst (temp); if (and_clause (Node *) clause )) {List * subkrases = (BoolExpr *) clause)-> args;/* check here... look here ..., remove public items */subclses = list_difference (subclses, winners); if (subclures! = NIL) {/* after elimination, splice the remaining parts to: (B = 1 OR C = 1 OR D = 1) */if (list_length (subclses) = 1) neworlist = lappend (neworlist, linitial (subclses); elseneworlist = lappend (neworlist, make_andclause (subclores);} else {/* indicates the sub-statement, all of them are public items in the form :*... WHERE (A = 1 and B = 1) OR (A = 1) ** at this time, the public item is A = 1, AND the first clause is (A = 1 and B = 1 ), the second clause is (A = 1). * If the second clause passes through list_difference, the returned result is NULL. * In this case, it can be reduced to A = 1, because (A = 1 and B = 1) must meet the condition that A = 1. */Neworlist = NIL;/* degenerate case, see above */break;} else {if (! List_member (winners, clause) neworlist = lappend (neworlist, clause); else {neworlist = NIL;/* degenerate case, see above */break ;}}} /** Append incluced OR to the winners list, if it's not degenerate, handling * the special case of one element correctly (can that really happen ?). * Also be careful to maintain AND/OR flatness in case we pulled up a * sub-OR-clause. */if (neworlist! = NIL) {if (list_length (neworlist) = 1) winners = lappend (winners, linitial (neworlist )); else/* neworlist should be (B = 1 OR C = 1 OR D = 1), so use make_orclause */winners = lappend (winners, make_orclause (pull_ors (neworlist);}/** And return the constructed AND clause, again being wary of a single * element and/OR flatness. */if (list_length (winners) = 1) return (Expr *) linitial (winners); else/* returns the form of :( A = 1) AND (B = 1 OR C = 1 OR D = 1), so make_andclause */return make_andclause (pull_ands (winners) will be used ));}

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>