Join causes redundant data to cause slow SQL

Business process encountered multiple join caused by slow SQL problem, the amount of data is not large, but the query is very slow, search a blog, reference to solve.

The business process is not recorded and reproduced in blog content:

Original sql:

Select    distinctAbc.pro_col1, Abc.col3 fromT0 PINNER JOINT1 ABC onP.id=abc.par_col2Inner JoinT2 S onS.col3=abc.col3Inner JoinT3 po onPo.id=S.col4whereP.state=2  andPo.state=3   Order  byAbc.pro_col1, Abc.col3;

The above SQL is the same as:

Select Select    distinctAbc.pro_col1, Abc.col3 fromt0 p, t1 ABC, t2 s, T3 powhereP.id=abc.par_col2 andS.col3=abc.col3 andPo.id=S.col4 andP.state=2  andPo.state=3 Order  byAbc.pro_col1, Abc.col3;

Analysis Optimization:

In terms of semantics, this SQL is a unique value that takes two fields from one of the tables after several joins.

However, each association can produce redundant values, resulting in a larger result set.

Modify the recommendations, each time the join outputs a unique value, reducing redundancy. That is, multiple joins cause the query result set to become larger (Cartesian product), you can put the filter conditions in front.

Select   distinctPro_col1, Col3 from  (      Select       distinctT1.pro_col1, T1.col3, S.col4 from       (          Select           distinctAbc.pro_col1, Abc.col3 fromT1 ABCINNER JOINT0 P on(p.id=Abc.par_col2 andP.state=2)) T1Inner JoinT2 S on(S.col3=t1.col3)) T2Inner JoinT3 po on(po.id=T2.col4 andPo.state=3)  Order  byT2.pro_col1, T2.col3;

The following examples:

Postgres=#Create TableRt1 (IDint, infotext); CREATE TABLEPostgres=#Create TableRt2 (IDint, infotext); CREATE TABLEPostgres=#Create TableRT3 (IDint, infotext); CREATE TABLEPostgres=#Create TableRT4 (IDint, infotext); CREATE TABLEPostgres=#Insert  intoRt1SelectGenerate_series (1, +),'Test'; INSERT 0  +Postgres=#Insert  intoRt2Select 1,'Test'  fromGenerate_series (1, +); INSERT 0  +Postgres=#Insert  intoRt3Select 1,'Test'  fromGenerate_series (1, +); INSERT 0  +Postgres=#Insert  intoRt4Select 1,'Test'  fromGenerate_series (1, +); INSERT 0  +  

Contrast:

Query after optimization:

From the execution time can be seen, the speed of optimization is more than fast.

Join causes redundant data to cause slow SQL