Received: from malur.postgresql.org ([217.196.149.56]) by arkaria.postgresql.org with esmtps (TLS1.3:ECDHE_RSA_AES_256_GCM_SHA384:256) (Exim 4.92) (envelope-from ) id 1pPkuq-0004uw-Jp for pgsql-hackers@arkaria.postgresql.org; Wed, 08 Feb 2023 13:55:17 +0000 Received: from localhost ([127.0.0.1] helo=malur.postgresql.org) by malur.postgresql.org with esmtp (Exim 4.92) (envelope-from ) id 1pPkup-0008TH-Hd for pgsql-hackers@arkaria.postgresql.org; Wed, 08 Feb 2023 13:55:15 +0000 Received: from magus.postgresql.org ([2a02:c0:301:0:ffff::29]) by malur.postgresql.org with esmtps (TLS1.3:ECDHE_RSA_AES_256_GCM_SHA384:256) (Exim 4.92) (envelope-from ) id 1pPkup-0008T6-2n for pgsql-hackers@lists.postgresql.org; Wed, 08 Feb 2023 13:55:15 +0000 Received: from ma03-relay.lansolnet.com ([176.95.46.36]) by magus.postgresql.org with esmtps (TLS1.3:ECDHE_RSA_AES_256_GCM_SHA384:256) (Exim 4.92) (envelope-from ) id 1pPkul-0007Vr-TS for pgsql-hackers@lists.postgresql.org; Wed, 08 Feb 2023 13:55:14 +0000 Received: from unknown ([192.168.8.230]) by ma03-relay.lansolnet.com; Wed, 08 Feb 2023 14:55:09 +0100 Received: from MBX200E.cloud4partner.com (192.168.8.234) by MBX200A.cloud4partner.com (192.168.8.230) with Microsoft SMTP Server (version=TLS1_2, cipher=TLS_ECDHE_RSA_WITH_AES_256_CBC_SHA384_P384) id 15.1.669.32; Wed, 8 Feb 2023 14:55:07 +0100 Received: from MBX200E.cloud4partner.com ([fe80::881b:2a52:f04f:d634]) by MBX200E.cloud4partner.com ([fe80::881b:2a52:f04f:d634%13]) with mapi id 15.01.0669.032; Wed, 8 Feb 2023 14:55:07 +0100 From: Hans Buschmann To: "pgsql-hackers@lists.postgresql.org" Subject: Wrong rows estimations with joins of CTEs slows queries by more than factor 500 Thread-Topic: Wrong rows estimations with joins of CTEs slows queries by more than factor 500 Thread-Index: AQHZO8TVbeQ5wt08FUmkJLHoS+8I0g== Date: Wed, 8 Feb 2023 13:55:07 +0000 Message-ID: <4504e67078d648cdac3651b2960da6e7@nidsa.net> Accept-Language: de-DE, en-US Content-Language: de-DE X-MS-Has-Attach: X-MS-TNEF-Correlator: x-originating-ip: [192.168.8.240] Content-Type: multipart/alternative; boundary="_000_4504e67078d648cdac3651b2960da6e7nidsanet_" MIME-Version: 1.0 X-cloud-security-sender:buschmann@nidsa.net X-cloud-security-recipient:pgsql-hackers@lists.postgresql.org X-cloud-security-Virusscan:CLEAN X-cloud-security-disclaimer: This E-Mail was scanned by E-Mailservice on ma03-relay.lansolnet.com with 23C591980A34 X-cloud-security-connect: unknown[192.168.8.230], TLS=1, IP=192.168.8.230 X-cloud-security:scantime:.5522 List-Id: List-Help: List-Subscribe: List-Post: List-Owner: List-Archive: Archived-At: Precedence: bulk --_000_4504e67078d648cdac3651b2960da6e7nidsanet_ Content-Type: text/plain; charset="iso-8859-1" Content-Transfer-Encoding: quoted-printable During data refactoring of our Application I encountered $subject when join= ing 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 ce= rtain data (4 CTEs qup,qli,qin,qou) The grouping of the data in the CTEs gives estimated row counts of about 10= 00 (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=3Dqup.curr_season and qli.curr_code=3Dqup= .curr_code and qli.ibitmask>0 and cardinality(qli.mat_arr) <=3D8) left join qin on (qin.curr_season=3Dqup.curr_season and qin.curr_code=3Dqup= .curr_code and qin.ibitmask>0 and cardinality(qin.mat_arr) <=3D8) left join qou on (qou.curr_season=3Dqup.curr_season and qou.curr_code=3Dqup= .curr_code and qou.ibitmask>0 and cardinality(qou.mat_arr) <=3D11) where qup.ibitmask>0 and cardinality(qup.mat_arr) <=3D21 The plan first retrieves qup and qli, taking the estimated row counts of 11= 63 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=3D33) QUERY P= LAN ---------------------------------------------------------------------------= ----------------------------------------------------------------------- Append (cost=3D13673.81..17463.30 rows=3D5734 width=3D104) (actual time= =3D168.307..222.670 rows=3D9963 loops=3D1) CTE qup -> GroupAggregate (cost=3D5231.22..6303.78 rows=3D10320 width=3D80) = (actual time=3D35.466..68.131 rows=3D10735 loops=3D1) Group Key: sa_upper.sup_season, sa_upper.sup_sa_code -> Sort (cost=3D5231.22..5358.64 rows=3D50969 width=3D18) (act= ual time=3D35.454..36.819 rows=3D50969 loops=3D1) Sort Key: sa_upper.sup_season, sa_upper.sup_sa_code COLLAT= E "C" Sort Method: quicksort Memory: 4722kB -> Hash Left Join (cost=3D41.71..1246.13 rows=3D50969 wi= dth=3D18) (actual time=3D0.148..10.687 rows=3D50969 loops=3D1) Hash Cond: ((sa_upper.sup_mat_code)::text =3D upper_= target.up_mat_code) -> Seq Scan on sa_upper (cost=3D0.00..884.69 rows= =3D50969 width=3D16) (actual time=3D0.005..1.972 rows=3D50969 loops=3D1) -> Hash (cost=3D35.53..35.53 rows=3D495 width=3D6)= (actual time=3D0.140..0.140 rows=3D495 loops=3D1) Buckets: 1024 Batches: 1 Memory Usage: 27kB -> Seq Scan on upper_target (cost=3D0.00..35= .53 rows=3D495 width=3D6) (actual time=3D0.007..0.103 rows=3D495 loops=3D1) Filter: (id_up <=3D 495) Rows Removed by Filter: 1467 CTE qli -> GroupAggregate (cost=3D1097.31..1486.56 rows=3D10469 width=3D80) = (actual time=3D9.446..27.388 rows=3D10469 loops=3D1) Group Key: sa_lining.sli_season, sa_lining.sli_sa_code -> Sort (cost=3D1097.31..1126.74 rows=3D11774 width=3D18) (act= ual time=3D9.440..9.811 rows=3D11774 loops=3D1) Sort Key: sa_lining.sli_season, sa_lining.sli_sa_code COLL= ATE "C" Sort Method: quicksort Memory: 1120kB -> Hash Left Join (cost=3D7.34..301.19 rows=3D11774 widt= h=3D18) (actual time=3D0.045..2.438 rows=3D11774 loops=3D1) Hash Cond: ((sa_lining.sli_mat_code)::text =3D linin= g_target.li_mat_code) -> Seq Scan on sa_lining (cost=3D0.00..204.74 rows= =3D11774 width=3D16) (actual time=3D0.008..0.470 rows=3D11774 loops=3D1) -> Hash (cost=3D5.86..5.86 rows=3D118 width=3D6) (= actual time=3D0.034..0.034 rows=3D119 loops=3D1) Buckets: 1024 Batches: 1 Memory Usage: 13kB -> Seq Scan on lining_target (cost=3D0.00..5= .86 rows=3D118 width=3D6) (actual time=3D0.008..0.024 rows=3D119 loops=3D1) Filter: (id_li <=3D 119) Rows Removed by Filter: 190 CTE qin -> GroupAggregate (cost=3D1427.34..1880.73 rows=3D10678 width=3D80) = (actual time=3D11.424..31.508 rows=3D10678 loops=3D1) Group Key: sa_insole.sin_season, sa_insole.sin_sa_code -> Sort (cost=3D1427.34..1465.41 rows=3D15230 width=3D18) (act= ual time=3D11.416..11.908 rows=3D15230 loops=3D1) Sort Key: sa_insole.sin_season, sa_insole.sin_sa_code COLL= ATE "C" Sort Method: quicksort Memory: 1336kB -> Hash Left Join (cost=3D10.49..369.26 rows=3D15230 wid= th=3D18) (actual time=3D0.051..3.108 rows=3D15230 loops=3D1) Hash Cond: ((sa_insole.sin_mat_code)::text =3D insol= e_target.in_mat_code) -> Seq Scan on sa_insole (cost=3D0.00..264.30 rows= =3D15230 width=3D16) (actual time=3D0.006..0.606 rows=3D15230 loops=3D1) -> Hash (cost=3D9.01..9.01 rows=3D118 width=3D6) (= actual time=3D0.042..0.043 rows=3D119 loops=3D1) Buckets: 1024 Batches: 1 Memory Usage: 13kB -> Seq Scan on insole_target (cost=3D0.00..9= .01 rows=3D118 width=3D6) (actual time=3D0.008..0.032 rows=3D119 loops=3D1) Filter: (id_in <=3D 119) Rows Removed by Filter: 362 CTE qou -> GroupAggregate (cost=3D2366.22..2986.89 rows=3D10699 width=3D80) = (actual time=3D18.198..41.812 rows=3D10699 loops=3D1) Group Key: sa_outsole.sou_season, sa_outsole.sou_sa_code -> Sort (cost=3D2366.22..2428.14 rows=3D24768 width=3D18) (act= ual time=3D18.187..18.967 rows=3D24768 loops=3D1) Sort Key: sa_outsole.sou_season, sa_outsole.sou_sa_code CO= LLATE "C" Sort Method: quicksort Memory: 2317kB -> Hash Left Join (cost=3D5.39..558.63 rows=3D24768 widt= h=3D18) (actual time=3D0.046..5.132 rows=3D24768 loops=3D1) Hash Cond: ((sa_outsole.sou_mat_code)::text =3D outs= ole_target.ou_mat_code) -> Seq Scan on sa_outsole (cost=3D0.00..430.68 row= s=3D24768 width=3D16) (actual time=3D0.010..1.015 rows=3D24768 loops=3D1) -> Hash (cost=3D5.03..5.03 rows=3D29 width=3D6) (a= ctual time=3D0.032..0.032 rows=3D29 loops=3D1) Buckets: 1024 Batches: 1 Memory Usage: 10kB -> Seq Scan on outsole_target (cost=3D0.00..= 5.03 rows=3D29 width=3D6) (actual time=3D0.010..0.025 rows=3D29 loops=3D1) Filter: (id_ou <=3D 29) Rows Removed by Filter: 213 -> Hash Join (cost=3D1015.85..1319.50 rows=3D1 width=3D104) (actual ti= me=3D168.307..215.513 rows=3D8548 loops=3D1) Hash Cond: ((qou.curr_season =3D qli.curr_season) AND ((qou.curr_c= ode)::text =3D (qli.curr_code)::text)) Join Filter: ((((qup.ibitmask | qin.ibitmask) | qli.ibitmask) | qo= u.ibitmask) IS NOT NULL) -> CTE Scan on qou (cost=3D0.00..294.22 rows=3D1189 width=3D76) = (actual time=3D18.200..45.188 rows=3D10275 loops=3D1) Filter: ((ibitmask > 0) AND (cardinality(mat_arr) <=3D 11)) Rows Removed by Filter: 424 -> Hash (cost=3D1015.83..1015.83 rows=3D1 width=3D228) (actual t= ime=3D150.094..150.095 rows=3D8845 loops=3D1) Buckets: 16384 (originally 1024) Batches: 1 (originally 1) = Memory Usage: 1899kB -> Hash Join (cost=3D707.35..1015.83 rows=3D1 width=3D228)= (actual time=3D121.898..147.726 rows=3D8845 loops=3D1) Hash Cond: ((qin.curr_season =3D qli.curr_season) AND = ((qin.curr_code)::text =3D (qli.curr_code)::text)) -> CTE Scan on qin (cost=3D0.00..293.65 rows=3D1186 = width=3D76) (actual time=3D11.425..34.674 rows=3D10197 loops=3D1) Filter: ((ibitmask > 0) AND (cardinality(mat_arr= ) <=3D 8)) Rows Removed by Filter: 481 -> Hash (cost=3D706.86..706.86 rows=3D33 width=3D152= ) (actual time=3D110.470..110.470 rows=3D9007 loops=3D1) Buckets: 16384 (originally 1024) Batches: 1 (or= iginally 1) Memory Usage: 1473kB -> Merge Join (cost=3D689.20..706.86 rows=3D33= width=3D152) (actual time=3D105.862..108.925 rows=3D9007 loops=3D1) Merge Cond: ((qup.curr_season =3D qli.curr= _season) AND ((qup.curr_code)::text =3D (qli.curr_code)::text)) -> Sort (cost=3D342.09..344.96 rows=3D11= 47 width=3D76) (actual time=3D73.419..73.653 rows=3D9320 loops=3D1) Sort Key: qup.curr_season, qup.curr_= code COLLATE "C" Sort Method: quicksort Memory: 1391= kB -> CTE Scan on qup (cost=3D0.00..2= 83.80 rows=3D1147 width=3D76) (actual time=3D35.467..71.904 rows=3D9320 loo= ps=3D1) Filter: ((ibitmask > 0) AND (c= ardinality(mat_arr) <=3D 21)) Rows Removed by Filter: 1415 -> Sort (cost=3D347.12..350.02 rows=3D11= 63 width=3D76) (actual time=3D32.440..32.697 rows=3D10289 loops=3D1) Sort Key: qli.curr_season, qli.curr_= code COLLATE "C" Sort Method: quicksort Memory: 1349= kB -> CTE Scan on qli (cost=3D0.00..2= 87.90 rows=3D1163 width=3D76) (actual time=3D9.447..30.666 rows=3D10289 loo= ps=3D1) Filter: ((ibitmask > 0) AND (c= ardinality(mat_arr) <=3D 8)) Rows Removed by Filter: 180 -> Merge Left Join (cost=3D2625.49..3399.84 rows=3D5733 width=3D104) (= actual time=3D4.597..6.700 rows=3D1415 loops=3D1) Merge Cond: ((qup_1.curr_season =3D qou_1.curr_season) AND ((qup_1= .curr_code)::text =3D (qou_1.curr_code)::text)) -> Merge Left Join (cost=3D1958.66..2135.28 rows=3D5733 width=3D= 136) (actual time=3D3.427..3.863 rows=3D1415 loops=3D1) Merge Cond: ((qup_1.curr_season =3D qin_1.curr_season) AND (= (qup_1.curr_code)::text =3D (qin_1.curr_code)::text)) -> Merge Left Join (cost=3D1293.25..1388.21 rows=3D5733 wi= dth=3D104) (actual time=3D2.321..2.556 rows=3D1415 loops=3D1) Merge Cond: ((qup_1.curr_season =3D qli_1.curr_season)= AND ((qup_1.curr_code)::text =3D (qli_1.curr_code)::text)) -> Sort (cost=3D641.68..656.02 rows=3D5733 width=3D7= 2) (actual time=3D1.286..1.324 rows=3D1415 loops=3D1) Sort Key: qup_1.curr_season, qup_1.curr_code COL= LATE "C" Sort Method: quicksort Memory: 204kB -> CTE Scan on qup qup_1 (cost=3D0.00..283.80 = rows=3D5733 width=3D72) (actual time=3D0.009..1.093 rows=3D1415 loops=3D1) Filter: ((ibitmask < 0) OR (cardinality(ma= t_arr) > 21)) Rows Removed by Filter: 9320 -> Sort (cost=3D651.57..666.11 rows=3D5816 width=3D7= 2) (actual time=3D1.033..1.038 rows=3D180 loops=3D1) Sort Key: qli_1.curr_season, qli_1.curr_code COL= LATE "C" Sort Method: quicksort Memory: 41kB -> CTE Scan on qli qli_1 (cost=3D0.00..287.90 = rows=3D5816 width=3D72) (actual time=3D0.055..1.007 rows=3D180 loops=3D1) Filter: ((ibitmask < 0) OR (cardinality(ma= t_arr) > 8)) Rows Removed by Filter: 10289 -> Sort (cost=3D665.41..680.24 rows=3D5932 width=3D72) (ac= tual time=3D1.104..1.117 rows=3D481 loops=3D1) Sort Key: qin_1.curr_season, qin_1.curr_code COLLATE "= C" Sort Method: quicksort Memory: 68kB -> CTE Scan on qin qin_1 (cost=3D0.00..293.65 rows= =3D5932 width=3D72) (actual time=3D0.016..1.038 rows=3D481 loops=3D1) Filter: ((ibitmask < 0) OR (cardinality(mat_arr)= > 8)) Rows Removed by Filter: 10197 -> Sort (cost=3D666.83..681.69 rows=3D5944 width=3D72) (actual t= ime=3D1.163..1.174 rows=3D417 loops=3D1) Sort Key: qou_1.curr_season, qou_1.curr_code COLLATE "C" Sort Method: quicksort Memory: 68kB -> CTE Scan on qou qou_1 (cost=3D0.00..294.22 rows=3D5944 = width=3D72) (actual time=3D0.029..1.068 rows=3D424 loops=3D1) 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=3Dqup.curr_season and qli.curr_code=3Dqup.curr= _code and qli.ibitmask>0 and cardinality(qli.mat_arr) <=3D8) join qin on (qin.curr_season=3Dqup.curr_season and qin.curr_code=3Dqup.curr= _code and qin.ibitmask>0 and cardinality(qin.mat_arr) <=3D8) join qou on (qou.curr_season=3Dqup.curr_season and qou.curr_code=3Dqup.curr= _code and qou.ibitmask>0 and cardinality(qou.mat_arr) <=3D11) where qup.ibitmask>0 and cardinality(qup.mat_arr) <=3D21 The same rows estimation takes place as with the left joins, but the planne= r now decides to use a nested loop for the last join, which results in a 50= 0fold execution time: QUERY PLAN ---------------------------------------------------------------------------= ----------------------------------------------------------------- Append (cost=3D13365.31..17472.18 rows=3D5734 width=3D104) (actual time= =3D139.037..13403.310 rows=3D9963 loops=3D1) CTE qup -> GroupAggregate (cost=3D5231.22..6303.78 rows=3D10320 width=3D80) = (actual time=3D35.399..67.102 rows=3D10735 loops=3D1) Group Key: sa_upper.sup_season, sa_upper.sup_sa_code -> Sort (cost=3D5231.22..5358.64 rows=3D50969 width=3D18) (act= ual time=3D35.382..36.743 rows=3D50969 loops=3D1) Sort Key: sa_upper.sup_season, sa_upper.sup_sa_code COLLAT= E "C" Sort Method: quicksort Memory: 4722kB -> Hash Left Join (cost=3D41.71..1246.13 rows=3D50969 wi= dth=3D18) (actual time=3D0.157..10.715 rows=3D50969 loops=3D1) Hash Cond: ((sa_upper.sup_mat_code)::text =3D upper_= target.up_mat_code) -> Seq Scan on sa_upper (cost=3D0.00..884.69 rows= =3D50969 width=3D16) (actual time=3D0.008..2.001 rows=3D50969 loops=3D1) -> Hash (cost=3D35.53..35.53 rows=3D495 width=3D6)= (actual time=3D0.146..0.146 rows=3D495 loops=3D1) Buckets: 1024 Batches: 1 Memory Usage: 27kB -> Seq Scan on upper_target (cost=3D0.00..35= .53 rows=3D495 width=3D6) (actual time=3D0.006..0.105 rows=3D495 loops=3D1) Filter: (id_up <=3D 495) Rows Removed by Filter: 1467 CTE qli -> GroupAggregate (cost=3D1097.31..1486.56 rows=3D10469 width=3D80) = (actual time=3D9.541..27.419 rows=3D10469 loops=3D1) Group Key: sa_lining.sli_season, sa_lining.sli_sa_code -> Sort (cost=3D1097.31..1126.74 rows=3D11774 width=3D18) (act= ual time=3D9.534..9.908 rows=3D11774 loops=3D1) Sort Key: sa_lining.sli_season, sa_lining.sli_sa_code COLL= ATE "C" Sort Method: quicksort Memory: 1120kB -> Hash Left Join (cost=3D7.34..301.19 rows=3D11774 widt= h=3D18) (actual time=3D0.049..2.451 rows=3D11774 loops=3D1) Hash Cond: ((sa_lining.sli_mat_code)::text =3D linin= g_target.li_mat_code) -> Seq Scan on sa_lining (cost=3D0.00..204.74 rows= =3D11774 width=3D16) (actual time=3D0.010..0.462 rows=3D11774 loops=3D1) -> Hash (cost=3D5.86..5.86 rows=3D118 width=3D6) (= actual time=3D0.035..0.035 rows=3D119 loops=3D1) Buckets: 1024 Batches: 1 Memory Usage: 13kB -> Seq Scan on lining_target (cost=3D0.00..5= .86 rows=3D118 width=3D6) (actual time=3D0.008..0.025 rows=3D119 loops=3D1) Filter: (id_li <=3D 119) Rows Removed by Filter: 190 CTE qin -> GroupAggregate (cost=3D1427.34..1880.73 rows=3D10678 width=3D80) = (actual time=3D11.649..30.910 rows=3D10678 loops=3D1) Group Key: sa_insole.sin_season, sa_insole.sin_sa_code -> Sort (cost=3D1427.34..1465.41 rows=3D15230 width=3D18) (act= ual time=3D11.642..12.115 rows=3D15230 loops=3D1) Sort Key: sa_insole.sin_season, sa_insole.sin_sa_code COLL= ATE "C" Sort Method: quicksort Memory: 1336kB -> Hash Left Join (cost=3D10.49..369.26 rows=3D15230 wid= th=3D18) (actual time=3D0.056..3.144 rows=3D15230 loops=3D1) Hash Cond: ((sa_insole.sin_mat_code)::text =3D insol= e_target.in_mat_code) -> Seq Scan on sa_insole (cost=3D0.00..264.30 rows= =3D15230 width=3D16) (actual time=3D0.008..0.594 rows=3D15230 loops=3D1) -> Hash (cost=3D9.01..9.01 rows=3D118 width=3D6) (= actual time=3D0.045..0.046 rows=3D119 loops=3D1) Buckets: 1024 Batches: 1 Memory Usage: 13kB -> Seq Scan on insole_target (cost=3D0.00..9= .01 rows=3D118 width=3D6) (actual time=3D0.008..0.034 rows=3D119 loops=3D1) Filter: (id_in <=3D 119) Rows Removed by Filter: 362 CTE qou -> GroupAggregate (cost=3D2366.22..2986.89 rows=3D10699 width=3D80) = (actual time=3D18.163..51.151 rows=3D10699 loops=3D1) Group Key: sa_outsole.sou_season, sa_outsole.sou_sa_code -> Sort (cost=3D2366.22..2428.14 rows=3D24768 width=3D18) (act= ual time=3D18.150..20.000 rows=3D24768 loops=3D1) Sort Key: sa_outsole.sou_season, sa_outsole.sou_sa_code CO= LLATE "C" Sort Method: quicksort Memory: 2317kB -> Hash Left Join (cost=3D5.39..558.63 rows=3D24768 widt= h=3D18) (actual time=3D0.036..5.106 rows=3D24768 loops=3D1) Hash Cond: ((sa_outsole.sou_mat_code)::text =3D outs= ole_target.ou_mat_code) -> Seq Scan on sa_outsole (cost=3D0.00..430.68 row= s=3D24768 width=3D16) (actual time=3D0.008..1.005 rows=3D24768 loops=3D1) -> Hash (cost=3D5.03..5.03 rows=3D29 width=3D6) (a= ctual time=3D0.024..0.024 rows=3D29 loops=3D1) Buckets: 1024 Batches: 1 Memory Usage: 10kB -> Seq Scan on outsole_target (cost=3D0.00..= 5.03 rows=3D29 width=3D6) (actual time=3D0.007..0.018 rows=3D29 loops=3D1) Filter: (id_ou <=3D 29) Rows Removed by Filter: 213 -> Nested Loop (cost=3D707.35..1328.37 rows=3D1 width=3D104) (actual t= ime=3D139.036..13395.820 rows=3D8548 loops=3D1) Join Filter: ((qli.curr_season =3D qin.curr_season) AND ((qli.curr= _code)::text =3D (qin.curr_code)::text)) Rows Removed by Join Filter: 88552397 -> Hash Join (cost=3D707.35..1016.45 rows=3D1 width=3D216) (actu= al time=3D127.374..168.249 rows=3D8685 loops=3D1) Hash Cond: ((qou.curr_season =3D qli.curr_season) AND ((qou.= curr_code)::text =3D (qli.curr_code)::text)) -> CTE Scan on qou (cost=3D0.00..294.22 rows=3D1189 width= =3D72) (actual time=3D18.165..54.968 rows=3D10275 loops=3D1) Filter: ((ibitmask > 0) AND (cardinality(mat_arr) <=3D= 11)) Rows Removed by Filter: 424 -> Hash (cost=3D706.86..706.86 rows=3D33 width=3D144) (act= ual time=3D109.205..109.207 rows=3D9007 loops=3D1) Buckets: 16384 (originally 1024) Batches: 1 (original= ly 1) Memory Usage: 1369kB -> Merge Join (cost=3D689.20..706.86 rows=3D33 width= =3D144) (actual time=3D104.785..107.748 rows=3D9007 loops=3D1) Merge Cond: ((qup.curr_season =3D qli.curr_seaso= n) AND ((qup.curr_code)::text =3D (qli.curr_code)::text)) -> Sort (cost=3D342.09..344.96 rows=3D1147 wid= th=3D72) (actual time=3D72.320..72.559 rows=3D9320 loops=3D1) Sort Key: qup.curr_season, qup.curr_code C= OLLATE "C" Sort Method: quicksort Memory: 1357kB -> CTE Scan on qup (cost=3D0.00..283.80 = rows=3D1147 width=3D72) (actual time=3D35.401..70.834 rows=3D9320 loops=3D1= ) Filter: ((ibitmask > 0) AND (cardina= lity(mat_arr) <=3D 21)) Rows Removed by Filter: 1415 -> Sort (cost=3D347.12..350.02 rows=3D1163 wid= th=3D72) (actual time=3D32.461..32.719 rows=3D10289 loops=3D1) Sort Key: qli.curr_season, qli.curr_code C= OLLATE "C" Sort Method: quicksort Memory: 1269kB -> CTE Scan on qli (cost=3D0.00..287.90 = rows=3D1163 width=3D72) (actual time=3D9.543..30.696 rows=3D10289 loops=3D1= ) Filter: ((ibitmask > 0) AND (cardina= lity(mat_arr) <=3D 8)) Rows Removed by Filter: 180 -> CTE Scan on qin (cost=3D0.00..293.65 rows=3D1186 width=3D72) = (actual time=3D0.001..1.159 rows=3D10197 loops=3D8685) Filter: ((ibitmask > 0) AND (cardinality(mat_arr) <=3D 8)) Rows Removed by Filter: 481 -> Merge Left Join (cost=3D2625.49..3399.84 rows=3D5733 width=3D104) (= actual time=3D4.606..6.733 rows=3D1415 loops=3D1) Merge Cond: ((qup_1.curr_season =3D qou_1.curr_season) AND ((qup_1= .curr_code)::text =3D (qou_1.curr_code)::text)) -> Merge Left Join (cost=3D1958.66..2135.28 rows=3D5733 width=3D= 136) (actual time=3D3.479..3.930 rows=3D1415 loops=3D1) Merge Cond: ((qup_1.curr_season =3D qin_1.curr_season) AND (= (qup_1.curr_code)::text =3D (qin_1.curr_code)::text)) -> Merge Left Join (cost=3D1293.25..1388.21 rows=3D5733 wi= dth=3D104) (actual time=3D2.368..2.610 rows=3D1415 loops=3D1) Merge Cond: ((qup_1.curr_season =3D qli_1.curr_season)= AND ((qup_1.curr_code)::text =3D (qli_1.curr_code)::text)) -> Sort (cost=3D641.68..656.02 rows=3D5733 width=3D7= 2) (actual time=3D1.296..1.335 rows=3D1415 loops=3D1) Sort Key: qup_1.curr_season, qup_1.curr_code COL= LATE "C" Sort Method: quicksort Memory: 204kB -> CTE Scan on qup qup_1 (cost=3D0.00..283.80 = rows=3D5733 width=3D72) (actual time=3D0.010..1.119 rows=3D1415 loops=3D1) Filter: ((ibitmask < 0) OR (cardinality(ma= t_arr) > 21)) Rows Removed by Filter: 9320 -> Sort (cost=3D651.57..666.11 rows=3D5816 width=3D7= 2) (actual time=3D1.069..1.075 rows=3D180 loops=3D1) Sort Key: qli_1.curr_season, qli_1.curr_code COL= LATE "C" Sort Method: quicksort Memory: 41kB -> CTE Scan on qli qli_1 (cost=3D0.00..287.90 = rows=3D5816 width=3D72) (actual time=3D0.057..1.026 rows=3D180 loops=3D1) Filter: ((ibitmask < 0) OR (cardinality(ma= t_arr) > 8)) Rows Removed by Filter: 10289 -> Sort (cost=3D665.41..680.24 rows=3D5932 width=3D72) (ac= tual time=3D1.110..1.124 rows=3D481 loops=3D1) Sort Key: qin_1.curr_season, qin_1.curr_code COLLATE "= C" Sort Method: quicksort Memory: 68kB -> CTE Scan on qin qin_1 (cost=3D0.00..293.65 rows= =3D5932 width=3D72) (actual time=3D0.016..1.046 rows=3D481 loops=3D1) Filter: ((ibitmask < 0) OR (cardinality(mat_arr)= > 8)) Rows Removed by Filter: 10197 -> Sort (cost=3D666.83..681.69 rows=3D5944 width=3D72) (actual t= ime=3D1.119..1.128 rows=3D417 loops=3D1) Sort Key: qou_1.curr_season, qou_1.curr_code COLLATE "C" Sort Method: quicksort Memory: 68kB -> CTE Scan on qou qou_1 (cost=3D0.00..294.22 rows=3D5944 = width=3D72) (actual time=3D0.029..1.056 rows=3D424 loops=3D1) 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 a= fter 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_se= ason an xx_code). Thanks for looking Hans Buschmann --_000_4504e67078d648cdac3651b2960da6e7nidsanet_ Content-Type: text/html; charset="iso-8859-1" Content-Transfer-Encoding: quoted-printable

During data refactoring of our Application I encountered $subject when j= oining 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) b= y 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=3Dqup.curr_season and qli.curr_code=3Dqup.curr= _code and qli.ibitmask>0 and cardinality(qli.mat_arr) <=3D8)
left = join qin on (qin.curr_season=3Dqup.curr_season and qin.curr_code=3Dqup.curr= _code and qin.ibitmask>0 and cardinality(qin.mat_arr) <=3D8)
left = join qou on (qou.curr_season=3Dqup.curr_season and qou.curr_code=3Dqup.curr= _code and qou.ibitmask>0 and cardinality(qou.mat_arr) <=3D11)<= /div>
where= qup.ibitmask>0 and cardinality(qup.mat_arr) <=3D21

The plan first retrieves qup and qli, taking the estimated row counts of 11= 63 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 ta= ble (here qup with 1163 rows)


The same algorithm which reduces the row estimation from 1163 to 33 is u= sed 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=3D33)


                    =                      = ;                     &nb= sp;     QUERY PLAN
----------------------------------------------------------------------= ---------------------------------------------------------------------------= -
 Append  (cost=3D13673.81..17463.30 rows=3D5734 width=3D104)= (actual time=3D168.307..222.670 rows=3D9963 loops=3D1)
   CTE qup
     ->  GroupAggregate  (cost=3D5231.22..= 6303.78 rows=3D10320 width=3D80) (actual time=3D35.466..68.131 rows=3D10735= loops=3D1)
           Group Key: sa_upper.sup_seaso= n, sa_upper.sup_sa_code
           ->  Sort  (cost= =3D5231.22..5358.64 rows=3D50969 width=3D18) (actual time=3D35.454..36.819 = rows=3D50969 loops=3D1)
                 Sort Key= : sa_upper.sup_season, sa_upper.sup_sa_code COLLATE "C"
                 Sort Met= hod: quicksort  Memory: 4722kB
                 ->&nb= sp; Hash Left Join  (cost=3D41.71..1246.13 rows=3D50969 width=3D18) (a= ctual time=3D0.148..10.687 rows=3D50969 loops=3D1)
                    =    Hash Cond: ((sa_upper.sup_mat_code)::text =3D upper_target.up_= mat_code)
                    =    ->  Seq Scan on sa_upper  (cost=3D0.00..884.69 ro= ws=3D50969 width=3D16) (actual time=3D0.005..1.972 rows=3D50969 loops=3D1)<= /div>
                    =    ->  Hash  (cost=3D35.53..35.53 rows=3D495 width= =3D6) (actual time=3D0.140..0.140 rows=3D495 loops=3D1)
                    =          Buckets: 1024  Batches: 1  Memo= ry Usage: 27kB
                    =          ->  Seq Scan on upper_target = ; (cost=3D0.00..35.53 rows=3D495 width=3D6) (actual time=3D0.007..0.103 row= s=3D495 loops=3D1)
                    =                Filter: (id_up <= =3D 495)
                    =                Rows Removed by Filt= er: 1467
   CTE qli
     ->  GroupAggregate  (cost=3D1097.31..= 1486.56 rows=3D10469 width=3D80) (actual time=3D9.446..27.388 rows=3D10469 = loops=3D1)
           Group Key: sa_lining.sli_seas= on, sa_lining.sli_sa_code
           ->  Sort  (cost= =3D1097.31..1126.74 rows=3D11774 width=3D18) (actual time=3D9.440..9.811 ro= ws=3D11774 loops=3D1)
                 Sort Key= : sa_lining.sli_season, sa_lining.sli_sa_code COLLATE "C"
                 Sort Met= hod: quicksort  Memory: 1120kB
                 ->&nb= sp; Hash Left Join  (cost=3D7.34..301.19 rows=3D11774 width=3D18) (act= ual time=3D0.045..2.438 rows=3D11774 loops=3D1)
                    =    Hash Cond: ((sa_lining.sli_mat_code)::text =3D lining_target.l= i_mat_code)
                    =    ->  Seq Scan on sa_lining  (cost=3D0.00..204.74 r= ows=3D11774 width=3D16) (actual time=3D0.008..0.470 rows=3D11774 loops=3D1)=
                    =    ->  Hash  (cost=3D5.86..5.86 rows=3D118 width=3D6= ) (actual time=3D0.034..0.034 rows=3D119 loops=3D1)
                    =          Buckets: 1024  Batches: 1  Memo= ry Usage: 13kB
                    =          ->  Seq Scan on lining_target&nbs= p; (cost=3D0.00..5.86 rows=3D118 width=3D6) (actual time=3D0.008..0.024 row= s=3D119 loops=3D1)
                    =                Filter: (id_li <= =3D 119)
                    =                Rows Removed by Filt= er: 190
   CTE qin
     ->  GroupAggregate  (cost=3D1427.34..= 1880.73 rows=3D10678 width=3D80) (actual time=3D11.424..31.508 rows=3D10678= loops=3D1)
           Group Key: sa_insole.sin_seas= on, sa_insole.sin_sa_code
           ->  Sort  (cost= =3D1427.34..1465.41 rows=3D15230 width=3D18) (actual time=3D11.416..11.908 = rows=3D15230 loops=3D1)
                 Sort Key= : sa_insole.sin_season, sa_insole.sin_sa_code COLLATE "C"
                 Sort Met= hod: quicksort  Memory: 1336kB
                 ->&nb= sp; Hash Left Join  (cost=3D10.49..369.26 rows=3D15230 width=3D18) (ac= tual time=3D0.051..3.108 rows=3D15230 loops=3D1)
                    =    Hash Cond: ((sa_insole.sin_mat_code)::text =3D insole_target.i= n_mat_code)
                    =    ->  Seq Scan on sa_insole  (cost=3D0.00..264.30 r= ows=3D15230 width=3D16) (actual time=3D0.006..0.606 rows=3D15230 loops=3D1)=
                    =    ->  Hash  (cost=3D9.01..9.01 rows=3D118 width=3D6= ) (actual time=3D0.042..0.043 rows=3D119 loops=3D1)
                    =          Buckets: 1024  Batches: 1  Memo= ry Usage: 13kB
                    =          ->  Seq Scan on insole_target&nbs= p; (cost=3D0.00..9.01 rows=3D118 width=3D6) (actual time=3D0.008..0.032 row= s=3D119 loops=3D1)
                    =                Filter: (id_in <= =3D 119)
                    =                Rows Removed by Filt= er: 362
   CTE qou
     ->  GroupAggregate  (cost=3D2366.22..= 2986.89 rows=3D10699 width=3D80) (actual time=3D18.198..41.812 rows=3D10699= loops=3D1)
           Group Key: sa_outsole.sou_sea= son, sa_outsole.sou_sa_code
           ->  Sort  (cost= =3D2366.22..2428.14 rows=3D24768 width=3D18) (actual time=3D18.187..18.967 = rows=3D24768 loops=3D1)
                 Sort Key= : sa_outsole.sou_season, sa_outsole.sou_sa_code COLLATE "C"
                 Sort Met= hod: quicksort  Memory: 2317kB
                 ->&nb= sp; Hash Left Join  (cost=3D5.39..558.63 rows=3D24768 width=3D18) (act= ual time=3D0.046..5.132 rows=3D24768 loops=3D1)
                    =    Hash Cond: ((sa_outsole.sou_mat_code)::text =3D outsole_target= .ou_mat_code)
                    =    ->  Seq Scan on sa_outsole  (cost=3D0.00..430.68 = rows=3D24768 width=3D16) (actual time=3D0.010..1.015 rows=3D24768 loops=3D1= )
                    =    ->  Hash  (cost=3D5.03..5.03 rows=3D29 width=3D6)= (actual time=3D0.032..0.032 rows=3D29 loops=3D1)
                    =          Buckets: 1024  Batches: 1  Memo= ry Usage: 10kB
                    =          ->  Seq Scan on outsole_target&nb= sp; (cost=3D0.00..5.03 rows=3D29 width=3D6) (actual time=3D0.010..0.025 row= s=3D29 loops=3D1)
                    =                Filter: (id_ou <= =3D 29)
                    =                Rows Removed by Filt= er: 213
   ->  Hash Join  (cost=3D1015.85..1319.50 rows= =3D1 width=3D104) (actual time=3D168.307..215.513 rows=3D8548 loops=3D1)
         Hash Cond: ((qou.curr_season =3D qli= .curr_season) AND ((qou.curr_code)::text =3D (qli.curr_code)::text))
         Join Filter: ((((qup.ibitmask | qin.= ibitmask) | qli.ibitmask) | qou.ibitmask) IS NOT NULL)
         ->  CTE Scan on qou  (c= ost=3D0.00..294.22 rows=3D1189 width=3D76) (actual time=3D18.200..45.188 ro= ws=3D10275 loops=3D1)
               Filter: ((ibitm= ask > 0) AND (cardinality(mat_arr) <=3D 11))
               Rows Removed by= Filter: 424
         ->  Hash  (cost=3D1015.= 83..1015.83 rows=3D1 width=3D228) (actual time=3D150.094..150.095 rows=3D88= 45 loops=3D1)
               Buckets: 16384 = (originally 1024)  Batches: 1 (originally 1)  Memory Usage: 1899k= B
               ->  Has= h Join  (cost=3D707.35..1015.83 rows=3D1 width=3D228) (actual time=3D1= 21.898..147.726 rows=3D8845 loops=3D1)
                    =  Hash Cond: ((qin.curr_season =3D qli.curr_season) AND ((qin.curr_code= )::text =3D (qli.curr_code)::text))
                    =  ->  CTE Scan on qin  (cost=3D0.00..293.65 rows=3D1186 wi= dth=3D76) (actual time=3D11.425..34.674 rows=3D10197 loops=3D1)
                    =        Filter: ((ibitmask > 0) AND (cardinality(mat_= arr) <=3D 8))
                    =        Rows Removed by Filter: 481
                    =  ->  Hash  (cost=3D706.86..706.86 rows=3D33 width=3D152) = (actual time=3D110.470..110.470 rows=3D9007 loops=3D1)
                    =        Buckets: 16384 (originally 1024)  Batches: = 1 (originally 1)  Memory Usage: 1473kB
                    =        ->  Merge Join  (cost=3D689.20..706= .86 rows=3D33 width=3D152) (actual time=3D105.862..108.925 rows=3D9007 loop= s=3D1)
                    =              Merge Cond: ((qup.curr_seas= on =3D qli.curr_season) AND ((qup.curr_code)::text =3D (qli.curr_code)::tex= t))
                    =              ->  Sort  (cos= t=3D342.09..344.96 rows=3D1147 width=3D76) (actual time=3D73.419..73.653 ro= ws=3D9320 loops=3D1)
                    =                    Sort K= ey: qup.curr_season, qup.curr_code COLLATE "C"
                    =                    Sort M= ethod: quicksort  Memory: 1391kB
                    =                    ->&= nbsp; CTE Scan on qup  (cost=3D0.00..283.80 rows=3D1147 width=3D76) (a= ctual time=3D35.467..71.904 rows=3D9320 loops=3D1)
                    =                      = ;    Filter: ((ibitmask > 0) AND (cardinality(mat_arr) <=3D= 21))
                    =                      = ;    Rows Removed by Filter: 1415
                    =              ->  Sort  (cos= t=3D347.12..350.02 rows=3D1163 width=3D76) (actual time=3D32.440..32.697 ro= ws=3D10289 loops=3D1)
                    =                    Sort K= ey: qli.curr_season, qli.curr_code COLLATE "C"
                    =                    Sort M= ethod: quicksort  Memory: 1349kB
                    =                    ->&= nbsp; CTE Scan on qli  (cost=3D0.00..287.90 rows=3D1163 width=3D76) (a= ctual time=3D9.447..30.666 rows=3D10289 loops=3D1)
                    =                      = ;    Filter: ((ibitmask > 0) AND (cardinality(mat_arr) <=3D= 8))
                    =                      = ;    Rows Removed by Filter: 180
   ->  Merge Left Join  (cost=3D2625.49..3399.8= 4 rows=3D5733 width=3D104) (actual time=3D4.597..6.700 rows=3D1415 loops=3D= 1)
         Merge Cond: ((qup_1.curr_season =3D = qou_1.curr_season) AND ((qup_1.curr_code)::text =3D (qou_1.curr_code)::text= ))
         ->  Merge Left Join  (c= ost=3D1958.66..2135.28 rows=3D5733 width=3D136) (actual time=3D3.427..3.863= rows=3D1415 loops=3D1)
               Merge Cond: ((q= up_1.curr_season =3D qin_1.curr_season) AND ((qup_1.curr_code)::text =3D (q= in_1.curr_code)::text))
               ->  Mer= ge Left Join  (cost=3D1293.25..1388.21 rows=3D5733 width=3D104) (actua= l time=3D2.321..2.556 rows=3D1415 loops=3D1)
                    =  Merge Cond: ((qup_1.curr_season =3D qli_1.curr_season) AND ((qup_1.cu= rr_code)::text =3D (qli_1.curr_code)::text))
                    =  ->  Sort  (cost=3D641.68..656.02 rows=3D5733 width=3D72)= (actual time=3D1.286..1.324 rows=3D1415 loops=3D1)
                    =        Sort Key: qup_1.curr_season, qup_1.curr_code COL= LATE "C"
                    =        Sort Method: quicksort  Memory: 204kB
                    =        ->  CTE Scan on qup qup_1  (cost=3D= 0.00..283.80 rows=3D5733 width=3D72) (actual time=3D0.009..1.093 rows=3D141= 5 loops=3D1)
                    =              Filter: ((ibitmask < 0) = OR (cardinality(mat_arr) > 21))
                    =              Rows Removed by Filter: 932= 0
                    =  ->  Sort  (cost=3D651.57..666.11 rows=3D5816 width=3D72)= (actual time=3D1.033..1.038 rows=3D180 loops=3D1)
                    =        Sort Key: qli_1.curr_season, qli_1.curr_code COL= LATE "C"
                    =        Sort Method: quicksort  Memory: 41kB
                    =        ->  CTE Scan on qli qli_1  (cost=3D= 0.00..287.90 rows=3D5816 width=3D72) (actual time=3D0.055..1.007 rows=3D180= loops=3D1)
                    =              Filter: ((ibitmask < 0) = OR (cardinality(mat_arr) > 8))
                    =              Rows Removed by Filter: 102= 89
               ->  Sor= t  (cost=3D665.41..680.24 rows=3D5932 width=3D72) (actual time=3D1.104= ..1.117 rows=3D481 loops=3D1)
                    =  Sort Key: qin_1.curr_season, qin_1.curr_code COLLATE "C"
                    =  Sort Method: quicksort  Memory: 68kB
                    =  ->  CTE Scan on qin qin_1  (cost=3D0.00..293.65 rows=3D5= 932 width=3D72) (actual time=3D0.016..1.038 rows=3D481 loops=3D1)
                    =        Filter: ((ibitmask < 0) OR (cardinality(mat_a= rr) > 8))
                    =        Rows Removed by Filter: 10197
         ->  Sort  (cost=3D666.8= 3..681.69 rows=3D5944 width=3D72) (actual time=3D1.163..1.174 rows=3D417 lo= ops=3D1)
               Sort Key: qou_1= .curr_season, qou_1.curr_code COLLATE "C"
               Sort Method: qu= icksort  Memory: 68kB
               ->  CTE= Scan on qou qou_1  (cost=3D0.00..294.22 rows=3D5944 width=3D72) (actu= al time=3D0.029..1.068 rows=3D424 loops=3D1)
                    =  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 joi= n

When the 3 left joins above are changed to inner joins like:

from = qup
join = qli on (qli.curr_season=3Dqup.curr_season and qli.curr_code=3Dqup.curr_code= and qli.ibitmask>0 and cardinality(qli.mat_arr) <=3D8)
join = qin on (qin.curr_season=3Dqup.curr_season and qin.curr_code=3Dqup.curr_code= and qin.ibitmask>0 and cardinality(qin.mat_arr) <=3D8)
join = qou on (qou.curr_season=3Dqup.curr_season and qou.curr_code=3Dqup.curr_code= and qou.ibitmask>0 and cardinality(qou.mat_arr) <=3D11)
where= qup.ibitmask>0 and cardinality(qup.mat_arr) <=3D21

The same rows estimation takes place as with the left joins, but the planne= r now decides to use a nested loop for the last join, which results in a 50= 0fold execution time:

                    =                      = ;                     &nb= sp;  QUERY PLAN
----------------------------------------------------------------------= ----------------------------------------------------------------------
 Append  (cost=3D13365.31..17472.18 rows=3D5734 width=3D104)= (actual time=3D139.037..13403.310 rows=3D9963 loops=3D1)
   CTE qup
     ->  GroupAggregate  (cost=3D5231.22..= 6303.78 rows=3D10320 width=3D80) (actual time=3D35.399..67.102 rows=3D10735= loops=3D1)
           Group Key: sa_upper.sup_seaso= n, sa_upper.sup_sa_code
           ->  Sort  (cost= =3D5231.22..5358.64 rows=3D50969 width=3D18) (actual time=3D35.382..36.743 = rows=3D50969 loops=3D1)
                 Sort Key= : sa_upper.sup_season, sa_upper.sup_sa_code COLLATE "C"
                 Sort Met= hod: quicksort  Memory: 4722kB
                 ->&nb= sp; Hash Left Join  (cost=3D41.71..1246.13 rows=3D50969 width=3D18) (a= ctual time=3D0.157..10.715 rows=3D50969 loops=3D1)
                    =    Hash Cond: ((sa_upper.sup_mat_code)::text =3D upper_target.up_= mat_code)
                    =    ->  Seq Scan on sa_upper  (cost=3D0.00..884.69 ro= ws=3D50969 width=3D16) (actual time=3D0.008..2.001 rows=3D50969 loops=3D1)<= /div>
                    =    ->  Hash  (cost=3D35.53..35.53 rows=3D495 width= =3D6) (actual time=3D0.146..0.146 rows=3D495 loops=3D1)
                    =          Buckets: 1024  Batches: 1  Memo= ry Usage: 27kB
                    =          ->  Seq Scan on upper_target = ; (cost=3D0.00..35.53 rows=3D495 width=3D6) (actual time=3D0.006..0.105 row= s=3D495 loops=3D1)
                    =                Filter: (id_up <= =3D 495)
                    =                Rows Removed by Filt= er: 1467
   CTE qli
     ->  GroupAggregate  (cost=3D1097.31..= 1486.56 rows=3D10469 width=3D80) (actual time=3D9.541..27.419 rows=3D10469 = loops=3D1)
           Group Key: sa_lining.sli_seas= on, sa_lining.sli_sa_code
           ->  Sort  (cost= =3D1097.31..1126.74 rows=3D11774 width=3D18) (actual time=3D9.534..9.908 ro= ws=3D11774 loops=3D1)
                 Sort Key= : sa_lining.sli_season, sa_lining.sli_sa_code COLLATE "C"
                 Sort Met= hod: quicksort  Memory: 1120kB
                 ->&nb= sp; Hash Left Join  (cost=3D7.34..301.19 rows=3D11774 width=3D18) (act= ual time=3D0.049..2.451 rows=3D11774 loops=3D1)
                    =    Hash Cond: ((sa_lining.sli_mat_code)::text =3D lining_target.l= i_mat_code)
                    =    ->  Seq Scan on sa_lining  (cost=3D0.00..204.74 r= ows=3D11774 width=3D16) (actual time=3D0.010..0.462 rows=3D11774 loops=3D1)=
                    =    ->  Hash  (cost=3D5.86..5.86 rows=3D118 width=3D6= ) (actual time=3D0.035..0.035 rows=3D119 loops=3D1)
                    =          Buckets: 1024  Batches: 1  Memo= ry Usage: 13kB
                    =          ->  Seq Scan on lining_target&nbs= p; (cost=3D0.00..5.86 rows=3D118 width=3D6) (actual time=3D0.008..0.025 row= s=3D119 loops=3D1)
                    =                Filter: (id_li <= =3D 119)
                    =                Rows Removed by Filt= er: 190
   CTE qin
     ->  GroupAggregate  (cost=3D1427.34..= 1880.73 rows=3D10678 width=3D80) (actual time=3D11.649..30.910 rows=3D10678= loops=3D1)
           Group Key: sa_insole.sin_seas= on, sa_insole.sin_sa_code
           ->  Sort  (cost= =3D1427.34..1465.41 rows=3D15230 width=3D18) (actual time=3D11.642..12.115 = rows=3D15230 loops=3D1)
                 Sort Key= : sa_insole.sin_season, sa_insole.sin_sa_code COLLATE "C"
                 Sort Met= hod: quicksort  Memory: 1336kB
                 ->&nb= sp; Hash Left Join  (cost=3D10.49..369.26 rows=3D15230 width=3D18) (ac= tual time=3D0.056..3.144 rows=3D15230 loops=3D1)
                    =    Hash Cond: ((sa_insole.sin_mat_code)::text =3D insole_target.i= n_mat_code)
                    =    ->  Seq Scan on sa_insole  (cost=3D0.00..264.30 r= ows=3D15230 width=3D16) (actual time=3D0.008..0.594 rows=3D15230 loops=3D1)=
                    =    ->  Hash  (cost=3D9.01..9.01 rows=3D118 width=3D6= ) (actual time=3D0.045..0.046 rows=3D119 loops=3D1)
                    =          Buckets: 1024  Batches: 1  Memo= ry Usage: 13kB
                    =          ->  Seq Scan on insole_target&nbs= p; (cost=3D0.00..9.01 rows=3D118 width=3D6) (actual time=3D0.008..0.034 row= s=3D119 loops=3D1)
                    =                Filter: (id_in <= =3D 119)
                    =                Rows Removed by Filt= er: 362
   CTE qou
     ->  GroupAggregate  (cost=3D2366.22..= 2986.89 rows=3D10699 width=3D80) (actual time=3D18.163..51.151 rows=3D10699= loops=3D1)
           Group Key: sa_outsole.sou_sea= son, sa_outsole.sou_sa_code
           ->  Sort  (cost= =3D2366.22..2428.14 rows=3D24768 width=3D18) (actual time=3D18.150..20.000 = rows=3D24768 loops=3D1)
                 Sort Key= : sa_outsole.sou_season, sa_outsole.sou_sa_code COLLATE "C"
                 Sort Met= hod: quicksort  Memory: 2317kB
                 ->&nb= sp; Hash Left Join  (cost=3D5.39..558.63 rows=3D24768 width=3D18) (act= ual time=3D0.036..5.106 rows=3D24768 loops=3D1)
                    =    Hash Cond: ((sa_outsole.sou_mat_code)::text =3D outsole_target= .ou_mat_code)
                    =    ->  Seq Scan on sa_outsole  (cost=3D0.00..430.68 = rows=3D24768 width=3D16) (actual time=3D0.008..1.005 rows=3D24768 loops=3D1= )
                    =    ->  Hash  (cost=3D5.03..5.03 rows=3D29 width=3D6)= (actual time=3D0.024..0.024 rows=3D29 loops=3D1)
                    =          Buckets: 1024  Batches: 1  Memo= ry Usage: 10kB
                    =          ->  Seq Scan on outsole_target&nb= sp; (cost=3D0.00..5.03 rows=3D29 width=3D6) (actual time=3D0.007..0.018 row= s=3D29 loops=3D1)
                    =                Filter: (id_ou <= =3D 29)
                    =                Rows Removed by Filt= er: 213
   ->  Nested Loop  (cost=3D707.35..1328.37 row= s=3D1 width=3D104) (actual time=3D139.036..13395.820 rows=3D8548 loops=3D1)=
         Join Filter: ((qli.curr_season =3D q= in.curr_season) AND ((qli.curr_code)::text =3D (qin.curr_code)::text))
         Rows Removed by Join Filter: 8855239= 7
         ->  Hash Join  (cost=3D= 707.35..1016.45 rows=3D1 width=3D216) (actual time=3D127.374..168.249 rows= =3D8685 loops=3D1)
               Hash Cond: ((qo= u.curr_season =3D qli.curr_season) AND ((qou.curr_code)::text =3D (qli.curr= _code)::text))
               ->  CTE= Scan on qou  (cost=3D0.00..294.22 rows=3D1189 width=3D72) (actual tim= e=3D18.165..54.968 rows=3D10275 loops=3D1)
                    =  Filter: ((ibitmask > 0) AND (cardinality(mat_arr) <=3D 11))
                    =  Rows Removed by Filter: 424
               ->  Has= h  (cost=3D706.86..706.86 rows=3D33 width=3D144) (actual time=3D109.20= 5..109.207 rows=3D9007 loops=3D1)
                    =  Buckets: 16384 (originally 1024)  Batches: 1 (originally 1) = ; Memory Usage: 1369kB
                    =  ->  Merge Join  (cost=3D689.20..706.86 rows=3D33 width= =3D144) (actual time=3D104.785..107.748 rows=3D9007 loops=3D1)
                    =        Merge Cond: ((qup.curr_season =3D qli.curr_seaso= n) AND ((qup.curr_code)::text =3D (qli.curr_code)::text))
                    =        ->  Sort  (cost=3D342.09..344.96 ro= ws=3D1147 width=3D72) (actual time=3D72.320..72.559 rows=3D9320 loops=3D1)<= /div>
                    =              Sort Key: qup.curr_season, = qup.curr_code COLLATE "C"
                    =              Sort Method: quicksort = ; Memory: 1357kB
                    =              ->  CTE Scan on qup=   (cost=3D0.00..283.80 rows=3D1147 width=3D72) (actual time=3D35.401..= 70.834 rows=3D9320 loops=3D1)
                    =                    Filter= : ((ibitmask > 0) AND (cardinality(mat_arr) <=3D 21))
                    =                    Rows R= emoved by Filter: 1415
                    =        ->  Sort  (cost=3D347.12..350.02 ro= ws=3D1163 width=3D72) (actual time=3D32.461..32.719 rows=3D10289 loops=3D1)=
                    =              Sort Key: qli.curr_season, = qli.curr_code COLLATE "C"
                    =              Sort Method: quicksort = ; Memory: 1269kB
                    =              ->  CTE Scan on qli=   (cost=3D0.00..287.90 rows=3D1163 width=3D72) (actual time=3D9.543..3= 0.696 rows=3D10289 loops=3D1)
                    =                    Filter= : ((ibitmask > 0) AND (cardinality(mat_arr) <=3D 8))
                    =                    Rows R= emoved by Filter: 180
         ->  CTE Scan on qin  (c= ost=3D0.00..293.65 rows=3D1186 width=3D72) (actual time=3D0.001..1.159 rows= =3D10197 loops=3D8685)
               Filter: ((ibitm= ask > 0) AND (cardinality(mat_arr) <=3D 8))
               Rows Removed by= Filter: 481
   ->  Merge Left Join  (cost=3D2625.49..3399.8= 4 rows=3D5733 width=3D104) (actual time=3D4.606..6.733 rows=3D1415 loops=3D= 1)
         Merge Cond: ((qup_1.curr_season =3D = qou_1.curr_season) AND ((qup_1.curr_code)::text =3D (qou_1.curr_code)::text= ))
         ->  Merge Left Join  (c= ost=3D1958.66..2135.28 rows=3D5733 width=3D136) (actual time=3D3.479..3.930= rows=3D1415 loops=3D1)
               Merge Cond: ((q= up_1.curr_season =3D qin_1.curr_season) AND ((qup_1.curr_code)::text =3D (q= in_1.curr_code)::text))
               ->  Mer= ge Left Join  (cost=3D1293.25..1388.21 rows=3D5733 width=3D104) (actua= l time=3D2.368..2.610 rows=3D1415 loops=3D1)
                    =  Merge Cond: ((qup_1.curr_season =3D qli_1.curr_season) AND ((qup_1.cu= rr_code)::text =3D (qli_1.curr_code)::text))
                    =  ->  Sort  (cost=3D641.68..656.02 rows=3D5733 width=3D72)= (actual time=3D1.296..1.335 rows=3D1415 loops=3D1)
                    =        Sort Key: qup_1.curr_season, qup_1.curr_code COL= LATE "C"
                    =        Sort Method: quicksort  Memory: 204kB
                    =        ->  CTE Scan on qup qup_1  (cost=3D= 0.00..283.80 rows=3D5733 width=3D72) (actual time=3D0.010..1.119 rows=3D141= 5 loops=3D1)
                    =              Filter: ((ibitmask < 0) = OR (cardinality(mat_arr) > 21))
                    =              Rows Removed by Filter: 932= 0
                    =  ->  Sort  (cost=3D651.57..666.11 rows=3D5816 width=3D72)= (actual time=3D1.069..1.075 rows=3D180 loops=3D1)
                    =        Sort Key: qli_1.curr_season, qli_1.curr_code COL= LATE "C"
                    =        Sort Method: quicksort  Memory: 41kB
                    =        ->  CTE Scan on qli qli_1  (cost=3D= 0.00..287.90 rows=3D5816 width=3D72) (actual time=3D0.057..1.026 rows=3D180= loops=3D1)
                    =              Filter: ((ibitmask < 0) = OR (cardinality(mat_arr) > 8))
                    =              Rows Removed by Filter: 102= 89
               ->  Sor= t  (cost=3D665.41..680.24 rows=3D5932 width=3D72) (actual time=3D1.110= ..1.124 rows=3D481 loops=3D1)
                    =  Sort Key: qin_1.curr_season, qin_1.curr_code COLLATE "C"
                    =  Sort Method: quicksort  Memory: 68kB
                    =  ->  CTE Scan on qin qin_1  (cost=3D0.00..293.65 rows=3D5= 932 width=3D72) (actual time=3D0.016..1.046 rows=3D481 loops=3D1)
                    =        Filter: ((ibitmask < 0) OR (cardinality(mat_a= rr) > 8))
                    =        Rows Removed by Filter: 10197
         ->  Sort  (cost=3D666.8= 3..681.69 rows=3D5944 width=3D72) (actual time=3D1.119..1.128 rows=3D417 lo= ops=3D1)
               Sort Key: qou_1= .curr_season, qou_1.curr_code COLLATE "C"
               Sort Method: qu= icksort  Memory: 68kB
               ->  CTE= Scan on qou qou_1  (cost=3D0.00..294.22 rows=3D5944 width=3D72) (actu= al time=3D0.029..1.056 rows=3D424 loops=3D1)
                    =  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 st= eps after left joins at all, and be a bit more conservative on inner j= oins.
This may be related to the fact that this case has 2 join-conditions (= xx_season an xx_code).

Thanks for looking

Hans Buschmann




--_000_4504e67078d648cdac3651b2960da6e7nidsanet_--