pg.ddx.io pgsql-hackers@postgresql.org mailing list archive
help / color / mirror / Atom feedWrong rows estimations with joins of CTEs slows queries by more than factor 500
29+ messages / 10 participants
[nested] [flat]
Commits from this thread:
2024-01-08 89b69db82a Allow examine_simple_variable() to work on INSERT RETURNING Vars.
2023-11-17 f7816aec23 Extract column statistics from CTE references, if possible.
* Wrong rows estimations with joins of CTEs slows queries by more than factor 500
@ 2023-02-08 13:55 Hans Buschmann <buschmann@nidsa.net>
2023-02-08 21:27 ` Re: Wrong rows estimations with joins of CTEs slows queries by more than factor 500 Tomas Vondra <tomas.vondra@enterprisedb.com>
2023-08-14 11:12 ` Re: Wrong rows estimations with joins of CTEs slows queries by more than factor 500 Jian Guo <gjian@vmware.com>
0 siblings, 2 replies; 29+ messages in thread
From: Hans Buschmann @ 2023-02-08 13:55 UTC (permalink / raw)
To: pgsql-hackers@lists.postgresql.org <pgsql-hackers@lists.postgresql.org>
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
^ permalink raw reply [nested|flat] 29+ messages in thread
* Re: Wrong rows estimations with joins of CTEs slows queries by more than factor 500
2023-02-08 13:55 Wrong rows estimations with joins of CTEs slows queries by more than factor 500 Hans Buschmann <buschmann@nidsa.net>
@ 2023-02-08 21:27 ` Tomas Vondra <tomas.vondra@enterprisedb.com>
1 sibling, 0 replies; 29+ messages in thread
From: Tomas Vondra @ 2023-02-08 21:27 UTC (permalink / raw)
To: Hans Buschmann <buschmann@nidsa.net>; pgsql-hackers@lists.postgresql.org <pgsql-hackers@lists.postgresql.org>
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:
https://github.com/postgres/postgres/blob/dbe8a1726cfd5a09cf1ef99e76f5f89e2efada71/src/backend/optim...
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
^ permalink raw reply [nested|flat] 29+ messages in thread
* Re: Wrong rows estimations with joins of CTEs slows queries by more than factor 500
2023-02-08 13:55 Wrong rows estimations with joins of CTEs slows queries by more than factor 500 Hans Buschmann <buschmann@nidsa.net>
@ 2023-08-14 11:12 ` Jian Guo <gjian@vmware.com>
2023-08-14 12:58 ` Re: Wrong rows estimations with joins of CTEs slows queries by more than factor 500 Tomas Vondra <tomas.vondra@enterprisedb.com>
1 sibling, 1 reply; 29+ messages in thread
From: Jian Guo @ 2023-08-14 11:12 UTC (permalink / raw)
To: Hans Buschmann <buschmann@nidsa.net>; pgsql-hackers@lists.postgresql.org <pgsql-hackers@lists.postgresql.org>
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.patch (text/x-patch, 903B) download
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
* Re: Wrong rows estimations with joins of CTEs slows queries by more than factor 500
2023-02-08 13:55 Wrong rows estimations with joins of CTEs slows queries by more than factor 500 Hans Buschmann <buschmann@nidsa.net>
2023-08-14 11:12 ` Re: Wrong rows estimations with joins of CTEs slows queries by more than factor 500 Jian Guo <gjian@vmware.com>
@ 2023-08-14 12:58 ` Tomas Vondra <tomas.vondra@enterprisedb.com>
2023-08-21 08:16 ` Re: Wrong rows estimations with joins of CTEs slows queries by more than factor 500 Jian Guo <gjian@vmware.com>
0 siblings, 1 reply; 29+ messages in thread
From: Tomas Vondra @ 2023-08-14 12:58 UTC (permalink / raw)
To: Jian Guo <gjian@vmware.com>; Hans Buschmann <buschmann@nidsa.net>; pgsql-hackers@lists.postgresql.org <pgsql-hackers@lists.postgresql.org>
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) <=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.
>
--
Tomas Vondra
EnterpriseDB: http://www.enterprisedb.com
The Enterprise PostgreSQL Company
^ permalink raw reply [nested|flat] 29+ messages in thread
* Re: Wrong rows estimations with joins of CTEs slows queries by more than factor 500
2023-02-08 13:55 Wrong rows estimations with joins of CTEs slows queries by more than factor 500 Hans Buschmann <buschmann@nidsa.net>
2023-08-14 11:12 ` Re: Wrong rows estimations with joins of CTEs slows queries by more than factor 500 Jian Guo <gjian@vmware.com>
2023-08-14 12:58 ` Re: Wrong rows estimations with joins of CTEs slows queries by more than factor 500 Tomas Vondra <tomas.vondra@enterprisedb.com>
@ 2023-08-21 08:16 ` Jian Guo <gjian@vmware.com>
2023-08-21 10:56 ` Re: Wrong rows estimations with joins of CTEs slows queries by more than factor 500 Tomas Vondra <tomas.vondra@enterprisedb.com>
2023-11-08 22:44 ` Re: Wrong rows estimations with joins of CTEs slows queries by more than factor 500 Tom Lane <tgl@sss.pgh.pa.us>
0 siblings, 2 replies; 29+ messages in thread
From: Jian Guo @ 2023-08-21 08:16 UTC (permalink / raw)
To: Tomas Vondra <tomas.vondra@enterprisedb.com>; Hans Buschmann <buschmann@nidsa.net>; pgsql-hackers@lists.postgresql.org <pgsql-hackers@lists.postgresql.org>; +Cc: Zhenghua Lyu <zlyu@vmware.com>
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.
See https://nam04.safelinks.protection.outlook.com/?url=https%3A%2F%2Fcommitfest.postgresql.org%2F&d...;
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) <=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.
>
--
Tomas Vondra
EnterpriseDB: https://nam04.safelinks.protection.outlook.com/?url=http%3A%2F%2Fwww.enterprisedb.com%2F&data=05...;
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.patch (text/x-patch, 2.6K) download
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
* Re: Wrong rows estimations with joins of CTEs slows queries by more than factor 500
2023-02-08 13:55 Wrong rows estimations with joins of CTEs slows queries by more than factor 500 Hans Buschmann <buschmann@nidsa.net>
2023-08-14 11:12 ` Re: Wrong rows estimations with joins of CTEs slows queries by more than factor 500 Jian Guo <gjian@vmware.com>
2023-08-14 12:58 ` Re: Wrong rows estimations with joins of CTEs slows queries by more than factor 500 Tomas Vondra <tomas.vondra@enterprisedb.com>
2023-08-21 08:16 ` Re: Wrong rows estimations with joins of CTEs slows queries by more than factor 500 Jian Guo <gjian@vmware.com>
@ 2023-08-21 10:56 ` Tomas Vondra <tomas.vondra@enterprisedb.com>
2023-08-22 02:35 ` Re: Wrong rows estimations with joins of CTEs slows queries by more than factor 500 Jian Guo <gjian@vmware.com>
1 sibling, 1 reply; 29+ messages in thread
From: Tomas Vondra @ 2023-08-21 10:56 UTC (permalink / raw)
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>
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]. The benefit is
that people are more likely to notice the patch and also cfbot [2] 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
^ permalink raw reply [nested|flat] 29+ messages in thread
* Re: Wrong rows estimations with joins of CTEs slows queries by more than factor 500
2023-02-08 13:55 Wrong rows estimations with joins of CTEs slows queries by more than factor 500 Hans Buschmann <buschmann@nidsa.net>
2023-08-14 11:12 ` Re: Wrong rows estimations with joins of CTEs slows queries by more than factor 500 Jian Guo <gjian@vmware.com>
2023-08-14 12:58 ` Re: Wrong rows estimations with joins of CTEs slows queries by more than factor 500 Tomas Vondra <tomas.vondra@enterprisedb.com>
2023-08-21 08:16 ` Re: Wrong rows estimations with joins of CTEs slows queries by more than factor 500 Jian Guo <gjian@vmware.com>
2023-08-21 10:56 ` Re: Wrong rows estimations with joins of CTEs slows queries by more than factor 500 Tomas Vondra <tomas.vondra@enterprisedb.com>
@ 2023-08-22 02:35 ` Jian Guo <gjian@vmware.com>
2023-09-06 06:00 ` Re: Wrong rows estimations with joins of CTEs slows queries by more than factor 500 jian he <jian.universality@gmail.com>
0 siblings, 1 reply; 29+ messages in thread
From: Jian Guo @ 2023-08-22 02:35 UTC (permalink / raw)
To: Tomas Vondra <tomas.vondra@enterprisedb.com>; Hans Buschmann <buschmann@nidsa.net>; pgsql-hackers@lists.postgresql.org <pgsql-hackers@lists.postgresql.org>
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]. The benefit is
that people are more likely to notice the patch and also cfbot [2] will
run regression tests.
[1] https://nam04.safelinks.protection.outlook.com/?url=https%3A%2F%2Fcommitfest.postgresql.org%2F&d...;
[2] https://nam04.safelinks.protection.outlook.com/?url=http%3A%2F%2Fcfbot.cputube.org%2F&data=05%7C...;
--
Tomas Vondra
EnterpriseDB: https://nam04.safelinks.protection.outlook.com/?url=http%3A%2F%2Fwww.enterprisedb.com%2F&data=05...;
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.
^ permalink raw reply [nested|flat] 29+ messages in thread
* Re: Wrong rows estimations with joins of CTEs slows queries by more than factor 500
2023-02-08 13:55 Wrong rows estimations with joins of CTEs slows queries by more than factor 500 Hans Buschmann <buschmann@nidsa.net>
2023-08-14 11:12 ` Re: Wrong rows estimations with joins of CTEs slows queries by more than factor 500 Jian Guo <gjian@vmware.com>
2023-08-14 12:58 ` Re: Wrong rows estimations with joins of CTEs slows queries by more than factor 500 Tomas Vondra <tomas.vondra@enterprisedb.com>
2023-08-21 08:16 ` Re: Wrong rows estimations with joins of CTEs slows queries by more than factor 500 Jian Guo <gjian@vmware.com>
2023-08-21 10:56 ` Re: Wrong rows estimations with joins of CTEs slows queries by more than factor 500 Tomas Vondra <tomas.vondra@enterprisedb.com>
2023-08-22 02:35 ` Re: Wrong rows estimations with joins of CTEs slows queries by more than factor 500 Jian Guo <gjian@vmware.com>
@ 2023-09-06 06:00 ` jian he <jian.universality@gmail.com>
2023-09-07 09:26 ` Re: Wrong rows estimations with joins of CTEs slows queries by more than factor 500 Jian Guo <gjian@vmware.com>
0 siblings, 1 reply; 29+ messages in thread
From: jian he @ 2023-09-06 06:00 UTC (permalink / raw)
To: Jian Guo <gjian@vmware.com>; +Cc: Tomas Vondra <tomas.vondra@enterprisedb.com>; Hans Buschmann <buschmann@nidsa.net>; pgsql-hackers@lists.postgresql.org <pgsql-hackers@lists.postgresql.org>
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.png (image/png, 472.1K) download
view image
* Re: Wrong rows estimations with joins of CTEs slows queries by more than factor 500
2023-02-08 13:55 Wrong rows estimations with joins of CTEs slows queries by more than factor 500 Hans Buschmann <buschmann@nidsa.net>
2023-08-14 11:12 ` Re: Wrong rows estimations with joins of CTEs slows queries by more than factor 500 Jian Guo <gjian@vmware.com>
2023-08-14 12:58 ` Re: Wrong rows estimations with joins of CTEs slows queries by more than factor 500 Tomas Vondra <tomas.vondra@enterprisedb.com>
2023-08-21 08:16 ` Re: Wrong rows estimations with joins of CTEs slows queries by more than factor 500 Jian Guo <gjian@vmware.com>
2023-08-21 10:56 ` Re: Wrong rows estimations with joins of CTEs slows queries by more than factor 500 Tomas Vondra <tomas.vondra@enterprisedb.com>
2023-08-22 02:35 ` Re: Wrong rows estimations with joins of CTEs slows queries by more than factor 500 Jian Guo <gjian@vmware.com>
2023-09-06 06:00 ` Re: Wrong rows estimations with joins of CTEs slows queries by more than factor 500 jian he <jian.universality@gmail.com>
@ 2023-09-07 09:26 ` Jian Guo <gjian@vmware.com>
0 siblings, 0 replies; 29+ messages in thread
From: Jian Guo @ 2023-09-07 09:26 UTC (permalink / raw)
To: jian he <jian.universality@gmail.com>; +Cc: Tomas Vondra <tomas.vondra@enterprisedb.com>; Hans Buschmann <buschmann@nidsa.net>; pgsql-hackers@lists.postgresql.org <pgsql-hackers@lists.postgresql.org>
Hi Jian He,
Thanks for fixing the compiler warnings, seems the CI used a little old compiler and complained:
ISO C90 forbids mixed declarations and code [-Werror=declaration-after-statement]
But later C standard have relaxed the requirements for this, ISO C99 and later standard allow declarations and code to be freely mixed within compound statements: https://gcc.gnu.org/onlinedocs/gcc/Mixed-Labels-and-Declarations.html
Mixed Labels and Declarations (Using the GNU Compiler Collection (GCC))<https://gcc.gnu.org/onlinedocs/gcc/Mixed-Labels-and-Declarations.html;
Mixed Labels and Declarations (Using the GNU Compiler Collection (GCC))
gcc.gnu.org
________________________________
From: jian he <jian.universality@gmail.com>
Sent: Wednesday, September 6, 2023 14:00
To: Jian Guo <gjian@vmware.com>
Cc: Tomas Vondra <tomas.vondra@enterprisedb.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
On Tue, Aug 22, 2023 at 10:35 AM Jian Guo <gjian@vmware.com> wrote:
>
> Sure, Tomas.
>
> Here is the PG Commitfest link: https://nam04.safelinks.protection.outlook.com/?url=https%3A%2F%2Fcommitfest.postgresql.org%2F44%2F4...;
> ________________________________
hi.
wondering around https://nam04.safelinks.protection.outlook.com/?url=http%3A%2F%2Fcfbot.cputube.org%2F&data=05%7C...;
there is a compiler warning: https://nam04.safelinks.protection.outlook.com/?url=https%3A%2F%2Fcirrus-ci.com%2Ftask%2F60520875999...;
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?
!! External Email: This email originated from outside of the organization. Do not click links or open attachments unless you recognize the sender.
^ permalink raw reply [nested|flat] 29+ messages in thread
* Re: Wrong rows estimations with joins of CTEs slows queries by more than factor 500
2023-02-08 13:55 Wrong rows estimations with joins of CTEs slows queries by more than factor 500 Hans Buschmann <buschmann@nidsa.net>
2023-08-14 11:12 ` Re: Wrong rows estimations with joins of CTEs slows queries by more than factor 500 Jian Guo <gjian@vmware.com>
2023-08-14 12:58 ` Re: Wrong rows estimations with joins of CTEs slows queries by more than factor 500 Tomas Vondra <tomas.vondra@enterprisedb.com>
2023-08-21 08:16 ` Re: Wrong rows estimations with joins of CTEs slows queries by more than factor 500 Jian Guo <gjian@vmware.com>
@ 2023-11-08 22:44 ` Tom Lane <tgl@sss.pgh.pa.us>
2023-11-16 09:24 ` Re: Wrong rows estimations with joins of CTEs slows queries by more than factor 500 Richard Guo <guofenglinux@gmail.com>
1 sibling, 1 reply; 29+ messages in thread
From: Tom Lane @ 2023-11-08 22:44 UTC (permalink / raw)
To: Jian Guo <gjian@vmware.com>; +Cc: Tomas Vondra <tomas.vondra@enterprisedb.com>; Hans Buschmann <buschmann@nidsa.net>; pgsql-hackers@lists.postgresql.org <pgsql-hackers@lists.postgresql.org>; Zhenghua Lyu <zlyu@vmware.com>
Jian Guo <gjian@vmware.com> writes:
> 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.
You have the right general idea, but there is nothing about this patch
that's right in detail. The outer Var doesn't refer to any particular
RTE within the subquery; it refers to a targetlist entry. You have to
drill down to that, see if it's a Var, and if so you can recurse into
the subroot with that Var. As this stands, it might accidentally get
the right answer for "SELECT * FROM foo" subqueries, but it will get
the wrong answer or even crash for anything that's not that.
The existing RTE_SUBQUERY stanza has most of what we need for this,
so I experimented with extending that to also handle RTE_CTE. It
seems to work, though I soon found out that it needed tweaking for
the case where the CTE is INSERT/UPDATE/DELETE RETURNING.
Interestingly, this does not change any existing regression test
results. I'd supposed there might be at least one place with a
visible plan change, but nope. Running a coverage test does show
that the new code paths are exercised, but I wonder if we ought
to try to devise a regression test that proves it more directly.
regards, tom lane
PS: please, please, please do not quote the entire damn thread
when replying. Trim it to just a minimum amount of relevant
text. You think people want to read all that again?
Attachments:
v2-0001-Examine-simple-variable-for-Var-in-CTE.patch (text/x-diff, 5.6K) download
diff --git a/src/backend/utils/adt/selfuncs.c b/src/backend/utils/adt/selfuncs.c
index c4fcd0076e..196f50b241 100644
--- a/src/backend/utils/adt/selfuncs.c
+++ b/src/backend/utils/adt/selfuncs.c
@@ -5493,13 +5493,17 @@ examine_simple_variable(PlannerInfo *root, Var *var,
vardata->acl_ok = true;
}
}
- else if (rte->rtekind == RTE_SUBQUERY && !rte->inh)
+ else if ((rte->rtekind == RTE_SUBQUERY && !rte->inh) ||
+ (rte->rtekind == RTE_CTE && !rte->self_reference))
{
/*
- * Plain subquery (not one that was converted to an appendrel).
+ * Plain subquery (not one that was converted to an appendrel) or
+ * non-recursive CTE. In either case, we can try to find out what the
+ * Var refers to within the subquery.
*/
- Query *subquery = rte->subquery;
- RelOptInfo *rel;
+ Query *subquery;
+ PlannerInfo *subroot;
+ List *subtlist;
TargetEntry *ste;
/*
@@ -5508,6 +5512,80 @@ examine_simple_variable(PlannerInfo *root, Var *var,
if (var->varattno == InvalidAttrNumber)
return;
+ /*
+ * Otherwise, find the subquery's query tree and planner subroot.
+ */
+ if (rte->rtekind == RTE_SUBQUERY)
+ {
+ RelOptInfo *rel;
+
+ /*
+ * Fetch RelOptInfo for subquery. Note that we don't change the
+ * rel returned in vardata, since caller expects it to be a rel of
+ * the caller's query level. Because we might already be
+ * recursing, we can't use that rel pointer either, but have to
+ * look up the Var's rel afresh.
+ */
+ rel = find_base_rel(root, var->varno);
+
+ subquery = rte->subquery;
+ subroot = rel->subroot;
+ }
+ else
+ {
+ /* CTE case is more difficult */
+ PlannerInfo *cteroot;
+ Index levelsup;
+ int ndx;
+ int plan_id;
+ ListCell *lc;
+
+ /*
+ * Find the referenced CTE, and locate the subroot previously made
+ * for it.
+ */
+ levelsup = rte->ctelevelsup;
+ cteroot = root;
+ while (levelsup-- > 0)
+ {
+ cteroot = cteroot->parent_root;
+ if (!cteroot) /* shouldn't happen */
+ elog(ERROR, "bad levelsup for CTE \"%s\"", rte->ctename);
+ }
+
+ /*
+ * Note: cte_plan_ids can be shorter than cteList, if we are still
+ * working on planning the CTEs (ie, this is a side-reference from
+ * another CTE). So we mustn't use forboth here.
+ */
+ ndx = 0;
+ subquery = NULL;
+ foreach(lc, cteroot->parse->cteList)
+ {
+ CommonTableExpr *cte = (CommonTableExpr *) lfirst(lc);
+
+ if (strcmp(cte->ctename, rte->ctename) == 0)
+ {
+ subquery = castNode(Query, cte->ctequery);
+ break;
+ }
+ ndx++;
+ }
+ if (subquery == NULL) /* shouldn't happen */
+ elog(ERROR, "could not find CTE \"%s\"", rte->ctename);
+ if (ndx >= list_length(cteroot->cte_plan_ids))
+ elog(ERROR, "could not find plan for CTE \"%s\"", rte->ctename);
+ plan_id = list_nth_int(cteroot->cte_plan_ids, ndx);
+ if (plan_id <= 0)
+ elog(ERROR, "no plan was made for CTE \"%s\"", rte->ctename);
+ subroot = list_nth(root->glob->subroots, plan_id - 1);
+ }
+
+ /* If the subquery hasn't been planned yet, we have to punt */
+ if (subroot == NULL)
+ return;
+ Assert(IsA(subroot, PlannerInfo));
+
/*
* Punt if subquery uses set operations or GROUP BY, as these will
* mash underlying columns' stats beyond recognition. (Set ops are
@@ -5521,20 +5599,6 @@ examine_simple_variable(PlannerInfo *root, Var *var,
subquery->groupingSets)
return;
- /*
- * OK, fetch RelOptInfo for subquery. Note that we don't change the
- * rel returned in vardata, since caller expects it to be a rel of the
- * caller's query level. Because we might already be recursing, we
- * can't use that rel pointer either, but have to look up the Var's
- * rel afresh.
- */
- rel = find_base_rel(root, var->varno);
-
- /* If the subquery hasn't been planned yet, we have to punt */
- if (rel->subroot == NULL)
- return;
- Assert(IsA(rel->subroot, PlannerInfo));
-
/*
* Switch our attention to the subquery as mangled by the planner. It
* was okay to look at the pre-planning version for the tests above,
@@ -5543,11 +5607,15 @@ examine_simple_variable(PlannerInfo *root, Var *var,
* planning, Vars in the targetlist might have gotten replaced, and we
* need to see the replacement expressions.
*/
- subquery = rel->subroot->parse;
+ subquery = subroot->parse;
Assert(IsA(subquery, Query));
/* Get the subquery output expression referenced by the upper Var */
- ste = get_tle_by_resno(subquery->targetList, var->varattno);
+ if (subquery->returningList)
+ subtlist = subquery->returningList;
+ else
+ subtlist = subquery->targetList;
+ ste = get_tle_by_resno(subtlist, var->varattno);
if (ste == NULL || ste->resjunk)
elog(ERROR, "subquery %s does not have attribute %d",
rte->eref->aliasname, var->varattno);
@@ -5595,16 +5663,16 @@ examine_simple_variable(PlannerInfo *root, Var *var,
* if the underlying column is unique, the subquery may have
* joined to other tables in a way that creates duplicates.
*/
- examine_simple_variable(rel->subroot, var, vardata);
+ examine_simple_variable(subroot, var, vardata);
}
}
else
{
/*
- * Otherwise, the Var comes from a FUNCTION, VALUES, or CTE RTE. (We
- * won't see RTE_JOIN here because join alias Vars have already been
+ * Otherwise, the Var comes from a FUNCTION or VALUES RTE. (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.
*/
}
}
* Re: Wrong rows estimations with joins of CTEs slows queries by more than factor 500
2023-02-08 13:55 Wrong rows estimations with joins of CTEs slows queries by more than factor 500 Hans Buschmann <buschmann@nidsa.net>
2023-08-14 11:12 ` Re: Wrong rows estimations with joins of CTEs slows queries by more than factor 500 Jian Guo <gjian@vmware.com>
2023-08-14 12:58 ` Re: Wrong rows estimations with joins of CTEs slows queries by more than factor 500 Tomas Vondra <tomas.vondra@enterprisedb.com>
2023-08-21 08:16 ` Re: Wrong rows estimations with joins of CTEs slows queries by more than factor 500 Jian Guo <gjian@vmware.com>
2023-11-08 22:44 ` Re: Wrong rows estimations with joins of CTEs slows queries by more than factor 500 Tom Lane <tgl@sss.pgh.pa.us>
@ 2023-11-16 09:24 ` Richard Guo <guofenglinux@gmail.com>
2023-11-16 18:16 ` Re: Wrong rows estimations with joins of CTEs slows queries by more than factor 500 Tom Lane <tgl@sss.pgh.pa.us>
0 siblings, 1 reply; 29+ messages in thread
From: Richard Guo @ 2023-11-16 09:24 UTC (permalink / raw)
To: Tom Lane <tgl@sss.pgh.pa.us>; +Cc: Jian Guo <gjian@vmware.com>; Tomas Vondra <tomas.vondra@enterprisedb.com>; Hans Buschmann <buschmann@nidsa.net>; pgsql-hackers@lists.postgresql.org <pgsql-hackers@lists.postgresql.org>; Zhenghua Lyu <zlyu@vmware.com>
On Thu, Nov 9, 2023 at 6:45 AM Tom Lane <tgl@sss.pgh.pa.us> wrote:
> The existing RTE_SUBQUERY stanza has most of what we need for this,
> so I experimented with extending that to also handle RTE_CTE. It
> seems to work, though I soon found out that it needed tweaking for
> the case where the CTE is INSERT/UPDATE/DELETE RETURNING.
The change looks good to me. To nitpick, should we modify the comment
of examine_simple_variable to also mention 'CTEs'?
* This is split out as a subroutine so that we can recurse to deal with
- * Vars referencing subqueries.
+ * Vars referencing subqueries or CTEs.
> Interestingly, this does not change any existing regression test
> results. I'd supposed there might be at least one place with a
> visible plan change, but nope. Running a coverage test does show
> that the new code paths are exercised, but I wonder if we ought
> to try to devise a regression test that proves it more directly.
I think we ought to. Here is one regression test that proves that this
change improves query plans in some cases.
Unpatched:
explain (costs off)
with x as MATERIALIZED (select unique1 from tenk1 b)
select count(*) from tenk1 a where unique1 in (select * from x);
QUERY PLAN
------------------------------------------------------------
Aggregate
CTE x
-> Index Only Scan using tenk1_unique1 on tenk1 b
-> Nested Loop
-> HashAggregate
Group Key: x.unique1
-> CTE Scan on x
-> Index Only Scan using tenk1_unique1 on tenk1 a
Index Cond: (unique1 = x.unique1)
(9 rows)
Patched:
explain (costs off)
with x as MATERIALIZED (select unique1 from tenk1 b)
select count(*) from tenk1 a where unique1 in (select * from x);
QUERY PLAN
------------------------------------------------------------
Aggregate
CTE x
-> Index Only Scan using tenk1_unique1 on tenk1 b
-> Hash Semi Join
Hash Cond: (a.unique1 = x.unique1)
-> Index Only Scan using tenk1_unique1 on tenk1 a
-> Hash
-> CTE Scan on x
(8 rows)
I think the second plan (patched) makes more sense. In the first plan
(unpatched), the HashAggregate node actually does not reduce the the
number of rows because it groups by 'unique1', but planner does not know
that because it lacks statistics for Vars referencing the CTE.
Thanks
Richard
^ permalink raw reply [nested|flat] 29+ messages in thread
* Re: Wrong rows estimations with joins of CTEs slows queries by more than factor 500
2023-02-08 13:55 Wrong rows estimations with joins of CTEs slows queries by more than factor 500 Hans Buschmann <buschmann@nidsa.net>
2023-08-14 11:12 ` Re: Wrong rows estimations with joins of CTEs slows queries by more than factor 500 Jian Guo <gjian@vmware.com>
2023-08-14 12:58 ` Re: Wrong rows estimations with joins of CTEs slows queries by more than factor 500 Tomas Vondra <tomas.vondra@enterprisedb.com>
2023-08-21 08:16 ` Re: Wrong rows estimations with joins of CTEs slows queries by more than factor 500 Jian Guo <gjian@vmware.com>
2023-11-08 22:44 ` Re: Wrong rows estimations with joins of CTEs slows queries by more than factor 500 Tom Lane <tgl@sss.pgh.pa.us>
2023-11-16 09:24 ` Re: Wrong rows estimations with joins of CTEs slows queries by more than factor 500 Richard Guo <guofenglinux@gmail.com>
@ 2023-11-16 18:16 ` Tom Lane <tgl@sss.pgh.pa.us>
2023-11-17 02:09 ` Re: Wrong rows estimations with joins of CTEs slows queries by more than factor 500 Richard Guo <guofenglinux@gmail.com>
0 siblings, 1 reply; 29+ messages in thread
From: Tom Lane @ 2023-11-16 18:16 UTC (permalink / raw)
To: Richard Guo <guofenglinux@gmail.com>; +Cc: Jian Guo <gjian@vmware.com>; Tomas Vondra <tomas.vondra@enterprisedb.com>; Hans Buschmann <buschmann@nidsa.net>; pgsql-hackers@lists.postgresql.org <pgsql-hackers@lists.postgresql.org>; Zhenghua Lyu <zlyu@vmware.com>
Richard Guo <guofenglinux@gmail.com> writes:
> I think the second plan (patched) makes more sense. In the first plan
> (unpatched), the HashAggregate node actually does not reduce the the
> number of rows because it groups by 'unique1', but planner does not know
> that because it lacks statistics for Vars referencing the CTE.
Yeah. It's faster in reality too:
regression=# explain analyze with x as MATERIALIZED (select unique1 from tenk1 b)
select count(*) from tenk1 a where unique1 in (select * from x);
QUERY PLAN
----------------------------------------------------------------------------------------------------------------------------------------------
Aggregate (cost=692.29..692.30 rows=1 width=8) (actual time=15.186..15.188 rows=1 loops=1)
CTE x
-> Index Only Scan using tenk1_unique1 on tenk1 b (cost=0.29..270.29 rows=10000 width=4) (actual time=0.028..0.754 rows=10000 loops=1)
Heap Fetches: 0
-> Nested Loop (cost=225.28..409.50 rows=5000 width=0) (actual time=3.652..14.733 rows=10000 loops=1)
-> HashAggregate (cost=225.00..227.00 rows=200 width=4) (actual time=3.644..4.510 rows=10000 loops=1)
Group Key: x.unique1
Batches: 1 Memory Usage: 929kB
-> CTE Scan on x (cost=0.00..200.00 rows=10000 width=4) (actual time=0.030..1.932 rows=10000 loops=1)
-> Index Only Scan using tenk1_unique1 on tenk1 a (cost=0.29..0.90 rows=1 width=4) (actual time=0.001..0.001 rows=1 loops=10000)
Index Cond: (unique1 = x.unique1)
Heap Fetches: 0
Planning Time: 0.519 ms
Execution Time: 15.479 ms
(14 rows)
vs
regression=# explain analyze with x as MATERIALIZED (select unique1 from tenk1 b)
select count(*) from tenk1 a where unique1 in (select * from x);
QUERY PLAN
--------------------------------------------------------------------------------------------------------------------------------------------------
Aggregate (cost=1028.07..1028.08 rows=1 width=8) (actual time=4.578..4.579 rows=1 loops=1)
CTE x
-> Index Only Scan using tenk1_unique1 on tenk1 b (cost=0.29..270.29 rows=10000 width=4) (actual time=0.011..0.751 rows=10000 loops=1)
Heap Fetches: 0
-> Hash Semi Join (cost=325.28..732.78 rows=10000 width=0) (actual time=2.706..4.305 rows=10000 loops=1)
Hash Cond: (a.unique1 = x.unique1)
-> Index Only Scan using tenk1_unique1 on tenk1 a (cost=0.29..270.29 rows=10000 width=4) (actual time=0.011..0.676 rows=10000 loops=1)
Heap Fetches: 0
-> Hash (cost=200.00..200.00 rows=10000 width=4) (actual time=2.655..2.655 rows=10000 loops=1)
Buckets: 16384 Batches: 1 Memory Usage: 480kB
-> CTE Scan on x (cost=0.00..200.00 rows=10000 width=4) (actual time=0.012..1.963 rows=10000 loops=1)
Planning Time: 0.504 ms
Execution Time: 4.821 ms
(13 rows)
Now, what you get if you remove MATERIALIZED is faster yet:
regression=# explain analyze with x as (select unique1 from tenk1 b)
select count(*) from tenk1 a where unique1 in (select * from x);
QUERY PLAN
--------------------------------------------------------------------------------------------------------------------------------------------------
Aggregate (cost=715.57..715.58 rows=1 width=8) (actual time=2.681..2.682 rows=1 loops=1)
-> Merge Semi Join (cost=0.57..690.57 rows=10000 width=0) (actual time=0.016..2.408 rows=10000 loops=1)
Merge Cond: (a.unique1 = b.unique1)
-> Index Only Scan using tenk1_unique1 on tenk1 a (cost=0.29..270.29 rows=10000 width=4) (actual time=0.007..0.696 rows=10000 loops=1)
Heap Fetches: 0
-> Index Only Scan using tenk1_unique1 on tenk1 b (cost=0.29..270.29 rows=10000 width=4) (actual time=0.007..0.655 rows=10000 loops=1)
Heap Fetches: 0
Planning Time: 0.160 ms
Execution Time: 2.718 ms
(9 rows)
I poked into that and found that the reason we don't get a mergejoin
with the materialized CTE is that the upper planner invocation doesn't
know that the CTE's output is sorted, so it thinks a separate sort
step would be needed.
So you could argue that there's more to do here, but I'm hesitant
to go further. Part of the point of MATERIALIZED is to be an
optimization fence, so breaking down that fence is something to be
wary of. Maybe we shouldn't even take this patch --- but on
balance I think it's an OK compromise.
regards, tom lane
^ permalink raw reply [nested|flat] 29+ messages in thread
* Re: Wrong rows estimations with joins of CTEs slows queries by more than factor 500
2023-02-08 13:55 Wrong rows estimations with joins of CTEs slows queries by more than factor 500 Hans Buschmann <buschmann@nidsa.net>
2023-08-14 11:12 ` Re: Wrong rows estimations with joins of CTEs slows queries by more than factor 500 Jian Guo <gjian@vmware.com>
2023-08-14 12:58 ` Re: Wrong rows estimations with joins of CTEs slows queries by more than factor 500 Tomas Vondra <tomas.vondra@enterprisedb.com>
2023-08-21 08:16 ` Re: Wrong rows estimations with joins of CTEs slows queries by more than factor 500 Jian Guo <gjian@vmware.com>
2023-11-08 22:44 ` Re: Wrong rows estimations with joins of CTEs slows queries by more than factor 500 Tom Lane <tgl@sss.pgh.pa.us>
2023-11-16 09:24 ` Re: Wrong rows estimations with joins of CTEs slows queries by more than factor 500 Richard Guo <guofenglinux@gmail.com>
2023-11-16 18:16 ` Re: Wrong rows estimations with joins of CTEs slows queries by more than factor 500 Tom Lane <tgl@sss.pgh.pa.us>
@ 2023-11-17 02:09 ` Richard Guo <guofenglinux@gmail.com>
2023-11-17 03:38 ` Re: Wrong rows estimations with joins of CTEs slows queries by more than factor 500 Tom Lane <tgl@sss.pgh.pa.us>
0 siblings, 1 reply; 29+ messages in thread
From: Richard Guo @ 2023-11-17 02:09 UTC (permalink / raw)
To: Tom Lane <tgl@sss.pgh.pa.us>; +Cc: Jian Guo <gjian@vmware.com>; Tomas Vondra <tomas.vondra@enterprisedb.com>; Hans Buschmann <buschmann@nidsa.net>; pgsql-hackers@lists.postgresql.org <pgsql-hackers@lists.postgresql.org>; Zhenghua Lyu <zlyu@vmware.com>
On Fri, Nov 17, 2023 at 2:16 AM Tom Lane <tgl@sss.pgh.pa.us> wrote:
> So you could argue that there's more to do here, but I'm hesitant
> to go further. Part of the point of MATERIALIZED is to be an
> optimization fence, so breaking down that fence is something to be
> wary of. Maybe we shouldn't even take this patch --- but on
> balance I think it's an OK compromise.
Agreed. I think the patch is still valuable on its own, although it
does not go down into MATERIALIZED case for further optimization. Maybe
we can take another query as regression test to prove its value, in
which the CTE is not inlined without MATERIALIZED, such as
explain (costs off)
with x as (select unique1, unique2 from tenk1 b)
select count(*) from tenk1 a
where unique1 in (select unique1 from x x1) and
unique1 in (select unique2 from x x2);
QUERY PLAN
------------------------------------------------------------------
Aggregate
CTE x
-> Seq Scan on tenk1 b
-> Hash Join
Hash Cond: (a.unique1 = x2.unique2)
-> Nested Loop
-> HashAggregate
Group Key: x1.unique1
-> CTE Scan on x x1
-> Index Only Scan using tenk1_unique1 on tenk1 a
Index Cond: (unique1 = x1.unique1)
-> Hash
-> HashAggregate
Group Key: x2.unique2
-> CTE Scan on x x2
(15 rows)
vs
explain (costs off)
with x as (select unique1, unique2 from tenk1 b)
select count(*) from tenk1 a
where unique1 in (select unique1 from x x1) and
unique1 in (select unique2 from x x2);
QUERY PLAN
------------------------------------------------------------------
Aggregate
CTE x
-> Seq Scan on tenk1 b
-> Hash Semi Join
Hash Cond: (a.unique1 = x2.unique2)
-> Hash Semi Join
Hash Cond: (a.unique1 = x1.unique1)
-> Index Only Scan using tenk1_unique1 on tenk1 a
-> Hash
-> CTE Scan on x x1
-> Hash
-> CTE Scan on x x2
(12 rows)
I believe the second plan is faster in reality too.
Thanks
Richard
^ permalink raw reply [nested|flat] 29+ messages in thread
* Re: Wrong rows estimations with joins of CTEs slows queries by more than factor 500
2023-02-08 13:55 Wrong rows estimations with joins of CTEs slows queries by more than factor 500 Hans Buschmann <buschmann@nidsa.net>
2023-08-14 11:12 ` Re: Wrong rows estimations with joins of CTEs slows queries by more than factor 500 Jian Guo <gjian@vmware.com>
2023-08-14 12:58 ` Re: Wrong rows estimations with joins of CTEs slows queries by more than factor 500 Tomas Vondra <tomas.vondra@enterprisedb.com>
2023-08-21 08:16 ` Re: Wrong rows estimations with joins of CTEs slows queries by more than factor 500 Jian Guo <gjian@vmware.com>
2023-11-08 22:44 ` Re: Wrong rows estimations with joins of CTEs slows queries by more than factor 500 Tom Lane <tgl@sss.pgh.pa.us>
2023-11-16 09:24 ` Re: Wrong rows estimations with joins of CTEs slows queries by more than factor 500 Richard Guo <guofenglinux@gmail.com>
2023-11-16 18:16 ` Re: Wrong rows estimations with joins of CTEs slows queries by more than factor 500 Tom Lane <tgl@sss.pgh.pa.us>
2023-11-17 02:09 ` Re: Wrong rows estimations with joins of CTEs slows queries by more than factor 500 Richard Guo <guofenglinux@gmail.com>
@ 2023-11-17 03:38 ` Tom Lane <tgl@sss.pgh.pa.us>
2023-11-17 03:45 ` Re: Wrong rows estimations with joins of CTEs slows queries by more than factor 500 David G. Johnston <david.g.johnston@gmail.com>
2023-11-17 03:53 ` Re: Wrong rows estimations with joins of CTEs slows queries by more than factor 500 Laurenz Albe <laurenz.albe@cybertec.at>
2023-11-17 06:41 ` Re: Wrong rows estimations with joins of CTEs slows queries by more than factor 500 Richard Guo <guofenglinux@gmail.com>
2023-11-20 02:42 ` Re: Wrong rows estimations with joins of CTEs slows queries by more than factor 500 Richard Guo <guofenglinux@gmail.com>
0 siblings, 4 replies; 29+ messages in thread
From: Tom Lane @ 2023-11-17 03:38 UTC (permalink / raw)
To: Richard Guo <guofenglinux@gmail.com>; +Cc: Jian Guo <gjian@vmware.com>; Tomas Vondra <tomas.vondra@enterprisedb.com>; Hans Buschmann <buschmann@nidsa.net>; pgsql-hackers@lists.postgresql.org <pgsql-hackers@lists.postgresql.org>; Zhenghua Lyu <zlyu@vmware.com>
Richard Guo <guofenglinux@gmail.com> writes:
> On Fri, Nov 17, 2023 at 2:16 AM Tom Lane <tgl@sss.pgh.pa.us> wrote:
>> So you could argue that there's more to do here, but I'm hesitant
>> to go further. Part of the point of MATERIALIZED is to be an
>> optimization fence, so breaking down that fence is something to be
>> wary of. Maybe we shouldn't even take this patch --- but on
>> balance I think it's an OK compromise.
> Agreed. I think the patch is still valuable on its own, although it
> does not go down into MATERIALIZED case for further optimization.
Right. My earlier response was rather rushed, so let me explain
my thinking a bit more.
When I realized that the discrepancy between MATERIALIZED-and-not
plans was due to the upper planner not seeing the pathkeys for the
CTE scan, my first thought was to try to export those pathkeys.
And my second thought was that the CTE should return multiple
potential paths, much as we do for sub-SELECT-in-FROM subqueries,
with the upper planner eventually choosing one of those paths.
But that second idea would break down the optimization fence
almost completely, because the opinions of the upper planner would
influence which plan we choose for the CTE query. I think we
shouldn't go there, at least not for a CTE explicitly marked
MATERIALIZED. (Maybe if it's not marked MATERIALIZED, but we
chose not to flatten it for some other reason, we could think
about that differently? Not sure.)
I think that when we say that MATERIALIZED is meant as an optimization
fence, what we mostly mean is that the upper query shouldn't influence
the choice of plan for the sub-query. However, we surely allow our
statistics or guesses for the sub-query to subsequently influence what
the upper planner does. If that weren't true, we shouldn't even
expose any non-default rowcount guess to the upper planner --- but
that would lead to really horrid results, so we allow that information
to percolate up from the sub-query. It seems like exposing column
statistics to the upper planner, as the proposed patch does, isn't
fundamentally different from exposing rowcount estimates.
That line of argument also leads to the conclusion that it'd be
okay to expose info about the ordering of the CTE result to the
upper planner. This patch doesn't do that, and I'm not sufficiently
excited about the issue to go write some code. But if someone else
does, I think we shouldn't exclude doing it on the grounds of wanting
to preserve an optimization fence. The fence is sort of one-way
in this line of thinking: information can propagate up to the outer
planner level, but not down into the CTE plan.
Thoughts?
regards, tom lane
^ permalink raw reply [nested|flat] 29+ messages in thread
* Re: Wrong rows estimations with joins of CTEs slows queries by more than factor 500
2023-02-08 13:55 Wrong rows estimations with joins of CTEs slows queries by more than factor 500 Hans Buschmann <buschmann@nidsa.net>
2023-08-14 11:12 ` Re: Wrong rows estimations with joins of CTEs slows queries by more than factor 500 Jian Guo <gjian@vmware.com>
2023-08-14 12:58 ` Re: Wrong rows estimations with joins of CTEs slows queries by more than factor 500 Tomas Vondra <tomas.vondra@enterprisedb.com>
2023-08-21 08:16 ` Re: Wrong rows estimations with joins of CTEs slows queries by more than factor 500 Jian Guo <gjian@vmware.com>
2023-11-08 22:44 ` Re: Wrong rows estimations with joins of CTEs slows queries by more than factor 500 Tom Lane <tgl@sss.pgh.pa.us>
2023-11-16 09:24 ` Re: Wrong rows estimations with joins of CTEs slows queries by more than factor 500 Richard Guo <guofenglinux@gmail.com>
2023-11-16 18:16 ` Re: Wrong rows estimations with joins of CTEs slows queries by more than factor 500 Tom Lane <tgl@sss.pgh.pa.us>
2023-11-17 02:09 ` Re: Wrong rows estimations with joins of CTEs slows queries by more than factor 500 Richard Guo <guofenglinux@gmail.com>
2023-11-17 03:38 ` Re: Wrong rows estimations with joins of CTEs slows queries by more than factor 500 Tom Lane <tgl@sss.pgh.pa.us>
@ 2023-11-17 03:45 ` David G. Johnston <david.g.johnston@gmail.com>
3 siblings, 0 replies; 29+ messages in thread
From: David G. Johnston @ 2023-11-17 03:45 UTC (permalink / raw)
To: Tom Lane <tgl@sss.pgh.pa.us>; +Cc: Richard Guo <guofenglinux@gmail.com>; Jian Guo <gjian@vmware.com>; Tomas Vondra <tomas.vondra@enterprisedb.com>; Hans Buschmann <buschmann@nidsa.net>; pgsql-hackers@lists.postgresql.org <pgsql-hackers@lists.postgresql.org>; Zhenghua Lyu <zlyu@vmware.com>
On Thursday, November 16, 2023, Tom Lane <tgl@sss.pgh.pa.us> wrote:
>
> That line of argument also leads to the conclusion that it'd be
> okay to expose info about the ordering of the CTE result to the
> upper planner. This patch doesn't do that, and I'm not sufficiently
> excited about the issue to go write some code. But if someone else
> does, I think we shouldn't exclude doing it on the grounds of wanting
> to preserve an optimization fence. The fence is sort of one-way
> in this line of thinking: information can propagate up to the outer
> planner level, but not down into the CTE plan.
>
This is indeed my understanding of what materialized means. Basically, the
CTE is done first and in isolation; but any knowledge of its result shape
can be used when referencing it.
David J.
^ permalink raw reply [nested|flat] 29+ messages in thread
* Re: Wrong rows estimations with joins of CTEs slows queries by more than factor 500
2023-02-08 13:55 Wrong rows estimations with joins of CTEs slows queries by more than factor 500 Hans Buschmann <buschmann@nidsa.net>
2023-08-14 11:12 ` Re: Wrong rows estimations with joins of CTEs slows queries by more than factor 500 Jian Guo <gjian@vmware.com>
2023-08-14 12:58 ` Re: Wrong rows estimations with joins of CTEs slows queries by more than factor 500 Tomas Vondra <tomas.vondra@enterprisedb.com>
2023-08-21 08:16 ` Re: Wrong rows estimations with joins of CTEs slows queries by more than factor 500 Jian Guo <gjian@vmware.com>
2023-11-08 22:44 ` Re: Wrong rows estimations with joins of CTEs slows queries by more than factor 500 Tom Lane <tgl@sss.pgh.pa.us>
2023-11-16 09:24 ` Re: Wrong rows estimations with joins of CTEs slows queries by more than factor 500 Richard Guo <guofenglinux@gmail.com>
2023-11-16 18:16 ` Re: Wrong rows estimations with joins of CTEs slows queries by more than factor 500 Tom Lane <tgl@sss.pgh.pa.us>
2023-11-17 02:09 ` Re: Wrong rows estimations with joins of CTEs slows queries by more than factor 500 Richard Guo <guofenglinux@gmail.com>
2023-11-17 03:38 ` Re: Wrong rows estimations with joins of CTEs slows queries by more than factor 500 Tom Lane <tgl@sss.pgh.pa.us>
@ 2023-11-17 03:53 ` Laurenz Albe <laurenz.albe@cybertec.at>
3 siblings, 0 replies; 29+ messages in thread
From: Laurenz Albe @ 2023-11-17 03:53 UTC (permalink / raw)
To: Tom Lane <tgl@sss.pgh.pa.us>; Richard Guo <guofenglinux@gmail.com>; +Cc: Jian Guo <gjian@vmware.com>; Tomas Vondra <tomas.vondra@enterprisedb.com>; Hans Buschmann <buschmann@nidsa.net>; pgsql-hackers@lists.postgresql.org <pgsql-hackers@lists.postgresql.org>; Zhenghua Lyu <zlyu@vmware.com>
On Thu, 2023-11-16 at 22:38 -0500, Tom Lane wrote:
> That line of argument also leads to the conclusion that it'd be
> okay to expose info about the ordering of the CTE result to the
> upper planner. [...] The fence is sort of one-way
> in this line of thinking: information can propagate up to the outer
> planner level, but not down into the CTE plan.
>
> Thoughts?
That agrees with my intuition about MATERIALIZED CTEs.
I think of them as "first calculate the CTE, then calculate the
rest of the query" or an ad-hoc temporary table for the duration
of a query. I would expect the upper planner to know estimates
and other data about the result of the CTE.
Yours,
Laurenz Albe
^ permalink raw reply [nested|flat] 29+ messages in thread
* Re: Wrong rows estimations with joins of CTEs slows queries by more than factor 500
2023-02-08 13:55 Wrong rows estimations with joins of CTEs slows queries by more than factor 500 Hans Buschmann <buschmann@nidsa.net>
2023-08-14 11:12 ` Re: Wrong rows estimations with joins of CTEs slows queries by more than factor 500 Jian Guo <gjian@vmware.com>
2023-08-14 12:58 ` Re: Wrong rows estimations with joins of CTEs slows queries by more than factor 500 Tomas Vondra <tomas.vondra@enterprisedb.com>
2023-08-21 08:16 ` Re: Wrong rows estimations with joins of CTEs slows queries by more than factor 500 Jian Guo <gjian@vmware.com>
2023-11-08 22:44 ` Re: Wrong rows estimations with joins of CTEs slows queries by more than factor 500 Tom Lane <tgl@sss.pgh.pa.us>
2023-11-16 09:24 ` Re: Wrong rows estimations with joins of CTEs slows queries by more than factor 500 Richard Guo <guofenglinux@gmail.com>
2023-11-16 18:16 ` Re: Wrong rows estimations with joins of CTEs slows queries by more than factor 500 Tom Lane <tgl@sss.pgh.pa.us>
2023-11-17 02:09 ` Re: Wrong rows estimations with joins of CTEs slows queries by more than factor 500 Richard Guo <guofenglinux@gmail.com>
2023-11-17 03:38 ` Re: Wrong rows estimations with joins of CTEs slows queries by more than factor 500 Tom Lane <tgl@sss.pgh.pa.us>
@ 2023-11-17 06:41 ` Richard Guo <guofenglinux@gmail.com>
2023-11-17 19:42 ` Re: Wrong rows estimations with joins of CTEs slows queries by more than factor 500 Tom Lane <tgl@sss.pgh.pa.us>
3 siblings, 1 reply; 29+ messages in thread
From: Richard Guo @ 2023-11-17 06:41 UTC (permalink / raw)
To: Tom Lane <tgl@sss.pgh.pa.us>; +Cc: Jian Guo <gjian@vmware.com>; Tomas Vondra <tomas.vondra@enterprisedb.com>; Hans Buschmann <buschmann@nidsa.net>; pgsql-hackers@lists.postgresql.org <pgsql-hackers@lists.postgresql.org>; Zhenghua Lyu <zlyu@vmware.com>
On Fri, Nov 17, 2023 at 11:38 AM Tom Lane <tgl@sss.pgh.pa.us> wrote:
> That line of argument also leads to the conclusion that it'd be
> okay to expose info about the ordering of the CTE result to the
> upper planner. This patch doesn't do that, and I'm not sufficiently
> excited about the issue to go write some code. But if someone else
> does, I think we shouldn't exclude doing it on the grounds of wanting
> to preserve an optimization fence. The fence is sort of one-way
> in this line of thinking: information can propagate up to the outer
> planner level, but not down into the CTE plan.
>
> Thoughts?
Exactly! Thanks for the detailed explanation.
Thanks
Richard
^ permalink raw reply [nested|flat] 29+ messages in thread
* Re: Wrong rows estimations with joins of CTEs slows queries by more than factor 500
2023-02-08 13:55 Wrong rows estimations with joins of CTEs slows queries by more than factor 500 Hans Buschmann <buschmann@nidsa.net>
2023-08-14 11:12 ` Re: Wrong rows estimations with joins of CTEs slows queries by more than factor 500 Jian Guo <gjian@vmware.com>
2023-08-14 12:58 ` Re: Wrong rows estimations with joins of CTEs slows queries by more than factor 500 Tomas Vondra <tomas.vondra@enterprisedb.com>
2023-08-21 08:16 ` Re: Wrong rows estimations with joins of CTEs slows queries by more than factor 500 Jian Guo <gjian@vmware.com>
2023-11-08 22:44 ` Re: Wrong rows estimations with joins of CTEs slows queries by more than factor 500 Tom Lane <tgl@sss.pgh.pa.us>
2023-11-16 09:24 ` Re: Wrong rows estimations with joins of CTEs slows queries by more than factor 500 Richard Guo <guofenglinux@gmail.com>
2023-11-16 18:16 ` Re: Wrong rows estimations with joins of CTEs slows queries by more than factor 500 Tom Lane <tgl@sss.pgh.pa.us>
2023-11-17 02:09 ` Re: Wrong rows estimations with joins of CTEs slows queries by more than factor 500 Richard Guo <guofenglinux@gmail.com>
2023-11-17 03:38 ` Re: Wrong rows estimations with joins of CTEs slows queries by more than factor 500 Tom Lane <tgl@sss.pgh.pa.us>
2023-11-17 06:41 ` Re: Wrong rows estimations with joins of CTEs slows queries by more than factor 500 Richard Guo <guofenglinux@gmail.com>
@ 2023-11-17 19:42 ` Tom Lane <tgl@sss.pgh.pa.us>
2024-01-06 09:00 ` Re: Wrong rows estimations with joins of CTEs slows queries by more than factor 500 Alexander Lakhin <exclusion@gmail.com>
0 siblings, 1 reply; 29+ messages in thread
From: Tom Lane @ 2023-11-17 19:42 UTC (permalink / raw)
To: Richard Guo <guofenglinux@gmail.com>; +Cc: Jian Guo <gjian@vmware.com>; Tomas Vondra <tomas.vondra@enterprisedb.com>; Hans Buschmann <buschmann@nidsa.net>; pgsql-hackers@lists.postgresql.org <pgsql-hackers@lists.postgresql.org>; Zhenghua Lyu <zlyu@vmware.com>
Richard Guo <guofenglinux@gmail.com> writes:
> On Fri, Nov 17, 2023 at 11:38 AM Tom Lane <tgl@sss.pgh.pa.us> wrote:
>> That line of argument also leads to the conclusion that it'd be
>> okay to expose info about the ordering of the CTE result to the
>> upper planner. This patch doesn't do that, and I'm not sufficiently
>> excited about the issue to go write some code. But if someone else
>> does, I think we shouldn't exclude doing it on the grounds of wanting
>> to preserve an optimization fence. The fence is sort of one-way
>> in this line of thinking: information can propagate up to the outer
>> planner level, but not down into the CTE plan.
> Exactly! Thanks for the detailed explanation.
OK. I pushed the patch after a bit more review: we can simplify
things some more by using the subroot->parse querytree for all
tests. After the previous refactoring, it wasn't buying us anything
to do some initial tests with the raw querytree. (The original
idea of that, I believe, was to avoid doing find_base_rel if we
could; but now that's not helpful.)
regards, tom lane
^ permalink raw reply [nested|flat] 29+ messages in thread
* Re: Wrong rows estimations with joins of CTEs slows queries by more than factor 500
2023-02-08 13:55 Wrong rows estimations with joins of CTEs slows queries by more than factor 500 Hans Buschmann <buschmann@nidsa.net>
2023-08-14 11:12 ` Re: Wrong rows estimations with joins of CTEs slows queries by more than factor 500 Jian Guo <gjian@vmware.com>
2023-08-14 12:58 ` Re: Wrong rows estimations with joins of CTEs slows queries by more than factor 500 Tomas Vondra <tomas.vondra@enterprisedb.com>
2023-08-21 08:16 ` Re: Wrong rows estimations with joins of CTEs slows queries by more than factor 500 Jian Guo <gjian@vmware.com>
2023-11-08 22:44 ` Re: Wrong rows estimations with joins of CTEs slows queries by more than factor 500 Tom Lane <tgl@sss.pgh.pa.us>
2023-11-16 09:24 ` Re: Wrong rows estimations with joins of CTEs slows queries by more than factor 500 Richard Guo <guofenglinux@gmail.com>
2023-11-16 18:16 ` Re: Wrong rows estimations with joins of CTEs slows queries by more than factor 500 Tom Lane <tgl@sss.pgh.pa.us>
2023-11-17 02:09 ` Re: Wrong rows estimations with joins of CTEs slows queries by more than factor 500 Richard Guo <guofenglinux@gmail.com>
2023-11-17 03:38 ` Re: Wrong rows estimations with joins of CTEs slows queries by more than factor 500 Tom Lane <tgl@sss.pgh.pa.us>
2023-11-17 06:41 ` Re: Wrong rows estimations with joins of CTEs slows queries by more than factor 500 Richard Guo <guofenglinux@gmail.com>
2023-11-17 19:42 ` Re: Wrong rows estimations with joins of CTEs slows queries by more than factor 500 Tom Lane <tgl@sss.pgh.pa.us>
@ 2024-01-06 09:00 ` Alexander Lakhin <exclusion@gmail.com>
2024-01-06 22:41 ` Re: Wrong rows estimations with joins of CTEs slows queries by more than factor 500 Tom Lane <tgl@sss.pgh.pa.us>
0 siblings, 1 reply; 29+ messages in thread
From: Alexander Lakhin @ 2024-01-06 09:00 UTC (permalink / raw)
To: Tom Lane <tgl@sss.pgh.pa.us>; Richard Guo <guofenglinux@gmail.com>; +Cc: Jian Guo <gjian@vmware.com>; Tomas Vondra <tomas.vondra@enterprisedb.com>; Hans Buschmann <buschmann@nidsa.net>; pgsql-hackers@lists.postgresql.org <pgsql-hackers@lists.postgresql.org>; Zhenghua Lyu <zlyu@vmware.com>
Hello Tom and Richard,
17.11.2023 22:42, Tom Lane wrote:
> OK. I pushed the patch after a bit more review: we can simplify
> things some more by using the subroot->parse querytree for all
> tests. After the previous refactoring, it wasn't buying us anything
> to do some initial tests with the raw querytree. (The original
> idea of that, I believe, was to avoid doing find_base_rel if we
> could; but now that's not helpful.)
Please look at the following query:
CREATE TABLE t(i int);
INSERT INTO t VALUES (1);
VACUUM ANALYZE t;
WITH ir AS (INSERT INTO t VALUES (2) RETURNING i)
SELECT * FROM ir WHERE i = 2;
which produces ERROR: no relation entry for relid 1
starting from f7816aec2.
Best regards,
Alexander
^ permalink raw reply [nested|flat] 29+ messages in thread
* Re: Wrong rows estimations with joins of CTEs slows queries by more than factor 500
2023-02-08 13:55 Wrong rows estimations with joins of CTEs slows queries by more than factor 500 Hans Buschmann <buschmann@nidsa.net>
2023-08-14 11:12 ` Re: Wrong rows estimations with joins of CTEs slows queries by more than factor 500 Jian Guo <gjian@vmware.com>
2023-08-14 12:58 ` Re: Wrong rows estimations with joins of CTEs slows queries by more than factor 500 Tomas Vondra <tomas.vondra@enterprisedb.com>
2023-08-21 08:16 ` Re: Wrong rows estimations with joins of CTEs slows queries by more than factor 500 Jian Guo <gjian@vmware.com>
2023-11-08 22:44 ` Re: Wrong rows estimations with joins of CTEs slows queries by more than factor 500 Tom Lane <tgl@sss.pgh.pa.us>
2023-11-16 09:24 ` Re: Wrong rows estimations with joins of CTEs slows queries by more than factor 500 Richard Guo <guofenglinux@gmail.com>
2023-11-16 18:16 ` Re: Wrong rows estimations with joins of CTEs slows queries by more than factor 500 Tom Lane <tgl@sss.pgh.pa.us>
2023-11-17 02:09 ` Re: Wrong rows estimations with joins of CTEs slows queries by more than factor 500 Richard Guo <guofenglinux@gmail.com>
2023-11-17 03:38 ` Re: Wrong rows estimations with joins of CTEs slows queries by more than factor 500 Tom Lane <tgl@sss.pgh.pa.us>
2023-11-17 06:41 ` Re: Wrong rows estimations with joins of CTEs slows queries by more than factor 500 Richard Guo <guofenglinux@gmail.com>
2023-11-17 19:42 ` Re: Wrong rows estimations with joins of CTEs slows queries by more than factor 500 Tom Lane <tgl@sss.pgh.pa.us>
2024-01-06 09:00 ` Re: Wrong rows estimations with joins of CTEs slows queries by more than factor 500 Alexander Lakhin <exclusion@gmail.com>
@ 2024-01-06 22:41 ` Tom Lane <tgl@sss.pgh.pa.us>
2024-01-08 11:14 ` Re: Wrong rows estimations with joins of CTEs slows queries by more than factor 500 Richard Guo <guofenglinux@gmail.com>
0 siblings, 1 reply; 29+ messages in thread
From: Tom Lane @ 2024-01-06 22:41 UTC (permalink / raw)
To: Alexander Lakhin <exclusion@gmail.com>; +Cc: Richard Guo <guofenglinux@gmail.com>; Jian Guo <gjian@vmware.com>; Tomas Vondra <tomas.vondra@enterprisedb.com>; Hans Buschmann <buschmann@nidsa.net>; pgsql-hackers@lists.postgresql.org <pgsql-hackers@lists.postgresql.org>; Zhenghua Lyu <zlyu@vmware.com>
Alexander Lakhin <exclusion@gmail.com> writes:
> Please look at the following query:
> CREATE TABLE t(i int);
> INSERT INTO t VALUES (1);
> VACUUM ANALYZE t;
> WITH ir AS (INSERT INTO t VALUES (2) RETURNING i)
> SELECT * FROM ir WHERE i = 2;
> which produces ERROR: no relation entry for relid 1
> starting from f7816aec2.
Thanks for the report! I guess we need something like the attached.
I'm surprised that this hasn't been noticed before; was the case
really unreachable before?
regards, tom lane
Attachments:
fix-examine-variable-for-INSERT-RETURNING.patch (text/x-diff, 4.7K) download
diff --git a/src/backend/optimizer/util/relnode.c b/src/backend/optimizer/util/relnode.c
index 5a9ce44532..22d01cef5b 100644
--- a/src/backend/optimizer/util/relnode.c
+++ b/src/backend/optimizer/util/relnode.c
@@ -420,6 +420,19 @@ find_base_rel(PlannerInfo *root, int relid)
return NULL; /* keep compiler quiet */
}
+/*
+ * find_base_rel_noerr
+ * Find a base or otherrel relation entry, returning NULL if there's none
+ */
+RelOptInfo *
+find_base_rel_noerr(PlannerInfo *root, int relid)
+{
+ /* use an unsigned comparison to prevent negative array element access */
+ if ((uint32) relid < (uint32) root->simple_rel_array_size)
+ return root->simple_rel_array[relid];
+ return NULL;
+}
+
/*
* find_base_rel_ignore_join
* Find a base or otherrel relation entry, which must already exist.
diff --git a/src/backend/utils/adt/selfuncs.c b/src/backend/utils/adt/selfuncs.c
index dbcd98d985..cea777e9d4 100644
--- a/src/backend/utils/adt/selfuncs.c
+++ b/src/backend/utils/adt/selfuncs.c
@@ -119,6 +119,7 @@
#include "optimizer/paths.h"
#include "optimizer/plancat.h"
#include "parser/parse_clause.h"
+#include "parser/parse_relation.h"
#include "parser/parsetree.h"
#include "statistics/statistics.h"
#include "storage/bufmgr.h"
@@ -5434,17 +5435,30 @@ examine_simple_variable(PlannerInfo *root, Var *var,
if (HeapTupleIsValid(vardata->statsTuple))
{
- RelOptInfo *onerel = find_base_rel(root, var->varno);
+ RelOptInfo *onerel = find_base_rel_noerr(root, var->varno);
Oid userid;
/*
* Check if user has permission to read this column. We require
* all rows to be accessible, so there must be no securityQuals
- * from security barrier views or RLS policies. Use
- * onerel->userid if it's set, in case we're accessing the table
- * via a view.
+ * from security barrier views or RLS policies.
+ *
+ * Normally the Var will have an associated RelOptInfo from which
+ * we can find out which userid to do the check as; but it might
+ * not if it's a RETURNING Var for an INSERT target relation. In
+ * that case use the RTEPermissionInfo associated with the RTE.
*/
- userid = OidIsValid(onerel->userid) ? onerel->userid : GetUserId();
+ if (onerel)
+ userid = onerel->userid;
+ else
+ {
+ RTEPermissionInfo *perminfo;
+
+ perminfo = getRTEPermissionInfo(root->parse->rteperminfos, rte);
+ userid = perminfo->checkAsUser;
+ }
+ if (!OidIsValid(userid))
+ userid = GetUserId();
vardata->acl_ok =
rte->securityQuals == NIL &&
diff --git a/src/include/optimizer/pathnode.h b/src/include/optimizer/pathnode.h
index 03a0e46e70..c43d97b48a 100644
--- a/src/include/optimizer/pathnode.h
+++ b/src/include/optimizer/pathnode.h
@@ -307,6 +307,7 @@ extern void expand_planner_arrays(PlannerInfo *root, int add_size);
extern RelOptInfo *build_simple_rel(PlannerInfo *root, int relid,
RelOptInfo *parent);
extern RelOptInfo *find_base_rel(PlannerInfo *root, int relid);
+extern RelOptInfo *find_base_rel_noerr(PlannerInfo *root, int relid);
extern RelOptInfo *find_base_rel_ignore_join(PlannerInfo *root, int relid);
extern RelOptInfo *find_join_rel(PlannerInfo *root, Relids relids);
extern RelOptInfo *build_join_rel(PlannerInfo *root,
diff --git a/src/test/regress/expected/with.out b/src/test/regress/expected/with.out
index 69c56ce207..3cf969d938 100644
--- a/src/test/regress/expected/with.out
+++ b/src/test/regress/expected/with.out
@@ -654,6 +654,24 @@ select count(*) from tenk1 a
-> CTE Scan on x
(8 rows)
+explain (costs off)
+with x as materialized (insert into tenk1 default values returning unique1)
+select count(*) from tenk1 a
+ where unique1 in (select * from x);
+ QUERY PLAN
+------------------------------------------------------------
+ Aggregate
+ CTE x
+ -> Insert on tenk1
+ -> Result
+ -> Nested Loop
+ -> HashAggregate
+ Group Key: x.unique1
+ -> CTE Scan on x
+ -> Index Only Scan using tenk1_unique1 on tenk1 a
+ Index Cond: (unique1 = x.unique1)
+(10 rows)
+
-- SEARCH clause
create temp table graph0( f int, t int, label text );
insert into graph0 values
diff --git a/src/test/regress/sql/with.sql b/src/test/regress/sql/with.sql
index 3ef9898866..ff68e21e2e 100644
--- a/src/test/regress/sql/with.sql
+++ b/src/test/regress/sql/with.sql
@@ -354,6 +354,11 @@ with x as materialized (select unique1 from tenk1 b)
select count(*) from tenk1 a
where unique1 in (select * from x);
+explain (costs off)
+with x as materialized (insert into tenk1 default values returning unique1)
+select count(*) from tenk1 a
+ where unique1 in (select * from x);
+
-- SEARCH clause
create temp table graph0( f int, t int, label text );
* Re: Wrong rows estimations with joins of CTEs slows queries by more than factor 500
2023-02-08 13:55 Wrong rows estimations with joins of CTEs slows queries by more than factor 500 Hans Buschmann <buschmann@nidsa.net>
2023-08-14 11:12 ` Re: Wrong rows estimations with joins of CTEs slows queries by more than factor 500 Jian Guo <gjian@vmware.com>
2023-08-14 12:58 ` Re: Wrong rows estimations with joins of CTEs slows queries by more than factor 500 Tomas Vondra <tomas.vondra@enterprisedb.com>
2023-08-21 08:16 ` Re: Wrong rows estimations with joins of CTEs slows queries by more than factor 500 Jian Guo <gjian@vmware.com>
2023-11-08 22:44 ` Re: Wrong rows estimations with joins of CTEs slows queries by more than factor 500 Tom Lane <tgl@sss.pgh.pa.us>
2023-11-16 09:24 ` Re: Wrong rows estimations with joins of CTEs slows queries by more than factor 500 Richard Guo <guofenglinux@gmail.com>
2023-11-16 18:16 ` Re: Wrong rows estimations with joins of CTEs slows queries by more than factor 500 Tom Lane <tgl@sss.pgh.pa.us>
2023-11-17 02:09 ` Re: Wrong rows estimations with joins of CTEs slows queries by more than factor 500 Richard Guo <guofenglinux@gmail.com>
2023-11-17 03:38 ` Re: Wrong rows estimations with joins of CTEs slows queries by more than factor 500 Tom Lane <tgl@sss.pgh.pa.us>
2023-11-17 06:41 ` Re: Wrong rows estimations with joins of CTEs slows queries by more than factor 500 Richard Guo <guofenglinux@gmail.com>
2023-11-17 19:42 ` Re: Wrong rows estimations with joins of CTEs slows queries by more than factor 500 Tom Lane <tgl@sss.pgh.pa.us>
2024-01-06 09:00 ` Re: Wrong rows estimations with joins of CTEs slows queries by more than factor 500 Alexander Lakhin <exclusion@gmail.com>
2024-01-06 22:41 ` Re: Wrong rows estimations with joins of CTEs slows queries by more than factor 500 Tom Lane <tgl@sss.pgh.pa.us>
@ 2024-01-08 11:14 ` Richard Guo <guofenglinux@gmail.com>
2024-01-08 16:51 ` Re: Wrong rows estimations with joins of CTEs slows queries by more than factor 500 Tom Lane <tgl@sss.pgh.pa.us>
0 siblings, 1 reply; 29+ messages in thread
From: Richard Guo @ 2024-01-08 11:14 UTC (permalink / raw)
To: Tom Lane <tgl@sss.pgh.pa.us>; +Cc: Alexander Lakhin <exclusion@gmail.com>; Jian Guo <gjian@vmware.com>; Tomas Vondra <tomas.vondra@enterprisedb.com>; Hans Buschmann <buschmann@nidsa.net>; pgsql-hackers@lists.postgresql.org <pgsql-hackers@lists.postgresql.org>; Zhenghua Lyu <zlyu@vmware.com>
On Sun, Jan 7, 2024 at 6:41 AM Tom Lane <tgl@sss.pgh.pa.us> wrote:
> Alexander Lakhin <exclusion@gmail.com> writes:
> > Please look at the following query:
> > CREATE TABLE t(i int);
> > INSERT INTO t VALUES (1);
> > VACUUM ANALYZE t;
>
> > WITH ir AS (INSERT INTO t VALUES (2) RETURNING i)
> > SELECT * FROM ir WHERE i = 2;
>
> > which produces ERROR: no relation entry for relid 1
> > starting from f7816aec2.
Nice catch.
> Thanks for the report! I guess we need something like the attached.
+1.
> I'm surprised that this hasn't been noticed before; was the case
> really unreachable before?
It seems that this case is only reachable with Vars of an INSERT target
relation, and it seems that there is no other way to reference such a
Var other than using CTE.
Thanks
Richard
^ permalink raw reply [nested|flat] 29+ messages in thread
* Re: Wrong rows estimations with joins of CTEs slows queries by more than factor 500
2023-02-08 13:55 Wrong rows estimations with joins of CTEs slows queries by more than factor 500 Hans Buschmann <buschmann@nidsa.net>
2023-08-14 11:12 ` Re: Wrong rows estimations with joins of CTEs slows queries by more than factor 500 Jian Guo <gjian@vmware.com>
2023-08-14 12:58 ` Re: Wrong rows estimations with joins of CTEs slows queries by more than factor 500 Tomas Vondra <tomas.vondra@enterprisedb.com>
2023-08-21 08:16 ` Re: Wrong rows estimations with joins of CTEs slows queries by more than factor 500 Jian Guo <gjian@vmware.com>
2023-11-08 22:44 ` Re: Wrong rows estimations with joins of CTEs slows queries by more than factor 500 Tom Lane <tgl@sss.pgh.pa.us>
2023-11-16 09:24 ` Re: Wrong rows estimations with joins of CTEs slows queries by more than factor 500 Richard Guo <guofenglinux@gmail.com>
2023-11-16 18:16 ` Re: Wrong rows estimations with joins of CTEs slows queries by more than factor 500 Tom Lane <tgl@sss.pgh.pa.us>
2023-11-17 02:09 ` Re: Wrong rows estimations with joins of CTEs slows queries by more than factor 500 Richard Guo <guofenglinux@gmail.com>
2023-11-17 03:38 ` Re: Wrong rows estimations with joins of CTEs slows queries by more than factor 500 Tom Lane <tgl@sss.pgh.pa.us>
2023-11-17 06:41 ` Re: Wrong rows estimations with joins of CTEs slows queries by more than factor 500 Richard Guo <guofenglinux@gmail.com>
2023-11-17 19:42 ` Re: Wrong rows estimations with joins of CTEs slows queries by more than factor 500 Tom Lane <tgl@sss.pgh.pa.us>
2024-01-06 09:00 ` Re: Wrong rows estimations with joins of CTEs slows queries by more than factor 500 Alexander Lakhin <exclusion@gmail.com>
2024-01-06 22:41 ` Re: Wrong rows estimations with joins of CTEs slows queries by more than factor 500 Tom Lane <tgl@sss.pgh.pa.us>
2024-01-08 11:14 ` Re: Wrong rows estimations with joins of CTEs slows queries by more than factor 500 Richard Guo <guofenglinux@gmail.com>
@ 2024-01-08 16:51 ` Tom Lane <tgl@sss.pgh.pa.us>
2024-01-27 02:07 ` Re: Wrong rows estimations with joins of CTEs slows queries by more than factor 500 vignesh C <vignesh21@gmail.com>
0 siblings, 1 reply; 29+ messages in thread
From: Tom Lane @ 2024-01-08 16:51 UTC (permalink / raw)
To: Richard Guo <guofenglinux@gmail.com>; +Cc: Alexander Lakhin <exclusion@gmail.com>; Jian Guo <gjian@vmware.com>; Tomas Vondra <tomas.vondra@enterprisedb.com>; Hans Buschmann <buschmann@nidsa.net>; pgsql-hackers@lists.postgresql.org <pgsql-hackers@lists.postgresql.org>; Zhenghua Lyu <zlyu@vmware.com>
Richard Guo <guofenglinux@gmail.com> writes:
> On Sun, Jan 7, 2024 at 6:41 AM Tom Lane <tgl@sss.pgh.pa.us> wrote:
>> Thanks for the report! I guess we need something like the attached.
> +1.
Pushed, thanks for looking at it.
>> I'm surprised that this hasn't been noticed before; was the case
>> really unreachable before?
> It seems that this case is only reachable with Vars of an INSERT target
> relation, and it seems that there is no other way to reference such a
> Var other than using CTE.
I'm a little uncomfortable with that conclusion, but for the moment
I refrained from back-patching. We can always add the patch to v16
later if we find it's not so unreachable. (Before v16, there was
no find_base_rel here at all.)
regards, tom lane
^ permalink raw reply [nested|flat] 29+ messages in thread
* Re: Wrong rows estimations with joins of CTEs slows queries by more than factor 500
2023-02-08 13:55 Wrong rows estimations with joins of CTEs slows queries by more than factor 500 Hans Buschmann <buschmann@nidsa.net>
2023-08-14 11:12 ` Re: Wrong rows estimations with joins of CTEs slows queries by more than factor 500 Jian Guo <gjian@vmware.com>
2023-08-14 12:58 ` Re: Wrong rows estimations with joins of CTEs slows queries by more than factor 500 Tomas Vondra <tomas.vondra@enterprisedb.com>
2023-08-21 08:16 ` Re: Wrong rows estimations with joins of CTEs slows queries by more than factor 500 Jian Guo <gjian@vmware.com>
2023-11-08 22:44 ` Re: Wrong rows estimations with joins of CTEs slows queries by more than factor 500 Tom Lane <tgl@sss.pgh.pa.us>
2023-11-16 09:24 ` Re: Wrong rows estimations with joins of CTEs slows queries by more than factor 500 Richard Guo <guofenglinux@gmail.com>
2023-11-16 18:16 ` Re: Wrong rows estimations with joins of CTEs slows queries by more than factor 500 Tom Lane <tgl@sss.pgh.pa.us>
2023-11-17 02:09 ` Re: Wrong rows estimations with joins of CTEs slows queries by more than factor 500 Richard Guo <guofenglinux@gmail.com>
2023-11-17 03:38 ` Re: Wrong rows estimations with joins of CTEs slows queries by more than factor 500 Tom Lane <tgl@sss.pgh.pa.us>
2023-11-17 06:41 ` Re: Wrong rows estimations with joins of CTEs slows queries by more than factor 500 Richard Guo <guofenglinux@gmail.com>
2023-11-17 19:42 ` Re: Wrong rows estimations with joins of CTEs slows queries by more than factor 500 Tom Lane <tgl@sss.pgh.pa.us>
2024-01-06 09:00 ` Re: Wrong rows estimations with joins of CTEs slows queries by more than factor 500 Alexander Lakhin <exclusion@gmail.com>
2024-01-06 22:41 ` Re: Wrong rows estimations with joins of CTEs slows queries by more than factor 500 Tom Lane <tgl@sss.pgh.pa.us>
2024-01-08 11:14 ` Re: Wrong rows estimations with joins of CTEs slows queries by more than factor 500 Richard Guo <guofenglinux@gmail.com>
2024-01-08 16:51 ` Re: Wrong rows estimations with joins of CTEs slows queries by more than factor 500 Tom Lane <tgl@sss.pgh.pa.us>
@ 2024-01-27 02:07 ` vignesh C <vignesh21@gmail.com>
2024-01-29 02:30 ` Re: Wrong rows estimations with joins of CTEs slows queries by more than factor 500 Richard Guo <guofenglinux@gmail.com>
0 siblings, 1 reply; 29+ messages in thread
From: vignesh C @ 2024-01-27 02:07 UTC (permalink / raw)
To: Tom Lane <tgl@sss.pgh.pa.us>; +Cc: Richard Guo <guofenglinux@gmail.com>; Alexander Lakhin <exclusion@gmail.com>; Jian Guo <gjian@vmware.com>; Tomas Vondra <tomas.vondra@enterprisedb.com>; Hans Buschmann <buschmann@nidsa.net>; pgsql-hackers@lists.postgresql.org <pgsql-hackers@lists.postgresql.org>; Zhenghua Lyu <zlyu@vmware.com>
On Mon, 8 Jan 2024 at 22:21, Tom Lane <tgl@sss.pgh.pa.us> wrote:
>
> Richard Guo <guofenglinux@gmail.com> writes:
> > On Sun, Jan 7, 2024 at 6:41 AM Tom Lane <tgl@sss.pgh.pa.us> wrote:
> >> Thanks for the report! I guess we need something like the attached.
>
> > +1.
>
> Pushed, thanks for looking at it.
I have changed the status of the commitfest entry to "Committed" as I
noticed the patch has already been committed.
Regards,
Vignesh
^ permalink raw reply [nested|flat] 29+ messages in thread
* Re: Wrong rows estimations with joins of CTEs slows queries by more than factor 500
2023-02-08 13:55 Wrong rows estimations with joins of CTEs slows queries by more than factor 500 Hans Buschmann <buschmann@nidsa.net>
2023-08-14 11:12 ` Re: Wrong rows estimations with joins of CTEs slows queries by more than factor 500 Jian Guo <gjian@vmware.com>
2023-08-14 12:58 ` Re: Wrong rows estimations with joins of CTEs slows queries by more than factor 500 Tomas Vondra <tomas.vondra@enterprisedb.com>
2023-08-21 08:16 ` Re: Wrong rows estimations with joins of CTEs slows queries by more than factor 500 Jian Guo <gjian@vmware.com>
2023-11-08 22:44 ` Re: Wrong rows estimations with joins of CTEs slows queries by more than factor 500 Tom Lane <tgl@sss.pgh.pa.us>
2023-11-16 09:24 ` Re: Wrong rows estimations with joins of CTEs slows queries by more than factor 500 Richard Guo <guofenglinux@gmail.com>
2023-11-16 18:16 ` Re: Wrong rows estimations with joins of CTEs slows queries by more than factor 500 Tom Lane <tgl@sss.pgh.pa.us>
2023-11-17 02:09 ` Re: Wrong rows estimations with joins of CTEs slows queries by more than factor 500 Richard Guo <guofenglinux@gmail.com>
2023-11-17 03:38 ` Re: Wrong rows estimations with joins of CTEs slows queries by more than factor 500 Tom Lane <tgl@sss.pgh.pa.us>
2023-11-17 06:41 ` Re: Wrong rows estimations with joins of CTEs slows queries by more than factor 500 Richard Guo <guofenglinux@gmail.com>
2023-11-17 19:42 ` Re: Wrong rows estimations with joins of CTEs slows queries by more than factor 500 Tom Lane <tgl@sss.pgh.pa.us>
2024-01-06 09:00 ` Re: Wrong rows estimations with joins of CTEs slows queries by more than factor 500 Alexander Lakhin <exclusion@gmail.com>
2024-01-06 22:41 ` Re: Wrong rows estimations with joins of CTEs slows queries by more than factor 500 Tom Lane <tgl@sss.pgh.pa.us>
2024-01-08 11:14 ` Re: Wrong rows estimations with joins of CTEs slows queries by more than factor 500 Richard Guo <guofenglinux@gmail.com>
2024-01-08 16:51 ` Re: Wrong rows estimations with joins of CTEs slows queries by more than factor 500 Tom Lane <tgl@sss.pgh.pa.us>
2024-01-27 02:07 ` Re: Wrong rows estimations with joins of CTEs slows queries by more than factor 500 vignesh C <vignesh21@gmail.com>
@ 2024-01-29 02:30 ` Richard Guo <guofenglinux@gmail.com>
2024-01-29 03:19 ` Re: Wrong rows estimations with joins of CTEs slows queries by more than factor 500 vignesh C <vignesh21@gmail.com>
0 siblings, 1 reply; 29+ messages in thread
From: Richard Guo @ 2024-01-29 02:30 UTC (permalink / raw)
To: vignesh C <vignesh21@gmail.com>; +Cc: Tom Lane <tgl@sss.pgh.pa.us>; Alexander Lakhin <exclusion@gmail.com>; Jian Guo <gjian@vmware.com>; Tomas Vondra <tomas.vondra@enterprisedb.com>; Hans Buschmann <buschmann@nidsa.net>; pgsql-hackers@lists.postgresql.org <pgsql-hackers@lists.postgresql.org>; Zhenghua Lyu <zlyu@vmware.com>
On Sat, Jan 27, 2024 at 10:08 AM vignesh C <vignesh21@gmail.com> wrote:
> On Mon, 8 Jan 2024 at 22:21, Tom Lane <tgl@sss.pgh.pa.us> wrote:
> >
> > Richard Guo <guofenglinux@gmail.com> writes:
> > > On Sun, Jan 7, 2024 at 6:41 AM Tom Lane <tgl@sss.pgh.pa.us> wrote:
> > >> Thanks for the report! I guess we need something like the attached.
> >
> > > +1.
> >
> > Pushed, thanks for looking at it.
>
> I have changed the status of the commitfest entry to "Committed" as I
> noticed the patch has already been committed.
Well, the situation seems a little complex here. At first, this thread
was dedicated to discussing the 'Examine-simple-variable-for-Var-in-CTE'
patch, which has already been pushed in [1]. Subsequently, I proposed
another patch 'Propagate-pathkeys-from-CTEs-up-to-the-outer-query' in
[2], which is currently under review and is what the commitfest entry
for. Later on, within the same thread, another patch was posted as a
fix to the first patch and was subsequently pushed in [3]. I believe
this sequence of events might have led to confusion.
What is the usual practice in such situations? I guess I'd better to
fork a new thread to discuss my proposed patch which is about the
'Propagate-pathkeys-from-CTEs-up-to-the-outer-query'.
[1] https://www.postgresql.org/message-id/754093.1700250120%40sss.pgh.pa.us
[2]
https://www.postgresql.org/message-id/CAMbWs49gAHeEOn0rpdUUYXryaa60KZ8JKwk1aSERttY9caCYkA%40mail.gma...
[3] https://www.postgresql.org/message-id/1941515.1704732682%40sss.pgh.pa.us
Thanks
Richard
^ permalink raw reply [nested|flat] 29+ messages in thread
* Re: Wrong rows estimations with joins of CTEs slows queries by more than factor 500
2023-02-08 13:55 Wrong rows estimations with joins of CTEs slows queries by more than factor 500 Hans Buschmann <buschmann@nidsa.net>
2023-08-14 11:12 ` Re: Wrong rows estimations with joins of CTEs slows queries by more than factor 500 Jian Guo <gjian@vmware.com>
2023-08-14 12:58 ` Re: Wrong rows estimations with joins of CTEs slows queries by more than factor 500 Tomas Vondra <tomas.vondra@enterprisedb.com>
2023-08-21 08:16 ` Re: Wrong rows estimations with joins of CTEs slows queries by more than factor 500 Jian Guo <gjian@vmware.com>
2023-11-08 22:44 ` Re: Wrong rows estimations with joins of CTEs slows queries by more than factor 500 Tom Lane <tgl@sss.pgh.pa.us>
2023-11-16 09:24 ` Re: Wrong rows estimations with joins of CTEs slows queries by more than factor 500 Richard Guo <guofenglinux@gmail.com>
2023-11-16 18:16 ` Re: Wrong rows estimations with joins of CTEs slows queries by more than factor 500 Tom Lane <tgl@sss.pgh.pa.us>
2023-11-17 02:09 ` Re: Wrong rows estimations with joins of CTEs slows queries by more than factor 500 Richard Guo <guofenglinux@gmail.com>
2023-11-17 03:38 ` Re: Wrong rows estimations with joins of CTEs slows queries by more than factor 500 Tom Lane <tgl@sss.pgh.pa.us>
2023-11-17 06:41 ` Re: Wrong rows estimations with joins of CTEs slows queries by more than factor 500 Richard Guo <guofenglinux@gmail.com>
2023-11-17 19:42 ` Re: Wrong rows estimations with joins of CTEs slows queries by more than factor 500 Tom Lane <tgl@sss.pgh.pa.us>
2024-01-06 09:00 ` Re: Wrong rows estimations with joins of CTEs slows queries by more than factor 500 Alexander Lakhin <exclusion@gmail.com>
2024-01-06 22:41 ` Re: Wrong rows estimations with joins of CTEs slows queries by more than factor 500 Tom Lane <tgl@sss.pgh.pa.us>
2024-01-08 11:14 ` Re: Wrong rows estimations with joins of CTEs slows queries by more than factor 500 Richard Guo <guofenglinux@gmail.com>
2024-01-08 16:51 ` Re: Wrong rows estimations with joins of CTEs slows queries by more than factor 500 Tom Lane <tgl@sss.pgh.pa.us>
2024-01-27 02:07 ` Re: Wrong rows estimations with joins of CTEs slows queries by more than factor 500 vignesh C <vignesh21@gmail.com>
2024-01-29 02:30 ` Re: Wrong rows estimations with joins of CTEs slows queries by more than factor 500 Richard Guo <guofenglinux@gmail.com>
@ 2024-01-29 03:19 ` vignesh C <vignesh21@gmail.com>
2024-01-29 03:40 ` Re: Wrong rows estimations with joins of CTEs slows queries by more than factor 500 Richard Guo <guofenglinux@gmail.com>
0 siblings, 1 reply; 29+ messages in thread
From: vignesh C @ 2024-01-29 03:19 UTC (permalink / raw)
To: Richard Guo <guofenglinux@gmail.com>; +Cc: Tom Lane <tgl@sss.pgh.pa.us>; Alexander Lakhin <exclusion@gmail.com>; Jian Guo <gjian@vmware.com>; Tomas Vondra <tomas.vondra@enterprisedb.com>; Hans Buschmann <buschmann@nidsa.net>; pgsql-hackers@lists.postgresql.org <pgsql-hackers@lists.postgresql.org>; Zhenghua Lyu <zlyu@vmware.com>
On Mon, 29 Jan 2024 at 08:01, Richard Guo <guofenglinux@gmail.com> wrote:
>
>
> On Sat, Jan 27, 2024 at 10:08 AM vignesh C <vignesh21@gmail.com> wrote:
>>
>> On Mon, 8 Jan 2024 at 22:21, Tom Lane <tgl@sss.pgh.pa.us> wrote:
>> >
>> > Richard Guo <guofenglinux@gmail.com> writes:
>> > > On Sun, Jan 7, 2024 at 6:41 AM Tom Lane <tgl@sss.pgh.pa.us> wrote:
>> > >> Thanks for the report! I guess we need something like the attached.
>> >
>> > > +1.
>> >
>> > Pushed, thanks for looking at it.
>>
>> I have changed the status of the commitfest entry to "Committed" as I
>> noticed the patch has already been committed.
>
>
> Well, the situation seems a little complex here. At first, this thread
> was dedicated to discussing the 'Examine-simple-variable-for-Var-in-CTE'
> patch, which has already been pushed in [1]. Subsequently, I proposed
> another patch 'Propagate-pathkeys-from-CTEs-up-to-the-outer-query' in
> [2], which is currently under review and is what the commitfest entry
> for. Later on, within the same thread, another patch was posted as a
> fix to the first patch and was subsequently pushed in [3]. I believe
> this sequence of events might have led to confusion.
>
> What is the usual practice in such situations? I guess I'd better to
> fork a new thread to discuss my proposed patch which is about the
> 'Propagate-pathkeys-from-CTEs-up-to-the-outer-query'.
Sorry I missed to notice that there was one pending patch yet to be
committed, I feel you can continue discussing here itself just to
avoid losing any historical information about the issue and the
continuation of the discussion. You can add a new commitfest entry for
this.
Regards,
Vignesh
^ permalink raw reply [nested|flat] 29+ messages in thread
* Re: Wrong rows estimations with joins of CTEs slows queries by more than factor 500
2023-02-08 13:55 Wrong rows estimations with joins of CTEs slows queries by more than factor 500 Hans Buschmann <buschmann@nidsa.net>
2023-08-14 11:12 ` Re: Wrong rows estimations with joins of CTEs slows queries by more than factor 500 Jian Guo <gjian@vmware.com>
2023-08-14 12:58 ` Re: Wrong rows estimations with joins of CTEs slows queries by more than factor 500 Tomas Vondra <tomas.vondra@enterprisedb.com>
2023-08-21 08:16 ` Re: Wrong rows estimations with joins of CTEs slows queries by more than factor 500 Jian Guo <gjian@vmware.com>
2023-11-08 22:44 ` Re: Wrong rows estimations with joins of CTEs slows queries by more than factor 500 Tom Lane <tgl@sss.pgh.pa.us>
2023-11-16 09:24 ` Re: Wrong rows estimations with joins of CTEs slows queries by more than factor 500 Richard Guo <guofenglinux@gmail.com>
2023-11-16 18:16 ` Re: Wrong rows estimations with joins of CTEs slows queries by more than factor 500 Tom Lane <tgl@sss.pgh.pa.us>
2023-11-17 02:09 ` Re: Wrong rows estimations with joins of CTEs slows queries by more than factor 500 Richard Guo <guofenglinux@gmail.com>
2023-11-17 03:38 ` Re: Wrong rows estimations with joins of CTEs slows queries by more than factor 500 Tom Lane <tgl@sss.pgh.pa.us>
2023-11-17 06:41 ` Re: Wrong rows estimations with joins of CTEs slows queries by more than factor 500 Richard Guo <guofenglinux@gmail.com>
2023-11-17 19:42 ` Re: Wrong rows estimations with joins of CTEs slows queries by more than factor 500 Tom Lane <tgl@sss.pgh.pa.us>
2024-01-06 09:00 ` Re: Wrong rows estimations with joins of CTEs slows queries by more than factor 500 Alexander Lakhin <exclusion@gmail.com>
2024-01-06 22:41 ` Re: Wrong rows estimations with joins of CTEs slows queries by more than factor 500 Tom Lane <tgl@sss.pgh.pa.us>
2024-01-08 11:14 ` Re: Wrong rows estimations with joins of CTEs slows queries by more than factor 500 Richard Guo <guofenglinux@gmail.com>
2024-01-08 16:51 ` Re: Wrong rows estimations with joins of CTEs slows queries by more than factor 500 Tom Lane <tgl@sss.pgh.pa.us>
2024-01-27 02:07 ` Re: Wrong rows estimations with joins of CTEs slows queries by more than factor 500 vignesh C <vignesh21@gmail.com>
2024-01-29 02:30 ` Re: Wrong rows estimations with joins of CTEs slows queries by more than factor 500 Richard Guo <guofenglinux@gmail.com>
2024-01-29 03:19 ` Re: Wrong rows estimations with joins of CTEs slows queries by more than factor 500 vignesh C <vignesh21@gmail.com>
@ 2024-01-29 03:40 ` Richard Guo <guofenglinux@gmail.com>
0 siblings, 0 replies; 29+ messages in thread
From: Richard Guo @ 2024-01-29 03:40 UTC (permalink / raw)
To: vignesh C <vignesh21@gmail.com>; +Cc: Tom Lane <tgl@sss.pgh.pa.us>; Alexander Lakhin <exclusion@gmail.com>; Jian Guo <gjian@vmware.com>; Tomas Vondra <tomas.vondra@enterprisedb.com>; Hans Buschmann <buschmann@nidsa.net>; pgsql-hackers@lists.postgresql.org <pgsql-hackers@lists.postgresql.org>; Zhenghua Lyu <zlyu@vmware.com>
On Mon, Jan 29, 2024 at 11:20 AM vignesh C <vignesh21@gmail.com> wrote:
> On Mon, 29 Jan 2024 at 08:01, Richard Guo <guofenglinux@gmail.com> wrote:
> > On Sat, Jan 27, 2024 at 10:08 AM vignesh C <vignesh21@gmail.com> wrote:
> >> I have changed the status of the commitfest entry to "Committed" as I
> >> noticed the patch has already been committed.
> >
> > Well, the situation seems a little complex here. At first, this thread
> > was dedicated to discussing the 'Examine-simple-variable-for-Var-in-CTE'
> > patch, which has already been pushed in [1]. Subsequently, I proposed
> > another patch 'Propagate-pathkeys-from-CTEs-up-to-the-outer-query' in
> > [2], which is currently under review and is what the commitfest entry
> > for. Later on, within the same thread, another patch was posted as a
> > fix to the first patch and was subsequently pushed in [3]. I believe
> > this sequence of events might have led to confusion.
> >
> > What is the usual practice in such situations? I guess I'd better to
> > fork a new thread to discuss my proposed patch which is about the
> > 'Propagate-pathkeys-from-CTEs-up-to-the-outer-query'.
>
> Sorry I missed to notice that there was one pending patch yet to be
> committed, I feel you can continue discussing here itself just to
> avoid losing any historical information about the issue and the
> continuation of the discussion. You can add a new commitfest entry for
> this.
It seems to me that a fresh new thread is a better option. I have just
started a new thread in [1], and have tried to migrate the necessary
context over there. I have also updated the commitfest entry
accordingly.
[1]
https://www.postgresql.org/message-id/flat/CAMbWs49xYd3f8CrE8-WW3--dV1zH_sDSDn-vs2DzHj81Wcnsew%40mai...
Thanks
Richard
^ permalink raw reply [nested|flat] 29+ messages in thread
* Re: Wrong rows estimations with joins of CTEs slows queries by more than factor 500
2023-02-08 13:55 Wrong rows estimations with joins of CTEs slows queries by more than factor 500 Hans Buschmann <buschmann@nidsa.net>
2023-08-14 11:12 ` Re: Wrong rows estimations with joins of CTEs slows queries by more than factor 500 Jian Guo <gjian@vmware.com>
2023-08-14 12:58 ` Re: Wrong rows estimations with joins of CTEs slows queries by more than factor 500 Tomas Vondra <tomas.vondra@enterprisedb.com>
2023-08-21 08:16 ` Re: Wrong rows estimations with joins of CTEs slows queries by more than factor 500 Jian Guo <gjian@vmware.com>
2023-11-08 22:44 ` Re: Wrong rows estimations with joins of CTEs slows queries by more than factor 500 Tom Lane <tgl@sss.pgh.pa.us>
2023-11-16 09:24 ` Re: Wrong rows estimations with joins of CTEs slows queries by more than factor 500 Richard Guo <guofenglinux@gmail.com>
2023-11-16 18:16 ` Re: Wrong rows estimations with joins of CTEs slows queries by more than factor 500 Tom Lane <tgl@sss.pgh.pa.us>
2023-11-17 02:09 ` Re: Wrong rows estimations with joins of CTEs slows queries by more than factor 500 Richard Guo <guofenglinux@gmail.com>
2023-11-17 03:38 ` Re: Wrong rows estimations with joins of CTEs slows queries by more than factor 500 Tom Lane <tgl@sss.pgh.pa.us>
@ 2023-11-20 02:42 ` Richard Guo <guofenglinux@gmail.com>
2023-11-20 17:45 ` Re: Wrong rows estimations with joins of CTEs slows queries by more than factor 500 Tom Lane <tgl@sss.pgh.pa.us>
3 siblings, 1 reply; 29+ messages in thread
From: Richard Guo @ 2023-11-20 02:42 UTC (permalink / raw)
To: Tom Lane <tgl@sss.pgh.pa.us>; +Cc: Jian Guo <gjian@vmware.com>; Tomas Vondra <tomas.vondra@enterprisedb.com>; Hans Buschmann <buschmann@nidsa.net>; pgsql-hackers@lists.postgresql.org <pgsql-hackers@lists.postgresql.org>; Zhenghua Lyu <zlyu@vmware.com>
On Fri, Nov 17, 2023 at 11:38 AM Tom Lane <tgl@sss.pgh.pa.us> wrote:
> That line of argument also leads to the conclusion that it'd be
> okay to expose info about the ordering of the CTE result to the
> upper planner. This patch doesn't do that, and I'm not sufficiently
> excited about the issue to go write some code. But if someone else
> does, I think we shouldn't exclude doing it on the grounds of wanting
> to preserve an optimization fence. The fence is sort of one-way
> in this line of thinking: information can propagate up to the outer
> planner level, but not down into the CTE plan.
In the light of this conclusion, I had a go at propagating the pathkeys
from CTEs up to the outer planner and came up with the attached.
Comments/thoughts?
Thanks
Richard
Attachments:
v1-0001-Propagate-pathkeys-from-CTEs-up-to-the-outer-query.patch (application/octet-stream, 5.7K) download
From b16759153bf73bb75a22c31785b045564c7fba20 Mon Sep 17 00:00:00 2001
From: Richard Guo <guofenglinux@gmail.com>
Date: Mon, 20 Nov 2023 10:08:28 +0800
Subject: [PATCH v1] Propagate pathkeys from CTEs up to the outer query
---
src/backend/optimizer/path/allpaths.c | 20 +++++++++++++++++++-
src/backend/optimizer/util/pathnode.c | 5 +++--
src/include/optimizer/pathnode.h | 2 +-
src/test/regress/expected/with.out | 17 +++++++++++++++++
src/test/regress/sql/with.sql | 7 +++++++
5 files changed, 47 insertions(+), 4 deletions(-)
diff --git a/src/backend/optimizer/path/allpaths.c b/src/backend/optimizer/path/allpaths.c
index 67921a0826..8ba3a90b5e 100644
--- a/src/backend/optimizer/path/allpaths.c
+++ b/src/backend/optimizer/path/allpaths.c
@@ -2876,12 +2876,16 @@ static void
set_cte_pathlist(PlannerInfo *root, RelOptInfo *rel, RangeTblEntry *rte)
{
Plan *cteplan;
+ PlannerInfo *ctesubroot;
PlannerInfo *cteroot;
Index levelsup;
int ndx;
ListCell *lc;
int plan_id;
Relids required_outer;
+ RelOptInfo *cte_final_rel;
+ Path *cte_best_path;
+ List *pathkeys;
/*
* Find the referenced CTE, and locate the plan previously made for it.
@@ -2921,6 +2925,20 @@ set_cte_pathlist(PlannerInfo *root, RelOptInfo *rel, RangeTblEntry *rte)
/* Mark rel with estimated output rows, width, etc */
set_cte_size_estimates(root, rel, cteplan->plan_rows);
+ /*
+ * Locate the best path previously made for the CTE. We need to know its
+ * pathkeys.
+ */
+ ctesubroot = (PlannerInfo *) list_nth(root->glob->subroots, plan_id - 1);
+ cte_final_rel = fetch_upper_rel(ctesubroot, UPPERREL_FINAL, NULL);
+ cte_best_path = cte_final_rel->cheapest_total_path;
+
+ /* Convert the pathkeys to outer representation */
+ pathkeys = convert_subquery_pathkeys(root,
+ rel,
+ cte_best_path->pathkeys,
+ make_tlist_from_pathtarget(cte_best_path->pathtarget));
+
/*
* We don't support pushing join clauses into the quals of a CTE scan, but
* it could still have required parameterization due to LATERAL refs in
@@ -2929,7 +2947,7 @@ set_cte_pathlist(PlannerInfo *root, RelOptInfo *rel, RangeTblEntry *rte)
required_outer = rel->lateral_relids;
/* Generate appropriate path */
- add_path(rel, create_ctescan_path(root, rel, required_outer));
+ add_path(rel, create_ctescan_path(root, rel, pathkeys, required_outer));
}
/*
diff --git a/src/backend/optimizer/util/pathnode.c b/src/backend/optimizer/util/pathnode.c
index 0b1d17b9d3..05b85305fe 100644
--- a/src/backend/optimizer/util/pathnode.c
+++ b/src/backend/optimizer/util/pathnode.c
@@ -2113,7 +2113,8 @@ create_valuesscan_path(PlannerInfo *root, RelOptInfo *rel,
* returning the pathnode.
*/
Path *
-create_ctescan_path(PlannerInfo *root, RelOptInfo *rel, Relids required_outer)
+create_ctescan_path(PlannerInfo *root, RelOptInfo *rel,
+ List *pathkeys, Relids required_outer)
{
Path *pathnode = makeNode(Path);
@@ -2125,7 +2126,7 @@ create_ctescan_path(PlannerInfo *root, RelOptInfo *rel, Relids required_outer)
pathnode->parallel_aware = false;
pathnode->parallel_safe = rel->consider_parallel;
pathnode->parallel_workers = 0;
- pathnode->pathkeys = NIL; /* XXX for now, result is always unordered */
+ pathnode->pathkeys = pathkeys;
cost_ctescan(pathnode, root, rel, pathnode->param_info);
diff --git a/src/include/optimizer/pathnode.h b/src/include/optimizer/pathnode.h
index 6e557bebc4..2d76471814 100644
--- a/src/include/optimizer/pathnode.h
+++ b/src/include/optimizer/pathnode.h
@@ -115,7 +115,7 @@ extern Path *create_valuesscan_path(PlannerInfo *root, RelOptInfo *rel,
extern Path *create_tablefuncscan_path(PlannerInfo *root, RelOptInfo *rel,
Relids required_outer);
extern Path *create_ctescan_path(PlannerInfo *root, RelOptInfo *rel,
- Relids required_outer);
+ List *pathkeys, Relids required_outer);
extern Path *create_namedtuplestorescan_path(PlannerInfo *root, RelOptInfo *rel,
Relids required_outer);
extern Path *create_resultscan_path(PlannerInfo *root, RelOptInfo *rel,
diff --git a/src/test/regress/expected/with.out b/src/test/regress/expected/with.out
index 69c56ce207..9549cdc3d1 100644
--- a/src/test/regress/expected/with.out
+++ b/src/test/regress/expected/with.out
@@ -654,6 +654,23 @@ select count(*) from tenk1 a
-> CTE Scan on x
(8 rows)
+-- test that pathkeys from a materialized CTE are propagated up to the
+-- outer query
+explain (costs off)
+with x as materialized (select unique1 from tenk1 b order by unique1)
+select count(*) from tenk1 a
+ where unique1 in (select * from x);
+ QUERY PLAN
+------------------------------------------------------------
+ Aggregate
+ CTE x
+ -> Index Only Scan using tenk1_unique1 on tenk1 b
+ -> Merge Semi Join
+ Merge Cond: (a.unique1 = x.unique1)
+ -> Index Only Scan using tenk1_unique1 on tenk1 a
+ -> CTE Scan on x
+(7 rows)
+
-- SEARCH clause
create temp table graph0( f int, t int, label text );
insert into graph0 values
diff --git a/src/test/regress/sql/with.sql b/src/test/regress/sql/with.sql
index 3ef9898866..54da8b2d21 100644
--- a/src/test/regress/sql/with.sql
+++ b/src/test/regress/sql/with.sql
@@ -354,6 +354,13 @@ with x as materialized (select unique1 from tenk1 b)
select count(*) from tenk1 a
where unique1 in (select * from x);
+-- test that pathkeys from a materialized CTE are propagated up to the
+-- outer query
+explain (costs off)
+with x as materialized (select unique1 from tenk1 b order by unique1)
+select count(*) from tenk1 a
+ where unique1 in (select * from x);
+
-- SEARCH clause
create temp table graph0( f int, t int, label text );
--
2.31.0
* Re: Wrong rows estimations with joins of CTEs slows queries by more than factor 500
2023-02-08 13:55 Wrong rows estimations with joins of CTEs slows queries by more than factor 500 Hans Buschmann <buschmann@nidsa.net>
2023-08-14 11:12 ` Re: Wrong rows estimations with joins of CTEs slows queries by more than factor 500 Jian Guo <gjian@vmware.com>
2023-08-14 12:58 ` Re: Wrong rows estimations with joins of CTEs slows queries by more than factor 500 Tomas Vondra <tomas.vondra@enterprisedb.com>
2023-08-21 08:16 ` Re: Wrong rows estimations with joins of CTEs slows queries by more than factor 500 Jian Guo <gjian@vmware.com>
2023-11-08 22:44 ` Re: Wrong rows estimations with joins of CTEs slows queries by more than factor 500 Tom Lane <tgl@sss.pgh.pa.us>
2023-11-16 09:24 ` Re: Wrong rows estimations with joins of CTEs slows queries by more than factor 500 Richard Guo <guofenglinux@gmail.com>
2023-11-16 18:16 ` Re: Wrong rows estimations with joins of CTEs slows queries by more than factor 500 Tom Lane <tgl@sss.pgh.pa.us>
2023-11-17 02:09 ` Re: Wrong rows estimations with joins of CTEs slows queries by more than factor 500 Richard Guo <guofenglinux@gmail.com>
2023-11-17 03:38 ` Re: Wrong rows estimations with joins of CTEs slows queries by more than factor 500 Tom Lane <tgl@sss.pgh.pa.us>
2023-11-20 02:42 ` Re: Wrong rows estimations with joins of CTEs slows queries by more than factor 500 Richard Guo <guofenglinux@gmail.com>
@ 2023-11-20 17:45 ` Tom Lane <tgl@sss.pgh.pa.us>
2023-11-21 06:18 ` Re: Wrong rows estimations with joins of CTEs slows queries by more than factor 500 Richard Guo <guofenglinux@gmail.com>
0 siblings, 1 reply; 29+ messages in thread
From: Tom Lane @ 2023-11-20 17:45 UTC (permalink / raw)
To: Richard Guo <guofenglinux@gmail.com>; +Cc: Jian Guo <gjian@vmware.com>; Tomas Vondra <tomas.vondra@enterprisedb.com>; Hans Buschmann <buschmann@nidsa.net>; pgsql-hackers@lists.postgresql.org <pgsql-hackers@lists.postgresql.org>; Zhenghua Lyu <zlyu@vmware.com>
Richard Guo <guofenglinux@gmail.com> writes:
> On Fri, Nov 17, 2023 at 11:38 AM Tom Lane <tgl@sss.pgh.pa.us> wrote:
>> That line of argument also leads to the conclusion that it'd be
>> okay to expose info about the ordering of the CTE result to the
>> upper planner.
> In the light of this conclusion, I had a go at propagating the pathkeys
> from CTEs up to the outer planner and came up with the attached.
Oh, nice! I remembered we had code already to do this for regular
SubqueryScans, but I thought we'd need to do some refactoring to
apply it to CTEs. I think you are right though that
convert_subquery_pathkeys can be used as-is. Some thoughts:
* Do we really need to use make_tlist_from_pathtarget? Why isn't
the tlist of the cteplan good enough (indeed, more so)?
* I don't love having this code assume that it knows how to find
the Path the cteplan was made from. It'd be better to make
SS_process_ctes save that somewhere, maybe in a list paralleling
root->cte_plan_ids.
Alternatively: maybe it's time to do what the comments in
SS_process_ctes vaguely speculate about, and just save the Path
at that point, with construction of the plan left for createplan()?
That might be a lot of refactoring for not much gain, so not sure.
regards, tom lane
^ permalink raw reply [nested|flat] 29+ messages in thread
* Re: Wrong rows estimations with joins of CTEs slows queries by more than factor 500
2023-02-08 13:55 Wrong rows estimations with joins of CTEs slows queries by more than factor 500 Hans Buschmann <buschmann@nidsa.net>
2023-08-14 11:12 ` Re: Wrong rows estimations with joins of CTEs slows queries by more than factor 500 Jian Guo <gjian@vmware.com>
2023-08-14 12:58 ` Re: Wrong rows estimations with joins of CTEs slows queries by more than factor 500 Tomas Vondra <tomas.vondra@enterprisedb.com>
2023-08-21 08:16 ` Re: Wrong rows estimations with joins of CTEs slows queries by more than factor 500 Jian Guo <gjian@vmware.com>
2023-11-08 22:44 ` Re: Wrong rows estimations with joins of CTEs slows queries by more than factor 500 Tom Lane <tgl@sss.pgh.pa.us>
2023-11-16 09:24 ` Re: Wrong rows estimations with joins of CTEs slows queries by more than factor 500 Richard Guo <guofenglinux@gmail.com>
2023-11-16 18:16 ` Re: Wrong rows estimations with joins of CTEs slows queries by more than factor 500 Tom Lane <tgl@sss.pgh.pa.us>
2023-11-17 02:09 ` Re: Wrong rows estimations with joins of CTEs slows queries by more than factor 500 Richard Guo <guofenglinux@gmail.com>
2023-11-17 03:38 ` Re: Wrong rows estimations with joins of CTEs slows queries by more than factor 500 Tom Lane <tgl@sss.pgh.pa.us>
2023-11-20 02:42 ` Re: Wrong rows estimations with joins of CTEs slows queries by more than factor 500 Richard Guo <guofenglinux@gmail.com>
2023-11-20 17:45 ` Re: Wrong rows estimations with joins of CTEs slows queries by more than factor 500 Tom Lane <tgl@sss.pgh.pa.us>
@ 2023-11-21 06:18 ` Richard Guo <guofenglinux@gmail.com>
0 siblings, 0 replies; 29+ messages in thread
From: Richard Guo @ 2023-11-21 06:18 UTC (permalink / raw)
To: Tom Lane <tgl@sss.pgh.pa.us>; +Cc: Jian Guo <gjian@vmware.com>; Tomas Vondra <tomas.vondra@enterprisedb.com>; Hans Buschmann <buschmann@nidsa.net>; pgsql-hackers@lists.postgresql.org <pgsql-hackers@lists.postgresql.org>; Zhenghua Lyu <zlyu@vmware.com>
On Tue, Nov 21, 2023 at 1:46 AM Tom Lane <tgl@sss.pgh.pa.us> wrote:
> * Do we really need to use make_tlist_from_pathtarget? Why isn't
> the tlist of the cteplan good enough (indeed, more so)?
I think you are right. The cteplan->targetlist is built for the CTE's
best path by build_path_tlist(), which is almost the same as
make_tlist_from_pathtarget() except that it also replaces nestloop
params. So cteplan->targetlist is good enough here.
> * I don't love having this code assume that it knows how to find
> the Path the cteplan was made from. It'd be better to make
> SS_process_ctes save that somewhere, maybe in a list paralleling
> root->cte_plan_ids.
Fair point.
I've updated the patch to v2 for the changes.
> Alternatively: maybe it's time to do what the comments in
> SS_process_ctes vaguely speculate about, and just save the Path
> at that point, with construction of the plan left for createplan()?
> That might be a lot of refactoring for not much gain, so not sure.
I'm not sure if this is worth the effort. And it seems that we have the
same situation with SubLinks where we construct the plan in subselect.c
rather than createplan.c.
Thanks
Richard
Attachments:
v2-0001-Propagate-pathkeys-from-CTEs-up-to-the-outer-query.patch (application/octet-stream, 9.2K) download
From 48c79d4fcba599ce17bbd66d09b9a05e92db54c8 Mon Sep 17 00:00:00 2001
From: Richard Guo <guofenglinux@gmail.com>
Date: Mon, 20 Nov 2023 10:08:28 +0800
Subject: [PATCH v2] Propagate pathkeys from CTEs up to the outer query
---
src/backend/optimizer/path/allpaths.c | 17 ++++++++++++++++-
src/backend/optimizer/plan/planner.c | 1 +
src/backend/optimizer/plan/subselect.c | 16 ++++++++++------
src/backend/optimizer/prep/prepjointree.c | 1 +
src/backend/optimizer/util/pathnode.c | 5 +++--
src/include/nodes/pathnodes.h | 5 +++++
src/include/optimizer/pathnode.h | 2 +-
src/test/regress/expected/with.out | 17 +++++++++++++++++
src/test/regress/sql/with.sql | 7 +++++++
9 files changed, 61 insertions(+), 10 deletions(-)
diff --git a/src/backend/optimizer/path/allpaths.c b/src/backend/optimizer/path/allpaths.c
index 67921a0826..749a69ff51 100644
--- a/src/backend/optimizer/path/allpaths.c
+++ b/src/backend/optimizer/path/allpaths.c
@@ -2882,6 +2882,8 @@ set_cte_pathlist(PlannerInfo *root, RelOptInfo *rel, RangeTblEntry *rte)
ListCell *lc;
int plan_id;
Relids required_outer;
+ Path *ctepath;
+ List *pathkeys;
/*
* Find the referenced CTE, and locate the plan previously made for it.
@@ -2921,6 +2923,19 @@ set_cte_pathlist(PlannerInfo *root, RelOptInfo *rel, RangeTblEntry *rte)
/* Mark rel with estimated output rows, width, etc */
set_cte_size_estimates(root, rel, cteplan->plan_rows);
+ /*
+ * Locate the best path previously made for the referenced CTE. We need to
+ * know its pathkeys.
+ */
+ Assert(list_length(cteroot->cte_plan_ids) == list_length(cteroot->cte_paths));
+ ctepath = (Path *) list_nth(cteroot->cte_paths, ndx);
+
+ /* Convert the pathkeys to outer representation */
+ pathkeys = convert_subquery_pathkeys(root,
+ rel,
+ ctepath->pathkeys,
+ cteplan->targetlist);
+
/*
* We don't support pushing join clauses into the quals of a CTE scan, but
* it could still have required parameterization due to LATERAL refs in
@@ -2929,7 +2944,7 @@ set_cte_pathlist(PlannerInfo *root, RelOptInfo *rel, RangeTblEntry *rte)
required_outer = rel->lateral_relids;
/* Generate appropriate path */
- add_path(rel, create_ctescan_path(root, rel, required_outer));
+ add_path(rel, create_ctescan_path(root, rel, pathkeys, required_outer));
}
/*
diff --git a/src/backend/optimizer/plan/planner.c b/src/backend/optimizer/plan/planner.c
index a8cea5efe1..7ac5d7b159 100644
--- a/src/backend/optimizer/plan/planner.c
+++ b/src/backend/optimizer/plan/planner.c
@@ -643,6 +643,7 @@ subquery_planner(PlannerGlobal *glob, Query *parse,
root->planner_cxt = CurrentMemoryContext;
root->init_plans = NIL;
root->cte_plan_ids = NIL;
+ root->cte_paths = NIL;
root->multiexpr_params = NIL;
root->join_domains = NIL;
root->eq_classes = NIL;
diff --git a/src/backend/optimizer/plan/subselect.c b/src/backend/optimizer/plan/subselect.c
index 7a9fe88fec..5a112b1562 100644
--- a/src/backend/optimizer/plan/subselect.c
+++ b/src/backend/optimizer/plan/subselect.c
@@ -885,16 +885,17 @@ hash_ok_operator(OpExpr *expr)
* unreferenced SELECT), "inline" it to create a regular sub-SELECT-in-FROM,
* or convert it to an initplan.
*
- * A side effect is to fill in root->cte_plan_ids with a list that
- * parallels root->parse->cteList and provides the subplan ID for
- * each CTE's initplan, or a dummy ID (-1) if we didn't make an initplan.
+ * A side effect is to fill in root->cte_plan_ids and root->cte_paths with
+ * lists that parallel root->parse->cteList and provide the subplan ID and
+ * best path for each CTE, or a dummy ID (-1) and a dummy Path (NULL) if we
+ * didn't make a subplan.
*/
void
SS_process_ctes(PlannerInfo *root)
{
ListCell *lc;
- Assert(root->cte_plan_ids == NIL);
+ Assert(root->cte_plan_ids == NIL && root->cte_paths == NIL);
foreach(lc, root->parse->cteList)
{
@@ -913,8 +914,9 @@ SS_process_ctes(PlannerInfo *root)
*/
if (cte->cterefcount == 0 && cmdType == CMD_SELECT)
{
- /* Make a dummy entry in cte_plan_ids */
+ /* Make a dummy entry in cte_plan_ids and cte_paths */
root->cte_plan_ids = lappend_int(root->cte_plan_ids, -1);
+ root->cte_paths = lappend(root->cte_paths, NULL);
continue;
}
@@ -960,8 +962,9 @@ SS_process_ctes(PlannerInfo *root)
!contain_volatile_functions(cte->ctequery))
{
inline_cte(root, cte);
- /* Make a dummy entry in cte_plan_ids */
+ /* Make a dummy entry in cte_plan_ids and cte_paths */
root->cte_plan_ids = lappend_int(root->cte_plan_ids, -1);
+ root->cte_paths = lappend(root->cte_paths, NULL);
continue;
}
@@ -1051,6 +1054,7 @@ SS_process_ctes(PlannerInfo *root)
root->init_plans = lappend(root->init_plans, splan);
root->cte_plan_ids = lappend_int(root->cte_plan_ids, splan->plan_id);
+ root->cte_paths = lappend(root->cte_paths, best_path);
/* Label the subplan for EXPLAIN purposes */
splan->plan_name = psprintf("CTE %s", cte->ctename);
diff --git a/src/backend/optimizer/prep/prepjointree.c b/src/backend/optimizer/prep/prepjointree.c
index 73ff40721c..4164ada823 100644
--- a/src/backend/optimizer/prep/prepjointree.c
+++ b/src/backend/optimizer/prep/prepjointree.c
@@ -990,6 +990,7 @@ pull_up_simple_subquery(PlannerInfo *root, Node *jtnode, RangeTblEntry *rte,
subroot->planner_cxt = CurrentMemoryContext;
subroot->init_plans = NIL;
subroot->cte_plan_ids = NIL;
+ subroot->cte_paths = NIL;
subroot->multiexpr_params = NIL;
subroot->join_domains = NIL;
subroot->eq_classes = NIL;
diff --git a/src/backend/optimizer/util/pathnode.c b/src/backend/optimizer/util/pathnode.c
index 0b1d17b9d3..05b85305fe 100644
--- a/src/backend/optimizer/util/pathnode.c
+++ b/src/backend/optimizer/util/pathnode.c
@@ -2113,7 +2113,8 @@ create_valuesscan_path(PlannerInfo *root, RelOptInfo *rel,
* returning the pathnode.
*/
Path *
-create_ctescan_path(PlannerInfo *root, RelOptInfo *rel, Relids required_outer)
+create_ctescan_path(PlannerInfo *root, RelOptInfo *rel,
+ List *pathkeys, Relids required_outer)
{
Path *pathnode = makeNode(Path);
@@ -2125,7 +2126,7 @@ create_ctescan_path(PlannerInfo *root, RelOptInfo *rel, Relids required_outer)
pathnode->parallel_aware = false;
pathnode->parallel_safe = rel->consider_parallel;
pathnode->parallel_workers = 0;
- pathnode->pathkeys = NIL; /* XXX for now, result is always unordered */
+ pathnode->pathkeys = pathkeys;
cost_ctescan(pathnode, root, rel, pathnode->param_info);
diff --git a/src/include/nodes/pathnodes.h b/src/include/nodes/pathnodes.h
index ed85dc7414..cacd566777 100644
--- a/src/include/nodes/pathnodes.h
+++ b/src/include/nodes/pathnodes.h
@@ -301,6 +301,11 @@ struct PlannerInfo
*/
List *cte_plan_ids;
+ /*
+ * per-CTE-item list of Paths (or NULL if no Path was made for that CTE)
+ */
+ List *cte_paths;
+
/* List of Lists of Params for MULTIEXPR subquery outputs */
List *multiexpr_params;
diff --git a/src/include/optimizer/pathnode.h b/src/include/optimizer/pathnode.h
index 6e557bebc4..2d76471814 100644
--- a/src/include/optimizer/pathnode.h
+++ b/src/include/optimizer/pathnode.h
@@ -115,7 +115,7 @@ extern Path *create_valuesscan_path(PlannerInfo *root, RelOptInfo *rel,
extern Path *create_tablefuncscan_path(PlannerInfo *root, RelOptInfo *rel,
Relids required_outer);
extern Path *create_ctescan_path(PlannerInfo *root, RelOptInfo *rel,
- Relids required_outer);
+ List *pathkeys, Relids required_outer);
extern Path *create_namedtuplestorescan_path(PlannerInfo *root, RelOptInfo *rel,
Relids required_outer);
extern Path *create_resultscan_path(PlannerInfo *root, RelOptInfo *rel,
diff --git a/src/test/regress/expected/with.out b/src/test/regress/expected/with.out
index 69c56ce207..9549cdc3d1 100644
--- a/src/test/regress/expected/with.out
+++ b/src/test/regress/expected/with.out
@@ -654,6 +654,23 @@ select count(*) from tenk1 a
-> CTE Scan on x
(8 rows)
+-- test that pathkeys from a materialized CTE are propagated up to the
+-- outer query
+explain (costs off)
+with x as materialized (select unique1 from tenk1 b order by unique1)
+select count(*) from tenk1 a
+ where unique1 in (select * from x);
+ QUERY PLAN
+------------------------------------------------------------
+ Aggregate
+ CTE x
+ -> Index Only Scan using tenk1_unique1 on tenk1 b
+ -> Merge Semi Join
+ Merge Cond: (a.unique1 = x.unique1)
+ -> Index Only Scan using tenk1_unique1 on tenk1 a
+ -> CTE Scan on x
+(7 rows)
+
-- SEARCH clause
create temp table graph0( f int, t int, label text );
insert into graph0 values
diff --git a/src/test/regress/sql/with.sql b/src/test/regress/sql/with.sql
index 3ef9898866..54da8b2d21 100644
--- a/src/test/regress/sql/with.sql
+++ b/src/test/regress/sql/with.sql
@@ -354,6 +354,13 @@ with x as materialized (select unique1 from tenk1 b)
select count(*) from tenk1 a
where unique1 in (select * from x);
+-- test that pathkeys from a materialized CTE are propagated up to the
+-- outer query
+explain (costs off)
+with x as materialized (select unique1 from tenk1 b order by unique1)
+select count(*) from tenk1 a
+ where unique1 in (select * from x);
+
-- SEARCH clause
create temp table graph0( f int, t int, label text );
--
2.31.0
end of thread, other threads:[~2024-01-29 03:40 UTC | newest]
Thread overview: 29+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2023-02-08 13:55 Wrong rows estimations with joins of CTEs slows queries by more than factor 500 Hans Buschmann <buschmann@nidsa.net>
2023-02-08 21:27 ` Tomas Vondra <tomas.vondra@enterprisedb.com>
2023-08-14 11:12 ` Jian Guo <gjian@vmware.com>
2023-08-14 12:58 ` Tomas Vondra <tomas.vondra@enterprisedb.com>
2023-08-21 08:16 ` Jian Guo <gjian@vmware.com>
2023-08-21 10:56 ` Tomas Vondra <tomas.vondra@enterprisedb.com>
2023-08-22 02:35 ` Jian Guo <gjian@vmware.com>
2023-09-06 06:00 ` jian he <jian.universality@gmail.com>
2023-09-07 09:26 ` Jian Guo <gjian@vmware.com>
2023-11-08 22:44 ` Tom Lane <tgl@sss.pgh.pa.us>
2023-11-16 09:24 ` Richard Guo <guofenglinux@gmail.com>
2023-11-16 18:16 ` Tom Lane <tgl@sss.pgh.pa.us>
2023-11-17 02:09 ` Richard Guo <guofenglinux@gmail.com>
2023-11-17 03:38 ` Tom Lane <tgl@sss.pgh.pa.us>
2023-11-17 03:45 ` David G. Johnston <david.g.johnston@gmail.com>
2023-11-17 03:53 ` Laurenz Albe <laurenz.albe@cybertec.at>
2023-11-17 06:41 ` Richard Guo <guofenglinux@gmail.com>
2023-11-17 19:42 ` Tom Lane <tgl@sss.pgh.pa.us>
2024-01-06 09:00 ` Alexander Lakhin <exclusion@gmail.com>
2024-01-06 22:41 ` Tom Lane <tgl@sss.pgh.pa.us>
2024-01-08 11:14 ` Richard Guo <guofenglinux@gmail.com>
2024-01-08 16:51 ` Tom Lane <tgl@sss.pgh.pa.us>
2024-01-27 02:07 ` vignesh C <vignesh21@gmail.com>
2024-01-29 02:30 ` Richard Guo <guofenglinux@gmail.com>
2024-01-29 03:19 ` vignesh C <vignesh21@gmail.com>
2024-01-29 03:40 ` Richard Guo <guofenglinux@gmail.com>
2023-11-20 02:42 ` Richard Guo <guofenglinux@gmail.com>
2023-11-20 17:45 ` Tom Lane <tgl@sss.pgh.pa.us>
2023-11-21 06:18 ` Richard Guo <guofenglinux@gmail.com>
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox