Wrong rows estimations with joins of CTEs slows queries by more than factor 500
During data refactoring of our Application I encountered $subject when joining 4 CTEs with left join or inner join.
1. Background
PG 15.1 on Windows x64 (OS seems no to have no meening here)
I try to collect data from 4 (analyzed) tables (up,li,in,ou) by grouping certain data (4 CTEs qup,qli,qin,qou)
The grouping of the data in the CTEs gives estimated row counts of about 1000 (1 tenth of the real value) This is OK for estimation.
These 4 CTEs are then used to combine the data by joining them.
2. Problem
The 4 CTEs are joined by left joins as shown below:
from qup
left join qli on (qli.curr_season=qup.curr_season and qli.curr_code=qup.curr_code and qli.ibitmask>0 and cardinality(qli.mat_arr) <=8)
left join qin on (qin.curr_season=qup.curr_season and qin.curr_code=qup.curr_code and qin.ibitmask>0 and cardinality(qin.mat_arr) <=8)
left join qou on (qou.curr_season=qup.curr_season and qou.curr_code=qup.curr_code and qou.ibitmask>0 and cardinality(qou.mat_arr) <=11)
where qup.ibitmask>0 and cardinality(qup.mat_arr) <=21
The plan first retrieves qup and qli, taking the estimated row counts of 1163 and 1147 respectively
BUT the result is then hashed and the row count is estimated as 33!
In a Left join the row count stays always the same as the one of left table (here qup with 1163 rows)
The same algorithm which reduces the row estimation from 1163 to 33 is used in the next step to give an estimation of 1 row.
This is totally wrong.
Here is the execution plan of the query:
(search the plan for rows=33)
QUERY PLAN
--------------------------------------------------------------------------------------------------------------------------------------------------
Append (cost=13673.81..17463.30 rows=5734 width=104) (actual time=168.307..222.670 rows=9963 loops=1)
CTE qup
-> GroupAggregate (cost=5231.22..6303.78 rows=10320 width=80) (actual time=35.466..68.131 rows=10735 loops=1)
Group Key: sa_upper.sup_season, sa_upper.sup_sa_code
-> Sort (cost=5231.22..5358.64 rows=50969 width=18) (actual time=35.454..36.819 rows=50969 loops=1)
Sort Key: sa_upper.sup_season, sa_upper.sup_sa_code COLLATE "C"
Sort Method: quicksort Memory: 4722kB
-> Hash Left Join (cost=41.71..1246.13 rows=50969 width=18) (actual time=0.148..10.687 rows=50969 loops=1)
Hash Cond: ((sa_upper.sup_mat_code)::text = upper_target.up_mat_code)
-> Seq Scan on sa_upper (cost=0.00..884.69 rows=50969 width=16) (actual time=0.005..1.972 rows=50969 loops=1)
-> Hash (cost=35.53..35.53 rows=495 width=6) (actual time=0.140..0.140 rows=495 loops=1)
Buckets: 1024 Batches: 1 Memory Usage: 27kB
-> Seq Scan on upper_target (cost=0.00..35.53 rows=495 width=6) (actual time=0.007..0.103 rows=495 loops=1)
Filter: (id_up <= 495)
Rows Removed by Filter: 1467
CTE qli
-> GroupAggregate (cost=1097.31..1486.56 rows=10469 width=80) (actual time=9.446..27.388 rows=10469 loops=1)
Group Key: sa_lining.sli_season, sa_lining.sli_sa_code
-> Sort (cost=1097.31..1126.74 rows=11774 width=18) (actual time=9.440..9.811 rows=11774 loops=1)
Sort Key: sa_lining.sli_season, sa_lining.sli_sa_code COLLATE "C"
Sort Method: quicksort Memory: 1120kB
-> Hash Left Join (cost=7.34..301.19 rows=11774 width=18) (actual time=0.045..2.438 rows=11774 loops=1)
Hash Cond: ((sa_lining.sli_mat_code)::text = lining_target.li_mat_code)
-> Seq Scan on sa_lining (cost=0.00..204.74 rows=11774 width=16) (actual time=0.008..0.470 rows=11774 loops=1)
-> Hash (cost=5.86..5.86 rows=118 width=6) (actual time=0.034..0.034 rows=119 loops=1)
Buckets: 1024 Batches: 1 Memory Usage: 13kB
-> Seq Scan on lining_target (cost=0.00..5.86 rows=118 width=6) (actual time=0.008..0.024 rows=119 loops=1)
Filter: (id_li <= 119)
Rows Removed by Filter: 190
CTE qin
-> GroupAggregate (cost=1427.34..1880.73 rows=10678 width=80) (actual time=11.424..31.508 rows=10678 loops=1)
Group Key: sa_insole.sin_season, sa_insole.sin_sa_code
-> Sort (cost=1427.34..1465.41 rows=15230 width=18) (actual time=11.416..11.908 rows=15230 loops=1)
Sort Key: sa_insole.sin_season, sa_insole.sin_sa_code COLLATE "C"
Sort Method: quicksort Memory: 1336kB
-> Hash Left Join (cost=10.49..369.26 rows=15230 width=18) (actual time=0.051..3.108 rows=15230 loops=1)
Hash Cond: ((sa_insole.sin_mat_code)::text = insole_target.in_mat_code)
-> Seq Scan on sa_insole (cost=0.00..264.30 rows=15230 width=16) (actual time=0.006..0.606 rows=15230 loops=1)
-> Hash (cost=9.01..9.01 rows=118 width=6) (actual time=0.042..0.043 rows=119 loops=1)
Buckets: 1024 Batches: 1 Memory Usage: 13kB
-> Seq Scan on insole_target (cost=0.00..9.01 rows=118 width=6) (actual time=0.008..0.032 rows=119 loops=1)
Filter: (id_in <= 119)
Rows Removed by Filter: 362
CTE qou
-> GroupAggregate (cost=2366.22..2986.89 rows=10699 width=80) (actual time=18.198..41.812 rows=10699 loops=1)
Group Key: sa_outsole.sou_season, sa_outsole.sou_sa_code
-> Sort (cost=2366.22..2428.14 rows=24768 width=18) (actual time=18.187..18.967 rows=24768 loops=1)
Sort Key: sa_outsole.sou_season, sa_outsole.sou_sa_code COLLATE "C"
Sort Method: quicksort Memory: 2317kB
-> Hash Left Join (cost=5.39..558.63 rows=24768 width=18) (actual time=0.046..5.132 rows=24768 loops=1)
Hash Cond: ((sa_outsole.sou_mat_code)::text = outsole_target.ou_mat_code)
-> Seq Scan on sa_outsole (cost=0.00..430.68 rows=24768 width=16) (actual time=0.010..1.015 rows=24768 loops=1)
-> Hash (cost=5.03..5.03 rows=29 width=6) (actual time=0.032..0.032 rows=29 loops=1)
Buckets: 1024 Batches: 1 Memory Usage: 10kB
-> Seq Scan on outsole_target (cost=0.00..5.03 rows=29 width=6) (actual time=0.010..0.025 rows=29 loops=1)
Filter: (id_ou <= 29)
Rows Removed by Filter: 213
-> Hash Join (cost=1015.85..1319.50 rows=1 width=104) (actual time=168.307..215.513 rows=8548 loops=1)
Hash Cond: ((qou.curr_season = qli.curr_season) AND ((qou.curr_code)::text = (qli.curr_code)::text))
Join Filter: ((((qup.ibitmask | qin.ibitmask) | qli.ibitmask) | qou.ibitmask) IS NOT NULL)
-> CTE Scan on qou (cost=0.00..294.22 rows=1189 width=76) (actual time=18.200..45.188 rows=10275 loops=1)
Filter: ((ibitmask > 0) AND (cardinality(mat_arr) <= 11))
Rows Removed by Filter: 424
-> Hash (cost=1015.83..1015.83 rows=1 width=228) (actual time=150.094..150.095 rows=8845 loops=1)
Buckets: 16384 (originally 1024) Batches: 1 (originally 1) Memory Usage: 1899kB
-> Hash Join (cost=707.35..1015.83 rows=1 width=228) (actual time=121.898..147.726 rows=8845 loops=1)
Hash Cond: ((qin.curr_season = qli.curr_season) AND ((qin.curr_code)::text = (qli.curr_code)::text))
-> CTE Scan on qin (cost=0.00..293.65 rows=1186 width=76) (actual time=11.425..34.674 rows=10197 loops=1)
Filter: ((ibitmask > 0) AND (cardinality(mat_arr) <= 8))
Rows Removed by Filter: 481
-> Hash (cost=706.86..706.86 rows=33 width=152) (actual time=110.470..110.470 rows=9007 loops=1)
Buckets: 16384 (originally 1024) Batches: 1 (originally 1) Memory Usage: 1473kB
-> Merge Join (cost=689.20..706.86 rows=33 width=152) (actual time=105.862..108.925 rows=9007 loops=1)
Merge Cond: ((qup.curr_season = qli.curr_season) AND ((qup.curr_code)::text = (qli.curr_code)::text))
-> Sort (cost=342.09..344.96 rows=1147 width=76) (actual time=73.419..73.653 rows=9320 loops=1)
Sort Key: qup.curr_season, qup.curr_code COLLATE "C"
Sort Method: quicksort Memory: 1391kB
-> CTE Scan on qup (cost=0.00..283.80 rows=1147 width=76) (actual time=35.467..71.904 rows=9320 loops=1)
Filter: ((ibitmask > 0) AND (cardinality(mat_arr) <= 21))
Rows Removed by Filter: 1415
-> Sort (cost=347.12..350.02 rows=1163 width=76) (actual time=32.440..32.697 rows=10289 loops=1)
Sort Key: qli.curr_season, qli.curr_code COLLATE "C"
Sort Method: quicksort Memory: 1349kB
-> CTE Scan on qli (cost=0.00..287.90 rows=1163 width=76) (actual time=9.447..30.666 rows=10289 loops=1)
Filter: ((ibitmask > 0) AND (cardinality(mat_arr) <= 8))
Rows Removed by Filter: 180
-> Merge Left Join (cost=2625.49..3399.84 rows=5733 width=104) (actual time=4.597..6.700 rows=1415 loops=1)
Merge Cond: ((qup_1.curr_season = qou_1.curr_season) AND ((qup_1.curr_code)::text = (qou_1.curr_code)::text))
-> Merge Left Join (cost=1958.66..2135.28 rows=5733 width=136) (actual time=3.427..3.863 rows=1415 loops=1)
Merge Cond: ((qup_1.curr_season = qin_1.curr_season) AND ((qup_1.curr_code)::text = (qin_1.curr_code)::text))
-> Merge Left Join (cost=1293.25..1388.21 rows=5733 width=104) (actual time=2.321..2.556 rows=1415 loops=1)
Merge Cond: ((qup_1.curr_season = qli_1.curr_season) AND ((qup_1.curr_code)::text = (qli_1.curr_code)::text))
-> Sort (cost=641.68..656.02 rows=5733 width=72) (actual time=1.286..1.324 rows=1415 loops=1)
Sort Key: qup_1.curr_season, qup_1.curr_code COLLATE "C"
Sort Method: quicksort Memory: 204kB
-> CTE Scan on qup qup_1 (cost=0.00..283.80 rows=5733 width=72) (actual time=0.009..1.093 rows=1415 loops=1)
Filter: ((ibitmask < 0) OR (cardinality(mat_arr) > 21))
Rows Removed by Filter: 9320
-> Sort (cost=651.57..666.11 rows=5816 width=72) (actual time=1.033..1.038 rows=180 loops=1)
Sort Key: qli_1.curr_season, qli_1.curr_code COLLATE "C"
Sort Method: quicksort Memory: 41kB
-> CTE Scan on qli qli_1 (cost=0.00..287.90 rows=5816 width=72) (actual time=0.055..1.007 rows=180 loops=1)
Filter: ((ibitmask < 0) OR (cardinality(mat_arr) > 8))
Rows Removed by Filter: 10289
-> Sort (cost=665.41..680.24 rows=5932 width=72) (actual time=1.104..1.117 rows=481 loops=1)
Sort Key: qin_1.curr_season, qin_1.curr_code COLLATE "C"
Sort Method: quicksort Memory: 68kB
-> CTE Scan on qin qin_1 (cost=0.00..293.65 rows=5932 width=72) (actual time=0.016..1.038 rows=481 loops=1)
Filter: ((ibitmask < 0) OR (cardinality(mat_arr) > 8))
Rows Removed by Filter: 10197
-> Sort (cost=666.83..681.69 rows=5944 width=72) (actual time=1.163..1.174 rows=417 loops=1)
Sort Key: qou_1.curr_season, qou_1.curr_code COLLATE "C"
Sort Method: quicksort Memory: 68kB
-> CTE Scan on qou qou_1 (cost=0.00..294.22 rows=5944 width=72) (actual time=0.029..1.068 rows=424 loops=1)
Filter: ((ibitmask < 0) OR (cardinality(mat_arr) > 11))
Rows Removed by Filter: 10275
Planning Time: 2.297 ms
Execution Time: 224.759 ms
(118 Zeilen)
3. Slow query from wrong plan as result on similar case with inner join
When the 3 left joins above are changed to inner joins like:
from qup
join qli on (qli.curr_season=qup.curr_season and qli.curr_code=qup.curr_code and qli.ibitmask>0 and cardinality(qli.mat_arr) <=8)
join qin on (qin.curr_season=qup.curr_season and qin.curr_code=qup.curr_code and qin.ibitmask>0 and cardinality(qin.mat_arr) <=8)
join qou on (qou.curr_season=qup.curr_season and qou.curr_code=qup.curr_code and qou.ibitmask>0 and cardinality(qou.mat_arr) <=11)
where qup.ibitmask>0 and cardinality(qup.mat_arr) <=21
The same rows estimation takes place as with the left joins, but the planner now decides to use a nested loop for the last join, which results in a 500fold execution time:
QUERY PLAN
--------------------------------------------------------------------------------------------------------------------------------------------
Append (cost=13365.31..17472.18 rows=5734 width=104) (actual time=139.037..13403.310 rows=9963 loops=1)
CTE qup
-> GroupAggregate (cost=5231.22..6303.78 rows=10320 width=80) (actual time=35.399..67.102 rows=10735 loops=1)
Group Key: sa_upper.sup_season, sa_upper.sup_sa_code
-> Sort (cost=5231.22..5358.64 rows=50969 width=18) (actual time=35.382..36.743 rows=50969 loops=1)
Sort Key: sa_upper.sup_season, sa_upper.sup_sa_code COLLATE "C"
Sort Method: quicksort Memory: 4722kB
-> Hash Left Join (cost=41.71..1246.13 rows=50969 width=18) (actual time=0.157..10.715 rows=50969 loops=1)
Hash Cond: ((sa_upper.sup_mat_code)::text = upper_target.up_mat_code)
-> Seq Scan on sa_upper (cost=0.00..884.69 rows=50969 width=16) (actual time=0.008..2.001 rows=50969 loops=1)
-> Hash (cost=35.53..35.53 rows=495 width=6) (actual time=0.146..0.146 rows=495 loops=1)
Buckets: 1024 Batches: 1 Memory Usage: 27kB
-> Seq Scan on upper_target (cost=0.00..35.53 rows=495 width=6) (actual time=0.006..0.105 rows=495 loops=1)
Filter: (id_up <= 495)
Rows Removed by Filter: 1467
CTE qli
-> GroupAggregate (cost=1097.31..1486.56 rows=10469 width=80) (actual time=9.541..27.419 rows=10469 loops=1)
Group Key: sa_lining.sli_season, sa_lining.sli_sa_code
-> Sort (cost=1097.31..1126.74 rows=11774 width=18) (actual time=9.534..9.908 rows=11774 loops=1)
Sort Key: sa_lining.sli_season, sa_lining.sli_sa_code COLLATE "C"
Sort Method: quicksort Memory: 1120kB
-> Hash Left Join (cost=7.34..301.19 rows=11774 width=18) (actual time=0.049..2.451 rows=11774 loops=1)
Hash Cond: ((sa_lining.sli_mat_code)::text = lining_target.li_mat_code)
-> Seq Scan on sa_lining (cost=0.00..204.74 rows=11774 width=16) (actual time=0.010..0.462 rows=11774 loops=1)
-> Hash (cost=5.86..5.86 rows=118 width=6) (actual time=0.035..0.035 rows=119 loops=1)
Buckets: 1024 Batches: 1 Memory Usage: 13kB
-> Seq Scan on lining_target (cost=0.00..5.86 rows=118 width=6) (actual time=0.008..0.025 rows=119 loops=1)
Filter: (id_li <= 119)
Rows Removed by Filter: 190
CTE qin
-> GroupAggregate (cost=1427.34..1880.73 rows=10678 width=80) (actual time=11.649..30.910 rows=10678 loops=1)
Group Key: sa_insole.sin_season, sa_insole.sin_sa_code
-> Sort (cost=1427.34..1465.41 rows=15230 width=18) (actual time=11.642..12.115 rows=15230 loops=1)
Sort Key: sa_insole.sin_season, sa_insole.sin_sa_code COLLATE "C"
Sort Method: quicksort Memory: 1336kB
-> Hash Left Join (cost=10.49..369.26 rows=15230 width=18) (actual time=0.056..3.144 rows=15230 loops=1)
Hash Cond: ((sa_insole.sin_mat_code)::text = insole_target.in_mat_code)
-> Seq Scan on sa_insole (cost=0.00..264.30 rows=15230 width=16) (actual time=0.008..0.594 rows=15230 loops=1)
-> Hash (cost=9.01..9.01 rows=118 width=6) (actual time=0.045..0.046 rows=119 loops=1)
Buckets: 1024 Batches: 1 Memory Usage: 13kB
-> Seq Scan on insole_target (cost=0.00..9.01 rows=118 width=6) (actual time=0.008..0.034 rows=119 loops=1)
Filter: (id_in <= 119)
Rows Removed by Filter: 362
CTE qou
-> GroupAggregate (cost=2366.22..2986.89 rows=10699 width=80) (actual time=18.163..51.151 rows=10699 loops=1)
Group Key: sa_outsole.sou_season, sa_outsole.sou_sa_code
-> Sort (cost=2366.22..2428.14 rows=24768 width=18) (actual time=18.150..20.000 rows=24768 loops=1)
Sort Key: sa_outsole.sou_season, sa_outsole.sou_sa_code COLLATE "C"
Sort Method: quicksort Memory: 2317kB
-> Hash Left Join (cost=5.39..558.63 rows=24768 width=18) (actual time=0.036..5.106 rows=24768 loops=1)
Hash Cond: ((sa_outsole.sou_mat_code)::text = outsole_target.ou_mat_code)
-> Seq Scan on sa_outsole (cost=0.00..430.68 rows=24768 width=16) (actual time=0.008..1.005 rows=24768 loops=1)
-> Hash (cost=5.03..5.03 rows=29 width=6) (actual time=0.024..0.024 rows=29 loops=1)
Buckets: 1024 Batches: 1 Memory Usage: 10kB
-> Seq Scan on outsole_target (cost=0.00..5.03 rows=29 width=6) (actual time=0.007..0.018 rows=29 loops=1)
Filter: (id_ou <= 29)
Rows Removed by Filter: 213
-> Nested Loop (cost=707.35..1328.37 rows=1 width=104) (actual time=139.036..13395.820 rows=8548 loops=1)
Join Filter: ((qli.curr_season = qin.curr_season) AND ((qli.curr_code)::text = (qin.curr_code)::text))
Rows Removed by Join Filter: 88552397
-> Hash Join (cost=707.35..1016.45 rows=1 width=216) (actual time=127.374..168.249 rows=8685 loops=1)
Hash Cond: ((qou.curr_season = qli.curr_season) AND ((qou.curr_code)::text = (qli.curr_code)::text))
-> CTE Scan on qou (cost=0.00..294.22 rows=1189 width=72) (actual time=18.165..54.968 rows=10275 loops=1)
Filter: ((ibitmask > 0) AND (cardinality(mat_arr) <= 11))
Rows Removed by Filter: 424
-> Hash (cost=706.86..706.86 rows=33 width=144) (actual time=109.205..109.207 rows=9007 loops=1)
Buckets: 16384 (originally 1024) Batches: 1 (originally 1) Memory Usage: 1369kB
-> Merge Join (cost=689.20..706.86 rows=33 width=144) (actual time=104.785..107.748 rows=9007 loops=1)
Merge Cond: ((qup.curr_season = qli.curr_season) AND ((qup.curr_code)::text = (qli.curr_code)::text))
-> Sort (cost=342.09..344.96 rows=1147 width=72) (actual time=72.320..72.559 rows=9320 loops=1)
Sort Key: qup.curr_season, qup.curr_code COLLATE "C"
Sort Method: quicksort Memory: 1357kB
-> CTE Scan on qup (cost=0.00..283.80 rows=1147 width=72) (actual time=35.401..70.834 rows=9320 loops=1)
Filter: ((ibitmask > 0) AND (cardinality(mat_arr) <= 21))
Rows Removed by Filter: 1415
-> Sort (cost=347.12..350.02 rows=1163 width=72) (actual time=32.461..32.719 rows=10289 loops=1)
Sort Key: qli.curr_season, qli.curr_code COLLATE "C"
Sort Method: quicksort Memory: 1269kB
-> CTE Scan on qli (cost=0.00..287.90 rows=1163 width=72) (actual time=9.543..30.696 rows=10289 loops=1)
Filter: ((ibitmask > 0) AND (cardinality(mat_arr) <= 8))
Rows Removed by Filter: 180
-> CTE Scan on qin (cost=0.00..293.65 rows=1186 width=72) (actual time=0.001..1.159 rows=10197 loops=8685)
Filter: ((ibitmask > 0) AND (cardinality(mat_arr) <= 8))
Rows Removed by Filter: 481
-> Merge Left Join (cost=2625.49..3399.84 rows=5733 width=104) (actual time=4.606..6.733 rows=1415 loops=1)
Merge Cond: ((qup_1.curr_season = qou_1.curr_season) AND ((qup_1.curr_code)::text = (qou_1.curr_code)::text))
-> Merge Left Join (cost=1958.66..2135.28 rows=5733 width=136) (actual time=3.479..3.930 rows=1415 loops=1)
Merge Cond: ((qup_1.curr_season = qin_1.curr_season) AND ((qup_1.curr_code)::text = (qin_1.curr_code)::text))
-> Merge Left Join (cost=1293.25..1388.21 rows=5733 width=104) (actual time=2.368..2.610 rows=1415 loops=1)
Merge Cond: ((qup_1.curr_season = qli_1.curr_season) AND ((qup_1.curr_code)::text = (qli_1.curr_code)::text))
-> Sort (cost=641.68..656.02 rows=5733 width=72) (actual time=1.296..1.335 rows=1415 loops=1)
Sort Key: qup_1.curr_season, qup_1.curr_code COLLATE "C"
Sort Method: quicksort Memory: 204kB
-> CTE Scan on qup qup_1 (cost=0.00..283.80 rows=5733 width=72) (actual time=0.010..1.119 rows=1415 loops=1)
Filter: ((ibitmask < 0) OR (cardinality(mat_arr) > 21))
Rows Removed by Filter: 9320
-> Sort (cost=651.57..666.11 rows=5816 width=72) (actual time=1.069..1.075 rows=180 loops=1)
Sort Key: qli_1.curr_season, qli_1.curr_code COLLATE "C"
Sort Method: quicksort Memory: 41kB
-> CTE Scan on qli qli_1 (cost=0.00..287.90 rows=5816 width=72) (actual time=0.057..1.026 rows=180 loops=1)
Filter: ((ibitmask < 0) OR (cardinality(mat_arr) > 8))
Rows Removed by Filter: 10289
-> Sort (cost=665.41..680.24 rows=5932 width=72) (actual time=1.110..1.124 rows=481 loops=1)
Sort Key: qin_1.curr_season, qin_1.curr_code COLLATE "C"
Sort Method: quicksort Memory: 68kB
-> CTE Scan on qin qin_1 (cost=0.00..293.65 rows=5932 width=72) (actual time=0.016..1.046 rows=481 loops=1)
Filter: ((ibitmask < 0) OR (cardinality(mat_arr) > 8))
Rows Removed by Filter: 10197
-> Sort (cost=666.83..681.69 rows=5944 width=72) (actual time=1.119..1.128 rows=417 loops=1)
Sort Key: qou_1.curr_season, qou_1.curr_code COLLATE "C"
Sort Method: quicksort Memory: 68kB
-> CTE Scan on qou qou_1 (cost=0.00..294.22 rows=5944 width=72) (actual time=0.029..1.056 rows=424 loops=1)
Filter: ((ibitmask < 0) OR (cardinality(mat_arr) > 11))
Rows Removed by Filter: 10275
Planning Time: 1.746 ms
Execution Time: 13405.503 ms
(116 Zeilen)
This case really brought me to detect the problem!
The original query and data are not shown here, but the principle should be clear from the execution plans.
I think the planner shouldn't change the row estimations on further steps after left joins at all, and be a bit more conservative on inner joins.
This may be related to the fact that this case has 2 join-conditions (xx_season an xx_code).
Thanks for looking
Hans Buschmann
On 2/8/23 14:55, Hans Buschmann wrote:
During data refactoring of our Application I encountered $subject when
joining 4 CTEs with left join or inner join.1. Background
PG 15.1 on Windows x64 (OS seems no to have no meening here)
I try to collect data from 4 (analyzed) tables (up,li,in,ou) by grouping
certain data (4 CTEs qup,qli,qin,qou)The grouping of the data in the CTEs gives estimated row counts of about
1000 (1 tenth of the real value) This is OK for estimation.These 4 CTEs are then used to combine the data by joining them.
2. Problem
The 4 CTEs are joined by left joins as shown below:
...
This case really brought me to detect the problem!
The original query and data are not shown here, but the principle should
be clear from the execution plans.I think the planner shouldn't change the row estimations on further
steps after left joins at all, and be a bit more conservative on inner
joins.
But the code should alredy do exactly that, see:
And in fact, the second part of the plains shows it's doing the trick:
-> Merge Left Join (cost=1293.25..1388.21 rows=5733 width=104)
(actual time=2.321..2.556 rows=1415 loops=1)
Merge Cond: ((qup_1.curr_season = qli_1.curr_season) AND
((qup_1.curr_code)::text = (qli_1.curr_code)::text))
-> Sort (cost=641.68..656.02 rows=5733 width=72)
-> Sort (cost=651.57..666.11 rows=5816 width=72)
But notice the first join (with rows=33) doesn't say "Left". And I see
there's Append on top, so presumably the query is much more complex, and
there's a regular join of these CTEs in some other part.
We'll need to se the whole query, not just one chunk of it.
FWIW it seems you're using materialized CTEs - that's likely pretty bad
for the estimates, because we don't propagate statistics from the CTE.
So a join on CTEs can't see statistics from the underlying tables, and
that can easily produce really bad estimates.
I'm assuming you're not using AS MATERIALIZED explicitly, so I'd bet
this happens because the "cardinality" function is marked as volatile.
Perhaps it can be redefined as stable/immutable.
This may be related to the fact that this case has 2 join-conditions
(xx_season an xx_code).
That shouldn't affect outer join estimates this way (but as I explained
above, the join does not seem to be "left" per the explain).
Multi-column joins can cause issues, no doubt about it - but CTEs make
it worse because we can't e.g. see foreign keys.
regards
--
Tomas Vondra
EnterpriseDB: http://www.enterprisedb.com
The Enterprise PostgreSQL Company
Hello Tomas,
Thank you for looking at.
First, I miscalculated the factor which should be about 50, not 500. Sorry.
Then I want to show you the table definitions (simple, very similar, ommited child_tables and additional indexes, here using always "ONLY"):
cpsdb_matcol=# \d sa_upper;
Tabelle ╗public.sa_upper½
Spalte | Typ | Sortierfolge | NULL erlaubt? | Vorgabewert
--------------+-----------------------+--------------+---------------+----------------------------------
id_sup | integer | | not null | generated by default as identity
sup_season | smallint | | |
sup_sa_code | character varying(10) | C | |
sup_mat_code | character varying(4) | C | |
sup_clr_code | character varying(3) | C | |
Indexe:
"sa_upper_active_pkey" PRIMARY KEY, btree (id_sup)
cpsdb_matcol=# \d sa_lining+;
Tabelle ╗public.sa_lining½
Spalte | Typ | Sortierfolge | NULL erlaubt? | Vorgabewert
--------------+-----------------------+--------------+---------------+----------------------------------
id_sli | integer | | not null | generated by default as identity
sli_season | smallint | | |
sli_sa_code | character varying(10) | C | |
sli_mat_code | character varying(4) | C | |
sli_clr_code | character varying(3) | C | |
Indexe:
"sa_lining_active_pkey" PRIMARY KEY, btree (id_sli)
cpsdb_matcol=# \d sa_insole;
Tabelle ╗public.sa_insole½
Spalte | Typ | Sortierfolge | NULL erlaubt? | Vorgabewert
--------------+-----------------------+--------------+---------------+----------------------------------
id_sin | integer | | not null | generated by default as identity
sin_season | smallint | | |
sin_sa_code | character varying(10) | C | |
sin_mat_code | character varying(4) | C | |
sin_clr_code | character varying(3) | C | |
Indexe:
"sa_insole_active_pkey" PRIMARY KEY, btree (id_sin)
cpsdb_matcol=# \d sa_outsole;
Tabelle ╗public.sa_outsole½
Spalte | Typ | Sortierfolge | NULL erlaubt? | Vorgabewert
--------------+-----------------------+--------------+---------------+----------------------------------
id_sou | integer | | not null | generated by default as identity
sou_season | smallint | | |
sou_sa_code | character varying(10) | C | |
sou_mat_code | character varying(4) | C | |
sou_clr_code | character varying(3) | C | |
Indexe:
"sa_outsole_active_pkey" PRIMARY KEY, btree (id_sou)
The xxx_target tables are very similiar, here the upper one as an example:
They are count_aggregates of the whole dataset, where up_mat_code=sup_mat_code etc.
cpsdb_matcol=# \d upper_target
Tabelle ╗admin.upper_target½
Spalte | Typ | Sortierfolge | NULL erlaubt? | Vorgabewert
-------------+----------+--------------+---------------+-------------
id_up | smallint | | |
nup | integer | | |
up_mat_code | text | C | |
I have reworked the two queries to show their complete explain plans:
1. query with left join in the qupd CTE:
\set only 'ONLY'
cpsdb_matcol=# explain analyze -- explain analyze verbose -- explain -- select * from ( -- select count(*) from ( -- select length(sel) from (
cpsdb_matcol-# with
cpsdb_matcol-# qup as (
cpsdb_matcol(# select
cpsdb_matcol(# curr_season -- all xxx_seasosn are always smallint
cpsdb_matcol(# ,curr_code-- all xx_code are always varchar(10)
cpsdb_matcol(# ,array_agg(id_up order by id_up)||array_fill(0::smallint,array[10]) as mat_arr
cpsdb_matcol(# ,array_agg(curr_mat_code order by id_up) as matcode_arr
cpsdb_matcol(# ,bit_or(imask) as ibitmask
cpsdb_matcol(# from(
cpsdb_matcol(# select
cpsdb_matcol(# sup_season as curr_season
cpsdb_matcol(# ,sup_sa_code as curr_code
cpsdb_matcol(# ,sup_mat_code as curr_mat_code
cpsdb_matcol(# ,sup_clr_code as curr_clr_code
cpsdb_matcol(# ,id_up
cpsdb_matcol(# ,coalesce(id_up,-1) as imask
cpsdb_matcol(# from :only sa_upper
cpsdb_matcol(# left join upper_target on up_mat_code=sup_mat_code and id_up <= (512-1-16)
cpsdb_matcol(# )qr
cpsdb_matcol(# group by 1,2
cpsdb_matcol(# )
cpsdb_matcol-# ,qli as (
cpsdb_matcol(# select
cpsdb_matcol(# curr_season
cpsdb_matcol(# ,curr_code
cpsdb_matcol(# ,array_agg(id_li order by id_li)||array_fill(0::smallint,array[4]) as mat_arr
cpsdb_matcol(# ,array_agg(curr_mat_code order by id_li) as matcode_arr
cpsdb_matcol(# ,bit_or(imask) as ibitmask
cpsdb_matcol(# from(
cpsdb_matcol(# select
cpsdb_matcol(# sli_season as curr_season
cpsdb_matcol(# ,sli_sa_code as curr_code
cpsdb_matcol(# ,sli_mat_code as curr_mat_code
cpsdb_matcol(# ,sli_clr_code as curr_clr_code
cpsdb_matcol(# ,id_li
cpsdb_matcol(# ,coalesce(id_li,-1) as imask
cpsdb_matcol(# from :only sa_lining
cpsdb_matcol(# left join lining_target on li_mat_code=sli_mat_code and id_li <= (128-1-8)
cpsdb_matcol(# )qr
cpsdb_matcol(# group by 1,2
cpsdb_matcol(# )
cpsdb_matcol-# ,qin as (
cpsdb_matcol(# select
cpsdb_matcol(# curr_season
cpsdb_matcol(# ,curr_code
cpsdb_matcol(# ,array_agg(id_in order by id_in)||array_fill(0::smallint,array[4]) as mat_arr
cpsdb_matcol(# ,array_agg(curr_mat_code order by id_in) as matcode_arr
cpsdb_matcol(# ,bit_or(imask) as ibitmask
cpsdb_matcol(# from(
cpsdb_matcol(# select
cpsdb_matcol(# sin_season as curr_season
cpsdb_matcol(# ,sin_sa_code as curr_code
cpsdb_matcol(# ,sin_mat_code as curr_mat_code
cpsdb_matcol(# ,sin_clr_code as curr_clr_code
cpsdb_matcol(# ,id_in
cpsdb_matcol(# ,coalesce(id_in,-1) as imask
cpsdb_matcol(# from :only sa_insole
cpsdb_matcol(# left join insole_target on in_mat_code=sin_mat_code and id_in <= (128-1-8)
cpsdb_matcol(# )qr
cpsdb_matcol(# group by 1,2
cpsdb_matcol(# )
cpsdb_matcol-# ,qou as (
cpsdb_matcol(# select
cpsdb_matcol(# curr_season
cpsdb_matcol(# ,curr_code
cpsdb_matcol(# ,array_agg(id_ou order by id_ou)||array_fill(0::smallint,array[6]) as mat_arr
cpsdb_matcol(# ,array_agg(curr_mat_code order by id_ou) as matcode_arr
cpsdb_matcol(# ,bit_or(imask) as ibitmask
cpsdb_matcol(# from(
cpsdb_matcol(# select
cpsdb_matcol(# sou_season as curr_season
cpsdb_matcol(# ,sou_sa_code as curr_code
cpsdb_matcol(# ,sou_mat_code as curr_mat_code
cpsdb_matcol(# ,sou_clr_code as curr_clr_code
cpsdb_matcol(# ,id_ou
cpsdb_matcol(# ,coalesce(id_ou,-1) as imask
cpsdb_matcol(# from :only sa_outsole
cpsdb_matcol(# left join outsole_target on ou_mat_code=sou_mat_code and id_ou <= (32-1-2)
cpsdb_matcol(# )qr
cpsdb_matcol(# group by 1,2
cpsdb_matcol(# )
cpsdb_matcol-# ,qupd as (
cpsdb_matcol(# select * from (
cpsdb_matcol(# select
cpsdb_matcol(# qup.curr_season
cpsdb_matcol(# ,qup.curr_code
cpsdb_matcol(# ,qup.ibitmask|qin.ibitmask|qli.ibitmask|qou.ibitmask as ibitmask
cpsdb_matcol(# -- the calculations of new_mat_x are simplified here
cpsdb_matcol(# -- in the production version they are a more complex combination of bit masks, bit shifts and bit or of different elements of the arrays
cpsdb_matcol(# ,(qup.mat_arr[1]|qli.mat_arr[1]|qin.mat_arr[1]|qou.mat_arr[1])::bigint as new_mat_1
cpsdb_matcol(#
cpsdb_matcol(# ,(qup.mat_arr[2]|qli.mat_arr[2]|qin.mat_arr[2]|qou.mat_arr[2])::bigint as new_mat_2
cpsdb_matcol(#
cpsdb_matcol(# ,(qup.mat_arr[3]|qli.mat_arr[3]|qin.mat_arr[3]|qou.mat_arr[3])::bigint as new_mat_3
cpsdb_matcol(#
cpsdb_matcol(# from qup
cpsdb_matcol(# left join qli on (qli.curr_season=qup.curr_season and qli.curr_code=qup.curr_code and qli.ibitmask>0 and cardinality(qli.mat_arr) <=8)
cpsdb_matcol(# left join qin on (qin.curr_season=qup.curr_season and qin.curr_code=qup.curr_code and qin.ibitmask>0 and cardinality(qin.mat_arr) <=8)
cpsdb_matcol(# left join qou on (qou.curr_season=qup.curr_season and qou.curr_code=qup.curr_code and qou.ibitmask>0 and cardinality(qou.mat_arr) <=11)
cpsdb_matcol(# where qup.ibitmask>0 and cardinality(qup.mat_arr) <=21
cpsdb_matcol(# )qj
cpsdb_matcol(# where ibitmask is not null
cpsdb_matcol(# )
cpsdb_matcol-# ,qupda as (
cpsdb_matcol(# select
cpsdb_matcol(# qup.curr_season
cpsdb_matcol(# ,qup.curr_code
cpsdb_matcol(# ,repeat('0',64)||
cpsdb_matcol(# repeat('11',coalesce(cardinality(qou.matcode_arr),0))||repeat('10',coalesce(cardinality(qin.matcode_arr),0))||
cpsdb_matcol(# repeat('01',coalesce(cardinality(qou.matcode_arr),0))||repeat('00',coalesce(cardinality(qup.matcode_arr),0))||
cpsdb_matcol(# '00' as curr_mattype_bitmask
cpsdb_matcol(# ,qup.matcode_arr||qli.matcode_arr||qin.matcode_arr||qou.matcode_arr as curr_matcode_arr
cpsdb_matcol(# from qup
cpsdb_matcol(# left join qli on qli.curr_season=qup.curr_season and qli.curr_code=qup.curr_code and (qli.ibitmask<0 or cardinality(qli.mat_arr) >8)
cpsdb_matcol(# left join qin on qin.curr_season=qup.curr_season and qin.curr_code=qup.curr_code and (qin.ibitmask<0 or cardinality(qin.mat_arr) >8)
cpsdb_matcol(# left join qou on qou.curr_season=qup.curr_season and qou.curr_code=qup.curr_code and (qou.ibitmask<0 or cardinality(qou.mat_arr) >11)
cpsdb_matcol(# where qup.ibitmask<0 or cardinality(qup.mat_arr) >21
cpsdb_matcol(# )
cpsdb_matcol-# select
cpsdb_matcol-# curr_season
cpsdb_matcol-# ,curr_code
cpsdb_matcol-# ,new_mat_1
cpsdb_matcol-# ,new_mat_2
cpsdb_matcol-# ,new_mat_3
cpsdb_matcol-# ,NULL::bigint as new_mattype_bitmask
cpsdb_matcol-# ,NULL as new_mat_codes
cpsdb_matcol-# from qupd
cpsdb_matcol-# union all
cpsdb_matcol-# select
cpsdb_matcol-# curr_season
cpsdb_matcol-# ,curr_code
cpsdb_matcol-# ,NULL::bigint as new_mat_1
cpsdb_matcol-# ,NULL::bigint as new_mat_2
cpsdb_matcol-# ,NULL::bigint as new_mat_3
cpsdb_matcol-# ,substr(curr_mattype_bitmask,length(curr_mattype_bitmask)-63)::bit(64)::bigint as new_mattype_bitmask
cpsdb_matcol-# ,curr_matcode_arr as new_mat_codes
cpsdb_matcol-# from qupda
cpsdb_matcol-# ;
QUERY PLAN
--------------------------------------------------------------------------------------------------------------------------------------------------
Append (cost=13673.81..17462.84 rows=5734 width=104) (actual time=169.382..210.799 rows=9963 loops=1)
CTE qup
-> GroupAggregate (cost=5231.22..6303.78 rows=10320 width=80) (actual time=35.064..68.308 rows=10735 loops=1)
Group Key: sa_upper.sup_season, sa_upper.sup_sa_code
-> Sort (cost=5231.22..5358.64 rows=50969 width=18) (actual time=35.053..36.412 rows=50969 loops=1)
Sort Key: sa_upper.sup_season, sa_upper.sup_sa_code COLLATE "C"
Sort Method: quicksort Memory: 4722kB
-> Hash Left Join (cost=41.71..1246.13 rows=50969 width=18) (actual time=0.165..10.562 rows=50969 loops=1)
Hash Cond: ((sa_upper.sup_mat_code)::text = upper_target.up_mat_code)
-> Seq Scan on sa_upper (cost=0.00..884.69 rows=50969 width=16) (actual time=0.006..1.990 rows=50969 loops=1)
-> Hash (cost=35.53..35.53 rows=495 width=6) (actual time=0.157..0.157 rows=495 loops=1)
Buckets: 1024 Batches: 1 Memory Usage: 27kB
-> Seq Scan on upper_target (cost=0.00..35.53 rows=495 width=6) (actual time=0.006..0.115 rows=495 loops=1)
Filter: (id_up <= 495)
Rows Removed by Filter: 1467
CTE qli
-> GroupAggregate (cost=1097.31..1486.56 rows=10469 width=80) (actual time=9.354..28.199 rows=10469 loops=1)
Group Key: sa_lining.sli_season, sa_lining.sli_sa_code
-> Sort (cost=1097.31..1126.74 rows=11774 width=18) (actual time=9.347..9.711 rows=11774 loops=1)
Sort Key: sa_lining.sli_season, sa_lining.sli_sa_code COLLATE "C"
Sort Method: quicksort Memory: 1120kB
-> Hash Left Join (cost=7.34..301.19 rows=11774 width=18) (actual time=0.049..2.397 rows=11774 loops=1)
Hash Cond: ((sa_lining.sli_mat_code)::text = lining_target.li_mat_code)
-> Seq Scan on sa_lining (cost=0.00..204.74 rows=11774 width=16) (actual time=0.009..0.469 rows=11774 loops=1)
-> Hash (cost=5.86..5.86 rows=118 width=6) (actual time=0.037..0.037 rows=119 loops=1)
Buckets: 1024 Batches: 1 Memory Usage: 13kB
-> Seq Scan on lining_target (cost=0.00..5.86 rows=118 width=6) (actual time=0.008..0.025 rows=119 loops=1)
Filter: (id_li <= 119)
Rows Removed by Filter: 190
CTE qin
-> GroupAggregate (cost=1427.34..1880.73 rows=10678 width=80) (actual time=11.453..32.317 rows=10678 loops=1)
Group Key: sa_insole.sin_season, sa_insole.sin_sa_code
-> Sort (cost=1427.34..1465.41 rows=15230 width=18) (actual time=11.444..11.943 rows=15230 loops=1)
Sort Key: sa_insole.sin_season, sa_insole.sin_sa_code COLLATE "C"
Sort Method: quicksort Memory: 1336kB
-> Hash Left Join (cost=10.49..369.26 rows=15230 width=18) (actual time=0.051..3.098 rows=15230 loops=1)
Hash Cond: ((sa_insole.sin_mat_code)::text = insole_target.in_mat_code)
-> Seq Scan on sa_insole (cost=0.00..264.30 rows=15230 width=16) (actual time=0.007..0.608 rows=15230 loops=1)
-> Hash (cost=9.01..9.01 rows=118 width=6) (actual time=0.041..0.041 rows=119 loops=1)
Buckets: 1024 Batches: 1 Memory Usage: 13kB
-> Seq Scan on insole_target (cost=0.00..9.01 rows=118 width=6) (actual time=0.007..0.031 rows=119 loops=1)
Filter: (id_in <= 119)
Rows Removed by Filter: 362
CTE qou
-> GroupAggregate (cost=2366.22..2986.89 rows=10699 width=80) (actual time=18.055..42.079 rows=10699 loops=1)
Group Key: sa_outsole.sou_season, sa_outsole.sou_sa_code
-> Sort (cost=2366.22..2428.14 rows=24768 width=18) (actual time=18.043..18.798 rows=24768 loops=1)
Sort Key: sa_outsole.sou_season, sa_outsole.sou_sa_code COLLATE "C"
Sort Method: quicksort Memory: 2317kB
-> Hash Left Join (cost=5.39..558.63 rows=24768 width=18) (actual time=0.037..5.017 rows=24768 loops=1)
Hash Cond: ((sa_outsole.sou_mat_code)::text = outsole_target.ou_mat_code)
-> Seq Scan on sa_outsole (cost=0.00..430.68 rows=24768 width=16) (actual time=0.008..0.998 rows=24768 loops=1)
-> Hash (cost=5.03..5.03 rows=29 width=6) (actual time=0.025..0.025 rows=29 loops=1)
Buckets: 1024 Batches: 1 Memory Usage: 10kB
-> Seq Scan on outsole_target (cost=0.00..5.03 rows=29 width=6) (actual time=0.009..0.020 rows=29 loops=1)
Filter: (id_ou <= 29)
Rows Removed by Filter: 213
-> Hash Join (cost=1015.85..1319.04 rows=1 width=104) (actual time=169.382..203.707 rows=8548 loops=1)
Hash Cond: ((qou.curr_season = qli.curr_season) AND ((qou.curr_code)::text = (qli.curr_code)::text))
Join Filter: ((((qup.ibitmask | qin.ibitmask) | qli.ibitmask) | qou.ibitmask) IS NOT NULL)
-> CTE Scan on qou (cost=0.00..294.22 rows=1189 width=76) (actual time=18.057..45.448 rows=10275 loops=1)
Filter: ((ibitmask > 0) AND (cardinality(mat_arr) <= 11))
Rows Removed by Filter: 424
-> Hash (cost=1015.83..1015.83 rows=1 width=228) (actual time=151.316..151.317 rows=8845 loops=1)
Buckets: 16384 (originally 1024) Batches: 1 (originally 1) Memory Usage: 1899kB
-> Hash Join (cost=707.35..1015.83 rows=1 width=228) (actual time=122.483..149.030 rows=8845 loops=1)
Hash Cond: ((qin.curr_season = qli.curr_season) AND ((qin.curr_code)::text = (qli.curr_code)::text))
-> CTE Scan on qin (cost=0.00..293.65 rows=1186 width=76) (actual time=11.454..35.456 rows=10197 loops=1)
Filter: ((ibitmask > 0) AND (cardinality(mat_arr) <= 8))
Rows Removed by Filter: 481
-> Hash (cost=706.86..706.86 rows=33 width=152) (actual time=111.026..111.027 rows=9007 loops=1)
Buckets: 16384 (originally 1024) Batches: 1 (originally 1) Memory Usage: 1473kB
-> Merge Join (cost=689.20..706.86 rows=33 width=152) (actual time=106.441..109.505 rows=9007 loops=1)
Merge Cond: ((qup.curr_season = qli.curr_season) AND ((qup.curr_code)::text = (qli.curr_code)::text))
-> Sort (cost=342.09..344.96 rows=1147 width=76) (actual time=73.200..73.429 rows=9320 loops=1)
Sort Key: qup.curr_season, qup.curr_code COLLATE "C"
Sort Method: quicksort Memory: 1391kB
-> CTE Scan on qup (cost=0.00..283.80 rows=1147 width=76) (actual time=35.067..71.872 rows=9320 loops=1)
Filter: ((ibitmask > 0) AND (cardinality(mat_arr) <= 21))
Rows Removed by Filter: 1415
-> Sort (cost=347.12..350.02 rows=1163 width=76) (actual time=33.239..33.490 rows=10289 loops=1)
Sort Key: qli.curr_season, qli.curr_code COLLATE "C"
Sort Method: quicksort Memory: 1349kB
-> CTE Scan on qli (cost=0.00..287.90 rows=1163 width=76) (actual time=9.355..31.457 rows=10289 loops=1)
Filter: ((ibitmask > 0) AND (cardinality(mat_arr) <= 8))
Rows Removed by Filter: 180
-> Merge Left Join (cost=2625.49..3399.84 rows=5733 width=104) (actual time=4.529..6.645 rows=1415 loops=1)
Merge Cond: ((qup_1.curr_season = qou_1.curr_season) AND ((qup_1.curr_code)::text = (qou_1.curr_code)::text))
-> Merge Left Join (cost=1958.66..2135.28 rows=5733 width=136) (actual time=3.388..3.833 rows=1415 loops=1)
Merge Cond: ((qup_1.curr_season = qin_1.curr_season) AND ((qup_1.curr_code)::text = (qin_1.curr_code)::text))
-> Merge Left Join (cost=1293.25..1388.21 rows=5733 width=104) (actual time=2.297..2.534 rows=1415 loops=1)
Merge Cond: ((qup_1.curr_season = qli_1.curr_season) AND ((qup_1.curr_code)::text = (qli_1.curr_code)::text))
-> Sort (cost=641.68..656.02 rows=5733 width=72) (actual time=1.278..1.315 rows=1415 loops=1)
Sort Key: qup_1.curr_season, qup_1.curr_code COLLATE "C"
Sort Method: quicksort Memory: 204kB
-> CTE Scan on qup qup_1 (cost=0.00..283.80 rows=5733 width=72) (actual time=0.009..1.081 rows=1415 loops=1)
Filter: ((ibitmask < 0) OR (cardinality(mat_arr) > 21))
Rows Removed by Filter: 9320
-> Sort (cost=651.57..666.11 rows=5816 width=72) (actual time=1.017..1.022 rows=180 loops=1)
Sort Key: qli_1.curr_season, qli_1.curr_code COLLATE "C"
Sort Method: quicksort Memory: 41kB
-> CTE Scan on qli qli_1 (cost=0.00..287.90 rows=5816 width=72) (actual time=0.054..0.994 rows=180 loops=1)
Filter: ((ibitmask < 0) OR (cardinality(mat_arr) > 8))
Rows Removed by Filter: 10289
-> Sort (cost=665.41..680.24 rows=5932 width=72) (actual time=1.089..1.103 rows=481 loops=1)
Sort Key: qin_1.curr_season, qin_1.curr_code COLLATE "C"
Sort Method: quicksort Memory: 68kB
-> CTE Scan on qin qin_1 (cost=0.00..293.65 rows=5932 width=72) (actual time=0.016..1.022 rows=481 loops=1)
Filter: ((ibitmask < 0) OR (cardinality(mat_arr) > 8))
Rows Removed by Filter: 10197
-> Sort (cost=666.83..681.69 rows=5944 width=72) (actual time=1.134..1.145 rows=417 loops=1)
Sort Key: qou_1.curr_season, qou_1.curr_code COLLATE "C"
Sort Method: quicksort Memory: 68kB
-> CTE Scan on qou qou_1 (cost=0.00..294.22 rows=5944 width=72) (actual time=0.029..1.038 rows=424 loops=1)
Filter: ((ibitmask < 0) OR (cardinality(mat_arr) > 11))
Rows Removed by Filter: 10275
Planning Time: 1.055 ms
Execution Time: 212.800 ms
(118 Zeilen)
As seen in the line of the qupd CTE
-> Merge Join (cost=689.20..706.86 rows=33 width=152) (actual time=106.441..109.505 rows=9007 loops=1)
the row count of the second join round drops to 33 and for the third round it drops to 1
-> Hash Join (cost=707.35..1015.83 rows=1 width=228) (actual time=122.483..149.030 rows=8845 loops=1)
BTW, I don't know, why the second join group (part of qupda) gets a complete different plan.
--------------------------------------------
Here is the second question, different from the first only by replacing the left join to inner join in the join group of qupd:
\set only 'ONLY'
cpsdb_matcol=# explain analyze -- explain analyze verbose -- explain -- select * from ( -- select count(*) from ( -- select length(sel) from (
cpsdb_matcol-# with
cpsdb_matcol-# qup as (
cpsdb_matcol(# select
cpsdb_matcol(# curr_season -- all xxx_seasosn are always smallint
cpsdb_matcol(# ,curr_code-- all xx_code are always varchar(10)
cpsdb_matcol(# ,array_agg(id_up order by id_up)||array_fill(0::smallint,array[10]) as mat_arr
cpsdb_matcol(# ,array_agg(curr_mat_code order by id_up) as matcode_arr
cpsdb_matcol(# ,bit_or(imask) as ibitmask
cpsdb_matcol(# from(
cpsdb_matcol(# select
cpsdb_matcol(# sup_season as curr_season
cpsdb_matcol(# ,sup_sa_code as curr_code
cpsdb_matcol(# ,sup_mat_code as curr_mat_code
cpsdb_matcol(# ,sup_clr_code as curr_clr_code
cpsdb_matcol(# ,id_up
cpsdb_matcol(# ,coalesce(id_up,-1) as imask
cpsdb_matcol(# from :only sa_upper
cpsdb_matcol(# left join upper_target on up_mat_code=sup_mat_code and id_up <= (512-1-16)
cpsdb_matcol(# )qr
cpsdb_matcol(# group by 1,2
cpsdb_matcol(# )
cpsdb_matcol-# ,qli as (
cpsdb_matcol(# select
cpsdb_matcol(# curr_season
cpsdb_matcol(# ,curr_code
cpsdb_matcol(# ,array_agg(id_li order by id_li)||array_fill(0::smallint,array[4]) as mat_arr
cpsdb_matcol(# ,array_agg(curr_mat_code order by id_li) as matcode_arr
cpsdb_matcol(# ,bit_or(imask) as ibitmask
cpsdb_matcol(# from(
cpsdb_matcol(# select
cpsdb_matcol(# sli_season as curr_season
cpsdb_matcol(# ,sli_sa_code as curr_code
cpsdb_matcol(# ,sli_mat_code as curr_mat_code
cpsdb_matcol(# ,sli_clr_code as curr_clr_code
cpsdb_matcol(# ,id_li
cpsdb_matcol(# ,coalesce(id_li,-1) as imask
cpsdb_matcol(# from :only sa_lining
cpsdb_matcol(# left join lining_target on li_mat_code=sli_mat_code and id_li <= (128-1-8)
cpsdb_matcol(# )qr
cpsdb_matcol(# group by 1,2
cpsdb_matcol(# )
cpsdb_matcol-# ,qin as (
cpsdb_matcol(# select
cpsdb_matcol(# curr_season
cpsdb_matcol(# ,curr_code
cpsdb_matcol(# ,array_agg(id_in order by id_in)||array_fill(0::smallint,array[4]) as mat_arr
cpsdb_matcol(# ,array_agg(curr_mat_code order by id_in) as matcode_arr
cpsdb_matcol(# ,bit_or(imask) as ibitmask
cpsdb_matcol(# from(
cpsdb_matcol(# select
cpsdb_matcol(# sin_season as curr_season
cpsdb_matcol(# ,sin_sa_code as curr_code
cpsdb_matcol(# ,sin_mat_code as curr_mat_code
cpsdb_matcol(# ,sin_clr_code as curr_clr_code
cpsdb_matcol(# ,id_in
cpsdb_matcol(# ,coalesce(id_in,-1) as imask
cpsdb_matcol(# from :only sa_insole
cpsdb_matcol(# left join insole_target on in_mat_code=sin_mat_code and id_in <= (128-1-8)
cpsdb_matcol(# )qr
cpsdb_matcol(# group by 1,2
cpsdb_matcol(# )
cpsdb_matcol-# ,qou as (
cpsdb_matcol(# select
cpsdb_matcol(# curr_season
cpsdb_matcol(# ,curr_code
cpsdb_matcol(# ,array_agg(id_ou order by id_ou)||array_fill(0::smallint,array[6]) as mat_arr
cpsdb_matcol(# ,array_agg(curr_mat_code order by id_ou) as matcode_arr
cpsdb_matcol(# ,bit_or(imask) as ibitmask
cpsdb_matcol(# from(
cpsdb_matcol(# select
cpsdb_matcol(# sou_season as curr_season
cpsdb_matcol(# ,sou_sa_code as curr_code
cpsdb_matcol(# ,sou_mat_code as curr_mat_code
cpsdb_matcol(# ,sou_clr_code as curr_clr_code
cpsdb_matcol(# ,id_ou
cpsdb_matcol(# ,coalesce(id_ou,-1) as imask
cpsdb_matcol(# from :only sa_outsole
cpsdb_matcol(# left join outsole_target on ou_mat_code=sou_mat_code and id_ou <= (32-1-2)
cpsdb_matcol(# )qr
cpsdb_matcol(# group by 1,2
cpsdb_matcol(# )
cpsdb_matcol-# ,qupd as (
cpsdb_matcol(# select
cpsdb_matcol(# qup.curr_season
cpsdb_matcol(# ,qup.curr_code
cpsdb_matcol(# ,qup.ibitmask|qin.ibitmask|qli.ibitmask|qou.ibitmask as ibitmask
cpsdb_matcol(# -- the calculations of new_mat_x are simplified here
cpsdb_matcol(# -- in the production version they are a more complex combination of bit masks, bit shifts and bit or of different elements of the arrays
cpsdb_matcol(# ,(qup.mat_arr[1]|qli.mat_arr[1]|qin.mat_arr[1]|qou.mat_arr[1])::bigint as new_mat_1
cpsdb_matcol(#
cpsdb_matcol(# ,(qup.mat_arr[2]|qli.mat_arr[2]|qin.mat_arr[2]|qou.mat_arr[2])::bigint as new_mat_2
cpsdb_matcol(#
cpsdb_matcol(# ,(qup.mat_arr[3]|qli.mat_arr[3]|qin.mat_arr[3]|qou.mat_arr[3])::bigint as new_mat_3
cpsdb_matcol(#
cpsdb_matcol(# from qup
cpsdb_matcol(# join qli on (qli.curr_season=qup.curr_season and qli.curr_code=qup.curr_code and qli.ibitmask>0 and cardinality(qli.mat_arr) <=8)
cpsdb_matcol(# join qin on (qin.curr_season=qup.curr_season and qin.curr_code=qup.curr_code and qin.ibitmask>0 and cardinality(qin.mat_arr) <=8)
cpsdb_matcol(# join qou on (qou.curr_season=qup.curr_season and qou.curr_code=qup.curr_code and qou.ibitmask>0 and cardinality(qou.mat_arr) <=11)
cpsdb_matcol(# where qup.ibitmask>0 and cardinality(qup.mat_arr) <=21
cpsdb_matcol(# )
cpsdb_matcol-# ,qupda as (
cpsdb_matcol(# select
cpsdb_matcol(# qup.curr_season
cpsdb_matcol(# ,qup.curr_code
cpsdb_matcol(# ,repeat('0',64)||
cpsdb_matcol(# repeat('11',coalesce(cardinality(qou.matcode_arr),0))||repeat('10',coalesce(cardinality(qin.matcode_arr),0))||
cpsdb_matcol(# repeat('01',coalesce(cardinality(qou.matcode_arr),0))||repeat('00',coalesce(cardinality(qup.matcode_arr),0))||
cpsdb_matcol(# '00' as curr_mattype_bitmask
cpsdb_matcol(# ,qup.matcode_arr||qli.matcode_arr||qin.matcode_arr||qou.matcode_arr as curr_matcode_arr
cpsdb_matcol(# from qup
cpsdb_matcol(# left join qli on qli.curr_season=qup.curr_season and qli.curr_code=qup.curr_code and (qli.ibitmask<0 or cardinality(qli.mat_arr) >8)
cpsdb_matcol(# left join qin on qin.curr_season=qup.curr_season and qin.curr_code=qup.curr_code and (qin.ibitmask<0 or cardinality(qin.mat_arr) >8)
cpsdb_matcol(# left join qou on qou.curr_season=qup.curr_season and qou.curr_code=qup.curr_code and (qou.ibitmask<0 or cardinality(qou.mat_arr) >11)
cpsdb_matcol(# where qup.ibitmask<0 or cardinality(qup.mat_arr) >21
cpsdb_matcol(# )
cpsdb_matcol-# select
cpsdb_matcol-# curr_season
cpsdb_matcol-# ,curr_code
cpsdb_matcol-# ,new_mat_1
cpsdb_matcol-# ,new_mat_2
cpsdb_matcol-# ,new_mat_3
cpsdb_matcol-# ,NULL::bigint as new_mattype_bitmask
cpsdb_matcol-# ,NULL as new_mat_codes
cpsdb_matcol-# from qupd
cpsdb_matcol-# union all
cpsdb_matcol-# select
cpsdb_matcol-# curr_season
cpsdb_matcol-# ,curr_code
cpsdb_matcol-# ,NULL::bigint as new_mat_1
cpsdb_matcol-# ,NULL::bigint as new_mat_2
cpsdb_matcol-# ,NULL::bigint as new_mat_3
cpsdb_matcol-# ,substr(curr_mattype_bitmask,length(curr_mattype_bitmask)-63)::bit(64)::bigint as new_mattype_bitmask
cpsdb_matcol-# ,curr_matcode_arr as new_mat_codes
cpsdb_matcol-# from qupda
cpsdb_matcol-# ;
QUERY PLAN
--------------------------------------------------------------------------------------------------------------------------------------------
Append (cost=13365.31..17471.72 rows=5734 width=104) (actual time=139.730..13430.641 rows=9963 loops=1)
CTE qup
-> GroupAggregate (cost=5231.22..6303.78 rows=10320 width=80) (actual time=35.337..67.779 rows=10735 loops=1)
Group Key: sa_upper.sup_season, sa_upper.sup_sa_code
-> Sort (cost=5231.22..5358.64 rows=50969 width=18) (actual time=35.326..36.704 rows=50969 loops=1)
Sort Key: sa_upper.sup_season, sa_upper.sup_sa_code COLLATE "C"
Sort Method: quicksort Memory: 4722kB
-> Hash Left Join (cost=41.71..1246.13 rows=50969 width=18) (actual time=0.179..10.787 rows=50969 loops=1)
Hash Cond: ((sa_upper.sup_mat_code)::text = upper_target.up_mat_code)
-> Seq Scan on sa_upper (cost=0.00..884.69 rows=50969 width=16) (actual time=0.009..1.990 rows=50969 loops=1)
-> Hash (cost=35.53..35.53 rows=495 width=6) (actual time=0.164..0.164 rows=495 loops=1)
Buckets: 1024 Batches: 1 Memory Usage: 27kB
-> Seq Scan on upper_target (cost=0.00..35.53 rows=495 width=6) (actual time=0.006..0.128 rows=495 loops=1)
Filter: (id_up <= 495)
Rows Removed by Filter: 1467
CTE qli
-> GroupAggregate (cost=1097.31..1486.56 rows=10469 width=80) (actual time=9.434..27.620 rows=10469 loops=1)
Group Key: sa_lining.sli_season, sa_lining.sli_sa_code
-> Sort (cost=1097.31..1126.74 rows=11774 width=18) (actual time=9.424..9.796 rows=11774 loops=1)
Sort Key: sa_lining.sli_season, sa_lining.sli_sa_code COLLATE "C"
Sort Method: quicksort Memory: 1120kB
-> Hash Left Join (cost=7.34..301.19 rows=11774 width=18) (actual time=0.049..2.444 rows=11774 loops=1)
Hash Cond: ((sa_lining.sli_mat_code)::text = lining_target.li_mat_code)
-> Seq Scan on sa_lining (cost=0.00..204.74 rows=11774 width=16) (actual time=0.009..0.476 rows=11774 loops=1)
-> Hash (cost=5.86..5.86 rows=118 width=6) (actual time=0.036..0.036 rows=119 loops=1)
Buckets: 1024 Batches: 1 Memory Usage: 13kB
-> Seq Scan on lining_target (cost=0.00..5.86 rows=118 width=6) (actual time=0.008..0.026 rows=119 loops=1)
Filter: (id_li <= 119)
Rows Removed by Filter: 190
CTE qin
-> GroupAggregate (cost=1427.34..1880.73 rows=10678 width=80) (actual time=11.578..31.510 rows=10678 loops=1)
Group Key: sa_insole.sin_season, sa_insole.sin_sa_code
-> Sort (cost=1427.34..1465.41 rows=15230 width=18) (actual time=11.572..12.044 rows=15230 loops=1)
Sort Key: sa_insole.sin_season, sa_insole.sin_sa_code COLLATE "C"
Sort Method: quicksort Memory: 1336kB
-> Hash Left Join (cost=10.49..369.26 rows=15230 width=18) (actual time=0.056..3.120 rows=15230 loops=1)
Hash Cond: ((sa_insole.sin_mat_code)::text = insole_target.in_mat_code)
-> Seq Scan on sa_insole (cost=0.00..264.30 rows=15230 width=16) (actual time=0.008..0.609 rows=15230 loops=1)
-> Hash (cost=9.01..9.01 rows=118 width=6) (actual time=0.044..0.045 rows=119 loops=1)
Buckets: 1024 Batches: 1 Memory Usage: 13kB
-> Seq Scan on insole_target (cost=0.00..9.01 rows=118 width=6) (actual time=0.008..0.033 rows=119 loops=1)
Filter: (id_in <= 119)
Rows Removed by Filter: 362
CTE qou
-> GroupAggregate (cost=2366.22..2986.89 rows=10699 width=80) (actual time=18.295..51.236 rows=10699 loops=1)
Group Key: sa_outsole.sou_season, sa_outsole.sou_sa_code
-> Sort (cost=2366.22..2428.14 rows=24768 width=18) (actual time=18.281..20.157 rows=24768 loops=1)
Sort Key: sa_outsole.sou_season, sa_outsole.sou_sa_code COLLATE "C"
Sort Method: quicksort Memory: 2317kB
-> Hash Left Join (cost=5.39..558.63 rows=24768 width=18) (actual time=0.036..5.080 rows=24768 loops=1)
Hash Cond: ((sa_outsole.sou_mat_code)::text = outsole_target.ou_mat_code)
-> Seq Scan on sa_outsole (cost=0.00..430.68 rows=24768 width=16) (actual time=0.009..1.017 rows=24768 loops=1)
-> Hash (cost=5.03..5.03 rows=29 width=6) (actual time=0.024..0.025 rows=29 loops=1)
Buckets: 1024 Batches: 1 Memory Usage: 10kB
-> Seq Scan on outsole_target (cost=0.00..5.03 rows=29 width=6) (actual time=0.007..0.018 rows=29 loops=1)
Filter: (id_ou <= 29)
Rows Removed by Filter: 213
-> Nested Loop (cost=707.35..1327.91 rows=1 width=104) (actual time=139.729..13423.084 rows=8548 loops=1)
Join Filter: ((qli.curr_season = qin.curr_season) AND ((qli.curr_code)::text = (qin.curr_code)::text))
Rows Removed by Join Filter: 88552397
-> Hash Join (cost=707.35..1016.45 rows=1 width=216) (actual time=128.145..169.287 rows=8685 loops=1)
Hash Cond: ((qou.curr_season = qli.curr_season) AND ((qou.curr_code)::text = (qli.curr_code)::text))
-> CTE Scan on qou (cost=0.00..294.22 rows=1189 width=72) (actual time=18.297..55.085 rows=10275 loops=1)
Filter: ((ibitmask > 0) AND (cardinality(mat_arr) <= 11))
Rows Removed by Filter: 424
-> Hash (cost=706.86..706.86 rows=33 width=144) (actual time=109.843..109.845 rows=9007 loops=1)
Buckets: 16384 (originally 1024) Batches: 1 (originally 1) Memory Usage: 1369kB
-> Merge Join (cost=689.20..706.86 rows=33 width=144) (actual time=105.294..108.377 rows=9007 loops=1)
Merge Cond: ((qup.curr_season = qli.curr_season) AND ((qup.curr_code)::text = (qli.curr_code)::text))
-> Sort (cost=342.09..344.96 rows=1147 width=72) (actual time=72.693..72.923 rows=9320 loops=1)
Sort Key: qup.curr_season, qup.curr_code COLLATE "C"
Sort Method: quicksort Memory: 1357kB
-> CTE Scan on qup (cost=0.00..283.80 rows=1147 width=72) (actual time=35.339..71.419 rows=9320 loops=1)
Filter: ((ibitmask > 0) AND (cardinality(mat_arr) <= 21))
Rows Removed by Filter: 1415
-> Sort (cost=347.12..350.02 rows=1163 width=72) (actual time=32.598..32.861 rows=10289 loops=1)
Sort Key: qli.curr_season, qli.curr_code COLLATE "C"
Sort Method: quicksort Memory: 1269kB
-> CTE Scan on qli (cost=0.00..287.90 rows=1163 width=72) (actual time=9.436..30.852 rows=10289 loops=1)
Filter: ((ibitmask > 0) AND (cardinality(mat_arr) <= 8))
Rows Removed by Filter: 180
-> CTE Scan on qin (cost=0.00..293.65 rows=1186 width=72) (actual time=0.001..1.163 rows=10197 loops=8685)
Filter: ((ibitmask > 0) AND (cardinality(mat_arr) <= 8))
Rows Removed by Filter: 481
-> Merge Left Join (cost=2625.49..3399.84 rows=5733 width=104) (actual time=4.622..6.715 rows=1415 loops=1)
Merge Cond: ((qup_1.curr_season = qou_1.curr_season) AND ((qup_1.curr_code)::text = (qou_1.curr_code)::text))
-> Merge Left Join (cost=1958.66..2135.28 rows=5733 width=136) (actual time=3.489..3.937 rows=1415 loops=1)
Merge Cond: ((qup_1.curr_season = qin_1.curr_season) AND ((qup_1.curr_code)::text = (qin_1.curr_code)::text))
-> Merge Left Join (cost=1293.25..1388.21 rows=5733 width=104) (actual time=2.376..2.614 rows=1415 loops=1)
Merge Cond: ((qup_1.curr_season = qli_1.curr_season) AND ((qup_1.curr_code)::text = (qli_1.curr_code)::text))
-> Sort (cost=641.68..656.02 rows=5733 width=72) (actual time=1.300..1.337 rows=1415 loops=1)
Sort Key: qup_1.curr_season, qup_1.curr_code COLLATE "C"
Sort Method: quicksort Memory: 204kB
-> CTE Scan on qup qup_1 (cost=0.00..283.80 rows=5733 width=72) (actual time=0.010..1.119 rows=1415 loops=1)
Filter: ((ibitmask < 0) OR (cardinality(mat_arr) > 21))
Rows Removed by Filter: 9320
-> Sort (cost=651.57..666.11 rows=5816 width=72) (actual time=1.073..1.078 rows=180 loops=1)
Sort Key: qli_1.curr_season, qli_1.curr_code COLLATE "C"
Sort Method: quicksort Memory: 41kB
-> CTE Scan on qli qli_1 (cost=0.00..287.90 rows=5816 width=72) (actual time=0.057..1.029 rows=180 loops=1)
Filter: ((ibitmask < 0) OR (cardinality(mat_arr) > 8))
Rows Removed by Filter: 10289
-> Sort (cost=665.41..680.24 rows=5932 width=72) (actual time=1.111..1.124 rows=481 loops=1)
Sort Key: qin_1.curr_season, qin_1.curr_code COLLATE "C"
Sort Method: quicksort Memory: 68kB
-> CTE Scan on qin qin_1 (cost=0.00..293.65 rows=5932 width=72) (actual time=0.016..1.045 rows=481 loops=1)
Filter: ((ibitmask < 0) OR (cardinality(mat_arr) > 8))
Rows Removed by Filter: 10197
-> Sort (cost=666.83..681.69 rows=5944 width=72) (actual time=1.125..1.135 rows=417 loops=1)
Sort Key: qou_1.curr_season, qou_1.curr_code COLLATE "C"
Sort Method: quicksort Memory: 68kB
-> CTE Scan on qou qou_1 (cost=0.00..294.22 rows=5944 width=72) (actual time=0.029..1.063 rows=424 loops=1)
Filter: ((ibitmask < 0) OR (cardinality(mat_arr) > 11))
Rows Removed by Filter: 10275
Planning Time: 0.969 ms
Execution Time: 13432.726 ms
(116 Zeilen)
(All plans are unchanged, cut/pasted from psql window)
In qupd we find the same rows estimations as above, as shown in the lines
-> Hash (cost=706.86..706.86 rows=33 width=144) (actual time=109.843..109.845 rows=9007 loops=1)
-> Nested Loop (cost=707.35..1327.91 rows=1 width=104) (actual time=139.729..13423.084 rows=8548 loops=1)
---------
In both queries I haven't used materialized CTEs explicitely, but the first 4 CTE's are used in 2 different subsequent CTE's.
This query is not fully optimized for frequent use, it is only used for refactoring old data, but finally it will use a 10fold bigger dataset.
(Optimizing could eleminate the cardinality function in join conditions, eliminate materialized CTEs etc).
I only encountered the long execution time in the second query (with inner joins), which let me analyze and dig to the root cause.
The use of the nested loop in the third inner join round took very long and eliminated about 9 million rows (on a quad join with 4 datasets of about 10000 tuples).
I wanted to draw attention on my accidently findings, but I am not able to fully understand or investigate in the source code :-(.
I conclude that the row estimation in this example seems wrong ((left) outer join case) or too strict (inner join case, only 1/33 estimated from the previous step!)
I Hope this updated information may help you
Hans Buschmann
________________________________
Von: Tomas Vondra <tomas.vondra@enterprisedb.com>
Gesendet: Mittwoch, 8. Februar 2023 22:27
An: Hans Buschmann; pgsql-hackers@lists.postgresql.org
Betreff: Re: Wrong rows estimations with joins of CTEs slows queries by more than factor 500
On 2/8/23 14:55, Hans Buschmann wrote:
During data refactoring of our Application I encountered $subject when
joining 4 CTEs with left join or inner join.1. Background
PG 15.1 on Windows x64 (OS seems no to have no meening here)
I try to collect data from 4 (analyzed) tables (up,li,in,ou) by grouping
certain data (4 CTEs qup,qli,qin,qou)The grouping of the data in the CTEs gives estimated row counts of about
1000 (1 tenth of the real value) This is OK for estimation.These 4 CTEs are then used to combine the data by joining them.
2. Problem
The 4 CTEs are joined by left joins as shown below:
...
This case really brought me to detect the problem!
The original query and data are not shown here, but the principle should
be clear from the execution plans.I think the planner shouldn't change the row estimations on further
steps after left joins at all, and be a bit more conservative on inner
joins.
But the code should alredy do exactly that, see:
And in fact, the second part of the plains shows it's doing the trick:
-> Merge Left Join (cost=1293.25..1388.21 rows=5733 width=104)
(actual time=2.321..2.556 rows=1415 loops=1)
Merge Cond: ((qup_1.curr_season = qli_1.curr_season) AND
((qup_1.curr_code)::text = (qli_1.curr_code)::text))
-> Sort (cost=641.68..656.02 rows=5733 width=72)
-> Sort (cost=651.57..666.11 rows=5816 width=72)
But notice the first join (with rows=33) doesn't say "Left". And I see
there's Append on top, so presumably the query is much more complex, and
there's a regular join of these CTEs in some other part.
We'll need to se the whole query, not just one chunk of it.
FWIW it seems you're using materialized CTEs - that's likely pretty bad
for the estimates, because we don't propagate statistics from the CTE.
So a join on CTEs can't see statistics from the underlying tables, and
that can easily produce really bad estimates.
I'm assuming you're not using AS MATERIALIZED explicitly, so I'd bet
this happens because the "cardinality" function is marked as volatile.
Perhaps it can be redefined as stable/immutable.
This may be related to the fact that this case has 2 join-conditions
(xx_season an xx_code).
That shouldn't affect outer join estimates this way (but as I explained
above, the join does not seem to be "left" per the explain).
Multi-column joins can cause issues, no doubt about it - but CTEs make
it worse because we can't e.g. see foreign keys.
regards
--
Tomas Vondra
EnterpriseDB: http://www.enterprisedb.com
The Enterprise PostgreSQL Company
On 2/9/23 10:03, Hans Buschmann wrote:
Hello Tomas,
Thank you for looking at.
First, I miscalculated the factor which should be about 50, not 500. Sorry.
Then I want to show you the table definitions (simple, very similar,
ommited child_tables and additional indexes, here using always "ONLY"):cpsdb_matcol=# \d sa_upper;
Tabelle ╗public.sa_upper½
Spalte | Typ | Sortierfolge | NULL erlaubt? |
Vorgabewert
--------------+-----------------------+--------------+---------------+----------------------------------
id_sup | integer | | not null |
generated by default as identity
sup_season | smallint | | |
sup_sa_code | character varying(10) | C | |
sup_mat_code | character varying(4) | C | |
sup_clr_code | character varying(3) | C | |
Indexe:
"sa_upper_active_pkey" PRIMARY KEY, btree (id_sup)
cpsdb_matcol=# \d sa_lining+;
Tabelle ╗public.sa_lining½
Spalte | Typ | Sortierfolge | NULL erlaubt? |
Vorgabewert
--------------+-----------------------+--------------+---------------+----------------------------------
id_sli | integer | | not null |
generated by default as identity
sli_season | smallint | | |
sli_sa_code | character varying(10) | C | |
sli_mat_code | character varying(4) | C | |
sli_clr_code | character varying(3) | C | |
Indexe:
"sa_lining_active_pkey" PRIMARY KEY, btree (id_sli)
cpsdb_matcol=# \d sa_insole;
Tabelle ╗public.sa_insole½
Spalte | Typ | Sortierfolge | NULL erlaubt? |
Vorgabewert
--------------+-----------------------+--------------+---------------+----------------------------------
id_sin | integer | | not null |
generated by default as identity
sin_season | smallint | | |
sin_sa_code | character varying(10) | C | |
sin_mat_code | character varying(4) | C | |
sin_clr_code | character varying(3) | C | |
Indexe:
"sa_insole_active_pkey" PRIMARY KEY, btree (id_sin)
cpsdb_matcol=# \d sa_outsole;
Tabelle ╗public.sa_outsole½
Spalte | Typ | Sortierfolge | NULL erlaubt? |
Vorgabewert
--------------+-----------------------+--------------+---------------+----------------------------------
id_sou | integer | | not null |
generated by default as identity
sou_season | smallint | | |
sou_sa_code | character varying(10) | C | |
sou_mat_code | character varying(4) | C | |
sou_clr_code | character varying(3) | C | |
Indexe:
"sa_outsole_active_pkey" PRIMARY KEY, btree (id_sou)
The xxx_target tables are very similiar, here the upper one as an example:
They are count_aggregates of the whole dataset, where
up_mat_code=sup_mat_code etc.cpsdb_matcol=# \d upper_target
Tabelle ╗admin.upper_target½
Spalte | Typ | Sortierfolge | NULL erlaubt? | Vorgabewert
-------------+----------+--------------+---------------+-------------
id_up | smallint | | |
nup | integer | | |
up_mat_code | text | C | |I have reworked the two queries to show their complete explain plans:
1. query with left join in the qupd CTE:
\set only 'ONLY'
cpsdb_matcol=# explain analyze -- explain analyze verbose -- explain --
select * from ( -- select count(*) from ( -- select length(sel) from (
cpsdb_matcol-# with
cpsdb_matcol-# qup as (
cpsdb_matcol(# select
cpsdb_matcol(# curr_season -- all xxx_seasosn are always smallint
cpsdb_matcol(# ,curr_code-- all xx_code are always varchar(10)
cpsdb_matcol(# ,array_agg(id_up order by
id_up)||array_fill(0::smallint,array[10]) as mat_arr
cpsdb_matcol(# ,array_agg(curr_mat_code order by id_up) as matcode_arr
cpsdb_matcol(# ,bit_or(imask) as ibitmask
cpsdb_matcol(# from(
cpsdb_matcol(# select
cpsdb_matcol(# sup_season as curr_season
cpsdb_matcol(# ,sup_sa_code as curr_code
cpsdb_matcol(# ,sup_mat_code as curr_mat_code
cpsdb_matcol(# ,sup_clr_code as curr_clr_code
cpsdb_matcol(# ,id_up
cpsdb_matcol(# ,coalesce(id_up,-1) as imask
cpsdb_matcol(# from :only sa_upper
cpsdb_matcol(# left join upper_target on up_mat_code=sup_mat_code and
id_up <= (512-1-16)
cpsdb_matcol(# )qr
cpsdb_matcol(# group by 1,2
cpsdb_matcol(# )
cpsdb_matcol-# ,qli as (
cpsdb_matcol(# select
cpsdb_matcol(# curr_season
cpsdb_matcol(# ,curr_code
cpsdb_matcol(# ,array_agg(id_li order by
id_li)||array_fill(0::smallint,array[4]) as mat_arr
cpsdb_matcol(# ,array_agg(curr_mat_code order by id_li) as matcode_arr
cpsdb_matcol(# ,bit_or(imask) as ibitmask
cpsdb_matcol(# from(
cpsdb_matcol(# select
cpsdb_matcol(# sli_season as curr_season
cpsdb_matcol(# ,sli_sa_code as curr_code
cpsdb_matcol(# ,sli_mat_code as curr_mat_code
cpsdb_matcol(# ,sli_clr_code as curr_clr_code
cpsdb_matcol(# ,id_li
cpsdb_matcol(# ,coalesce(id_li,-1) as imask
cpsdb_matcol(# from :only sa_lining
cpsdb_matcol(# left join lining_target on li_mat_code=sli_mat_code and
id_li <= (128-1-8)
cpsdb_matcol(# )qr
cpsdb_matcol(# group by 1,2
cpsdb_matcol(# )
cpsdb_matcol-# ,qin as (
cpsdb_matcol(# select
cpsdb_matcol(# curr_season
cpsdb_matcol(# ,curr_code
cpsdb_matcol(# ,array_agg(id_in order by
id_in)||array_fill(0::smallint,array[4]) as mat_arr
cpsdb_matcol(# ,array_agg(curr_mat_code order by id_in) as matcode_arr
cpsdb_matcol(# ,bit_or(imask) as ibitmask
cpsdb_matcol(# from(
cpsdb_matcol(# select
cpsdb_matcol(# sin_season as curr_season
cpsdb_matcol(# ,sin_sa_code as curr_code
cpsdb_matcol(# ,sin_mat_code as curr_mat_code
cpsdb_matcol(# ,sin_clr_code as curr_clr_code
cpsdb_matcol(# ,id_in
cpsdb_matcol(# ,coalesce(id_in,-1) as imask
cpsdb_matcol(# from :only sa_insole
cpsdb_matcol(# left join insole_target on in_mat_code=sin_mat_code and
id_in <= (128-1-8)
cpsdb_matcol(# )qr
cpsdb_matcol(# group by 1,2
cpsdb_matcol(# )
cpsdb_matcol-# ,qou as (
cpsdb_matcol(# select
cpsdb_matcol(# curr_season
cpsdb_matcol(# ,curr_code
cpsdb_matcol(# ,array_agg(id_ou order by
id_ou)||array_fill(0::smallint,array[6]) as mat_arr
cpsdb_matcol(# ,array_agg(curr_mat_code order by id_ou) as matcode_arr
cpsdb_matcol(# ,bit_or(imask) as ibitmask
cpsdb_matcol(# from(
cpsdb_matcol(# select
cpsdb_matcol(# sou_season as curr_season
cpsdb_matcol(# ,sou_sa_code as curr_code
cpsdb_matcol(# ,sou_mat_code as curr_mat_code
cpsdb_matcol(# ,sou_clr_code as curr_clr_code
cpsdb_matcol(# ,id_ou
cpsdb_matcol(# ,coalesce(id_ou,-1) as imask
cpsdb_matcol(# from :only sa_outsole
cpsdb_matcol(# left join outsole_target on ou_mat_code=sou_mat_code and
id_ou <= (32-1-2)
cpsdb_matcol(# )qr
cpsdb_matcol(# group by 1,2
cpsdb_matcol(# )
cpsdb_matcol-# ,qupd as (
cpsdb_matcol(# select * from (
cpsdb_matcol(# select
cpsdb_matcol(# qup.curr_season
cpsdb_matcol(# ,qup.curr_code
cpsdb_matcol(# ,qup.ibitmask|qin.ibitmask|qli.ibitmask|qou.ibitmask as
ibitmask
cpsdb_matcol(# -- the calculations of new_mat_x are simplified here
cpsdb_matcol(# -- in the production version they are a more complex
combination of bit masks, bit shifts and bit or of different elements of
the arrays
cpsdb_matcol(#
,(qup.mat_arr[1]|qli.mat_arr[1]|qin.mat_arr[1]|qou.mat_arr[1])::bigint
as new_mat_1
cpsdb_matcol(#
cpsdb_matcol(#
,(qup.mat_arr[2]|qli.mat_arr[2]|qin.mat_arr[2]|qou.mat_arr[2])::bigint
as new_mat_2
cpsdb_matcol(#
cpsdb_matcol(#
,(qup.mat_arr[3]|qli.mat_arr[3]|qin.mat_arr[3]|qou.mat_arr[3])::bigint
as new_mat_3
cpsdb_matcol(#
cpsdb_matcol(# from qup
cpsdb_matcol(# left join qli on (qli.curr_season=qup.curr_season and
qli.curr_code=qup.curr_code and qli.ibitmask>0 and
cardinality(qli.mat_arr) <=8)
cpsdb_matcol(# left join qin on (qin.curr_season=qup.curr_season and
qin.curr_code=qup.curr_code and qin.ibitmask>0 and
cardinality(qin.mat_arr) <=8)
cpsdb_matcol(# left join qou on (qou.curr_season=qup.curr_season and
qou.curr_code=qup.curr_code and qou.ibitmask>0 and
cardinality(qou.mat_arr) <=11)
cpsdb_matcol(# where qup.ibitmask>0 and cardinality(qup.mat_arr) <=21
cpsdb_matcol(# )qj
cpsdb_matcol(# where ibitmask is not null
cpsdb_matcol(# )
cpsdb_matcol-# ,qupda as (
cpsdb_matcol(# select
cpsdb_matcol(# qup.curr_season
cpsdb_matcol(# ,qup.curr_code
cpsdb_matcol(# ,repeat('0',64)||
cpsdb_matcol(#
repeat('11',coalesce(cardinality(qou.matcode_arr),0))||repeat('10',coalesce(cardinality(qin.matcode_arr),0))||
cpsdb_matcol(#
repeat('01',coalesce(cardinality(qou.matcode_arr),0))||repeat('00',coalesce(cardinality(qup.matcode_arr),0))||
cpsdb_matcol(# '00' as curr_mattype_bitmask
cpsdb_matcol(#
,qup.matcode_arr||qli.matcode_arr||qin.matcode_arr||qou.matcode_arr as
curr_matcode_arr
cpsdb_matcol(# from qup
cpsdb_matcol(# left join qli on qli.curr_season=qup.curr_season and
qli.curr_code=qup.curr_code and (qli.ibitmask<0 or
cardinality(qli.mat_arr) >8)
cpsdb_matcol(# left join qin on qin.curr_season=qup.curr_season and
qin.curr_code=qup.curr_code and (qin.ibitmask<0 or
cardinality(qin.mat_arr) >8)
cpsdb_matcol(# left join qou on qou.curr_season=qup.curr_season and
qou.curr_code=qup.curr_code and (qou.ibitmask<0 or
cardinality(qou.mat_arr) >11)
cpsdb_matcol(# where qup.ibitmask<0 or cardinality(qup.mat_arr) >21
cpsdb_matcol(# )
cpsdb_matcol-# select
cpsdb_matcol-# curr_season
cpsdb_matcol-# ,curr_code
cpsdb_matcol-# ,new_mat_1
cpsdb_matcol-# ,new_mat_2
cpsdb_matcol-# ,new_mat_3
cpsdb_matcol-# ,NULL::bigint as new_mattype_bitmask
cpsdb_matcol-# ,NULL as new_mat_codes
cpsdb_matcol-# from qupd
cpsdb_matcol-# union all
cpsdb_matcol-# select
cpsdb_matcol-# curr_season
cpsdb_matcol-# ,curr_code
cpsdb_matcol-# ,NULL::bigint as new_mat_1
cpsdb_matcol-# ,NULL::bigint as new_mat_2
cpsdb_matcol-# ,NULL::bigint as new_mat_3
cpsdb_matcol-#
,substr(curr_mattype_bitmask,length(curr_mattype_bitmask)-63)::bit(64)::bigint as new_mattype_bitmask
cpsdb_matcol-# ,curr_matcode_arr as new_mat_codes
cpsdb_matcol-# from qupda,qup.ibitmask|qin.ibitmask|qli.ibitmask|qou.ibitmask as ibitmask
cpsdb_matcol-# ;
QUERY PLAN
--------------------------------------------------------------------------------------------------------------------------------------------------
Append (cost=13673.81..17462.84 rows=5734 width=104) (actual
time=169.382..210.799 rows=9963 loops=1)
CTE qup
-> GroupAggregate (cost=5231.22..6303.78 rows=10320 width=80)
(actual time=35.064..68.308 rows=10735 loops=1)
Group Key: sa_upper.sup_season, sa_upper.sup_sa_code
-> Sort (cost=5231.22..5358.64 rows=50969 width=18) (actual
time=35.053..36.412 rows=50969 loops=1)
Sort Key: sa_upper.sup_season, sa_upper.sup_sa_code
COLLATE "C"
Sort Method: quicksort Memory: 4722kB
-> Hash Left Join (cost=41.71..1246.13 rows=50969
width=18) (actual time=0.165..10.562 rows=50969 loops=1)
Hash Cond: ((sa_upper.sup_mat_code)::text =
upper_target.up_mat_code)
-> Seq Scan on sa_upper (cost=0.00..884.69
rows=50969 width=16) (actual time=0.006..1.990 rows=50969 loops=1)
-> Hash (cost=35.53..35.53 rows=495 width=6)
(actual time=0.157..0.157 rows=495 loops=1)
Buckets: 1024 Batches: 1 Memory Usage: 27kB
-> Seq Scan on upper_target
(cost=0.00..35.53 rows=495 width=6) (actual time=0.006..0.115 rows=495
loops=1)
Filter: (id_up <= 495)
Rows Removed by Filter: 1467
CTE qli
-> GroupAggregate (cost=1097.31..1486.56 rows=10469 width=80)
(actual time=9.354..28.199 rows=10469 loops=1)
Group Key: sa_lining.sli_season, sa_lining.sli_sa_code
-> Sort (cost=1097.31..1126.74 rows=11774 width=18) (actual
time=9.347..9.711 rows=11774 loops=1)
Sort Key: sa_lining.sli_season, sa_lining.sli_sa_code
COLLATE "C"
Sort Method: quicksort Memory: 1120kB
-> Hash Left Join (cost=7.34..301.19 rows=11774
width=18) (actual time=0.049..2.397 rows=11774 loops=1)
Hash Cond: ((sa_lining.sli_mat_code)::text =
lining_target.li_mat_code)
-> Seq Scan on sa_lining (cost=0.00..204.74
rows=11774 width=16) (actual time=0.009..0.469 rows=11774 loops=1)
-> Hash (cost=5.86..5.86 rows=118 width=6)
(actual time=0.037..0.037 rows=119 loops=1)
Buckets: 1024 Batches: 1 Memory Usage: 13kB
-> Seq Scan on lining_target
(cost=0.00..5.86 rows=118 width=6) (actual time=0.008..0.025 rows=119
loops=1)
Filter: (id_li <= 119)
Rows Removed by Filter: 190
CTE qin
-> GroupAggregate (cost=1427.34..1880.73 rows=10678 width=80)
(actual time=11.453..32.317 rows=10678 loops=1)
Group Key: sa_insole.sin_season, sa_insole.sin_sa_code
-> Sort (cost=1427.34..1465.41 rows=15230 width=18) (actual
time=11.444..11.943 rows=15230 loops=1)
Sort Key: sa_insole.sin_season, sa_insole.sin_sa_code
COLLATE "C"
Sort Method: quicksort Memory: 1336kB
-> Hash Left Join (cost=10.49..369.26 rows=15230
width=18) (actual time=0.051..3.098 rows=15230 loops=1)
Hash Cond: ((sa_insole.sin_mat_code)::text =
insole_target.in_mat_code)
-> Seq Scan on sa_insole (cost=0.00..264.30
rows=15230 width=16) (actual time=0.007..0.608 rows=15230 loops=1)
-> Hash (cost=9.01..9.01 rows=118 width=6)
(actual time=0.041..0.041 rows=119 loops=1)
Buckets: 1024 Batches: 1 Memory Usage: 13kB
-> Seq Scan on insole_target
(cost=0.00..9.01 rows=118 width=6) (actual time=0.007..0.031 rows=119
loops=1)
Filter: (id_in <= 119)
Rows Removed by Filter: 362
CTE qou
-> GroupAggregate (cost=2366.22..2986.89 rows=10699 width=80)
(actual time=18.055..42.079 rows=10699 loops=1)
Group Key: sa_outsole.sou_season, sa_outsole.sou_sa_code
-> Sort (cost=2366.22..2428.14 rows=24768 width=18) (actual
time=18.043..18.798 rows=24768 loops=1)
Sort Key: sa_outsole.sou_season, sa_outsole.sou_sa_code
COLLATE "C"
Sort Method: quicksort Memory: 2317kB
-> Hash Left Join (cost=5.39..558.63 rows=24768
width=18) (actual time=0.037..5.017 rows=24768 loops=1)
Hash Cond: ((sa_outsole.sou_mat_code)::text =
outsole_target.ou_mat_code)
-> Seq Scan on sa_outsole (cost=0.00..430.68
rows=24768 width=16) (actual time=0.008..0.998 rows=24768 loops=1)
-> Hash (cost=5.03..5.03 rows=29 width=6)
(actual time=0.025..0.025 rows=29 loops=1)
Buckets: 1024 Batches: 1 Memory Usage: 10kB
-> Seq Scan on outsole_target
(cost=0.00..5.03 rows=29 width=6) (actual time=0.009..0.020 rows=29 loops=1)
Filter: (id_ou <= 29)
Rows Removed by Filter: 213
-> Hash Join (cost=1015.85..1319.04 rows=1 width=104) (actual
time=169.382..203.707 rows=8548 loops=1)
Hash Cond: ((qou.curr_season = qli.curr_season) AND
((qou.curr_code)::text = (qli.curr_code)::text))
Join Filter: ((((qup.ibitmask | qin.ibitmask) | qli.ibitmask) |
qou.ibitmask) IS NOT NULL)
-> CTE Scan on qou (cost=0.00..294.22 rows=1189 width=76)
(actual time=18.057..45.448 rows=10275 loops=1)
Filter: ((ibitmask > 0) AND (cardinality(mat_arr) <= 11))
Rows Removed by Filter: 424
-> Hash (cost=1015.83..1015.83 rows=1 width=228) (actual
time=151.316..151.317 rows=8845 loops=1)
Buckets: 16384 (originally 1024) Batches: 1 (originally
1) Memory Usage: 1899kB
-> Hash Join (cost=707.35..1015.83 rows=1 width=228)
(actual time=122.483..149.030 rows=8845 loops=1)
Hash Cond: ((qin.curr_season = qli.curr_season) AND
((qin.curr_code)::text = (qli.curr_code)::text))
-> CTE Scan on qin (cost=0.00..293.65 rows=1186
width=76) (actual time=11.454..35.456 rows=10197 loops=1)
Filter: ((ibitmask > 0) AND
(cardinality(mat_arr) <= 8))
Rows Removed by Filter: 481
-> Hash (cost=706.86..706.86 rows=33 width=152)
(actual time=111.026..111.027 rows=9007 loops=1)
Buckets: 16384 (originally 1024) Batches: 1
(originally 1) Memory Usage: 1473kB
-> Merge Join (cost=689.20..706.86 rows=33
width=152) (actual time=106.441..109.505 rows=9007 loops=1)
Merge Cond: ((qup.curr_season =
qli.curr_season) AND ((qup.curr_code)::text = (qli.curr_code)::text))
-> Sort (cost=342.09..344.96
rows=1147 width=76) (actual time=73.200..73.429 rows=9320 loops=1)
Sort Key: qup.curr_season,
qup.curr_code COLLATE "C"
Sort Method: quicksort Memory:
1391kB
-> CTE Scan on qup
(cost=0.00..283.80 rows=1147 width=76) (actual time=35.067..71.872
rows=9320 loops=1)
Filter: ((ibitmask > 0) AND
(cardinality(mat_arr) <= 21))
Rows Removed by Filter: 1415
-> Sort (cost=347.12..350.02
rows=1163 width=76) (actual time=33.239..33.490 rows=10289 loops=1)
Sort Key: qli.curr_season,
qli.curr_code COLLATE "C"
Sort Method: quicksort Memory:
1349kB
-> CTE Scan on qli
(cost=0.00..287.90 rows=1163 width=76) (actual time=9.355..31.457
rows=10289 loops=1)
Filter: ((ibitmask > 0) AND
(cardinality(mat_arr) <= 8))
Rows Removed by Filter: 180
-> Merge Left Join (cost=2625.49..3399.84 rows=5733 width=104)
(actual time=4.529..6.645 rows=1415 loops=1)
Merge Cond: ((qup_1.curr_season = qou_1.curr_season) AND
((qup_1.curr_code)::text = (qou_1.curr_code)::text))
-> Merge Left Join (cost=1958.66..2135.28 rows=5733
width=136) (actual time=3.388..3.833 rows=1415 loops=1)
Merge Cond: ((qup_1.curr_season = qin_1.curr_season) AND
((qup_1.curr_code)::text = (qin_1.curr_code)::text))
-> Merge Left Join (cost=1293.25..1388.21 rows=5733
width=104) (actual time=2.297..2.534 rows=1415 loops=1)
Merge Cond: ((qup_1.curr_season =
qli_1.curr_season) AND ((qup_1.curr_code)::text = (qli_1.curr_code)::text))
-> Sort (cost=641.68..656.02 rows=5733 width=72)
(actual time=1.278..1.315 rows=1415 loops=1)
Sort Key: qup_1.curr_season, qup_1.curr_code
COLLATE "C"
Sort Method: quicksort Memory: 204kB
-> CTE Scan on qup qup_1 (cost=0.00..283.80
rows=5733 width=72) (actual time=0.009..1.081 rows=1415 loops=1)
Filter: ((ibitmask < 0) OR
(cardinality(mat_arr) > 21))
Rows Removed by Filter: 9320
-> Sort (cost=651.57..666.11 rows=5816 width=72)
(actual time=1.017..1.022 rows=180 loops=1)
Sort Key: qli_1.curr_season, qli_1.curr_code
COLLATE "C"
Sort Method: quicksort Memory: 41kB
-> CTE Scan on qli qli_1 (cost=0.00..287.90
rows=5816 width=72) (actual time=0.054..0.994 rows=180 loops=1)
Filter: ((ibitmask < 0) OR
(cardinality(mat_arr) > 8))
Rows Removed by Filter: 10289
-> Sort (cost=665.41..680.24 rows=5932 width=72)
(actual time=1.089..1.103 rows=481 loops=1)
Sort Key: qin_1.curr_season, qin_1.curr_code
COLLATE "C"
Sort Method: quicksort Memory: 68kB
-> CTE Scan on qin qin_1 (cost=0.00..293.65
rows=5932 width=72) (actual time=0.016..1.022 rows=481 loops=1)
Filter: ((ibitmask < 0) OR
(cardinality(mat_arr) > 8))
Rows Removed by Filter: 10197
-> Sort (cost=666.83..681.69 rows=5944 width=72) (actual
time=1.134..1.145 rows=417 loops=1)
Sort Key: qou_1.curr_season, qou_1.curr_code COLLATE "C"
Sort Method: quicksort Memory: 68kB
-> CTE Scan on qou qou_1 (cost=0.00..294.22 rows=5944
width=72) (actual time=0.029..1.038 rows=424 loops=1)
Filter: ((ibitmask < 0) OR (cardinality(mat_arr) > 11))
Rows Removed by Filter: 10275
Planning Time: 1.055 ms
Execution Time: 212.800 ms
(118 Zeilen)As seen in the line of the qupd CTE
-> Merge Join (cost=689.20..706.86 rows=33
width=152) (actual time=106.441..109.505 rows=9007 loops=1)the row count of the second join round drops to 33 and for the third
round it drops to 1-> Hash Join (cost=707.35..1015.83 rows=1 width=228)
(actual time=122.483..149.030 rows=8845 loops=1)BTW, I don't know, why the second join group (part of qupda) gets a
complete different plan.
It gets a different plan because the "qupd" CTE does this:
SELECT
...
,qup.ibitmask|qin.ibitmask|qli.ibitmask|qou.ibitmask as ibitmask
...
FROM ... left join of the CTEs
WHERE qup.ibitmask>0 AND ..
Which means all the inputs must be non-NULL, hence the optimizer changes
the plan to inner join (and that seems to be perfectly correct).
I think this suggests this join cardinality estimation is not the real
issue. The estimates are off, but there's an order of magnitude
difference for the scans, like here:
-> CTE Scan on qup (cost=0.00..283.80 rows=1147 width=72)
(actual time=35.339..71.419 rows=9320 loops=1)
and this tends to "snowball" in the join estimation (it amplifies the
issue - it can't really improve them, except by chance).
FWIW the UNION ALL also explains why we materialize the CTEs, because by
default we fold CTEs into the query only when there's a single
reference. And here both "qupd" and "qupda" reference them.
I'd suggest adding AS NOT MATERIALIZED to the CTEs, to fold them into
the main query despite multiple references. That might improve the
estimate, with a bit of luck.
If not, you'll need to look into improving the scan estimates first,
it's pointless to try to make join estimates better when the input
estimates are this off. This however depends on the conditions, and as
the CTEs do aggregations that may not be possible.
FWIW I suggest you provide the data in a form that's easier to use (like
a working SQL script). More people are likely to look and help than when
they have to extract stuff from an e-mail, fill in missing pieces etc.
regards
--
Tomas Vondra
EnterpriseDB: http://www.enterprisedb.com
The Enterprise PostgreSQL Company
FWIW I suggest you provide the data in a form that's easier to use (like
a working SQL script). More people are likely to look and help than when
they have to extract stuff from an e-mail, fill in missing pieces etc.
BTW if anyone wants to play with this, here are the SQL scripts I used
to create the tables and the queries. There's no data, but it's enough
to see how the plans change.
regards
--
Tomas Vondra
EnterpriseDB: http://www.enterprisedb.com
The Enterprise PostgreSQL Company
Hi hackers,
I have written a patch to add stats info for Vars in CTEs. With this patch, the join size estimation on the upper of CTE scans became more accurate.
In the function selfuncs.c:eqjoinsel it uses the number of the distinct values of the two join variables to estimate join size, and in the function selfuncs.c:get_variable_numdistinct return a default value DEFAULT_NUM_DISTINCT (200 in Postgres and 1000 in Greenplum), with the default value, you can never expect a good plan.
Thanks if anyone could give a review.
Regards,
Jian
________________________________
From: Hans Buschmann <buschmann@nidsa.net>
Sent: Wednesday, February 8, 2023 21:55
To: pgsql-hackers@lists.postgresql.org <pgsql-hackers@lists.postgresql.org>
Subject: Wrong rows estimations with joins of CTEs slows queries by more than factor 500
!! External Email
During data refactoring of our Application I encountered $subject when joining 4 CTEs with left join or inner join.
1. Background
PG 15.1 on Windows x64 (OS seems no to have no meening here)
I try to collect data from 4 (analyzed) tables (up,li,in,ou) by grouping certain data (4 CTEs qup,qli,qin,qou)
The grouping of the data in the CTEs gives estimated row counts of about 1000 (1 tenth of the real value) This is OK for estimation.
These 4 CTEs are then used to combine the data by joining them.
2. Problem
The 4 CTEs are joined by left joins as shown below:
from qup
left join qli on (qli.curr_season=qup.curr_season and qli.curr_code=qup.curr_code and qli.ibitmask>0 and cardinality(qli.mat_arr) <=8)
left join qin on (qin.curr_season=qup.curr_season and qin.curr_code=qup.curr_code and qin.ibitmask>0 and cardinality(qin.mat_arr) <=8)
left join qou on (qou.curr_season=qup.curr_season and qou.curr_code=qup.curr_code and qou.ibitmask>0 and cardinality(qou.mat_arr) <=11)
where qup.ibitmask>0 and cardinality(qup.mat_arr) <=21
The plan first retrieves qup and qli, taking the estimated row counts of 1163 and 1147 respectively
BUT the result is then hashed and the row count is estimated as 33!
In a Left join the row count stays always the same as the one of left table (here qup with 1163 rows)
The same algorithm which reduces the row estimation from 1163 to 33 is used in the next step to give an estimation of 1 row.
This is totally wrong.
Here is the execution plan of the query:
(search the plan for rows=33)
QUERY PLAN
--------------------------------------------------------------------------------------------------------------------------------------------------
Append (cost=13673.81..17463.30 rows=5734 width=104) (actual time=168.307..222.670 rows=9963 loops=1)
CTE qup
-> GroupAggregate (cost=5231.22..6303.78 rows=10320 width=80) (actual time=35.466..68.131 rows=10735 loops=1)
Group Key: sa_upper.sup_season, sa_upper.sup_sa_code
-> Sort (cost=5231.22..5358.64 rows=50969 width=18) (actual time=35.454..36.819 rows=50969 loops=1)
Sort Key: sa_upper.sup_season, sa_upper.sup_sa_code COLLATE "C"
Sort Method: quicksort Memory: 4722kB
-> Hash Left Join (cost=41.71..1246.13 rows=50969 width=18) (actual time=0.148..10.687 rows=50969 loops=1)
Hash Cond: ((sa_upper.sup_mat_code)::text = upper_target.up_mat_code)
-> Seq Scan on sa_upper (cost=0.00..884.69 rows=50969 width=16) (actual time=0.005..1.972 rows=50969 loops=1)
-> Hash (cost=35.53..35.53 rows=495 width=6) (actual time=0.140..0.140 rows=495 loops=1)
Buckets: 1024 Batches: 1 Memory Usage: 27kB
-> Seq Scan on upper_target (cost=0.00..35.53 rows=495 width=6) (actual time=0.007..0.103 rows=495 loops=1)
Filter: (id_up <= 495)
Rows Removed by Filter: 1467
CTE qli
-> GroupAggregate (cost=1097.31..1486.56 rows=10469 width=80) (actual time=9.446..27.388 rows=10469 loops=1)
Group Key: sa_lining.sli_season, sa_lining.sli_sa_code
-> Sort (cost=1097.31..1126.74 rows=11774 width=18) (actual time=9.440..9.811 rows=11774 loops=1)
Sort Key: sa_lining.sli_season, sa_lining.sli_sa_code COLLATE "C"
Sort Method: quicksort Memory: 1120kB
-> Hash Left Join (cost=7.34..301.19 rows=11774 width=18) (actual time=0.045..2.438 rows=11774 loops=1)
Hash Cond: ((sa_lining.sli_mat_code)::text = lining_target.li_mat_code)
-> Seq Scan on sa_lining (cost=0.00..204.74 rows=11774 width=16) (actual time=0.008..0.470 rows=11774 loops=1)
-> Hash (cost=5.86..5.86 rows=118 width=6) (actual time=0.034..0.034 rows=119 loops=1)
Buckets: 1024 Batches: 1 Memory Usage: 13kB
-> Seq Scan on lining_target (cost=0.00..5.86 rows=118 width=6) (actual time=0.008..0.024 rows=119 loops=1)
Filter: (id_li <= 119)
Rows Removed by Filter: 190
CTE qin
-> GroupAggregate (cost=1427.34..1880.73 rows=10678 width=80) (actual time=11.424..31.508 rows=10678 loops=1)
Group Key: sa_insole.sin_season, sa_insole.sin_sa_code
-> Sort (cost=1427.34..1465.41 rows=15230 width=18) (actual time=11.416..11.908 rows=15230 loops=1)
Sort Key: sa_insole.sin_season, sa_insole.sin_sa_code COLLATE "C"
Sort Method: quicksort Memory: 1336kB
-> Hash Left Join (cost=10.49..369.26 rows=15230 width=18) (actual time=0.051..3.108 rows=15230 loops=1)
Hash Cond: ((sa_insole.sin_mat_code)::text = insole_target.in_mat_code)
-> Seq Scan on sa_insole (cost=0.00..264.30 rows=15230 width=16) (actual time=0.006..0.606 rows=15230 loops=1)
-> Hash (cost=9.01..9.01 rows=118 width=6) (actual time=0.042..0.043 rows=119 loops=1)
Buckets: 1024 Batches: 1 Memory Usage: 13kB
-> Seq Scan on insole_target (cost=0.00..9.01 rows=118 width=6) (actual time=0.008..0.032 rows=119 loops=1)
Filter: (id_in <= 119)
Rows Removed by Filter: 362
CTE qou
-> GroupAggregate (cost=2366.22..2986.89 rows=10699 width=80) (actual time=18.198..41.812 rows=10699 loops=1)
Group Key: sa_outsole.sou_season, sa_outsole.sou_sa_code
-> Sort (cost=2366.22..2428.14 rows=24768 width=18) (actual time=18.187..18.967 rows=24768 loops=1)
Sort Key: sa_outsole.sou_season, sa_outsole.sou_sa_code COLLATE "C"
Sort Method: quicksort Memory: 2317kB
-> Hash Left Join (cost=5.39..558.63 rows=24768 width=18) (actual time=0.046..5.132 rows=24768 loops=1)
Hash Cond: ((sa_outsole.sou_mat_code)::text = outsole_target.ou_mat_code)
-> Seq Scan on sa_outsole (cost=0.00..430.68 rows=24768 width=16) (actual time=0.010..1.015 rows=24768 loops=1)
-> Hash (cost=5.03..5.03 rows=29 width=6) (actual time=0.032..0.032 rows=29 loops=1)
Buckets: 1024 Batches: 1 Memory Usage: 10kB
-> Seq Scan on outsole_target (cost=0.00..5.03 rows=29 width=6) (actual time=0.010..0.025 rows=29 loops=1)
Filter: (id_ou <= 29)
Rows Removed by Filter: 213
-> Hash Join (cost=1015.85..1319.50 rows=1 width=104) (actual time=168.307..215.513 rows=8548 loops=1)
Hash Cond: ((qou.curr_season = qli.curr_season) AND ((qou.curr_code)::text = (qli.curr_code)::text))
Join Filter: ((((qup.ibitmask | qin.ibitmask) | qli.ibitmask) | qou.ibitmask) IS NOT NULL)
-> CTE Scan on qou (cost=0.00..294.22 rows=1189 width=76) (actual time=18.200..45.188 rows=10275 loops=1)
Filter: ((ibitmask > 0) AND (cardinality(mat_arr) <= 11))
Rows Removed by Filter: 424
-> Hash (cost=1015.83..1015.83 rows=1 width=228) (actual time=150.094..150.095 rows=8845 loops=1)
Buckets: 16384 (originally 1024) Batches: 1 (originally 1) Memory Usage: 1899kB
-> Hash Join (cost=707.35..1015.83 rows=1 width=228) (actual time=121.898..147.726 rows=8845 loops=1)
Hash Cond: ((qin.curr_season = qli.curr_season) AND ((qin.curr_code)::text = (qli.curr_code)::text))
-> CTE Scan on qin (cost=0.00..293.65 rows=1186 width=76) (actual time=11.425..34.674 rows=10197 loops=1)
Filter: ((ibitmask > 0) AND (cardinality(mat_arr) <= 8))
Rows Removed by Filter: 481
-> Hash (cost=706.86..706.86 rows=33 width=152) (actual time=110.470..110.470 rows=9007 loops=1)
Buckets: 16384 (originally 1024) Batches: 1 (originally 1) Memory Usage: 1473kB
-> Merge Join (cost=689.20..706.86 rows=33 width=152) (actual time=105.862..108.925 rows=9007 loops=1)
Merge Cond: ((qup.curr_season = qli.curr_season) AND ((qup.curr_code)::text = (qli.curr_code)::text))
-> Sort (cost=342.09..344.96 rows=1147 width=76) (actual time=73.419..73.653 rows=9320 loops=1)
Sort Key: qup.curr_season, qup.curr_code COLLATE "C"
Sort Method: quicksort Memory: 1391kB
-> CTE Scan on qup (cost=0.00..283.80 rows=1147 width=76) (actual time=35.467..71.904 rows=9320 loops=1)
Filter: ((ibitmask > 0) AND (cardinality(mat_arr) <= 21))
Rows Removed by Filter: 1415
-> Sort (cost=347.12..350.02 rows=1163 width=76) (actual time=32.440..32.697 rows=10289 loops=1)
Sort Key: qli.curr_season, qli.curr_code COLLATE "C"
Sort Method: quicksort Memory: 1349kB
-> CTE Scan on qli (cost=0.00..287.90 rows=1163 width=76) (actual time=9.447..30.666 rows=10289 loops=1)
Filter: ((ibitmask > 0) AND (cardinality(mat_arr) <= 8))
Rows Removed by Filter: 180
-> Merge Left Join (cost=2625.49..3399.84 rows=5733 width=104) (actual time=4.597..6.700 rows=1415 loops=1)
Merge Cond: ((qup_1.curr_season = qou_1.curr_season) AND ((qup_1.curr_code)::text = (qou_1.curr_code)::text))
-> Merge Left Join (cost=1958.66..2135.28 rows=5733 width=136) (actual time=3.427..3.863 rows=1415 loops=1)
Merge Cond: ((qup_1.curr_season = qin_1.curr_season) AND ((qup_1.curr_code)::text = (qin_1.curr_code)::text))
-> Merge Left Join (cost=1293.25..1388.21 rows=5733 width=104) (actual time=2.321..2.556 rows=1415 loops=1)
Merge Cond: ((qup_1.curr_season = qli_1.curr_season) AND ((qup_1.curr_code)::text = (qli_1.curr_code)::text))
-> Sort (cost=641.68..656.02 rows=5733 width=72) (actual time=1.286..1.324 rows=1415 loops=1)
Sort Key: qup_1.curr_season, qup_1.curr_code COLLATE "C"
Sort Method: quicksort Memory: 204kB
-> CTE Scan on qup qup_1 (cost=0.00..283.80 rows=5733 width=72) (actual time=0.009..1.093 rows=1415 loops=1)
Filter: ((ibitmask < 0) OR (cardinality(mat_arr) > 21))
Rows Removed by Filter: 9320
-> Sort (cost=651.57..666.11 rows=5816 width=72) (actual time=1.033..1.038 rows=180 loops=1)
Sort Key: qli_1.curr_season, qli_1.curr_code COLLATE "C"
Sort Method: quicksort Memory: 41kB
-> CTE Scan on qli qli_1 (cost=0.00..287.90 rows=5816 width=72) (actual time=0.055..1.007 rows=180 loops=1)
Filter: ((ibitmask < 0) OR (cardinality(mat_arr) > 8))
Rows Removed by Filter: 10289
-> Sort (cost=665.41..680.24 rows=5932 width=72) (actual time=1.104..1.117 rows=481 loops=1)
Sort Key: qin_1.curr_season, qin_1.curr_code COLLATE "C"
Sort Method: quicksort Memory: 68kB
-> CTE Scan on qin qin_1 (cost=0.00..293.65 rows=5932 width=72) (actual time=0.016..1.038 rows=481 loops=1)
Filter: ((ibitmask < 0) OR (cardinality(mat_arr) > 8))
Rows Removed by Filter: 10197
-> Sort (cost=666.83..681.69 rows=5944 width=72) (actual time=1.163..1.174 rows=417 loops=1)
Sort Key: qou_1.curr_season, qou_1.curr_code COLLATE "C"
Sort Method: quicksort Memory: 68kB
-> CTE Scan on qou qou_1 (cost=0.00..294.22 rows=5944 width=72) (actual time=0.029..1.068 rows=424 loops=1)
Filter: ((ibitmask < 0) OR (cardinality(mat_arr) > 11))
Rows Removed by Filter: 10275
Planning Time: 2.297 ms
Execution Time: 224.759 ms
(118 Zeilen)
3. Slow query from wrong plan as result on similar case with inner join
When the 3 left joins above are changed to inner joins like:
from qup
join qli on (qli.curr_season=qup.curr_season and qli.curr_code=qup.curr_code and qli.ibitmask>0 and cardinality(qli.mat_arr) <=8)
join qin on (qin.curr_season=qup.curr_season and qin.curr_code=qup.curr_code and qin.ibitmask>0 and cardinality(qin.mat_arr) <=8)
join qou on (qou.curr_season=qup.curr_season and qou.curr_code=qup.curr_code and qou.ibitmask>0 and cardinality(qou.mat_arr) <=11)
where qup.ibitmask>0 and cardinality(qup.mat_arr) <=21
The same rows estimation takes place as with the left joins, but the planner now decides to use a nested loop for the last join, which results in a 500fold execution time:
QUERY PLAN
--------------------------------------------------------------------------------------------------------------------------------------------
Append (cost=13365.31..17472.18 rows=5734 width=104) (actual time=139.037..13403.310 rows=9963 loops=1)
CTE qup
-> GroupAggregate (cost=5231.22..6303.78 rows=10320 width=80) (actual time=35.399..67.102 rows=10735 loops=1)
Group Key: sa_upper.sup_season, sa_upper.sup_sa_code
-> Sort (cost=5231.22..5358.64 rows=50969 width=18) (actual time=35.382..36.743 rows=50969 loops=1)
Sort Key: sa_upper.sup_season, sa_upper.sup_sa_code COLLATE "C"
Sort Method: quicksort Memory: 4722kB
-> Hash Left Join (cost=41.71..1246.13 rows=50969 width=18) (actual time=0.157..10.715 rows=50969 loops=1)
Hash Cond: ((sa_upper.sup_mat_code)::text = upper_target.up_mat_code)
-> Seq Scan on sa_upper (cost=0.00..884.69 rows=50969 width=16) (actual time=0.008..2.001 rows=50969 loops=1)
-> Hash (cost=35.53..35.53 rows=495 width=6) (actual time=0.146..0.146 rows=495 loops=1)
Buckets: 1024 Batches: 1 Memory Usage: 27kB
-> Seq Scan on upper_target (cost=0.00..35.53 rows=495 width=6) (actual time=0.006..0.105 rows=495 loops=1)
Filter: (id_up <= 495)
Rows Removed by Filter: 1467
CTE qli
-> GroupAggregate (cost=1097.31..1486.56 rows=10469 width=80) (actual time=9.541..27.419 rows=10469 loops=1)
Group Key: sa_lining.sli_season, sa_lining.sli_sa_code
-> Sort (cost=1097.31..1126.74 rows=11774 width=18) (actual time=9.534..9.908 rows=11774 loops=1)
Sort Key: sa_lining.sli_season, sa_lining.sli_sa_code COLLATE "C"
Sort Method: quicksort Memory: 1120kB
-> Hash Left Join (cost=7.34..301.19 rows=11774 width=18) (actual time=0.049..2.451 rows=11774 loops=1)
Hash Cond: ((sa_lining.sli_mat_code)::text = lining_target.li_mat_code)
-> Seq Scan on sa_lining (cost=0.00..204.74 rows=11774 width=16) (actual time=0.010..0.462 rows=11774 loops=1)
-> Hash (cost=5.86..5.86 rows=118 width=6) (actual time=0.035..0.035 rows=119 loops=1)
Buckets: 1024 Batches: 1 Memory Usage: 13kB
-> Seq Scan on lining_target (cost=0.00..5.86 rows=118 width=6) (actual time=0.008..0.025 rows=119 loops=1)
Filter: (id_li <= 119)
Rows Removed by Filter: 190
CTE qin
-> GroupAggregate (cost=1427.34..1880.73 rows=10678 width=80) (actual time=11.649..30.910 rows=10678 loops=1)
Group Key: sa_insole.sin_season, sa_insole.sin_sa_code
-> Sort (cost=1427.34..1465.41 rows=15230 width=18) (actual time=11.642..12.115 rows=15230 loops=1)
Sort Key: sa_insole.sin_season, sa_insole.sin_sa_code COLLATE "C"
Sort Method: quicksort Memory: 1336kB
-> Hash Left Join (cost=10.49..369.26 rows=15230 width=18) (actual time=0.056..3.144 rows=15230 loops=1)
Hash Cond: ((sa_insole.sin_mat_code)::text = insole_target.in_mat_code)
-> Seq Scan on sa_insole (cost=0.00..264.30 rows=15230 width=16) (actual time=0.008..0.594 rows=15230 loops=1)
-> Hash (cost=9.01..9.01 rows=118 width=6) (actual time=0.045..0.046 rows=119 loops=1)
Buckets: 1024 Batches: 1 Memory Usage: 13kB
-> Seq Scan on insole_target (cost=0.00..9.01 rows=118 width=6) (actual time=0.008..0.034 rows=119 loops=1)
Filter: (id_in <= 119)
Rows Removed by Filter: 362
CTE qou
-> GroupAggregate (cost=2366.22..2986.89 rows=10699 width=80) (actual time=18.163..51.151 rows=10699 loops=1)
Group Key: sa_outsole.sou_season, sa_outsole.sou_sa_code
-> Sort (cost=2366.22..2428.14 rows=24768 width=18) (actual time=18.150..20.000 rows=24768 loops=1)
Sort Key: sa_outsole.sou_season, sa_outsole.sou_sa_code COLLATE "C"
Sort Method: quicksort Memory: 2317kB
-> Hash Left Join (cost=5.39..558.63 rows=24768 width=18) (actual time=0.036..5.106 rows=24768 loops=1)
Hash Cond: ((sa_outsole.sou_mat_code)::text = outsole_target.ou_mat_code)
-> Seq Scan on sa_outsole (cost=0.00..430.68 rows=24768 width=16) (actual time=0.008..1.005 rows=24768 loops=1)
-> Hash (cost=5.03..5.03 rows=29 width=6) (actual time=0.024..0.024 rows=29 loops=1)
Buckets: 1024 Batches: 1 Memory Usage: 10kB
-> Seq Scan on outsole_target (cost=0.00..5.03 rows=29 width=6) (actual time=0.007..0.018 rows=29 loops=1)
Filter: (id_ou <= 29)
Rows Removed by Filter: 213
-> Nested Loop (cost=707.35..1328.37 rows=1 width=104) (actual time=139.036..13395.820 rows=8548 loops=1)
Join Filter: ((qli.curr_season = qin.curr_season) AND ((qli.curr_code)::text = (qin.curr_code)::text))
Rows Removed by Join Filter: 88552397
-> Hash Join (cost=707.35..1016.45 rows=1 width=216) (actual time=127.374..168.249 rows=8685 loops=1)
Hash Cond: ((qou.curr_season = qli.curr_season) AND ((qou.curr_code)::text = (qli.curr_code)::text))
-> CTE Scan on qou (cost=0.00..294.22 rows=1189 width=72) (actual time=18.165..54.968 rows=10275 loops=1)
Filter: ((ibitmask > 0) AND (cardinality(mat_arr) <= 11))
Rows Removed by Filter: 424
-> Hash (cost=706.86..706.86 rows=33 width=144) (actual time=109.205..109.207 rows=9007 loops=1)
Buckets: 16384 (originally 1024) Batches: 1 (originally 1) Memory Usage: 1369kB
-> Merge Join (cost=689.20..706.86 rows=33 width=144) (actual time=104.785..107.748 rows=9007 loops=1)
Merge Cond: ((qup.curr_season = qli.curr_season) AND ((qup.curr_code)::text = (qli.curr_code)::text))
-> Sort (cost=342.09..344.96 rows=1147 width=72) (actual time=72.320..72.559 rows=9320 loops=1)
Sort Key: qup.curr_season, qup.curr_code COLLATE "C"
Sort Method: quicksort Memory: 1357kB
-> CTE Scan on qup (cost=0.00..283.80 rows=1147 width=72) (actual time=35.401..70.834 rows=9320 loops=1)
Filter: ((ibitmask > 0) AND (cardinality(mat_arr) <= 21))
Rows Removed by Filter: 1415
-> Sort (cost=347.12..350.02 rows=1163 width=72) (actual time=32.461..32.719 rows=10289 loops=1)
Sort Key: qli.curr_season, qli.curr_code COLLATE "C"
Sort Method: quicksort Memory: 1269kB
-> CTE Scan on qli (cost=0.00..287.90 rows=1163 width=72) (actual time=9.543..30.696 rows=10289 loops=1)
Filter: ((ibitmask > 0) AND (cardinality(mat_arr) <= 8))
Rows Removed by Filter: 180
-> CTE Scan on qin (cost=0.00..293.65 rows=1186 width=72) (actual time=0.001..1.159 rows=10197 loops=8685)
Filter: ((ibitmask > 0) AND (cardinality(mat_arr) <= 8))
Rows Removed by Filter: 481
-> Merge Left Join (cost=2625.49..3399.84 rows=5733 width=104) (actual time=4.606..6.733 rows=1415 loops=1)
Merge Cond: ((qup_1.curr_season = qou_1.curr_season) AND ((qup_1.curr_code)::text = (qou_1.curr_code)::text))
-> Merge Left Join (cost=1958.66..2135.28 rows=5733 width=136) (actual time=3.479..3.930 rows=1415 loops=1)
Merge Cond: ((qup_1.curr_season = qin_1.curr_season) AND ((qup_1.curr_code)::text = (qin_1.curr_code)::text))
-> Merge Left Join (cost=1293.25..1388.21 rows=5733 width=104) (actual time=2.368..2.610 rows=1415 loops=1)
Merge Cond: ((qup_1.curr_season = qli_1.curr_season) AND ((qup_1.curr_code)::text = (qli_1.curr_code)::text))
-> Sort (cost=641.68..656.02 rows=5733 width=72) (actual time=1.296..1.335 rows=1415 loops=1)
Sort Key: qup_1.curr_season, qup_1.curr_code COLLATE "C"
Sort Method: quicksort Memory: 204kB
-> CTE Scan on qup qup_1 (cost=0.00..283.80 rows=5733 width=72) (actual time=0.010..1.119 rows=1415 loops=1)
Filter: ((ibitmask < 0) OR (cardinality(mat_arr) > 21))
Rows Removed by Filter: 9320
-> Sort (cost=651.57..666.11 rows=5816 width=72) (actual time=1.069..1.075 rows=180 loops=1)
Sort Key: qli_1.curr_season, qli_1.curr_code COLLATE "C"
Sort Method: quicksort Memory: 41kB
-> CTE Scan on qli qli_1 (cost=0.00..287.90 rows=5816 width=72) (actual time=0.057..1.026 rows=180 loops=1)
Filter: ((ibitmask < 0) OR (cardinality(mat_arr) > 8))
Rows Removed by Filter: 10289
-> Sort (cost=665.41..680.24 rows=5932 width=72) (actual time=1.110..1.124 rows=481 loops=1)
Sort Key: qin_1.curr_season, qin_1.curr_code COLLATE "C"
Sort Method: quicksort Memory: 68kB
-> CTE Scan on qin qin_1 (cost=0.00..293.65 rows=5932 width=72) (actual time=0.016..1.046 rows=481 loops=1)
Filter: ((ibitmask < 0) OR (cardinality(mat_arr) > 8))
Rows Removed by Filter: 10197
-> Sort (cost=666.83..681.69 rows=5944 width=72) (actual time=1.119..1.128 rows=417 loops=1)
Sort Key: qou_1.curr_season, qou_1.curr_code COLLATE "C"
Sort Method: quicksort Memory: 68kB
-> CTE Scan on qou qou_1 (cost=0.00..294.22 rows=5944 width=72) (actual time=0.029..1.056 rows=424 loops=1)
Filter: ((ibitmask < 0) OR (cardinality(mat_arr) > 11))
Rows Removed by Filter: 10275
Planning Time: 1.746 ms
Execution Time: 13405.503 ms
(116 Zeilen)
This case really brought me to detect the problem!
The original query and data are not shown here, but the principle should be clear from the execution plans.
I think the planner shouldn't change the row estimations on further steps after left joins at all, and be a bit more conservative on inner joins.
This may be related to the fact that this case has 2 join-conditions (xx_season an xx_code).
Thanks for looking
Hans Buschmann
!! External Email: This email originated from outside of the organization. Do not click links or open attachments unless you recognize the sender.
Attachments:
0001-Add-stats-info-for-Vars-in-CTEs.patchtext/x-patch; name=0001-Add-stats-info-for-Vars-in-CTEs.patchDownload
From 29db9f3a6e6592cf277b011b7f92bb7bc0c69baf Mon Sep 17 00:00:00 2001
From: Jian Guo <xihuke@gmail.com>
Date: Wed, 9 Aug 2023 03:32:01 -0400
Subject: [PATCH] Add stats info for Vars in CTEs.
Signed-off-by: Jian Guo <gjian@vmware.com>
---
src/backend/utils/adt/selfuncs.c | 5 ++++-
1 file changed, 4 insertions(+), 1 deletion(-)
diff --git a/src/backend/utils/adt/selfuncs.c b/src/backend/utils/adt/selfuncs.c
index 9f5536c04b6..b70b779a595 100644
--- a/src/backend/utils/adt/selfuncs.c
+++ b/src/backend/utils/adt/selfuncs.c
@@ -5343,7 +5343,10 @@ get_variable_numdistinct(VariableStatData *vardata, bool *isdefault)
stadistinct = getgpsegmentCount();
break;
default:
- stadistinct = 0.0; /* means "unknown" */
+ if (vardata->rel->rtekind == RTE_CTE)
+ stadistinct = -1.0;
+ else
+ stadistinct = 0.0; /* means "unknown" */
break;
}
}
--
2.37.3
Hi,
I haven't looked at the patch, but please add the patch to the next
commit fest (2023-09), so that we don't lose track of it.
See https://commitfest.postgresql.org
regards
Tomas
On 8/14/23 13:12, Jian Guo wrote:
Hi hackers,
I have written a patch to add stats info for Vars in CTEs. With this
patch, the join size estimation on the upper of CTE scans became more
accurate.In the function |selfuncs.c:eqjoinsel| it uses the number of the
distinct values of the two join variables to estimate join size, and in
the function |selfuncs.c:get_variable_numdistinct| return a default
value |DEFAULT_NUM_DISTINCT| (200 in Postgres and 1000 in Greenplum),
with the default value, you can never expect a good plan.Thanks if anyone could give a review.
Regards,
Jian------------------------------------------------------------------------
*From:* Hans Buschmann <buschmann@nidsa.net>
*Sent:* Wednesday, February 8, 2023 21:55
*To:* pgsql-hackers@lists.postgresql.org
<pgsql-hackers@lists.postgresql.org>
*Subject:* Wrong rows estimations with joins of CTEs slows queries by
more than factor 500
!! External Email
During data refactoring of our Application I encountered $subject when
joining 4 CTEs with left join or inner join.1. Background
PG 15.1 on Windows x64 (OS seems no to have no meening here)
I try to collect data from 4 (analyzed) tables (up,li,in,ou) by grouping
certain data (4 CTEs qup,qli,qin,qou)The grouping of the data in the CTEs gives estimated row counts of about
1000 (1 tenth of the real value) This is OK for estimation.These 4 CTEs are then used to combine the data by joining them.
2. Problem
The 4 CTEs are joined by left joins as shown below:
from qup
left join qli on (qli.curr_season=qup.curr_season and
qli.curr_code=qup.curr_code and qli.ibitmask>0 and
cardinality(qli.mat_arr) <=8)
left join qin on (qin.curr_season=qup.curr_season and
qin.curr_code=qup.curr_code and qin.ibitmask>0 and
cardinality(qin.mat_arr) <=8)
left join qou on (qou.curr_season=qup.curr_season and
qou.curr_code=qup.curr_code and qou.ibitmask>0 and
cardinality(qou.mat_arr) <=11)
where qup.ibitmask>0 and cardinality(qup.mat_arr) <=21The plan first retrieves qup and qli, taking the estimated row counts of
1163 and 1147 respectivelyBUT the result is then hashed and the row count is estimated as 33!
In a Left join the row count stays always the same as the one of left
table (here qup with 1163 rows)The same algorithm which reduces the row estimation from 1163 to 33 is
used in the next step to give an estimation of 1 row.This is totally wrong.
Here is the execution plan of the query:
(search the plan for rows=33)
QUERY PLAN
--------------------------------------------------------------------------------------------------------------------------------------------------
Append (cost=13673.81..17463.30 rows=5734 width=104) (actual
time=168.307..222.670 rows=9963 loops=1)
CTE qup
-> GroupAggregate (cost=5231.22..6303.78 rows=10320 width=80)
(actual time=35.466..68.131 rows=10735 loops=1)
Group Key: sa_upper.sup_season, sa_upper.sup_sa_code
-> Sort (cost=5231.22..5358.64 rows=50969 width=18) (actual
time=35.454..36.819 rows=50969 loops=1)
Sort Key: sa_upper.sup_season, sa_upper.sup_sa_code
COLLATE "C"
Sort Method: quicksort Memory: 4722kB
-> Hash Left Join (cost=41.71..1246.13 rows=50969
width=18) (actual time=0.148..10.687 rows=50969 loops=1)
Hash Cond: ((sa_upper.sup_mat_code)::text =
upper_target.up_mat_code)
-> Seq Scan on sa_upper (cost=0.00..884.69
rows=50969 width=16) (actual time=0.005..1.972 rows=50969 loops=1)
-> Hash (cost=35.53..35.53 rows=495 width=6)
(actual time=0.140..0.140 rows=495 loops=1)
Buckets: 1024 Batches: 1 Memory Usage: 27kB
-> Seq Scan on upper_target
(cost=0.00..35.53 rows=495 width=6) (actual time=0.007..0.103 rows=495
loops=1)
Filter: (id_up <= 495)
Rows Removed by Filter: 1467
CTE qli
-> GroupAggregate (cost=1097.31..1486.56 rows=10469 width=80)
(actual time=9.446..27.388 rows=10469 loops=1)
Group Key: sa_lining.sli_season, sa_lining.sli_sa_code
-> Sort (cost=1097.31..1126.74 rows=11774 width=18) (actual
time=9.440..9.811 rows=11774 loops=1)
Sort Key: sa_lining.sli_season, sa_lining.sli_sa_code
COLLATE "C"
Sort Method: quicksort Memory: 1120kB
-> Hash Left Join (cost=7.34..301.19 rows=11774
width=18) (actual time=0.045..2.438 rows=11774 loops=1)
Hash Cond: ((sa_lining.sli_mat_code)::text =
lining_target.li_mat_code)
-> Seq Scan on sa_lining (cost=0.00..204.74
rows=11774 width=16) (actual time=0.008..0.470 rows=11774 loops=1)
-> Hash (cost=5.86..5.86 rows=118 width=6)
(actual time=0.034..0.034 rows=119 loops=1)
Buckets: 1024 Batches: 1 Memory Usage: 13kB
-> Seq Scan on lining_target
(cost=0.00..5.86 rows=118 width=6) (actual time=0.008..0.024 rows=119
loops=1)
Filter: (id_li <= 119)
Rows Removed by Filter: 190
CTE qin
-> GroupAggregate (cost=1427.34..1880.73 rows=10678 width=80)
(actual time=11.424..31.508 rows=10678 loops=1)
Group Key: sa_insole.sin_season, sa_insole.sin_sa_code
-> Sort (cost=1427.34..1465.41 rows=15230 width=18) (actual
time=11.416..11.908 rows=15230 loops=1)
Sort Key: sa_insole.sin_season, sa_insole.sin_sa_code
COLLATE "C"
Sort Method: quicksort Memory: 1336kB
-> Hash Left Join (cost=10.49..369.26 rows=15230
width=18) (actual time=0.051..3.108 rows=15230 loops=1)
Hash Cond: ((sa_insole.sin_mat_code)::text =
insole_target.in_mat_code)
-> Seq Scan on sa_insole (cost=0.00..264.30
rows=15230 width=16) (actual time=0.006..0.606 rows=15230 loops=1)
-> Hash (cost=9.01..9.01 rows=118 width=6)
(actual time=0.042..0.043 rows=119 loops=1)
Buckets: 1024 Batches: 1 Memory Usage: 13kB
-> Seq Scan on insole_target
(cost=0.00..9.01 rows=118 width=6) (actual time=0.008..0.032 rows=119
loops=1)
Filter: (id_in <= 119)
Rows Removed by Filter: 362
CTE qou
-> GroupAggregate (cost=2366.22..2986.89 rows=10699 width=80)
(actual time=18.198..41.812 rows=10699 loops=1)
Group Key: sa_outsole.sou_season, sa_outsole.sou_sa_code
-> Sort (cost=2366.22..2428.14 rows=24768 width=18) (actual
time=18.187..18.967 rows=24768 loops=1)
Sort Key: sa_outsole.sou_season, sa_outsole.sou_sa_code
COLLATE "C"
Sort Method: quicksort Memory: 2317kB
-> Hash Left Join (cost=5.39..558.63 rows=24768
width=18) (actual time=0.046..5.132 rows=24768 loops=1)
Hash Cond: ((sa_outsole.sou_mat_code)::text =
outsole_target.ou_mat_code)
-> Seq Scan on sa_outsole (cost=0.00..430.68
rows=24768 width=16) (actual time=0.010..1.015 rows=24768 loops=1)
-> Hash (cost=5.03..5.03 rows=29 width=6)
(actual time=0.032..0.032 rows=29 loops=1)
Buckets: 1024 Batches: 1 Memory Usage: 10kB
-> Seq Scan on outsole_target
(cost=0.00..5.03 rows=29 width=6) (actual time=0.010..0.025 rows=29 loops=1)
Filter: (id_ou <= 29)
Rows Removed by Filter: 213
-> Hash Join (cost=1015.85..1319.50 rows=1 width=104) (actual
time=168.307..215.513 rows=8548 loops=1)
Hash Cond: ((qou.curr_season = qli.curr_season) AND
((qou.curr_code)::text = (qli.curr_code)::text))
Join Filter: ((((qup.ibitmask | qin.ibitmask) | qli.ibitmask) |
qou.ibitmask) IS NOT NULL)
-> CTE Scan on qou (cost=0.00..294.22 rows=1189 width=76)
(actual time=18.200..45.188 rows=10275 loops=1)
Filter: ((ibitmask > 0) AND (cardinality(mat_arr) <= 11))
Rows Removed by Filter: 424
-> Hash (cost=1015.83..1015.83 rows=1 width=228) (actual
time=150.094..150.095 rows=8845 loops=1)
Buckets: 16384 (originally 1024) Batches: 1 (originally
1) Memory Usage: 1899kB
-> Hash Join (cost=707.35..1015.83 rows=1 width=228)
(actual time=121.898..147.726 rows=8845 loops=1)
Hash Cond: ((qin.curr_season = qli.curr_season) AND
((qin.curr_code)::text = (qli.curr_code)::text))
-> CTE Scan on qin (cost=0.00..293.65 rows=1186
width=76) (actual time=11.425..34.674 rows=10197 loops=1)
Filter: ((ibitmask > 0) AND
(cardinality(mat_arr) <= 8))
Rows Removed by Filter: 481
-> Hash (cost=706.86..706.86 rows=33 width=152)
(actual time=110.470..110.470 rows=9007 loops=1)
Buckets: 16384 (originally 1024) Batches: 1
(originally 1) Memory Usage: 1473kB
-> Merge Join (cost=689.20..706.86 rows=33
width=152) (actual time=105.862..108.925 rows=9007 loops=1)
Merge Cond: ((qup.curr_season =
qli.curr_season) AND ((qup.curr_code)::text = (qli.curr_code)::text))
-> Sort (cost=342.09..344.96
rows=1147 width=76) (actual time=73.419..73.653 rows=9320 loops=1)
Sort Key: qup.curr_season,
qup.curr_code COLLATE "C"
Sort Method: quicksort Memory:
1391kB
-> CTE Scan on qup
(cost=0.00..283.80 rows=1147 width=76) (actual time=35.467..71.904
rows=9320 loops=1)
Filter: ((ibitmask > 0) AND
(cardinality(mat_arr) <= 21))
Rows Removed by Filter: 1415
-> Sort (cost=347.12..350.02
rows=1163 width=76) (actual time=32.440..32.697 rows=10289 loops=1)
Sort Key: qli.curr_season,
qli.curr_code COLLATE "C"
Sort Method: quicksort Memory:
1349kB
-> CTE Scan on qli
(cost=0.00..287.90 rows=1163 width=76) (actual time=9.447..30.666
rows=10289 loops=1)
Filter: ((ibitmask > 0) AND
(cardinality(mat_arr) <= 8))
Rows Removed by Filter: 180
-> Merge Left Join (cost=2625.49..3399.84 rows=5733 width=104)
(actual time=4.597..6.700 rows=1415 loops=1)
Merge Cond: ((qup_1.curr_season = qou_1.curr_season) AND
((qup_1.curr_code)::text = (qou_1.curr_code)::text))
-> Merge Left Join (cost=1958.66..2135.28 rows=5733
width=136) (actual time=3.427..3.863 rows=1415 loops=1)
Merge Cond: ((qup_1.curr_season = qin_1.curr_season) AND
((qup_1.curr_code)::text = (qin_1.curr_code)::text))
-> Merge Left Join (cost=1293.25..1388.21 rows=5733
width=104) (actual time=2.321..2.556 rows=1415 loops=1)
Merge Cond: ((qup_1.curr_season =
qli_1.curr_season) AND ((qup_1.curr_code)::text = (qli_1.curr_code)::text))
-> Sort (cost=641.68..656.02 rows=5733 width=72)
(actual time=1.286..1.324 rows=1415 loops=1)
Sort Key: qup_1.curr_season, qup_1.curr_code
COLLATE "C"
Sort Method: quicksort Memory: 204kB
-> CTE Scan on qup qup_1 (cost=0.00..283.80
rows=5733 width=72) (actual time=0.009..1.093 rows=1415 loops=1)
Filter: ((ibitmask < 0) OR
(cardinality(mat_arr) > 21))
Rows Removed by Filter: 9320
-> Sort (cost=651.57..666.11 rows=5816 width=72)
(actual time=1.033..1.038 rows=180 loops=1)
Sort Key: qli_1.curr_season, qli_1.curr_code
COLLATE "C"
Sort Method: quicksort Memory: 41kB
-> CTE Scan on qli qli_1 (cost=0.00..287.90
rows=5816 width=72) (actual time=0.055..1.007 rows=180 loops=1)
Filter: ((ibitmask < 0) OR
(cardinality(mat_arr) > 8))
Rows Removed by Filter: 10289
-> Sort (cost=665.41..680.24 rows=5932 width=72)
(actual time=1.104..1.117 rows=481 loops=1)
Sort Key: qin_1.curr_season, qin_1.curr_code
COLLATE "C"
Sort Method: quicksort Memory: 68kB
-> CTE Scan on qin qin_1 (cost=0.00..293.65
rows=5932 width=72) (actual time=0.016..1.038 rows=481 loops=1)
Filter: ((ibitmask < 0) OR
(cardinality(mat_arr) > 8))
Rows Removed by Filter: 10197
-> Sort (cost=666.83..681.69 rows=5944 width=72) (actual
time=1.163..1.174 rows=417 loops=1)
Sort Key: qou_1.curr_season, qou_1.curr_code COLLATE "C"
Sort Method: quicksort Memory: 68kB
-> CTE Scan on qou qou_1 (cost=0.00..294.22 rows=5944
width=72) (actual time=0.029..1.068 rows=424 loops=1)
Filter: ((ibitmask < 0) OR (cardinality(mat_arr) > 11))
Rows Removed by Filter: 10275
Planning Time: 2.297 ms
Execution Time: 224.759 ms
(118 Zeilen)3. Slow query from wrong plan as result on similar case with inner join
When the 3 left joins above are changed to inner joins like:
from qup
join qli on (qli.curr_season=qup.curr_season and
qli.curr_code=qup.curr_code and qli.ibitmask>0 and
cardinality(qli.mat_arr) <=8)
join qin on (qin.curr_season=qup.curr_season and
qin.curr_code=qup.curr_code and qin.ibitmask>0 and
cardinality(qin.mat_arr) <=8)
join qou on (qou.curr_season=qup.curr_season and
qou.curr_code=qup.curr_code and qou.ibitmask>0 and
cardinality(qou.mat_arr) <=11)
where qup.ibitmask>0 and cardinality(qup.mat_arr) <=21The same rows estimation takes place as with the left joins, but the
planner now decides to use a nested loop for the last join, which
results in a 500fold execution time:QUERY PLAN
--------------------------------------------------------------------------------------------------------------------------------------------
Append (cost=13365.31..17472.18 rows=5734 width=104) (actual
time=139.037..13403.310 rows=9963 loops=1)
CTE qup
-> GroupAggregate (cost=5231.22..6303.78 rows=10320 width=80)
(actual time=35.399..67.102 rows=10735 loops=1)
Group Key: sa_upper.sup_season, sa_upper.sup_sa_code
-> Sort (cost=5231.22..5358.64 rows=50969 width=18) (actual
time=35.382..36.743 rows=50969 loops=1)
Sort Key: sa_upper.sup_season, sa_upper.sup_sa_code
COLLATE "C"
Sort Method: quicksort Memory: 4722kB
-> Hash Left Join (cost=41.71..1246.13 rows=50969
width=18) (actual time=0.157..10.715 rows=50969 loops=1)
Hash Cond: ((sa_upper.sup_mat_code)::text =
upper_target.up_mat_code)
-> Seq Scan on sa_upper (cost=0.00..884.69
rows=50969 width=16) (actual time=0.008..2.001 rows=50969 loops=1)
-> Hash (cost=35.53..35.53 rows=495 width=6)
(actual time=0.146..0.146 rows=495 loops=1)
Buckets: 1024 Batches: 1 Memory Usage: 27kB
-> Seq Scan on upper_target
(cost=0.00..35.53 rows=495 width=6) (actual time=0.006..0.105 rows=495
loops=1)
Filter: (id_up <= 495)
Rows Removed by Filter: 1467
CTE qli
-> GroupAggregate (cost=1097.31..1486.56 rows=10469 width=80)
(actual time=9.541..27.419 rows=10469 loops=1)
Group Key: sa_lining.sli_season, sa_lining.sli_sa_code
-> Sort (cost=1097.31..1126.74 rows=11774 width=18) (actual
time=9.534..9.908 rows=11774 loops=1)
Sort Key: sa_lining.sli_season, sa_lining.sli_sa_code
COLLATE "C"
Sort Method: quicksort Memory: 1120kB
-> Hash Left Join (cost=7.34..301.19 rows=11774
width=18) (actual time=0.049..2.451 rows=11774 loops=1)
Hash Cond: ((sa_lining.sli_mat_code)::text =
lining_target.li_mat_code)
-> Seq Scan on sa_lining (cost=0.00..204.74
rows=11774 width=16) (actual time=0.010..0.462 rows=11774 loops=1)
-> Hash (cost=5.86..5.86 rows=118 width=6)
(actual time=0.035..0.035 rows=119 loops=1)
Buckets: 1024 Batches: 1 Memory Usage: 13kB
-> Seq Scan on lining_target
(cost=0.00..5.86 rows=118 width=6) (actual time=0.008..0.025 rows=119
loops=1)
Filter: (id_li <= 119)
Rows Removed by Filter: 190
CTE qin
-> GroupAggregate (cost=1427.34..1880.73 rows=10678 width=80)
(actual time=11.649..30.910 rows=10678 loops=1)
Group Key: sa_insole.sin_season, sa_insole.sin_sa_code
-> Sort (cost=1427.34..1465.41 rows=15230 width=18) (actual
time=11.642..12.115 rows=15230 loops=1)
Sort Key: sa_insole.sin_season, sa_insole.sin_sa_code
COLLATE "C"
Sort Method: quicksort Memory: 1336kB
-> Hash Left Join (cost=10.49..369.26 rows=15230
width=18) (actual time=0.056..3.144 rows=15230 loops=1)
Hash Cond: ((sa_insole.sin_mat_code)::text =
insole_target.in_mat_code)
-> Seq Scan on sa_insole (cost=0.00..264.30
rows=15230 width=16) (actual time=0.008..0.594 rows=15230 loops=1)
-> Hash (cost=9.01..9.01 rows=118 width=6)
(actual time=0.045..0.046 rows=119 loops=1)
Buckets: 1024 Batches: 1 Memory Usage: 13kB
-> Seq Scan on insole_target
(cost=0.00..9.01 rows=118 width=6) (actual time=0.008..0.034 rows=119
loops=1)
Filter: (id_in <= 119)
Rows Removed by Filter: 362
CTE qou
-> GroupAggregate (cost=2366.22..2986.89 rows=10699 width=80)
(actual time=18.163..51.151 rows=10699 loops=1)
Group Key: sa_outsole.sou_season, sa_outsole.sou_sa_code
-> Sort (cost=2366.22..2428.14 rows=24768 width=18) (actual
time=18.150..20.000 rows=24768 loops=1)
Sort Key: sa_outsole.sou_season, sa_outsole.sou_sa_code
COLLATE "C"
Sort Method: quicksort Memory: 2317kB
-> Hash Left Join (cost=5.39..558.63 rows=24768
width=18) (actual time=0.036..5.106 rows=24768 loops=1)
Hash Cond: ((sa_outsole.sou_mat_code)::text =
outsole_target.ou_mat_code)
-> Seq Scan on sa_outsole (cost=0.00..430.68
rows=24768 width=16) (actual time=0.008..1.005 rows=24768 loops=1)
-> Hash (cost=5.03..5.03 rows=29 width=6)
(actual time=0.024..0.024 rows=29 loops=1)
Buckets: 1024 Batches: 1 Memory Usage: 10kB
-> Seq Scan on outsole_target
(cost=0.00..5.03 rows=29 width=6) (actual time=0.007..0.018 rows=29 loops=1)
Filter: (id_ou <= 29)
Rows Removed by Filter: 213
-> Nested Loop (cost=707.35..1328.37 rows=1 width=104) (actual
time=139.036..13395.820 rows=8548 loops=1)
Join Filter: ((qli.curr_season = qin.curr_season) AND
((qli.curr_code)::text = (qin.curr_code)::text))
Rows Removed by Join Filter: 88552397
-> Hash Join (cost=707.35..1016.45 rows=1 width=216) (actual
time=127.374..168.249 rows=8685 loops=1)
Hash Cond: ((qou.curr_season = qli.curr_season) AND
((qou.curr_code)::text = (qli.curr_code)::text))
-> CTE Scan on qou (cost=0.00..294.22 rows=1189
width=72) (actual time=18.165..54.968 rows=10275 loops=1)
Filter: ((ibitmask > 0) AND (cardinality(mat_arr)
<= 11))
Rows Removed by Filter: 424
-> Hash (cost=706.86..706.86 rows=33 width=144) (actual
time=109.205..109.207 rows=9007 loops=1)
Buckets: 16384 (originally 1024) Batches: 1
(originally 1) Memory Usage: 1369kB
-> Merge Join (cost=689.20..706.86 rows=33
width=144) (actual time=104.785..107.748 rows=9007 loops=1)
Merge Cond: ((qup.curr_season =
qli.curr_season) AND ((qup.curr_code)::text = (qli.curr_code)::text))
-> Sort (cost=342.09..344.96 rows=1147
width=72) (actual time=72.320..72.559 rows=9320 loops=1)
Sort Key: qup.curr_season,
qup.curr_code COLLATE "C"
Sort Method: quicksort Memory: 1357kB
-> CTE Scan on qup (cost=0.00..283.80
rows=1147 width=72) (actual time=35.401..70.834 rows=9320 loops=1)
Filter: ((ibitmask > 0) AND
(cardinality(mat_arr) <= 21))
Rows Removed by Filter: 1415
-> Sort (cost=347.12..350.02 rows=1163
width=72) (actual time=32.461..32.719 rows=10289 loops=1)
Sort Key: qli.curr_season,
qli.curr_code COLLATE "C"
Sort Method: quicksort Memory: 1269kB
-> CTE Scan on qli (cost=0.00..287.90
rows=1163 width=72) (actual time=9.543..30.696 rows=10289 loops=1)
Filter: ((ibitmask > 0) AND
(cardinality(mat_arr) <= 8))
Rows Removed by Filter: 180
-> CTE Scan on qin (cost=0.00..293.65 rows=1186 width=72)
(actual time=0.001..1.159 rows=10197 loops=8685)
Filter: ((ibitmask > 0) AND (cardinality(mat_arr) <= 8))
Rows Removed by Filter: 481
-> Merge Left Join (cost=2625.49..3399.84 rows=5733 width=104)
(actual time=4.606..6.733 rows=1415 loops=1)
Merge Cond: ((qup_1.curr_season = qou_1.curr_season) AND
((qup_1.curr_code)::text = (qou_1.curr_code)::text))
-> Merge Left Join (cost=1958.66..2135.28 rows=5733
width=136) (actual time=3.479..3.930 rows=1415 loops=1)
Merge Cond: ((qup_1.curr_season = qin_1.curr_season) AND
((qup_1.curr_code)::text = (qin_1.curr_code)::text))
-> Merge Left Join (cost=1293.25..1388.21 rows=5733
width=104) (actual time=2.368..2.610 rows=1415 loops=1)
Merge Cond: ((qup_1.curr_season =
qli_1.curr_season) AND ((qup_1.curr_code)::text = (qli_1.curr_code)::text))
-> Sort (cost=641.68..656.02 rows=5733 width=72)
(actual time=1.296..1.335 rows=1415 loops=1)
Sort Key: qup_1.curr_season, qup_1.curr_code
COLLATE "C"
Sort Method: quicksort Memory: 204kB
-> CTE Scan on qup qup_1 (cost=0.00..283.80
rows=5733 width=72) (actual time=0.010..1.119 rows=1415 loops=1)
Filter: ((ibitmask < 0) OR
(cardinality(mat_arr) > 21))
Rows Removed by Filter: 9320
-> Sort (cost=651.57..666.11 rows=5816 width=72)
(actual time=1.069..1.075 rows=180 loops=1)
Sort Key: qli_1.curr_season, qli_1.curr_code
COLLATE "C"
Sort Method: quicksort Memory: 41kB
-> CTE Scan on qli qli_1 (cost=0.00..287.90
rows=5816 width=72) (actual time=0.057..1.026 rows=180 loops=1)
Filter: ((ibitmask < 0) OR
(cardinality(mat_arr) > 8))
Rows Removed by Filter: 10289
-> Sort (cost=665.41..680.24 rows=5932 width=72)
(actual time=1.110..1.124 rows=481 loops=1)
Sort Key: qin_1.curr_season, qin_1.curr_code
COLLATE "C"
Sort Method: quicksort Memory: 68kB
-> CTE Scan on qin qin_1 (cost=0.00..293.65
rows=5932 width=72) (actual time=0.016..1.046 rows=481 loops=1)
Filter: ((ibitmask < 0) OR
(cardinality(mat_arr) > 8))
Rows Removed by Filter: 10197
-> Sort (cost=666.83..681.69 rows=5944 width=72) (actual
time=1.119..1.128 rows=417 loops=1)
Sort Key: qou_1.curr_season, qou_1.curr_code COLLATE "C"
Sort Method: quicksort Memory: 68kB
-> CTE Scan on qou qou_1 (cost=0.00..294.22 rows=5944
width=72) (actual time=0.029..1.056 rows=424 loops=1)
Filter: ((ibitmask < 0) OR (cardinality(mat_arr) > 11))
Rows Removed by Filter: 10275
Planning Time: 1.746 ms
Execution Time: 13405.503 ms
(116 Zeilen)This case really brought me to detect the problem!
The original query and data are not shown here, but the principle should
be clear from the execution plans.I think the planner shouldn't change the row estimations on further
steps after left joins at all, and be a bit more conservative on inner
joins.
This may be related to the fact that this case has 2 join-conditions
(xx_season an xx_code).Thanks for looking
Hans Buschmann
!! External Email: This email originated from outside of the
organization. Do not click links or open attachments unless you
recognize the sender.
--
Tomas Vondra
EnterpriseDB: http://www.enterprisedb.com
The Enterprise PostgreSQL Company
Hi hackers,
I found a new approach to fix this issue, which seems better, so I would like to post another version of the patch here. The origin patch made the assumption of the values of Vars from CTE must be unique, which could be very wrong. This patch examines variables for Vars inside CTE, which avoided the bad assumption, so the results could be much more accurate.
Regards,
Jian
________________________________
From: Tomas Vondra <tomas.vondra@enterprisedb.com>
Sent: Monday, August 14, 2023 20:58
To: Jian Guo <gjian@vmware.com>; Hans Buschmann <buschmann@nidsa.net>; pgsql-hackers@lists.postgresql.org <pgsql-hackers@lists.postgresql.org>
Subject: Re: Wrong rows estimations with joins of CTEs slows queries by more than factor 500
!! External Email
Hi,
I haven't looked at the patch, but please add the patch to the next
commit fest (2023-09), so that we don't lose track of it.
regards
Tomas
On 8/14/23 13:12, Jian Guo wrote:
Hi hackers,
I have written a patch to add stats info for Vars in CTEs. With this
patch, the join size estimation on the upper of CTE scans became more
accurate.In the function |selfuncs.c:eqjoinsel| it uses the number of the
distinct values of the two join variables to estimate join size, and in
the function |selfuncs.c:get_variable_numdistinct| return a default
value |DEFAULT_NUM_DISTINCT| (200 in Postgres and 1000 in Greenplum),
with the default value, you can never expect a good plan.Thanks if anyone could give a review.
Regards,
Jian------------------------------------------------------------------------
*From:* Hans Buschmann <buschmann@nidsa.net>
*Sent:* Wednesday, February 8, 2023 21:55
*To:* pgsql-hackers@lists.postgresql.org
<pgsql-hackers@lists.postgresql.org>
*Subject:* Wrong rows estimations with joins of CTEs slows queries by
more than factor 500!! External Email
During data refactoring of our Application I encountered $subject when
joining 4 CTEs with left join or inner join.1. Background
PG 15.1 on Windows x64 (OS seems no to have no meening here)
I try to collect data from 4 (analyzed) tables (up,li,in,ou) by grouping
certain data (4 CTEs qup,qli,qin,qou)The grouping of the data in the CTEs gives estimated row counts of about
1000 (1 tenth of the real value) This is OK for estimation.These 4 CTEs are then used to combine the data by joining them.
2. Problem
The 4 CTEs are joined by left joins as shown below:
from qup
left join qli on (qli.curr_season=qup.curr_season and
qli.curr_code=qup.curr_code and qli.ibitmask>0 and
cardinality(qli.mat_arr) <=8)
left join qin on (qin.curr_season=qup.curr_season and
qin.curr_code=qup.curr_code and qin.ibitmask>0 and
cardinality(qin.mat_arr) <=8)
left join qou on (qou.curr_season=qup.curr_season and
qou.curr_code=qup.curr_code and qou.ibitmask>0 and
cardinality(qou.mat_arr) <=11)
where qup.ibitmask>0 and cardinality(qup.mat_arr) <=21The plan first retrieves qup and qli, taking the estimated row counts of
1163 and 1147 respectivelyBUT the result is then hashed and the row count is estimated as 33!
In a Left join the row count stays always the same as the one of left
table (here qup with 1163 rows)The same algorithm which reduces the row estimation from 1163 to 33 is
used in the next step to give an estimation of 1 row.This is totally wrong.
Here is the execution plan of the query:
(search the plan for rows=33)
QUERY PLAN
--------------------------------------------------------------------------------------------------------------------------------------------------
Append (cost=13673.81..17463.30 rows=5734 width=104) (actual
time=168.307..222.670 rows=9963 loops=1)
CTE qup
-> GroupAggregate (cost=5231.22..6303.78 rows=10320 width=80)
(actual time=35.466..68.131 rows=10735 loops=1)
Group Key: sa_upper.sup_season, sa_upper.sup_sa_code
-> Sort (cost=5231.22..5358.64 rows=50969 width=18) (actual
time=35.454..36.819 rows=50969 loops=1)
Sort Key: sa_upper.sup_season, sa_upper.sup_sa_code
COLLATE "C"
Sort Method: quicksort Memory: 4722kB
-> Hash Left Join (cost=41.71..1246.13 rows=50969
width=18) (actual time=0.148..10.687 rows=50969 loops=1)
Hash Cond: ((sa_upper.sup_mat_code)::text =
upper_target.up_mat_code)
-> Seq Scan on sa_upper (cost=0.00..884.69
rows=50969 width=16) (actual time=0.005..1.972 rows=50969 loops=1)
-> Hash (cost=35.53..35.53 rows=495 width=6)
(actual time=0.140..0.140 rows=495 loops=1)
Buckets: 1024 Batches: 1 Memory Usage: 27kB
-> Seq Scan on upper_target
(cost=0.00..35.53 rows=495 width=6) (actual time=0.007..0.103 rows=495
loops=1)
Filter: (id_up <= 495)
Rows Removed by Filter: 1467
CTE qli
-> GroupAggregate (cost=1097.31..1486.56 rows=10469 width=80)
(actual time=9.446..27.388 rows=10469 loops=1)
Group Key: sa_lining.sli_season, sa_lining.sli_sa_code
-> Sort (cost=1097.31..1126.74 rows=11774 width=18) (actual
time=9.440..9.811 rows=11774 loops=1)
Sort Key: sa_lining.sli_season, sa_lining.sli_sa_code
COLLATE "C"
Sort Method: quicksort Memory: 1120kB
-> Hash Left Join (cost=7.34..301.19 rows=11774
width=18) (actual time=0.045..2.438 rows=11774 loops=1)
Hash Cond: ((sa_lining.sli_mat_code)::text =
lining_target.li_mat_code)
-> Seq Scan on sa_lining (cost=0.00..204.74
rows=11774 width=16) (actual time=0.008..0.470 rows=11774 loops=1)
-> Hash (cost=5.86..5.86 rows=118 width=6)
(actual time=0.034..0.034 rows=119 loops=1)
Buckets: 1024 Batches: 1 Memory Usage: 13kB
-> Seq Scan on lining_target
(cost=0.00..5.86 rows=118 width=6) (actual time=0.008..0.024 rows=119
loops=1)
Filter: (id_li <= 119)
Rows Removed by Filter: 190
CTE qin
-> GroupAggregate (cost=1427.34..1880.73 rows=10678 width=80)
(actual time=11.424..31.508 rows=10678 loops=1)
Group Key: sa_insole.sin_season, sa_insole.sin_sa_code
-> Sort (cost=1427.34..1465.41 rows=15230 width=18) (actual
time=11.416..11.908 rows=15230 loops=1)
Sort Key: sa_insole.sin_season, sa_insole.sin_sa_code
COLLATE "C"
Sort Method: quicksort Memory: 1336kB
-> Hash Left Join (cost=10.49..369.26 rows=15230
width=18) (actual time=0.051..3.108 rows=15230 loops=1)
Hash Cond: ((sa_insole.sin_mat_code)::text =
insole_target.in_mat_code)
-> Seq Scan on sa_insole (cost=0.00..264.30
rows=15230 width=16) (actual time=0.006..0.606 rows=15230 loops=1)
-> Hash (cost=9.01..9.01 rows=118 width=6)
(actual time=0.042..0.043 rows=119 loops=1)
Buckets: 1024 Batches: 1 Memory Usage: 13kB
-> Seq Scan on insole_target
(cost=0.00..9.01 rows=118 width=6) (actual time=0.008..0.032 rows=119
loops=1)
Filter: (id_in <= 119)
Rows Removed by Filter: 362
CTE qou
-> GroupAggregate (cost=2366.22..2986.89 rows=10699 width=80)
(actual time=18.198..41.812 rows=10699 loops=1)
Group Key: sa_outsole.sou_season, sa_outsole.sou_sa_code
-> Sort (cost=2366.22..2428.14 rows=24768 width=18) (actual
time=18.187..18.967 rows=24768 loops=1)
Sort Key: sa_outsole.sou_season, sa_outsole.sou_sa_code
COLLATE "C"
Sort Method: quicksort Memory: 2317kB
-> Hash Left Join (cost=5.39..558.63 rows=24768
width=18) (actual time=0.046..5.132 rows=24768 loops=1)
Hash Cond: ((sa_outsole.sou_mat_code)::text =
outsole_target.ou_mat_code)
-> Seq Scan on sa_outsole (cost=0.00..430.68
rows=24768 width=16) (actual time=0.010..1.015 rows=24768 loops=1)
-> Hash (cost=5.03..5.03 rows=29 width=6)
(actual time=0.032..0.032 rows=29 loops=1)
Buckets: 1024 Batches: 1 Memory Usage: 10kB
-> Seq Scan on outsole_target
(cost=0.00..5.03 rows=29 width=6) (actual time=0.010..0.025 rows=29 loops=1)
Filter: (id_ou <= 29)
Rows Removed by Filter: 213
-> Hash Join (cost=1015.85..1319.50 rows=1 width=104) (actual
time=168.307..215.513 rows=8548 loops=1)
Hash Cond: ((qou.curr_season = qli.curr_season) AND
((qou.curr_code)::text = (qli.curr_code)::text))
Join Filter: ((((qup.ibitmask | qin.ibitmask) | qli.ibitmask) |
qou.ibitmask) IS NOT NULL)
-> CTE Scan on qou (cost=0.00..294.22 rows=1189 width=76)
(actual time=18.200..45.188 rows=10275 loops=1)
Filter: ((ibitmask > 0) AND (cardinality(mat_arr) <= 11))
Rows Removed by Filter: 424
-> Hash (cost=1015.83..1015.83 rows=1 width=228) (actual
time=150.094..150.095 rows=8845 loops=1)
Buckets: 16384 (originally 1024) Batches: 1 (originally
1) Memory Usage: 1899kB
-> Hash Join (cost=707.35..1015.83 rows=1 width=228)
(actual time=121.898..147.726 rows=8845 loops=1)
Hash Cond: ((qin.curr_season = qli.curr_season) AND
((qin.curr_code)::text = (qli.curr_code)::text))
-> CTE Scan on qin (cost=0.00..293.65 rows=1186
width=76) (actual time=11.425..34.674 rows=10197 loops=1)
Filter: ((ibitmask > 0) AND
(cardinality(mat_arr) <= 8))
Rows Removed by Filter: 481
-> Hash (cost=706.86..706.86 rows=33 width=152)
(actual time=110.470..110.470 rows=9007 loops=1)
Buckets: 16384 (originally 1024) Batches: 1
(originally 1) Memory Usage: 1473kB
-> Merge Join (cost=689.20..706.86 rows=33
width=152) (actual time=105.862..108.925 rows=9007 loops=1)
Merge Cond: ((qup.curr_season =
qli.curr_season) AND ((qup.curr_code)::text = (qli.curr_code)::text))
-> Sort (cost=342.09..344.96
rows=1147 width=76) (actual time=73.419..73.653 rows=9320 loops=1)
Sort Key: qup.curr_season,
qup.curr_code COLLATE "C"
Sort Method: quicksort Memory:
1391kB
-> CTE Scan on qup
(cost=0.00..283.80 rows=1147 width=76) (actual time=35.467..71.904
rows=9320 loops=1)
Filter: ((ibitmask > 0) AND
(cardinality(mat_arr) <= 21))
Rows Removed by Filter: 1415
-> Sort (cost=347.12..350.02
rows=1163 width=76) (actual time=32.440..32.697 rows=10289 loops=1)
Sort Key: qli.curr_season,
qli.curr_code COLLATE "C"
Sort Method: quicksort Memory:
1349kB
-> CTE Scan on qli
(cost=0.00..287.90 rows=1163 width=76) (actual time=9.447..30.666
rows=10289 loops=1)
Filter: ((ibitmask > 0) AND
(cardinality(mat_arr) <= 8))
Rows Removed by Filter: 180
-> Merge Left Join (cost=2625.49..3399.84 rows=5733 width=104)
(actual time=4.597..6.700 rows=1415 loops=1)
Merge Cond: ((qup_1.curr_season = qou_1.curr_season) AND
((qup_1.curr_code)::text = (qou_1.curr_code)::text))
-> Merge Left Join (cost=1958.66..2135.28 rows=5733
width=136) (actual time=3.427..3.863 rows=1415 loops=1)
Merge Cond: ((qup_1.curr_season = qin_1.curr_season) AND
((qup_1.curr_code)::text = (qin_1.curr_code)::text))
-> Merge Left Join (cost=1293.25..1388.21 rows=5733
width=104) (actual time=2.321..2.556 rows=1415 loops=1)
Merge Cond: ((qup_1.curr_season =
qli_1.curr_season) AND ((qup_1.curr_code)::text = (qli_1.curr_code)::text))
-> Sort (cost=641.68..656.02 rows=5733 width=72)
(actual time=1.286..1.324 rows=1415 loops=1)
Sort Key: qup_1.curr_season, qup_1.curr_code
COLLATE "C"
Sort Method: quicksort Memory: 204kB
-> CTE Scan on qup qup_1 (cost=0.00..283.80
rows=5733 width=72) (actual time=0.009..1.093 rows=1415 loops=1)
Filter: ((ibitmask < 0) OR
(cardinality(mat_arr) > 21))
Rows Removed by Filter: 9320
-> Sort (cost=651.57..666.11 rows=5816 width=72)
(actual time=1.033..1.038 rows=180 loops=1)
Sort Key: qli_1.curr_season, qli_1.curr_code
COLLATE "C"
Sort Method: quicksort Memory: 41kB
-> CTE Scan on qli qli_1 (cost=0.00..287.90
rows=5816 width=72) (actual time=0.055..1.007 rows=180 loops=1)
Filter: ((ibitmask < 0) OR
(cardinality(mat_arr) > 8))
Rows Removed by Filter: 10289
-> Sort (cost=665.41..680.24 rows=5932 width=72)
(actual time=1.104..1.117 rows=481 loops=1)
Sort Key: qin_1.curr_season, qin_1.curr_code
COLLATE "C"
Sort Method: quicksort Memory: 68kB
-> CTE Scan on qin qin_1 (cost=0.00..293.65
rows=5932 width=72) (actual time=0.016..1.038 rows=481 loops=1)
Filter: ((ibitmask < 0) OR
(cardinality(mat_arr) > 8))
Rows Removed by Filter: 10197
-> Sort (cost=666.83..681.69 rows=5944 width=72) (actual
time=1.163..1.174 rows=417 loops=1)
Sort Key: qou_1.curr_season, qou_1.curr_code COLLATE "C"
Sort Method: quicksort Memory: 68kB
-> CTE Scan on qou qou_1 (cost=0.00..294.22 rows=5944
width=72) (actual time=0.029..1.068 rows=424 loops=1)
Filter: ((ibitmask < 0) OR (cardinality(mat_arr) > 11))
Rows Removed by Filter: 10275
Planning Time: 2.297 ms
Execution Time: 224.759 ms
(118 Zeilen)3. Slow query from wrong plan as result on similar case with inner join
When the 3 left joins above are changed to inner joins like:
from qup
join qli on (qli.curr_season=qup.curr_season and
qli.curr_code=qup.curr_code and qli.ibitmask>0 and
cardinality(qli.mat_arr) <=8)
join qin on (qin.curr_season=qup.curr_season and
qin.curr_code=qup.curr_code and qin.ibitmask>0 and
cardinality(qin.mat_arr) <=8)
join qou on (qou.curr_season=qup.curr_season and
qou.curr_code=qup.curr_code and qou.ibitmask>0 and
cardinality(qou.mat_arr) <=11)
where qup.ibitmask>0 and cardinality(qup.mat_arr) <=21The same rows estimation takes place as with the left joins, but the
planner now decides to use a nested loop for the last join, which
results in a 500fold execution time:QUERY PLAN
--------------------------------------------------------------------------------------------------------------------------------------------
Append (cost=13365.31..17472.18 rows=5734 width=104) (actual
time=139.037..13403.310 rows=9963 loops=1)
CTE qup
-> GroupAggregate (cost=5231.22..6303.78 rows=10320 width=80)
(actual time=35.399..67.102 rows=10735 loops=1)
Group Key: sa_upper.sup_season, sa_upper.sup_sa_code
-> Sort (cost=5231.22..5358.64 rows=50969 width=18) (actual
time=35.382..36.743 rows=50969 loops=1)
Sort Key: sa_upper.sup_season, sa_upper.sup_sa_code
COLLATE "C"
Sort Method: quicksort Memory: 4722kB
-> Hash Left Join (cost=41.71..1246.13 rows=50969
width=18) (actual time=0.157..10.715 rows=50969 loops=1)
Hash Cond: ((sa_upper.sup_mat_code)::text =
upper_target.up_mat_code)
-> Seq Scan on sa_upper (cost=0.00..884.69
rows=50969 width=16) (actual time=0.008..2.001 rows=50969 loops=1)
-> Hash (cost=35.53..35.53 rows=495 width=6)
(actual time=0.146..0.146 rows=495 loops=1)
Buckets: 1024 Batches: 1 Memory Usage: 27kB
-> Seq Scan on upper_target
(cost=0.00..35.53 rows=495 width=6) (actual time=0.006..0.105 rows=495
loops=1)
Filter: (id_up <= 495)
Rows Removed by Filter: 1467
CTE qli
-> GroupAggregate (cost=1097.31..1486.56 rows=10469 width=80)
(actual time=9.541..27.419 rows=10469 loops=1)
Group Key: sa_lining.sli_season, sa_lining.sli_sa_code
-> Sort (cost=1097.31..1126.74 rows=11774 width=18) (actual
time=9.534..9.908 rows=11774 loops=1)
Sort Key: sa_lining.sli_season, sa_lining.sli_sa_code
COLLATE "C"
Sort Method: quicksort Memory: 1120kB
-> Hash Left Join (cost=7.34..301.19 rows=11774
width=18) (actual time=0.049..2.451 rows=11774 loops=1)
Hash Cond: ((sa_lining.sli_mat_code)::text =
lining_target.li_mat_code)
-> Seq Scan on sa_lining (cost=0.00..204.74
rows=11774 width=16) (actual time=0.010..0.462 rows=11774 loops=1)
-> Hash (cost=5.86..5.86 rows=118 width=6)
(actual time=0.035..0.035 rows=119 loops=1)
Buckets: 1024 Batches: 1 Memory Usage: 13kB
-> Seq Scan on lining_target
(cost=0.00..5.86 rows=118 width=6) (actual time=0.008..0.025 rows=119
loops=1)
Filter: (id_li <= 119)
Rows Removed by Filter: 190
CTE qin
-> GroupAggregate (cost=1427.34..1880.73 rows=10678 width=80)
(actual time=11.649..30.910 rows=10678 loops=1)
Group Key: sa_insole.sin_season, sa_insole.sin_sa_code
-> Sort (cost=1427.34..1465.41 rows=15230 width=18) (actual
time=11.642..12.115 rows=15230 loops=1)
Sort Key: sa_insole.sin_season, sa_insole.sin_sa_code
COLLATE "C"
Sort Method: quicksort Memory: 1336kB
-> Hash Left Join (cost=10.49..369.26 rows=15230
width=18) (actual time=0.056..3.144 rows=15230 loops=1)
Hash Cond: ((sa_insole.sin_mat_code)::text =
insole_target.in_mat_code)
-> Seq Scan on sa_insole (cost=0.00..264.30
rows=15230 width=16) (actual time=0.008..0.594 rows=15230 loops=1)
-> Hash (cost=9.01..9.01 rows=118 width=6)
(actual time=0.045..0.046 rows=119 loops=1)
Buckets: 1024 Batches: 1 Memory Usage: 13kB
-> Seq Scan on insole_target
(cost=0.00..9.01 rows=118 width=6) (actual time=0.008..0.034 rows=119
loops=1)
Filter: (id_in <= 119)
Rows Removed by Filter: 362
CTE qou
-> GroupAggregate (cost=2366.22..2986.89 rows=10699 width=80)
(actual time=18.163..51.151 rows=10699 loops=1)
Group Key: sa_outsole.sou_season, sa_outsole.sou_sa_code
-> Sort (cost=2366.22..2428.14 rows=24768 width=18) (actual
time=18.150..20.000 rows=24768 loops=1)
Sort Key: sa_outsole.sou_season, sa_outsole.sou_sa_code
COLLATE "C"
Sort Method: quicksort Memory: 2317kB
-> Hash Left Join (cost=5.39..558.63 rows=24768
width=18) (actual time=0.036..5.106 rows=24768 loops=1)
Hash Cond: ((sa_outsole.sou_mat_code)::text =
outsole_target.ou_mat_code)
-> Seq Scan on sa_outsole (cost=0.00..430.68
rows=24768 width=16) (actual time=0.008..1.005 rows=24768 loops=1)
-> Hash (cost=5.03..5.03 rows=29 width=6)
(actual time=0.024..0.024 rows=29 loops=1)
Buckets: 1024 Batches: 1 Memory Usage: 10kB
-> Seq Scan on outsole_target
(cost=0.00..5.03 rows=29 width=6) (actual time=0.007..0.018 rows=29 loops=1)
Filter: (id_ou <= 29)
Rows Removed by Filter: 213
-> Nested Loop (cost=707.35..1328.37 rows=1 width=104) (actual
time=139.036..13395.820 rows=8548 loops=1)
Join Filter: ((qli.curr_season = qin.curr_season) AND
((qli.curr_code)::text = (qin.curr_code)::text))
Rows Removed by Join Filter: 88552397
-> Hash Join (cost=707.35..1016.45 rows=1 width=216) (actual
time=127.374..168.249 rows=8685 loops=1)
Hash Cond: ((qou.curr_season = qli.curr_season) AND
((qou.curr_code)::text = (qli.curr_code)::text))
-> CTE Scan on qou (cost=0.00..294.22 rows=1189
width=72) (actual time=18.165..54.968 rows=10275 loops=1)
Filter: ((ibitmask > 0) AND (cardinality(mat_arr)
<= 11))
Rows Removed by Filter: 424
-> Hash (cost=706.86..706.86 rows=33 width=144) (actual
time=109.205..109.207 rows=9007 loops=1)
Buckets: 16384 (originally 1024) Batches: 1
(originally 1) Memory Usage: 1369kB
-> Merge Join (cost=689.20..706.86 rows=33
width=144) (actual time=104.785..107.748 rows=9007 loops=1)
Merge Cond: ((qup.curr_season =
qli.curr_season) AND ((qup.curr_code)::text = (qli.curr_code)::text))
-> Sort (cost=342.09..344.96 rows=1147
width=72) (actual time=72.320..72.559 rows=9320 loops=1)
Sort Key: qup.curr_season,
qup.curr_code COLLATE "C"
Sort Method: quicksort Memory: 1357kB
-> CTE Scan on qup (cost=0.00..283.80
rows=1147 width=72) (actual time=35.401..70.834 rows=9320 loops=1)
Filter: ((ibitmask > 0) AND
(cardinality(mat_arr) <= 21))
Rows Removed by Filter: 1415
-> Sort (cost=347.12..350.02 rows=1163
width=72) (actual time=32.461..32.719 rows=10289 loops=1)
Sort Key: qli.curr_season,
qli.curr_code COLLATE "C"
Sort Method: quicksort Memory: 1269kB
-> CTE Scan on qli (cost=0.00..287.90
rows=1163 width=72) (actual time=9.543..30.696 rows=10289 loops=1)
Filter: ((ibitmask > 0) AND
(cardinality(mat_arr) <= 8))
Rows Removed by Filter: 180
-> CTE Scan on qin (cost=0.00..293.65 rows=1186 width=72)
(actual time=0.001..1.159 rows=10197 loops=8685)
Filter: ((ibitmask > 0) AND (cardinality(mat_arr) <= 8))
Rows Removed by Filter: 481
-> Merge Left Join (cost=2625.49..3399.84 rows=5733 width=104)
(actual time=4.606..6.733 rows=1415 loops=1)
Merge Cond: ((qup_1.curr_season = qou_1.curr_season) AND
((qup_1.curr_code)::text = (qou_1.curr_code)::text))
-> Merge Left Join (cost=1958.66..2135.28 rows=5733
width=136) (actual time=3.479..3.930 rows=1415 loops=1)
Merge Cond: ((qup_1.curr_season = qin_1.curr_season) AND
((qup_1.curr_code)::text = (qin_1.curr_code)::text))
-> Merge Left Join (cost=1293.25..1388.21 rows=5733
width=104) (actual time=2.368..2.610 rows=1415 loops=1)
Merge Cond: ((qup_1.curr_season =
qli_1.curr_season) AND ((qup_1.curr_code)::text = (qli_1.curr_code)::text))
-> Sort (cost=641.68..656.02 rows=5733 width=72)
(actual time=1.296..1.335 rows=1415 loops=1)
Sort Key: qup_1.curr_season, qup_1.curr_code
COLLATE "C"
Sort Method: quicksort Memory: 204kB
-> CTE Scan on qup qup_1 (cost=0.00..283.80
rows=5733 width=72) (actual time=0.010..1.119 rows=1415 loops=1)
Filter: ((ibitmask < 0) OR
(cardinality(mat_arr) > 21))
Rows Removed by Filter: 9320
-> Sort (cost=651.57..666.11 rows=5816 width=72)
(actual time=1.069..1.075 rows=180 loops=1)
Sort Key: qli_1.curr_season, qli_1.curr_code
COLLATE "C"
Sort Method: quicksort Memory: 41kB
-> CTE Scan on qli qli_1 (cost=0.00..287.90
rows=5816 width=72) (actual time=0.057..1.026 rows=180 loops=1)
Filter: ((ibitmask < 0) OR
(cardinality(mat_arr) > 8))
Rows Removed by Filter: 10289
-> Sort (cost=665.41..680.24 rows=5932 width=72)
(actual time=1.110..1.124 rows=481 loops=1)
Sort Key: qin_1.curr_season, qin_1.curr_code
COLLATE "C"
Sort Method: quicksort Memory: 68kB
-> CTE Scan on qin qin_1 (cost=0.00..293.65
rows=5932 width=72) (actual time=0.016..1.046 rows=481 loops=1)
Filter: ((ibitmask < 0) OR
(cardinality(mat_arr) > 8))
Rows Removed by Filter: 10197
-> Sort (cost=666.83..681.69 rows=5944 width=72) (actual
time=1.119..1.128 rows=417 loops=1)
Sort Key: qou_1.curr_season, qou_1.curr_code COLLATE "C"
Sort Method: quicksort Memory: 68kB
-> CTE Scan on qou qou_1 (cost=0.00..294.22 rows=5944
width=72) (actual time=0.029..1.056 rows=424 loops=1)
Filter: ((ibitmask < 0) OR (cardinality(mat_arr) > 11))
Rows Removed by Filter: 10275
Planning Time: 1.746 ms
Execution Time: 13405.503 ms
(116 Zeilen)This case really brought me to detect the problem!
The original query and data are not shown here, but the principle should
be clear from the execution plans.I think the planner shouldn't change the row estimations on further
steps after left joins at all, and be a bit more conservative on inner
joins.
This may be related to the fact that this case has 2 join-conditions
(xx_season an xx_code).Thanks for looking
Hans Buschmann
!! External Email: This email originated from outside of the
organization. Do not click links or open attachments unless you
recognize the sender.
--
Tomas Vondra
EnterpriseDB: https://nam04.safelinks.protection.outlook.com/?url=http%3A%2F%2Fwww.enterprisedb.com%2F&data=05%7C01%7Cgjian%40vmware.com%7C9d40e84af2c946f3517a08db9cc61ee2%7Cb39138ca3cee4b4aa4d6cd83d9dd62f0%7C0%7C0%7C638276146959658928%7CUnknown%7CTWFpbGZsb3d8eyJWIjoiMC4wLjAwMDAiLCJQIjoiV2luMzIiLCJBTiI6Ik1haWwiLCJXVCI6Mn0%3D%7C3000%7C%7C%7C&sdata=VzTmxC6ay28C8%2BaA3Dsi%2BDDWxGEgh9UVaPfc%2BMiL5Mo%3D&reserved=0<http://www.enterprisedb.com/>
The Enterprise PostgreSQL Company
!! External Email: This email originated from outside of the organization. Do not click links or open attachments unless you recognize the sender.
Attachments:
0001-Examine-simple-variable-for-Var-in-CTE.patchtext/x-patch; name=0001-Examine-simple-variable-for-Var-in-CTE.patchDownload
From 0ade8f4e9a0741fa4f1a6998abf2488c5b846aa7 Mon Sep 17 00:00:00 2001
From: Jian Guo <gjian@vmware.com>
Date: Mon, 21 Aug 2023 16:07:41 +0800
Subject: [PATCH] Examine simple variable for Var in CTE.
Signed-off-by: Jian Guo <gjian@vmware.com>
---
src/backend/utils/adt/selfuncs.c | 37 +++++++++++++++++++++++++++++---
1 file changed, 34 insertions(+), 3 deletions(-)
diff --git a/src/backend/utils/adt/selfuncs.c b/src/backend/utils/adt/selfuncs.c
index c4fcd0076e..bcf0fb4e16 100644
--- a/src/backend/utils/adt/selfuncs.c
+++ b/src/backend/utils/adt/selfuncs.c
@@ -5598,13 +5598,43 @@ examine_simple_variable(PlannerInfo *root, Var *var,
examine_simple_variable(rel->subroot, var, vardata);
}
}
+ else if (rte->rtekind == RTE_CTE && !rte->inh)
+ {
+ /*
+ * Punt if it's a whole-row var rather than a plain column reference.
+ */
+ if (var->varattno == InvalidAttrNumber)
+ return;
+ /*
+ * OK, fetch RelOptInfo for subquery.
+ */
+ RelOptInfo *rel = find_base_rel(root, var->varno);
+ /* Can only handle a simple Var of subquery's query level */
+ if (var && IsA(var, Var) &&
+ var->varlevelsup == 0)
+ {
+ /*
+ * OK, recurse into the subquery. Note that the original setting
+ * of vardata->isunique (which will surely be false) is left
+ * unchanged in this situation. That's what we want, since even
+ * if the underlying column is unique, the subquery may have
+ * joined to other tables in a way that creates duplicates.
+ */
+ Index varnoSaved = var->varno;
+ /* Mock a fake index for CTE */
+ var->varno = 1;
+ if (rel->subroot)
+ examine_simple_variable(rel->subroot, var, vardata);
+ var->varno = varnoSaved;
+ }
+ }
else
{
/*
- * Otherwise, the Var comes from a FUNCTION, VALUES, or CTE RTE. (We
+ * Otherwise, the Var comes from a FUNCTION, VALUES. (We
* won't see RTE_JOIN here because join alias Vars have already been
* flattened.) There's not much we can do with function outputs, but
- * maybe someday try to be smarter about VALUES and/or CTEs.
+ * maybe someday try to be smarter about VALUES.
*/
}
}
@@ -5866,7 +5896,8 @@ get_variable_range(PlannerInfo *root, VariableStatData *vardata,
* data. Proceed only if the MCVs represent the whole table (to within
* roundoff error).
*/
- if (get_attstatsslot(&sslot, vardata->statsTuple,
+ bool has_mcv = (!vardata->rel || vardata->rel->rtekind != RTE_CTE);
+ if (has_mcv && get_attstatsslot(&sslot, vardata->statsTuple,
STATISTIC_KIND_MCV, InvalidOid,
have_data ? ATTSTATSSLOT_VALUES :
(ATTSTATSSLOT_VALUES | ATTSTATSSLOT_NUMBERS)))
--
2.41.0
On 8/21/23 10:16, Jian Guo wrote:
Hi hackers,
I found a new approach to fix this issue, which seems better, so I would
like to post another version of the patch here. The origin patch made
the assumption of the values of Vars from CTE must be unique, which
could be very wrong. This patch examines variables for Vars inside CTE,
which avoided the bad assumption, so the results could be much more
accurate.
No problem with posting a reworked patch to the same thread, but I'll
repeat my suggestion to register this in the CF app [1]https://commitfest.postgresql.org. The benefit is
that people are more likely to notice the patch and also cfbot [2]http://cfbot.cputube.org/ will
run regression tests.
[1]: https://commitfest.postgresql.org
[2]: http://cfbot.cputube.org/
--
Tomas Vondra
EnterpriseDB: http://www.enterprisedb.com
The Enterprise PostgreSQL Company
Sure, Tomas.
Here is the PG Commitfest link: https://commitfest.postgresql.org/44/4510/
________________________________
From: Tomas Vondra <tomas.vondra@enterprisedb.com>
Sent: Monday, August 21, 2023 18:56
To: Jian Guo <gjian@vmware.com>; Hans Buschmann <buschmann@nidsa.net>; pgsql-hackers@lists.postgresql.org <pgsql-hackers@lists.postgresql.org>
Cc: Zhenghua Lyu <zlyu@vmware.com>
Subject: Re: Wrong rows estimations with joins of CTEs slows queries by more than factor 500
!! External Email
On 8/21/23 10:16, Jian Guo wrote:
Hi hackers,
I found a new approach to fix this issue, which seems better, so I would
like to post another version of the patch here. The origin patch made
the assumption of the values of Vars from CTE must be unique, which
could be very wrong. This patch examines variables for Vars inside CTE,
which avoided the bad assumption, so the results could be much more
accurate.
No problem with posting a reworked patch to the same thread, but I'll
repeat my suggestion to register this in the CF app [1]https://nam04.safelinks.protection.outlook.com/?url=https%3A%2F%2Fcommitfest.postgresql.org%2F&data=05%7C01%7Cgjian%40vmware.com%7C4562125966b248a1e18308dba2353d8f%7Cb39138ca3cee4b4aa4d6cd83d9dd62f0%7C0%7C0%7C638282121775872407%7CUnknown%7CTWFpbGZsb3d8eyJWIjoiMC4wLjAwMDAiLCJQIjoiV2luMzIiLCJBTiI6Ik1haWwiLCJXVCI6Mn0%3D%7C3000%7C%7C%7C&sdata=OmMo0lQtSvDFWu8VbI0ZorDpZ3BuxsmkTjagGfnryEc%3D&reserved=0<https://commitfest.postgresql.org/>. The benefit is
that people are more likely to notice the patch and also cfbot [2]https://nam04.safelinks.protection.outlook.com/?url=http%3A%2F%2Fcfbot.cputube.org%2F&data=05%7C01%7Cgjian%40vmware.com%7C4562125966b248a1e18308dba2353d8f%7Cb39138ca3cee4b4aa4d6cd83d9dd62f0%7C0%7C0%7C638282121775872407%7CUnknown%7CTWFpbGZsb3d8eyJWIjoiMC4wLjAwMDAiLCJQIjoiV2luMzIiLCJBTiI6Ik1haWwiLCJXVCI6Mn0%3D%7C3000%7C%7C%7C&sdata=xTYDRybLm0AYyvRNqtN85fZWeUJREshIq7PYhz8bMgU%3D&reserved=0<http://cfbot.cputube.org/> will
run regression tests.
[1]: https://nam04.safelinks.protection.outlook.com/?url=https%3A%2F%2Fcommitfest.postgresql.org%2F&data=05%7C01%7Cgjian%40vmware.com%7C4562125966b248a1e18308dba2353d8f%7Cb39138ca3cee4b4aa4d6cd83d9dd62f0%7C0%7C0%7C638282121775872407%7CUnknown%7CTWFpbGZsb3d8eyJWIjoiMC4wLjAwMDAiLCJQIjoiV2luMzIiLCJBTiI6Ik1haWwiLCJXVCI6Mn0%3D%7C3000%7C%7C%7C&sdata=OmMo0lQtSvDFWu8VbI0ZorDpZ3BuxsmkTjagGfnryEc%3D&reserved=0<https://commitfest.postgresql.org/>
[2]: https://nam04.safelinks.protection.outlook.com/?url=http%3A%2F%2Fcfbot.cputube.org%2F&data=05%7C01%7Cgjian%40vmware.com%7C4562125966b248a1e18308dba2353d8f%7Cb39138ca3cee4b4aa4d6cd83d9dd62f0%7C0%7C0%7C638282121775872407%7CUnknown%7CTWFpbGZsb3d8eyJWIjoiMC4wLjAwMDAiLCJQIjoiV2luMzIiLCJBTiI6Ik1haWwiLCJXVCI6Mn0%3D%7C3000%7C%7C%7C&sdata=xTYDRybLm0AYyvRNqtN85fZWeUJREshIq7PYhz8bMgU%3D&reserved=0<http://cfbot.cputube.org/>
--
Tomas Vondra
EnterpriseDB: https://nam04.safelinks.protection.outlook.com/?url=http%3A%2F%2Fwww.enterprisedb.com%2F&data=05%7C01%7Cgjian%40vmware.com%7C4562125966b248a1e18308dba2353d8f%7Cb39138ca3cee4b4aa4d6cd83d9dd62f0%7C0%7C0%7C638282121775872407%7CUnknown%7CTWFpbGZsb3d8eyJWIjoiMC4wLjAwMDAiLCJQIjoiV2luMzIiLCJBTiI6Ik1haWwiLCJXVCI6Mn0%3D%7C3000%7C%7C%7C&sdata=Zn4W8nPFmKxCLQ3XM555UlnM%2F9q1XLkJU5PRxT1VSig%3D&reserved=0<http://www.enterprisedb.com/>
The Enterprise PostgreSQL Company
!! External Email: This email originated from outside of the organization. Do not click links or open attachments unless you recognize the sender.
On Tue, Aug 22, 2023 at 10:35 AM Jian Guo <gjian@vmware.com> wrote:
Sure, Tomas.
Here is the PG Commitfest link: https://commitfest.postgresql.org/44/4510/
________________________________
hi.
wondering around http://cfbot.cputube.org/
there is a compiler warning: https://cirrus-ci.com/task/6052087599988736
I slightly edited the code, making the compiler warning out.
I am not sure if the following duplicate comment from (rte->rtekind ==
RTE_SUBQUERY && !rte->inh) branch is correct.
/*
* OK, recurse into the subquery. Note that the original setting
* of vardata->isunique (which will surely be false) is left
* unchanged in this situation. That's what we want, since even
* if the underlying column is unique, the subquery may have
* joined to other tables in a way that creates duplicates.
*/
Index varnoSaved = var->varno;
here varnoSaved should be int?
image attached is the coverage report
if I understand coverage report correctly,
`
if (rel->subroot) examine_simple_variable(rel->subroot, var, vardata);
`
the above never actually executed?
Attachments:
src_backend_utils_adt_selfuncs.c.gcov.html.pngimage/png; name=src_backend_utils_adt_selfuncs.c.gcov.html.pngDownload
�PNG
IHDR z � n�W� sBIT|d� tEXtSoftware gnome-screenshot��>