On Tuesday, July 20th, 2021 at 16:34, Tom Lane <tgl@xxxxxxxxxxxxx> wrote: > Nicolas Seinlet nicolas@xxxxxxxxxxx writes: > > > I'm facing a strange behaviour and I don't understand why. From a wider query plan, https://explain.dalibo.com/plan/7yh ,a table is scanned and filter based on its primary key, returning 98 records. Then, those records are sorted on another field, and the output is 758,247,643 records. > > You haven't showed us the whole plan; tsk tsk. > > However, I'm going to bet that this sort is the inner input of a merge > > join, and that the apparent "extra" tuples are caused by the merge backing > > up and rescanning the sort result to deal with duplicate join keys in its > > outer input. There must be a heck of a lot of duplicates. The planner > > would typically avoid using merge join if it knew that, so I'm wondering > > if you have up-to-date statistics for the outer side of the join. > > regards, tom lane Many thanks for the fast response. The full query plan is below, and the Dalibo link points to it as well. You're right, it's a merge join. One table doesn't have up-to-date statistics, because the table is filled during the transaction involving this query. A workaround we found, but we don't understand why and it's frustrating, is to remove a join from the query (the one with currency table) and replace it with a new column in the table for which the sort generates records. the currency table has up to date statistics, is vacuumed, ... The table with no up-to-date statistics is involved in a where clause: AND NOT EXISTS (SELECT invl_id FROM invl_aml_mapping WHERE invl_id=il.id) AND NOT EXISTS (SELECT aml_id FROM invl_aml_mapping WHERE aml_id=ml.id) Query: INSERT INTO invl_aml_mapping_temp(invl_id, aml_id, cond) SELECT il.id, ml.id, 48 FROM account_invoice_line il JOIN account_invoice i ON i.id = il.invoice_id JOIN account_move m ON m.id = i.move_id JOIN account_move_line ml ON ml.move_id = m.id JOIN res_company comp ON comp.id = i.company_id WHERE il.display_type IS NULL AND ml.tax_line_id IS NULL AND NOT EXISTS (SELECT invl_id FROM invl_aml_mapping WHERE invl_id=il.id) AND NOT EXISTS (SELECT aml_id FROM invl_aml_mapping WHERE aml_id=ml.id) AND i.id = ANY(ARRAY[3839,6912,7164,11026,6479,9973,3599,11306,4092,10652,2732,8625,198,10536,5876,7864,5498,13080,4660,8948,597,10762,12573,11613,12905,11483,11227,12876,4470,8954,5628,4386,9321,4398,11595,7125,8116,1231,1610,6828,2105,9658,1616,5735,9066,4631,51,1185,11736,9579,4303,9983,12759,3889,4667,11258,9423,11855,6555,3164,7597,5596,10396,620,10330,4082,926,3252,5375,8118,7141,3500,12065,2794,2367,12088,7454,7998,11439,8893,6304,10814,7723,4393,3901,3383,5770,5857,4746,1858,5320,4574,8314,11914,7930,7308,6228,3627]) AND il.account_id = ml.account_id AND ( ARRAY(SELECT r.tax_id FROM account_invoice_line_tax r WHERE r.invoice_line_id = il.id ORDER BY r.tax_id) = ARRAY(SELECT r.account_tax_id FROM account_move_line_account_tax_rel r WHERE r.account_move_line_id = ml.id ORDER BY r.account_tax_id) ) AND ROUND(il.price_subtotal - ml._mig_124_precomputed_amount, i.decimal_places) = 0.0 AND il.product_id = ml.product_id; Plan: ----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------- ------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------ Insert on invl_aml_mapping_temp (cost=241.69..505.08 rows=1 width=12) (actual time=185635.849..185635.883 rows=0 loops=1) -> Nested Loop Anti Join (cost=241.69..505.08 rows=1 width=12) (actual time=185635.847..185635.880 rows=0 loops=1) -> Nested Loop (cost=241.27..504.61 rows=1 width=8) (actual time=185635.846..185635.873 rows=0 loops=1) -> Nested Loop (cost=241.15..504.43 rows=1 width=12) (actual time=185635.839..185635.866 rows=0 loops=1) Join Filter: (i.move_id = m.id) -> Merge Join (cost=240.73..503.98 rows=1 width=20) (actual time=185635.815..185635.836 rows=0 loops=1) Merge Cond: (res_currency.id = i.currency_id) Join Filter: ((il.invoice_id = i.id) AND (ml.move_id = i.move_id)) Rows Removed by Join Filter: 766064628 -> Nested Loop (cost=12.22..16793.68 rows=64 width=20) (actual time=7.908..91222.587 rows=39084931 loops=1) Join Filter: (round((il.price_subtotal - ml._mig_124_precomputed_amount), res_currency.decimal_places) = 0.0) Rows Removed by Join Filter: 2990678 -> Index Scan using res_currency_pkey on res_currency (cost=0.14..14.27 rows=176 width=8) (actual time=0.015..0.254 rows=6 loops=1) -> Materialize (cost=12.07..16522.64 rows=73 width=28) (actual time=0.386..13293.316 rows=7012602 loops=6) -> Nested Loop (cost=12.07..16522.27 rows=73 width=28) (actual time=2.262..74119.980 rows=8415119 loops=1) -> Nested Loop Anti Join (cost=0.42..11037.09 rows=306 width=22) (actual time=0.063..481.749 rows=54864 loops=1) -> Seq Scan on account_invoice_line il (cost=0.00..9885.48 rows=752 width=22) (actual time=0.049..121.238 rows=150448 loops=1) Filter: (display_type IS NULL) -> Index Only Scan using _upg_invl_aml_mapping_invl_id_17ac41c4868 on invl_aml_mapping (cost=0.42..2.26 rows=2 width=4) (actual time=0.002..0.002 rows=1 loops=150448) Index Cond: (invl_id = il.id) Heap Fetches: 95944 -> Bitmap Heap Scan on account_move_line ml (cost=11.65..17.92 rows=1 width=22) (actual time=0.565..1.322 rows=153 loops=54864) Recheck Cond: ((product_id = il.product_id) AND (account_id = il.account_id)) Filter: ((tax_line_id IS NULL) AND ((SubPlan 1) = (SubPlan 2))) Rows Removed by Filter: 0 Heap Blocks: exact=4597567 -> BitmapAnd (cost=11.65..11.65 rows=1 width=0) (actual time=0.550..0.550 rows=0 loops=54864) -> Bitmap Index Scan on upgrade_fk_related_idx_42 (cost=0.00..1.33 rows=15 width=0) (actual time=0.013..0.013 rows=161 loops=54864) Index Cond: (product_id = il.product_id) -> Bitmap Index Scan on account_move_line_account_id_index (cost=0.00..10.07 rows=1047 width=0) (actual time=0.968..0.968 rows=37775 loops=30031) Index Cond: (account_id = il.account_id) SubPlan 1 -> Index Only Scan using account_invoice_line_tax_pkey on account_invoice_line_tax r (cost=0.42..2.64 rows=1 width=4) (actual time=0.001..0.001 rows=0 loops=8425000) Index Cond: (invoice_line_id = il.id) Heap Fetches: 0 SubPlan 2 -> Index Only Scan using account_move_line_account_tax_rel_pkey on account_move_line_account_tax_rel r_1 (cost=0.29..2.51 rows=1 width=4) (actual time=0.001..0.001 rows=0 loops=8425000) Index Cond: (account_move_line_id = ml.id) Heap Fetches: 1099 -> Sort (cost=228.52..228.76 rows=98 width=16) (actual time=1.502..31540.651 rows=758247643 loops=1) Sort Key: i.currency_id Sort Method: quicksort Memory: 29kB -> Index Scan using account_invoice_pkey on account_invoice i (cost=0.29..225.28 rows=98 width=16) (actual time=0.035..1.481 rows=98 loops=1) Index Cond: (id = ANY ('{3839,6912,7164,11026,6479,9973,3599,11306,4092,10652,2732,8625,198,10536,5876,7864,5498,13080,4660,8948,597,10762,12573,11613,12905,11483,11227,12876,4470,8954,5628,4386,9321,4398,11595,7125,8116,1231,1610,6828,2105,9658,1616,5735,9066,4631,51,1185,1173 6,9579,4303,9983,12759,3889,4667,11258,9423,11855,6555,3164,7597,5596,10396,620,10330,4082,926,3252,5375,8118,7141,3500,12065,2794,2367,12088,7454,7998,11439,8893,6304,10814,7723,4393,3901,3383,5770,5857,4746,1858,5320,4574,8314,11914,7930,7308,6228,3627}'::integer[])) -> Index Only Scan using account_move_pkey on account_move m (cost=0.41..0.44 rows=1 width=4) (never executed) Index Cond: (id = ml.move_id) Heap Fetches: 0 -> Index Only Scan using res_company_pkey on res_company comp (cost=0.12..0.18 rows=1 width=4) (never executed) Index Cond: (id = i.company_id) Heap Fetches: 0 -> Index Only Scan using _upg_invl_aml_mapping_aml_id_17ac41c486f on invl_aml_mapping invl_aml_mapping_1 (cost=0.42..0.46 rows=2 width=4) (never executed) Index Cond: (aml_id = ml.id) Heap Fetches: 0 Planning Time: 14.884 ms Execution Time: 185674.278 ms (55 rows) Thanks again, Nicolas.
Attachment:
publickey - nicolas@seinlet.com - 0xCAEB7FAF.asc
Description: application/pgp-keys
Attachment:
signature.asc
Description: OpenPGP digital signature
