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

This article introduces the relevant knowledge of "what is the process of eliminating external joins in PostgreSQL query optimization". In the operation of actual cases, many people will encounter such a dilemma, so let the editor lead you to learn how to deal with these situations. I hope you can read it carefully and be able to achieve something!

Test script used:

Drop table if exists tweak null1: create table t_null1 (c1 int); insert into t_null1 values (1); insert into t_null1 values (2); insert into t_null1 values (null); drop table if exists tweak null2 X create table t_null2 (c1 int); insert into t_null2 values (1); insert into t_null2 values (null); I. basic concepts

The code comments to eliminate external connections are as follows:

/ * * reduce_outer_joins * Attempt to reduce outer joins to plain inner joins. * * The idea here is that given a query like * SELECT FROM a LEFT JOIN b ON (...) WHERE b.y = 42; * we can reduce the LEFT JOIN to a plain JOIN if the "=" operator in WHERE * is strict. The strict operator will always return NULL, causing the outer * WHERE to fail, on any row where the LEFT JOIN filled in NULLs for b's * columns. Therefore, there's no need for the join to produce null-extended * rows in the first place-which makes it a plain join not an outer join. * (This scenario may not be very likely in a query written out by hand, but * it's reasonably likely when pushing quals down into complex views.) * * More generally, an outer join can be reduced in strength if there is a * strict qual above it in the qual tree that constrains a Var from the * nullable side of the join to be non-null. (For FULL joins this applies * to each side separately.) * * Another transformation we apply here is to recognize cases like * SELECT. FROM a LEFT JOIN b ON (A.X = b.y) WHERE b.y IS NULL; * If the join clause is strict for b.y, then only null-extended rows could * pass the upper WHERE, and we can conclude that what the query is really * specifying is an anti-semijoin. We change the join type from JOIN_LEFT * to JOIN_ANTI. The IS NULL clause then becomes redundant, and must be * removed to prevent bogus selectivity calculations, but we leave it to * distribute_qual_to_rels to get rid of such clauses. * Also, we get rid of JOIN_RIGHT cases by flipping them around to become * JOIN_LEFT. This saves some code here and in some later planner routines, * but the main reason to do it is to not need to invent a JOIN_REVERSE_ANTI * join type. * * To ease recognition of strict qual clauses, we require this routine to be * run after expression preprocessing (i.e., qual canonicalization and JOIN * alias-var expansion). , /

There are two types of external joins that can be eliminated. The first is a statement that looks like the following:

SELECT... FROM a LEFT JOIN b ON (...) WHERE b.y = 42

If this statement satisfies the condition, it can be transformed into INNER_JOIN.

The reason why this statement can be transformed into an inner join is that the result of such a statement is the same as that of an inner join, because on the Nullable-Side side (one end that needs to be filled with a null value), there are filtering conditions to ensure that this end cannot be a null value, such as a strong (strict) filtering condition such as IS NOT NULL/y = 42.

Testdb=# explain verbose select * from t_null1 a left join t_null2 b on a.c1 = b.c1 QUERY PLAN-Merge Left Join (cost=359.57..860.00 rows=32512 width=8)-- external connection Output: a.c1 B.c1 Merge Cond: (a.c1 = b.c1)-> Sort (cost=179.78..186.16 rows=2550 width=4) Output: a.c1 Sort Key: a.c1-> Seq Scan on public.t_null1 a (cost=0.00..35.50 rows=2550 width=4) Output: a.c1-> Sort (cost=179.78..186.16 rows=2550 width=4) Output: b.c1 Sort Key: b.c1-> Seq Scan on public.t_null2 b (cost=0.00..35.50 rows=2550 width=4) Output: b.c1 (13 rows) testdb=# explain verbose select * from t_null1 a left join t_null2 b on a.c1 = b.c1 where b.c1 = 1 QUERY PLAN-Nested Loop (cost=0. 00.85.89 rows=169 width=8)-external join (Left keyword) has been eliminated Output: a.c1 B.c1-> Seq Scan on public.t_null1 a (cost=0.00..41.88 rows=13 width=4) Output: a.c1 Filter: (a.c1 = 1)-> Materialize (cost=0.00..41.94 rows=13 width=4) Output: b.c1-> Seq Scan on public.t_null2 b (cost=0.00..41.88 rows=13 width=4) Output: b.c1 Filter: (b.c1 = 1) (10 rows)

The second form is as follows:

SELECT... FROM a LEFT JOIN b ON (a.x = b.y) WHERE b.y IS NULL

Such a statement can be transformed into an inverse semi-join (ANTI-SEMIJOIN) if the condition is met.

The filter condition explicitly requires the Nullable-Side side y IS NULL. If the join condition is a strict (strict) condition such as A. x = b. Y, then the result of such an outer join is the same as that of an inverse semi-join.

Testdb=# explain verbose select * from t_null1 a left join t_null2 b on a.c1 = b.c1 where b.c1 is null QUERY PLAN-Hash Anti Join (cost=67.38..152.44 rows=1275 width=8)-- transform to disconnect Output: a.c1 B.c1 Hash Cond: (a.c1 = b.c1)-> Seq Scan on public.t_null1 a (cost=0.00..35.50 rows=2550 width=4) Output: a.c1-> Hash (cost=35.50..35.50 rows=2550 width=4) Output: b.c1-> Seq Scan on public.t_null2 b (cost=0.00..35.50 rows=2550 width=4) Output: b.c1 (9 rows)

It is worth mentioning that in PG, it is shaped like SELECT. FROM a LEFT JOIN b ON (...) WHERE b.y = 42; such a SQL statement, such as WHERE b.y = 42, can be regarded as the upper filter condition of the join. In the query tree, Jointree- > fromlist (element type is JoinExpr) is at the same level as Jointree- > quals. Because the quals in JoinExpr is the same layer condition, the upper layer is Jointree- > quals. If you are interested, you can view the log output to view the Query query tree structure.

Second, source code interpretation

The code that removes external joins is implemented by calling the reduce_outer_joins function in the main function subquery_planner. The code snippet is as follows:

/ * If we have any outer joins, try to reduce them to plain inner joins. * This step is most easily done after we've done expression * preprocessing. * / if (hasOuterJoins) reduce_outer_joins (root)

Reduce_outer_joins

Related data structures and dependent subfunctions:

Reduce_outer_joins_state

Typedef struct reduce_outer_joins_state {Relids relids; / * base relids within this subtree * / bool contains_outer; / * does subtree contain outer join (s)? * / List * sub_states; / * List of states for subtree components * /} reduce_outer_joins_state

BitmapXX

Typedef struct Bitmapset {int nwords; / * number of words in array * / bitmapword words [flex _ ARRAY_MEMBER]; / * really [nwords] * /} Bitmapset; # define WORDNUM (x) ((x) / BITS_PER_BITMAPWORD) # define BITNUM (x) ((x)% BITS_PER_BITMAPWORD) / * The unit size can be adjusted by changing these three declarations: * / # define BITS_PER_BITMAPWORD 32 typedef uint32 bitmapword / * must be an unsigned type * / * * bms_make_singleton-build a bitmapset containing a single member * / Bitmapset * bms_make_singleton (int x) {Bitmapset * result; int wordnum, bitnum; if (x)

Nwords = wordnum + 1; result- > words [wordnum] = ((bitmapword) 1 = a-> nwords) {int oldnwords = a-> nwords; int i; a = (Bitmapset *) repalloc (a, BITMAPSET_SIZE (wordnum + 1)); a-> nwords = wordnum + 1; / * zero out the enlarged portion * / for (I = oldnwords; I

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.