Daily updates from Odoo
Thursday, August 6, 2026
6 changes · 19.0
Enhancements to existing features
The cash flow report now handles large payment datasets in a way that lets the database choose more reliable execution plans. This helps prevent the report from hanging or slowing dramatically after database maintenance or restores, while keeping normal performance about the same.
Original PR description
The payment_move_ids CTE of the cash flow statement collapsed all the liquidity move ids into a single array with array_agg(DISTINCT move_id), and the consumer queries filtered with move_id IN…
The payment_move_ids CTE of the cash flow statement collapsed all the
liquidity move ids into a single array with array_agg(DISTINCT move_id),
and the consumer queries filtered with
move_id IN (SELECT unnest(payment_move_ids.move_id)).
PostgreSQL cannot estimate the cardinality of unnest() over a
non-constant array: the ProjectSet node is always planned with a fixed
guess (rows=10) regardless of how many moves the period contains. On the
affected database the CTE returns 30,307 moves for a single month, a
3,000x planner misestimation that is visible in the EXPLAIN below even
when the query happens to be fast. Whenever the surrounding statistics
degrade (e.g. right after a pg_restore, before any ANALYZE runs), that
guess collapses every downstream join into nested loops over
account_move_line (39M rows) and account_partial_reconcile (9.2M rows)
and the report never finishes (>5 minutes, killed). The unnest() call
was also repeated 7 times (3 in _get_liquidity_moves, 4 in
_get_reconciled_moves).
Make the CTE return a plain row set (SELECT DISTINCT move_id) and filter
with regular IN/NOT IN subqueries so the planner works with real row
estimates and can choose hash semi/anti joins or index nested loops
based on actual costs.
With healthy statistics both forms now perform the same (~0.6s for the
liquidity moves query below); the difference is that the new form
degrades gracefully when estimates drift instead of falling off a
cliff. Full report render on the 39M-line database: 6.4s.
Related operational findings on the affected database (not part of this
patch, applied at the DB level):
- The database had been restored without ANALYZE: pg_stats had 0 rows
for account_move_line, which is what made the report hang for >5
minutes regardless of this patch. Fixed with:
vacuumdb --analyze -t account_move_line -t account_move
-t account_partial_reconcile ... (6.5s)
- move_id n_distinct was estimated at 169,957 vs 9,844,904 real (58x
off), pushing the planner away from the efficient move_id index
probes. Fixed with:
ALTER TABLE account_move_line
ALTER COLUMN move_id SET (n_distinct = -0.25);
ANALYZE account_move_line;
- account_partial_reconcile has no index on max_date; the intermediate
plans seq-scanned 9.2M rows per UNION branch. Added:
CREATE INDEX account_partial_reconcile__max_date_index
ON account_partial_reconcile (max_date);
<details>
<summary>Problematic query BEFORE the change</summary>
```sql
(WITH payment_move_ids AS (
SELECT
array_agg(DISTINCT account_move_line.move_id) AS move_id
FROM "account_move_line"
WHERE ("account_move_line"."account_id" IN (1274, 15, 1941, 1942, 672, 673, 674, 675, 676, 37, 38, 677, 678, 679, 680, 681, 1079, 63, 64, 65, 66, 67, 68, 69, 70, 71, 72, 73, 1346, 1353, 1236, 1111, 1252, 1254, 1385, 1898, 1899, 1900, 1264, 1908, 1909, 1018, 1275) AND "account_move_line"."company_id" IN (1) AND "account_move_line"."date" <= '2026-07-31'::date AND "account_move_line"."date" >= '2026-07-01'::date AND "account_move_line"."display_type" NOT IN ('line_section', 'line_subsection', 'line_note') AND "account_move_line"."journal_id" IN (244, 245, 247, 249, 251, 252, 255, 377, 637, 256, 258, 259, 261, 651, 433, 511, 30, 8, 349, 11, 10, 370, 43, 679, 12, 13, 15, 14, 434, 598, 599, 648, 263, 248, 228, 229, 231, 234, 236, 238, 239, 241, 243, 22, 24, 25, 7, 405, 374, 28, 20, 23, 375, 27, 371, 19, 26, 425, 559, 373, 21, 607, 372, 542, 680, 5, 42, 36, 32, 522, 530, 529, 527, 516, 520, 521, 526, 519, 528, 531, 517, 29, 214, 215, 216, 217, 409, 218, 220, 191, 336, 6, 681, 38, 4, 45, 262, 264, 266, 268, 233, 237, 240, 246, 254, 260, 265, 270, 272, 273, 235, 242, 257, 267, 271, 274, 277, 278, 232, 253, 269, 276, 279, 281, 250, 275, 337, 338, 407, 280, 282, 230, 283, 284, 285, 219, 222, 224, 17, 16, 435, 286, 289, 290, 291, 297, 339, 340, 341, 342, 343, 344, 345, 346, 347, 348, 426, 611, 31, 35, 3, 33, 41, 37, 312, 313, 315, 316, 317, 318, 320, 311, 321, 323, 324, 326, 327, 328, 305, 306, 606, 376, 40, 34, 602, 294, 302, 287, 295, 296, 298, 303, 288, 299, 304, 225, 221, 226, 227, 307, 308, 309, 310, 314, 319, 322, 325, 329, 223, 403, 404, 676, 39, 18, 9, 292, 300, 293, 301, 406, 330, 331, 332, 333, 334, 195, 196, 197, 335, 198, 203, 208, 192, 204, 193, 205, 209, 199, 194, 200, 210, 211, 212, 206, 213, 201, 202, 207, 544, 545, 546, 547, 548, 549, 550, 551, 552, 553, 554, 555, 556, 557, 558, 410, 411, 412, 413, 414, 415, 416, 417, 418, 419, 420, 421, 422, 423, 424, 390, 392, 393, 395, 397, 399, 401, 402, 400, 378, 389, 391, 394, 396, 398, 682, 350, 351, 352, 353, 354, 355, 356, 357, 358, 359, 360, 361, 362, 363, 364, 365, 366, 367, 368, 369) AND "account_move_line"."parent_state" IN ('posted'))
)
-- Credit amount of each account
SELECT
'((''forced_options'', ((''date'', ((''currency_table_period_key'', ''2026-07-01_2026-07-31''), (''date_from'', ''2026-07-01''), (''date_to'', ''2026-07-31''), (''filter'', ''custom''), (''mode'', ''range''), (''period_type'', ''month''), (''string'', ''Jul 2026''))),)), (''horizontal_groupby_element'', ()))' AS column_group_key,
account_move_line.account_id,
(COALESCE("account_move_line__account_id"."code_store"->'1',to_jsonb(NULL::VARCHAR))->>0)::VARCHAR AS account_code,
"account_move_line__account_id"."name"->>'en_US' AS account_name,
"account_move_line__account_id"."account_type" AS account_account_type,
account_account_account_tag.account_account_tag_id AS account_tag_id,
SUM((account_partial_reconcile.amount) * COALESCE(account_currency_table.rate, 1)) AS balance
FROM "account_move_line" JOIN "account_account" AS "account_move_line__account_id" ON ("account_move_line"."account_id" = "account_move_line__account_id"."id")
JOIN (VALUES (1, CAST(NULL AS VARCHAR), CAST(NULL AS DATE), CAST(NULL AS DATE), 'current', 1)) AS account_currency_table(company_id, period_key, date_from, date_next, rate_type, rate)
ON account_move_line.company_id = account_currency_table.company_id
AND (account_currency_table.period_key = '2026-07-01_2026-07-31' OR account_currency_table.period_key IS NULL)
LEFT JOIN account_partial_reconcile
ON account_partial_reconcile.credit_move_id = account_move_line.id
LEFT JOIN account_account_account_tag
ON account_account_account_tag.account_account_id = account_move_line.account_id
AND account_account_account_tag.account_account_tag_id IN (1, 3, 2)
WHERE account_move_line.move_id IN (SELECT unnest(payment_move_ids.move_id) FROM payment_move_ids)
AND account_move_line.account_id NOT IN (1274, 15, 1941, 1942, 672, 673, 674, 675, 676, 37, 38, 677, 678, 679, 680, 681, 1079, 63, 64, 65, 66, 67, 68, 69, 70, 71, 72, 73, 1346, 1353, 1236, 1111, 1252, 1254, 1385, 1898, 1899, 1900, 1264, 1908, 1909, 1018, 1275)
AND account_partial_reconcile.max_date BETWEEN '2026-07-01' AND '2026-07-31'
GROUP BY account_move_line.company_id, account_move_line.account_id, (COALESCE("account_move_line__account_id"."code_store"->'1',to_jsonb(NULL::VARCHAR))->>0)::VARCHAR, "account_move_line__account_id"."name"->>'en_US', account_account_type, account_account_account_tag.account_account_tag_id
UNION ALL
-- Debit amount of each account
SELECT
'((''forced_options'', ((''date'', ((''currency_table_period_key'', ''2026-07-01_2026-07-31''), (''date_from'', ''2026-07-01''), (''date_to'', ''2026-07-31''), (''filter'', ''custom''), (''mode'', ''range''), (''period_type'', ''month''), (''string'', ''Jul 2026''))),)), (''horizontal_groupby_element'', ()))' AS column_group_key,
account_move_line.account_id,
(COALESCE("account_move_line__account_id"."code_store"->'1',to_jsonb(NULL::VARCHAR))->>0)::VARCHAR AS account_code,
"account_move_line__account_id"."name"->>'en_US' AS account_name,
"account_move_line__account_id"."account_type" AS account_account_type,
account_account_account_tag.account_account_tag_id AS account_tag_id,
-SUM((account_partial_reconcile.amount) * COALESCE(account_currency_table.rate, 1)) AS balance
FROM "account_move_line" JOIN "account_account" AS "account_move_line__account_id" ON ("account_move_line"."account_id" = "account_move_line__account_id"."id")
JOIN (VALUES (1, CAST(NULL AS VARCHAR), CAST(NULL AS DATE), CAST(NULL AS DATE), 'current', 1)) AS account_currency_table(company_id, period_key, date_from, date_next, rate_type, rate)
ON account_move_line.company_id = account_currency_table.company_id
AND (account_currency_table.period_key = '2026-07-01_2026-07-31' OR account_currency_table.period_key IS NULL)
LEFT JOIN account_partial_reconcile
ON account_partial_reconcile.debit_move_id = account_move_line.id
LEFT JOIN account_account_account_tag
ON account_account_account_tag.account_account_id = account_move_line.account_id
AND account_account_account_tag.account_account_tag_id IN (1, 3, 2)
WHERE account_move_line.move_id IN (SELECT unnest(payment_move_ids.move_id) FROM payment_move_ids)
AND account_move_line.account_id NOT IN (1274, 15, 1941, 1942, 672, 673, 674, 675, 676, 37, 38, 677, 678, 679, 680, 681, 1079, 63, 64, 65, 66, 67, 68, 69, 70, 71, 72, 73, 1346, 1353, 1236, 1111, 1252, 1254, 1385, 1898, 1899, 1900, 1264, 1908, 1909, 1018, 1275)
AND account_partial_reconcile.max_date BETWEEN '2026-07-01' AND '2026-07-31'
GROUP BY account_move_line.company_id, account_move_line.account_id, (COALESCE("account_move_line__account_id"."code_store"->'1',to_jsonb(NULL::VARCHAR))->>0)::VARCHAR, "account_move_line__account_id"."name"->>'en_US', account_account_type, account_account_account_tag.account_account_tag_id
UNION ALL
-- Total amount of each account
SELECT
'((''forced_options'', ((''date'', ((''currency_table_period_key'', ''2026-07-01_2026-07-31''), (''date_from'', ''2026-07-01''), (''date_to'', ''2026-07-31''), (''filter'', ''custom''), (''mode'', ''range''), (''period_type'', ''month''), (''string'', ''Jul 2026''))),)), (''horizontal_groupby_element'', ()))' AS column_group_key,
account_move_line.account_id AS account_id,
(COALESCE("account_move_line__account_id"."code_store"->'1',to_jsonb(NULL::VARCHAR))->>0)::VARCHAR AS account_code,
"account_move_line__account_id"."name"->>'en_US' AS account_name,
"account_move_line__account_id"."account_type" AS account_account_type,
account_account_account_tag.account_account_tag_id AS account_tag_id,
SUM((account_move_line.balance) * COALESCE(account_currency_table.rate, 1)) AS balance
FROM "account_move_line" JOIN "account_account" AS "account_move_line__account_id" ON ("account_move_line"."account_id" = "account_move_line__account_id"."id")
JOIN (VALUES (1, CAST(NULL AS VARCHAR), CAST(NULL AS DATE), CAST(NULL AS DATE), 'current', 1)) AS account_currency_table(company_id, period_key, date_from, date_next, rate_type, rate)
ON account_move_line.company_id = account_currency_table.company_id
AND (account_currency_table.period_key = '2026-07-01_2026-07-31' OR account_currency_table.period_key IS NULL)
LEFT JOIN account_account_account_tag
ON account_account_account_tag.account_account_id = account_move_line.account_id
AND account_account_account_tag.account_account_tag_id IN (1, 3, 2)
WHERE account_move_line.move_id IN (SELECT unnest(payment_move_ids.move_id) FROM payment_move_ids)
AND account_move_line.account_id NOT IN (1274, 15, 1941, 1942, 672, 673, 674, 675, 676, 37, 38, 677, 678, 679, 680, 681, 1079, 63, 64, 65, 66, 67, 68, 69, 70, 71, 72, 73, 1346, 1353, 1236, 1111, 1252, 1254, 1385, 1898, 1899, 1900, 1264, 1908, 1909, 1018, 1275)
GROUP BY account_move_line.account_id, (COALESCE("account_move_line__account_id"."code_store"->'1',to_jsonb(NULL::VARCHAR))->>0)::VARCHAR, "account_move_line__account_id"."name"->>'en_US', account_account_type, account_account_account_tag.account_account_tag_id)
```
</details>
<details>
<summary>EXPLAIN (ANALYZE, BUFFERS) BEFORE the change</summary>
note the ProjectSet rows=10 estimate vs 30,307 actual rows coming out of unnest
```txt
QUERY PLAN
------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
Append (cost=26694.10..26849.66 rows=40 width=148) (actual time=307.108..638.422 rows=62 loops=1)
Buffers: shared hit=789523
CTE payment_move_ids
-> Aggregate (cost=26613.84..26613.85 rows=1 width=32) (actual time=96.170..96.172 rows=1 loops=1)
Buffers: shared hit=18968
-> Sort (cost=26552.25..26583.04 rows=12317 width=4) (actual time=92.110..93.591 rows=37545 loops=1)
Sort Key: account_move_line_3.move_id
Sort Method: quicksort Memory: 1537kB
Buffers: shared hit=18968
-> Index Scan using account_move_line_account_id_date_idx on account_move_line account_move_line_3 (cost=1.23..25715.41 rows=12317 width=4) (actual time=0.101..86.829 rows=37545 loops=1)
Index Cond: ((account_id = ANY ('{1274,15,1941,1942,672,673,674,675,676,37,38,677,678,679,680,681,1079,63,64,65,66,67,68,69,70,71,72,73,1346,1353,1236,1111,1252,1254,1385,1898,1899,1900,1264,1908,1909,1018,1275}'::integer[])) AND (date <= '2026-07-31'::date) AND (date >= '2026-07-01'::date))
Filter: ((company_id = 1) AND ((parent_state)::text = 'posted'::text) AND ((display_type)::text <> ALL ('{line_section,line_subsection,line_note}'::text[])) AND (journal_id = ANY ('{244,245,247,249,251,252,255,377,637,256,258,259,261,651,433,511,30,8,349,11,10,370,43,679,12,13,15,14,434,598,599,648,263,248,228,229,231,234,236,238,239,241,243,22,24,25,7,405,374,28,20,23,375,27,371,19,26,425,559,373,21,607,372,542,680,5,42,36,32,522,530,529,527,516,520,521,526,519,528,531,517,29,214,215,216,217,409,218,220,191,336,6,681,38,4,45,262,264,266,268,233,237,240,246,254,260,265,270,272,273,235,242,257,267,271,274,277,278,232,253,269,276,279,281,250,275,337,338,407,280,282,230,283,284,285,219,222,224,17,16,435,286,289,290,291,297,339,340,341,342,343,344,345,346,347,348,426,611,31,35,3,33,41,37,312,313,315,316,317,318,320,311,321,323,324,326,327,328,305,306,606,376,40,34,602,294,302,287,295,296,298,303,288,299,304,225,221,226,227,307,308,309,310,314,319,322,325,329,223,403,404,676,39,18,9,292,300,293,301,406,330,331,332,333,334,195,196,197,335,198,203,208,192,204,193,205,209,199,194,200,210,211,212,206,213,201,202,207,544,545,546,547,548,549,550,551,552,553,554,555,556,557,558,410,411,412,413,414,415,416,417,418,419,420,421,422,423,424,390,392,393,395,397,399,401,402,400,378,389,391,394,396,398,682,350,351,352,353,354,355,356,357,358,359,360,361,362,363,364,365,366,367,368,369}'::integer[])))
Rows Removed by Filter: 117
Buffers: shared hit=18968
-> Subquery Scan on "*SELECT* 1_1" (cost=80.25..80.30 rows=1 width=148) (actual time=307.108..309.550 rows=2 loops=1)
Buffers: shared hit=320873
-> GroupAggregate (cost=80.25..80.29 rows=1 width=152) (actual time=307.106..309.547 rows=2 loops=1)
Group Key: account_move_line.account_id, (((COALESCE((account_move_line__account_id.code_store -> '1'::text)) ->> 0))::character varying), ((account_move_line__account_id.name ->> 'en_US'::text)), account_move_line__account_id.account_type, account_account_account_tag.account_account_tag_id
Buffers: shared hit=320873
-> Sort (cost=80.25..80.26 rows=1 width=95) (actual time=305.265..305.862 rows=14982 loops=1)
Sort Key: account_move_line.account_id, (((COALESCE((account_move_line__account_id.code_store -> '1'::text)) ->> 0))::character varying), ((account_move_line__account_id.name ->> 'en_US'::text)), account_move_line__account_id.account_type, account_account_account_tag.account_account_tag_id
Sort Method: quicksort Memory: 1488kB
Buffers: shared hit=320873
-> Nested Loop Left Join (cost=1.63..80.24 rows=1 width=95) (actual time=105.455..300.078 rows=14982 loops=1)
Buffers: shared hit=320867
-> Nested Loop (cost=1.36..79.64 rows=1 width=100) (actual time=105.434..279.121 rows=14982 loops=1)
Buffers: shared hit=290903
-> Nested Loop (cost=1.08..79.35 rows=1 width=15) (actual time=105.423..265.444 rows=14982 loops=1)
Buffers: shared hit=245957
-> Nested Loop (cost=0.65..58.84 rows=38 width=12) (actual time=105.398..220.996 rows=23165 loops=1)
Buffers: shared hit=162104
-> HashAggregate (cost=0.10..0.20 rows=10 width=4) (actual time=105.370..111.578 rows=30307 loops=1)
Group Key: unnest(payment_move_ids.move_id)
Batches: 1 Memory Usage: 3625kB
Buffers: shared hit=18968
-> ProjectSet (cost=0.00..0.08 rows=10 width=4) (actual time=96.186..97.942 rows=30307 loops=1)
Buffers: shared hit=18968
-> CTE Scan on payment_move_ids (cost=0.00..0.02 rows=1 width=32) (actual time=96.183..96.184 rows=1 loops=1)
Buffers: shared hit=18968
-> Index Scan using account_move_line__move_id_index on account_move_line (cost=0.55..5.82 rows=4 width=16) (actual time=0.003..0.003 rows=1 loops=30307)
Index Cond: (move_id = (unnest(payment_move_ids.move_id)))
Filter: ((company_id = 1) AND (account_id <> ALL ('{1274,15,1941,1942,672,673,674,675,676,37,38,677,678,679,680,681,1079,63,64,65,66,67,68,69,70,71,72,73,1346,1353,1236,1111,1252,1254,1385,1898,1899,1900,1264,1908,1909,1018,1275}'::integer[])))
Rows Removed by Filter: 1
Buffers: shared hit=143136
-> Index Scan using account_partial_reconcile__credit_move_id_index on account_partial_reconcile (cost=0.43..0.53 rows=1 width=11) (actual time=0.002..0.002 rows=1 loops=23165)
Index Cond: (credit_move_id = account_move_line.id)
Filter: ((max_date >= '2026-07-01'::date) AND (max_date <= '2026-07-31'::date))
Buffers: shared hit=83853
-> Index Scan using account_account_pkey on account_account account_move_line__account_id (cost=0.28..0.29 rows=1 width=89) (actual time=0.001..0.001 rows=1 loops=14982)
Index Cond: (id = account_move_line.account_id)
Buffers: shared hit=44946
-> Index Only Scan using account_account_account_tag_sh_auto_pk on account_account_account_tag (cost=0.28..0.58 rows=1 width=8) (actual time=0.001..0.001 rows=0 loops=14982)
Index Cond: ((account_account_id = account_move_line.account_id) AND (account_account_tag_id = ANY ('{1,3,2}'::integer[])))
Heap Fetches: 0
Buffers: shared hit=29964
-> Subquery Scan on "*SELECT* 2" (cost=80.25..80.31 rows=1 width=148) (actual time=172.103..172.363 rows=10 loops=1)
Buffers: shared hit=256017
-> GroupAggregate (cost=80.25..80.30 rows=1 width=152) (actual time=172.102..172.359 rows=10 loops=1)
Group Key: account_move_line_1.account_id, (((COALESCE((account_move_line__account_id_1.code_store -> '1'::text)) ->> 0))::character varying), ((account_move_line__account_id_1.name ->> 'en_US'::text)), account_move_line__account_id_1.account_type, account_account_account_tag_1.account_account_tag_id
Buffers: shared hit=256017
-> Sort (cost=80.25..80.26 rows=1 width=95) (actual time=170.281..170.572 rows=7235 loops=1)
Sort Key: account_move_line_1.account_id, (((COALESCE((account_move_line__account_id_1.code_store -> '1'::text)) ->> 0))::character varying), ((account_move_line__account_id_1.name ->> 'en_US'::text)), account_move_line__account_id_1.account_type, account_account_account_tag_1.account_account_tag_id
Sort Method: quicksort Memory: 764kB
Buffers: shared hit=256017
-> Nested Loop Left Join (cost=1.63..80.24 rows=1 width=95) (actual time=8.816..167.962 rows=7235 loops=1)
Buffers: shared hit=256017
-> Nested Loop (cost=1.36..79.64 rows=1 width=100) (actual time=8.805..157.802 rows=7235 loops=1)
Buffers: shared hit=241547
-> Nested Loop (cost=1.08..79.35 rows=1 width=15) (actual time=8.801..151.024 rows=7235 loops=1)
Buffers: shared hit=219842
-> Nested Loop (cost=0.65..58.84 rows=38 width=12) (actual time=8.791..112.486 rows=23165 loops=1)
Buffers: shared hit=143136
-> HashAggregate (cost=0.10..0.20 rows=10 width=4) (actual time=8.774..14.897 rows=30307 loops=1)
Group Key: unnest(payment_move_ids_1.move_id)
Batches: 1 Memory Usage: 3625kB
-> ProjectSet (cost=0.00..0.08 rows=10 width=4) (actual time=0.011..1.752 rows=30307 loops=1)
-> CTE Scan on payment_move_ids payment_move_ids_1 (cost=0.00..0.02 rows=1 width=32) (actual time=0.008..0.008 rows=1 loops=1)
-> Index Scan using account_move_line__move_id_index on account_move_line account_move_line_1 (cost=0.55..5.82 rows=4 width=16) (actual time=0.003..0.003 rows=1 loops=30307)
Index Cond: (move_id = (unnest(payment_move_ids_1.move_id)))
Filter: ((company_id = 1) AND (account_id <> ALL ('{1274,15,1941,1942,672,673,674,675,676,37,38,677,678,679,680,681,1079,63,64,65,66,67,68,69,70,71,72,73,1346,1353,1236,1111,1252,1254,1385,1898,1899,1900,1264,1908,1909,1018,1275}'::integer[])))
Rows Removed by Filter: 1
Buffers: shared hit=143136
-> Index Scan using account_partial_reconcile__debit_move_id_index on account_partial_reconcile account_partial_reconcile_1 (cost=0.43..0.53 rows=1 width=11) (actual time=0.001..0.001 rows=0 loops=23165)
Index Cond: (debit_move_id = account_move_line_1.id)
Filter: ((max_date >= '2026-07-01'::date) AND (max_date <= '2026-07-31'::date))
Buffers: shared hit=76706
-> Index Scan using account_account_pkey on account_account account_move_line__account_id_1 (cost=0.28..0.29 rows=1 width=89) (actual time=0.001..0.001 rows=1 loops=7235)
Index Cond: (id = account_move_line_1.account_id)
Buffers: shared hit=21705
-> Index Only Scan using account_account_account_tag_sh_auto_pk on account_account_account_tag account_account_account_tag_1 (cost=0.28..0.58 rows=1 width=8) (actual time=0.001..0.001 rows=0 loops=7235)
Index Cond: ((account_account_id = account_move_line_1.account_id) AND (account_account_tag_id = ANY ('{1,3,2}'::integer[])))
Heap Fetches: 0
Buffers: shared hit=14470
-> HashAggregate (cost=74.25..75.01 rows=38 width=148) (actual time=156.479..156.498 rows=50 loops=1)
Group Key: account_move_line_2.account_id, ((COALESCE((account_move_line__account_id_2.code_store -> '1'::text)) ->> 0))::character varying, (account_move_line__account_id_2.name ->> 'en_US'::text), account_move_line__account_id_2.account_type, account_account_account_tag_2.account_account_tag_id
Batches: 1 Memory Usage: 48kB
Buffers: shared hit=212633
-> Nested Loop Left Join (cost=1.20..73.58 rows=38 width=90) (actual time=9.037..145.306 rows=23165 loops=1)
Join Filter: (account_account_account_tag_2.account_account_id = account_move_line_2.account_id)
Buffers: shared hit=212633
-> Nested Loop (cost=0.93..69.95 rows=38 width=95) (actual time=9.027..135.207 rows=23165 loops=1)
Buffers: shared hit=212631
-> Nested Loop (cost=0.65..58.84 rows=38 width=10) (actual time=9.022..113.369 rows=23165 loops=1)
Buffers: shared hit=143136
-> HashAggregate (cost=0.10..0.20 rows=10 width=4) (actual time=9.005..14.381 rows=30307 loops=1)
Group Key: unnest(payment_move_ids_2.move_id)
Batches: 1 Memory Usage: 3625kB
-> ProjectSet (cost=0.00..0.08 rows=10 width=4) (actual time=0.080..1.829 rows=30307 loops=1)
-> CTE Scan on payment_move_ids payment_move_ids_2 (cost=0.00..0.02 rows=1 width=32) (actual time=0.077..0.077 rows=1 loops=1)
-> Index Scan using account_move_line__move_id_index on account_move_line account_move_line_2 (cost=0.55..5.82 rows=4 width=14) (actual time=0.003..0.003 rows=1 loops=30307)
Index Cond: (move_id = (unnest(payment_move_ids_2.move_id)))
Filter: ((company_id = 1) AND (account_id <> ALL ('{1274,15,1941,1942,672,673,674,675,676,37,38,677,678,679,680,681,1079,63,64,65,66,67,68,69,70,71,72,73,1346,1353,1236,1111,1252,1254,1385,1898,1899,1900,1264,1908,1909,1018,1275}'::integer[])))
Rows Removed by Filter: 1
Buffers: shared hit=143136
-> Index Scan using account_account_pkey on account_account account_move_line__account_id_2 (cost=0.28..0.29 rows=1 width=89) (actual time=0.001..0.001 rows=1 loops=23165)
Index Cond: (id = account_move_line_2.account_id)
Buffers: shared hit=69495
-> Materialize (cost=0.28..2.78 rows=1 width=8) (actual time=0.000..0.000 rows=0 loops=23165)
Buffers: shared hit=2
-> Index Only Scan using account_account_account_tag_account_account_tag_id_account__idx on account_account_account_tag account_account_account_tag_2 (cost=0.28..2.77 rows=1 width=8) (actual time=0.007..0.007 rows=0 loops=1)
Index Cond: (account_account_tag_id = ANY ('{1,3,2}'::integer[]))
Heap Fetches: 0
Buffers: shared hit=2
Planning:
Buffers: shared hit=1089
Planning Time: 11.934 ms
Execution Time: 639.826 ms
```
</details>
<details>
<summary>Query AFTER the change</summary>
```sql
(WITH payment_move_ids AS (
SELECT DISTINCT
account_move_line.move_id AS move_id
FROM "account_move_line"
WHERE ("account_move_line"."account_id" IN (1274, 15, 1941, 1942, 672, 673, 674, 675, 676, 37, 38, 677, 678, 679, 680, 681, 1079, 63, 64, 65, 66, 67, 68, 69, 70, 71, 72, 73, 1346, 1353, 1236, 1111, 1252, 1254, 1385, 1898, 1899, 1900, 1264, 1908, 1909, 1018, 1275) AND "account_move_line"."company_id" IN (1) AND "account_move_line"."date" <= '2026-07-31'::date AND "account_move_line"."date" >= '2026-07-01'::date AND "account_move_line"."display_type" NOT IN ('line_section', 'line_subsection', 'line_note') AND "account_move_line"."journal_id" IN (244, 245, 247, 249, 251, 252, 255, 377, 637, 256, 258, 259, 261, 651, 433, 511, 30, 8, 349, 11, 10, 370, 43, 679, 12, 13, 15, 14, 434, 598, 599, 648, 263, 248, 228, 229, 231, 234, 236, 238, 239, 241, 243, 22, 24, 25, 7, 405, 374, 28, 20, 23, 375, 27, 371, 19, 26, 425, 559, 373, 21, 607, 372, 542, 680, 5, 42, 36, 32, 522, 530, 529, 527, 516, 520, 521, 526, 519, 528, 531, 517, 29, 214, 215, 216, 217, 409, 218, 220, 191, 336, 6, 681, 38, 4, 45, 262, 264, 266, 268, 233, 237, 240, 246, 254, 260, 265, 270, 272, 273, 235, 242, 257, 267, 271, 274, 277, 278, 232, 253, 269, 276, 279, 281, 250, 275, 337, 338, 407, 280, 282, 230, 283, 284, 285, 219, 222, 224, 17, 16, 435, 286, 289, 290, 291, 297, 339, 340, 341, 342, 343, 344, 345, 346, 347, 348, 426, 611, 31, 35, 3, 33, 41, 37, 312, 313, 315, 316, 317, 318, 320, 311, 321, 323, 324, 326, 327, 328, 305, 306, 606, 376, 40, 34, 602, 294, 302, 287, 295, 296, 298, 303, 288, 299, 304, 225, 221, 226, 227, 307, 308, 309, 310, 314, 319, 322, 325, 329, 223, 403, 404, 676, 39, 18, 9, 292, 300, 293, 301, 406, 330, 331, 332, 333, 334, 195, 196, 197, 335, 198, 203, 208, 192, 204, 193, 205, 209, 199, 194, 200, 210, 211, 212, 206, 213, 201, 202, 207, 544, 545, 546, 547, 548, 549, 550, 551, 552, 553, 554, 555, 556, 557, 558, 410, 411, 412, 413, 414, 415, 416, 417, 418, 419, 420, 421, 422, 423, 424, 390, 392, 393, 395, 397, 399, 401, 402, 400, 378, 389, 391, 394, 396, 398, 682, 350, 351, 352, 353, 354, 355, 356, 357, 358, 359, 360, 361, 362, 363, 364, 365, 366, 367, 368, 369) AND "account_move_line"."parent_state" IN ('posted'))
)
-- Credit amount of each account
SELECT
'((''forced_options'', ((''date'', ((''currency_table_period_key'', ''2026-07-01_2026-07-31''), (''date_from'', ''2026-07-01''), (''date_to'', ''2026-07-31''), (''filter'', ''custom''), (''mode'', ''range''), (''period_type'', ''month''), (''string'', ''Jul 2026''))),)), (''horizontal_groupby_element'', ()))' AS column_group_key,
account_move_line.account_id,
(COALESCE("account_move_line__account_id"."code_store"->'1',to_jsonb(NULL::VARCHAR))->>0)::VARCHAR AS account_code,
"account_move_line__account_id"."name"->>'en_US' AS account_name,
"account_move_line__account_id"."account_type" AS account_account_type,
account_account_account_tag.account_account_tag_id AS account_tag_id,
SUM((account_partial_reconcile.amount) * COALESCE(account_currency_table.rate, 1)) AS balance
FROM "account_move_line" JOIN "account_account" AS "account_move_line__account_id" ON ("account_move_line"."account_id" = "account_move_line__account_id"."id")
JOIN (VALUES (1, CAST(NULL AS VARCHAR), CAST(NULL AS DATE), CAST(NULL AS DATE), 'current', 1)) AS account_currency_table(company_id, period_key, date_from, date_next, rate_type, rate)
ON account_move_line.company_id = account_currency_table.company_id
AND (account_currency_table.period_key = '2026-07-01_2026-07-31' OR account_currency_table.period_key IS NULL)
LEFT JOIN account_partial_reconcile
ON account_partial_reconcile.credit_move_id = account_move_line.id
LEFT JOIN account_account_account_tag
ON account_account_account_tag.account_account_id = account_move_line.account_id
AND account_account_account_tag.account_account_tag_id IN (1, 3, 2)
WHERE account_move_line.move_id IN (SELECT payment_move_ids.move_id FROM payment_move_ids)
AND account_move_line.account_id NOT IN (1274, 15, 1941, 1942, 672, 673, 674, 675, 676, 37, 38, 677, 678, 679, 680, 681, 1079, 63, 64, 65, 66, 67, 68, 69, 70, 71, 72, 73, 1346, 1353, 1236, 1111, 1252, 1254, 1385, 1898, 1899, 1900, 1264, 1908, 1909, 1018, 1275)
AND account_partial_reconcile.max_date BETWEEN '2026-07-01' AND '2026-07-31'
GROUP BY account_move_line.company_id, account_move_line.account_id, (COALESCE("account_move_line__account_id"."code_store"->'1',to_jsonb(NULL::VARCHAR))->>0)::VARCHAR, "account_move_line__account_id"."name"->>'en_US', account_account_type, account_account_account_tag.account_account_tag_id
UNION ALL
-- Debit amount of each account
SELECT
'((''forced_options'', ((''date'', ((''currency_table_period_key'', ''2026-07-01_2026-07-31''), (''date_from'', ''2026-07-01''), (''date_to'', ''2026-07-31''), (''filter'', ''custom''), (''mode'', ''range''), (''period_type'', ''month''), (''string'', ''Jul 2026''))),)), (''horizontal_groupby_element'', ()))' AS column_group_key,
account_move_line.account_id,
(COALESCE("account_move_line__account_id"."code_store"->'1',to_jsonb(NULL::VARCHAR))->>0)::VARCHAR AS account_code,
"account_move_line__account_id"."name"->>'en_US' AS account_name,
"account_move_line__account_id"."account_type" AS account_account_type,
account_account_account_tag.account_account_tag_id AS account_tag_id,
-SUM((account_partial_reconcile.amount) * COALESCE(account_currency_table.rate, 1)) AS balance
FROM "account_move_line" JOIN "account_account" AS "account_move_line__account_id" ON ("account_move_line"."account_id" = "account_move_line__account_id"."id")
JOIN (VALUES (1, CAST(NULL AS VARCHAR), CAST(NULL AS DATE), CAST(NULL AS DATE), 'current', 1)) AS account_currency_table(company_id, period_key, date_from, date_next, rate_type, rate)
ON account_move_line.company_id = account_currency_table.company_id
AND (account_currency_table.period_key = '2026-07-01_2026-07-31' OR account_currency_table.period_key IS NULL)
LEFT JOIN account_partial_reconcile
ON account_partial_reconcile.debit_move_id = account_move_line.id
LEFT JOIN account_account_account_tag
ON account_account_account_tag.account_account_id = account_move_line.account_id
AND account_account_account_tag.account_account_tag_id IN (1, 3, 2)
WHERE account_move_line.move_id IN (SELECT payment_move_ids.move_id FROM payment_move_ids)
AND account_move_line.account_id NOT IN (1274, 15, 1941, 1942, 672, 673, 674, 675, 676, 37, 38, 677, 678, 679, 680, 681, 1079, 63, 64, 65, 66, 67, 68, 69, 70, 71, 72, 73, 1346, 1353, 1236, 1111, 1252, 1254, 1385, 1898, 1899, 1900, 1264, 1908, 1909, 1018, 1275)
AND account_partial_reconcile.max_date BETWEEN '2026-07-01' AND '2026-07-31'
GROUP BY account_move_line.company_id, account_move_line.account_id, (COALESCE("account_move_line__account_id"."code_store"->'1',to_jsonb(NULL::VARCHAR))->>0)::VARCHAR, "account_move_line__account_id"."name"->>'en_US', account_account_type, account_account_account_tag.account_account_tag_id
UNION ALL
-- Total amount of each account
SELECT
'((''forced_options'', ((''date'', ((''currency_table_period_key'', ''2026-07-01_2026-07-31''), (''date_from'', ''2026-07-01''), (''date_to'', ''2026-07-31''), (''filter'', ''custom''), (''mode'', ''range''), (''period_type'', ''month''), (''string'', ''Jul 2026''))),)), (''horizontal_groupby_element'', ()))' AS column_group_key,
account_move_line.account_id AS account_id,
(COALESCE("account_move_line__account_id"."code_store"->'1',to_jsonb(NULL::VARCHAR))->>0)::VARCHAR AS account_code,
"account_move_line__account_id"."name"->>'en_US' AS account_name,
"account_move_line__account_id"."account_type" AS account_account_type,
account_account_account_tag.account_account_tag_id AS account_tag_id,
SUM((account_move_line.balance) * COALESCE(account_currency_table.rate, 1)) AS balance
FROM "account_move_line" JOIN "account_account" AS "account_move_line__account_id" ON ("account_move_line"."account_id" = "account_move_line__account_id"."id")
JOIN (VALUES (1, CAST(NULL AS VARCHAR), CAST(NULL AS DATE), CAST(NULL AS DATE), 'current', 1)) AS account_currency_table(company_id, period_key, date_from, date_next, rate_type, rate)
ON account_move_line.company_id = account_currency_table.company_id
AND (account_currency_table.period_key = '2026-07-01_2026-07-31' OR account_currency_table.period_key IS NULL)
LEFT JOIN account_account_account_tag
ON account_account_account_tag.account_account_id = account_move_line.account_id
AND account_account_account_tag.account_account_tag_id IN (1, 3, 2)
WHERE account_move_line.move_id IN (SELECT payment_move_ids.move_id FROM payment_move_ids)
AND account_move_line.account_id NOT IN (1274, 15, 1941, 1942, 672, 673, 674, 675, 676, 37, 38, 677, 678, 679, 680, 681, 1079, 63, 64, 65, 66, 67, 68, 69, 70, 71, 72, 73, 1346, 1353, 1236, 1111, 1252, 1254, 1385, 1898, 1899, 1900, 1264, 1908, 1909, 1018, 1275)
GROUP BY account_move_line.account_id, (COALESCE("account_move_line__account_id"."code_store"->'1',to_jsonb(NULL::VARCHAR))->>0)::VARCHAR, "account_move_line__account_id"."name"->>'en_US', account_account_type, account_account_account_tag.account_account_tag_id)
```
</details>
<details>
<summary>EXPLAIN (ANALYZE, BUFFERS) AFTER the change</summary>
the CTE scan is now estimated from real statistics
```txt
QUERY PLAN
------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
Append (cost=122015.01..291526.05 rows=47439 width=148) (actual time=346.625..638.772 rows=62 loops=1)
Buffers: shared hit=675626
CTE payment_move_ids
-> HashAggregate (cost=25746.20..25869.31 rows=12311 width=4) (actual time=149.934..153.654 rows=30307 loops=1)
Group Key: account_move_line_3.move_id
Batches: 1 Memory Usage: 3601kB
Buffers: shared hit=18968
-> Index Scan using account_move_line_account_id_date_idx on account_move_line account_move_line_3 (cost=1.23..25715.41 rows=12317 width=4) (actual time=53.024..140.718 rows=37545 loops=1)
Index Cond: ((account_id = ANY ('{1274,15,1941,1942,672,673,674,675,676,37,38,677,678,679,680,681,1079,63,64,65,66,67,68,69,70,71,72,73,1346,1353,1236,1111,1252,1254,1385,1898,1899,1900,1264,1908,1909,1018,1275}'::integer[])) AND (date <= '2026-07-31'::date) AND (date >= '2026-07-01'::date))
Filter: ((company_id = 1) AND ((parent_state)::text = 'posted'::text) AND ((display_type)::text <> ALL ('{line_section,line_subsection,line_note}'::text[])) AND (journal_id = ANY ('{244,245,247,249,251,252,255,377,637,256,258,259,261,651,433,511,30,8,349,11,10,370,43,679,12,13,15,14,434,598,599,648,263,248,228,229,231,234,236,238,239,241,243,22,24,25,7,405,374,28,20,23,375,27,371,19,26,425,559,373,21,607,372,542,680,5,42,36,32,522,530,529,527,516,520,521,526,519,528,531,517,29,214,215,216,217,409,218,220,191,336,6,681,38,4,45,262,264,266,268,233,237,240,246,254,260,265,270,272,273,235,242,257,267,271,274,277,278,232,253,269,276,279,281,250,275,337,338,407,280,282,230,283,284,285,219,222,224,17,16,435,286,289,290,291,297,339,340,341,342,343,344,345,346,347,348,426,611,31,35,3,33,41,37,312,313,315,316,317,318,320,311,321,323,324,326,327,328,305,306,606,376,40,34,602,294,302,287,295,296,298,303,288,299,304,225,221,226,227,307,308,309,310,314,319,322,325,329,223,403,404,676,39,18,9,292,300,293,301,406,330,331,332,333,334,195,196,197,335,198,203,208,192,204,193,205,209,199,194,200,210,211,212,206,213,201,202,207,544,545,546,547,548,549,550,551,552,553,554,555,556,557,558,410,411,412,413,414,415,416,417,418,419,420,421,422,423,424,390,392,393,395,397,399,401,402,400,378,389,391,394,396,398,682,350,351,352,353,354,355,356,357,358,359,360,361,362,363,364,365,366,367,368,369}'::integer[])))
Rows Removed by Filter: 117
Buffers: shared hit=18968
-> Subquery Scan on "*SELECT* 1_1" (cost=96145.70..96153.45 rows=155 width=148) (actual time=346.624..348.928 rows=2 loops=1)
Buffers: shared hit=290911
-> GroupAggregate (cost=96145.70..96151.90 rows=155 width=152) (actual time=346.621..348.924 rows=2 loops=1)
Group Key: account_move_line.account_id, (((COALESCE((account_move_line__account_id.code_store -> '1'::text)) ->> 0))::character varying), ((account_move_line__account_id.name ->> 'en_US'::text)), account_move_line__account_id.account_type, account_account_account_tag.account_account_tag_id
Buffers: shared hit=290911
-> Sort (cost=96145.70..96146.09 rows=155 width=95) (actual time=344.851..345.445 rows=14982 loops=1)
Sort Key: account_move_line.account_id, (((COALESCE((account_move_line__account_id.code_store -> '1'::text)) ->> 0))::character varying), ((account_move_line__account_id.name ->> 'en_US'::text)), account_move_line__account_id.account_type, account_account_account_tag.account_account_tag_id
Sort Method: quicksort Memory: 1488kB
Buffers: shared hit=290911
-> Hash Left Join (cost=281.04..96140.06 rows=155 width=95) (actual time=163.798..339.652 rows=14982 loops=1)
Hash Cond: (account_move_line.account_id = account_account_account_tag.account_account_id)
Buffers: shared hit=290905
-> Nested Loop (cost=278.25..96135.52 rows=155 width=100) (actual time=163.762..333.768 rows=14982 loops=1)
Buffers: shared hit=290903
-> Nested Loop (cost=277.98..96090.18 rows=155 width=15) (actual time=163.745..319.832 rows=14982 loops=1)
Buffers: shared hit=245957
-> Nested Loop (cost=277.55..70646.77 rows=47129 width=12) (actual time=163.712..275.793 rows=23165 loops=1)
Buffers: shared hit=162104
-> HashAggregate (cost=277.00..400.11 rows=12311 width=4) (actual time=163.675..168.382 rows=30307 loops=1)
Group Key: payment_move_ids.move_id
Batches: 1 Memory Usage: 3601kB
Buffers: shared hit=18968
-> CTE Scan on payment_move_ids (cost=0.00..246.22 rows=12311 width=4) (actual time=149.936..157.948 rows=30307 loops=1)
Buffers: shared hit=18968
-> Index Scan using account_move_line__move_id_index on account_move_line (cost=0.55..5.67 rows=4 width=16) (actual time=0.003..0.003 rows=1 loops=30307)
Index Cond: (move_id = payment_move_ids.move_id)
Filter: ((company_id = 1) AND (account_id <> ALL ('{1274,15,1941,1942,672,673,674,675,676,37,38,677,678,679,680,681,1079,63,64,65,66,67,68,69,70,71,72,73,1346,1353,1236,1111,1252,1254,1385,1898,1899,1900,1264,1908,1909,1018,1275}'::integer[])))
Rows Removed by Filter: 1
Buffers: shared hit=143136
-> Index Scan using account_partial_reconcile__credit_move_id_index on account_partial_reconcile (cost=0.43..0.53 rows=1 width=11) (actual time=0.002..0.002 rows=1 loops=23165)
Index Cond: (credit_move_id = account_move_line.id)
Filter: ((max_date >= '2026-07-01'::date) AND (max_date <= '2026-07-31'::date))
Buffers: shared hit=83853
-> Index Scan using account_account_pkey on account_account account_move_line__account_id (cost=0.28..0.29 rows=1 width=89) (actual time=0.001..0.001 rows=1 loops=14982)
Index Cond: (id = account_move_line.account_id)
Buffers: shared hit=44946
-> Hash (cost=2.77..2.77 rows=1 width=8) (actual time=0.011..0.012 rows=0 loops=1)
Buckets: 1024 Batches: 1 Memory Usage: 8kB
Buffers: shared hit=2
-> Index Only Scan using account_account_account_tag_account_account_tag_id_account__idx on account_account_account_tag (cost=0.28..2.77 rows=1 width=8) (actual time=0.011..0.011 rows=0 loops=1)
Index Cond: (account_account_tag_id = ANY ('{1,3,2}'::integer[]))
Heap Fetches: 0
Buffers: shared hit=2
-> Subquery Scan on "*SELECT* 2" (cost=96145.50..96153.64 rows=155 width=148) (actual time=159.664..159.903 rows=10 loops=1)
Buffers: shared hit=241549
-> GroupAggregate (cost=96145.50..96152.09 rows=155 width=152) (actual time=159.661..159.898 rows=10 loops=1)
Group Key: account_move_line_1.account_id, (((COALESCE((account_move_line__account_id_1.code_store -> '1'::text)) ->> 0))::character varying), ((account_move_line__account_id_1.name ->> 'en_US'::text)), account_move_line__account_id_1.account_type, account_account_account_tag_1.account_account_tag_id
Buffers: shared hit=241549
-> Sort (cost=96145.50..96145.89 rows=155 width=95) (actual time=157.870..158.167 rows=7235 loops=1)
Sort Key: account_move_line_1.account_id, (((COALESCE((account_move_line__account_id_1.code_store -> '1'::text)) ->> 0))::character varying), ((account_move_line__account_id_1.name ->> 'en_US'::text)), account_move_line__account_id_1.account_type, account_account_account_tag_1.account_account_tag_id
Sort Method: quicksort Memory: 764kB
Buffers: shared hit=241549
-> Hash Left Join (cost=281.04..96139.86 rows=155 width=95) (actual time=7.401..155.573 rows=7235 loops=1)
Hash Cond: (account_move_line_1.account_id = account_account_account_tag_1.account_account_id)
Buffers: shared hit=241549
-> Nested Loop (cost=278.25..96135.32 rows=155 width=100) (actual time=7.380..152.835 rows=7235 loops=1)
Buffers: shared hit=241547
-> Nested Loop (cost=277.98..96089.98 rows=155 width=15) (actual time=7.365..145.962 rows=7235 loops=1)
Buffers: shared hit=219842
-> Nested Loop (cost=277.55..70646.77 rows=47129 width=12) (actual time=7.349..107.795 rows=23165 loops=1)
Buffers: shared hit=143136
-> HashAggregate (cost=277.00..400.11 rows=12311 width=4) (actual time=7.329..11.783 rows=30307 loops=1)
Group Key: payment_move_ids_1.move_id
Batches: 1 Memory Usage: 3601kB
-> CTE Scan on payment_move_ids payment_move_ids_1 (cost=0.00..246.22 rows=12311 width=4) (actual time=0.001..1.685 rows=30307 loops=1)
-> Index Scan using account_move_line__move_id_index on account_move_line account_move_line_1 (cost=0.55..5.67 rows=4 width=16) (actual time=0.003..0.003 rows=1 loops=30307)
Index Cond: (move_id = payment_move_ids_1.move_id)
Filter: ((company_id = 1) AND (account_id <> ALL ('{1274,15,1941,1942,672,673,674,675,676,37,38,677,678,679,680,681,1079,63,64,65,66,67,68,69,70,71,72,73,1346,1353,1236,1111,1252,1254,1385,1898,1899,1900,1264,1908,1909,1018,1275}'::integer[])))
Rows Removed by Filter: 1
Buffers: shared hit=143136
-> Index Scan using account_partial_reconcile__debit_move_id_index on account_partial_reconcile account_partial_reconcile_1 (cost=0.43..0.53 rows=1 width=11) (actual time=0.001..0.001 rows=0 loops=23165)
Index Cond: (debit_move_id = account_move_line_1.id)
Filter: ((max_date >= '2026-07-01'::date) AND (max_date <= '2026-07-31'::date))
Buffers: shared hit=76706
-> Index Scan using account_account_pkey on account_account account_move_line__account_id_1 (cost=0.28..0.29 rows=1 width=89) (actual time=0.001..0.001 rows=1 loops=7235)
Index Cond: (id = account_move_line_1.account_id)
Buffers: shared hit=21705
-> Hash (cost=2.77..2.77 rows=1 width=8) (actual time=0.008..0.008 rows=0 loops=1)
Buckets: 1024 Batches: 1 Memory Usage: 8kB
Buffers: shared hit=2
-> Index Only Scan using account_account_account_tag_account_account_tag_id_account__idx on account_account_account_tag account_account_account_tag_1 (cost=0.28..2.77 rows=1 width=8) (actual time=0.007..0.008 rows=0 loops=1)
Index Cond: (account_account_tag_id = ANY ('{1,3,2}'::integer[]))
Heap Fetches: 0
Buffers: shared hit=2
-> HashAggregate (cost=72169.88..73112.46 rows=47129 width=148) (actual time=129.819..129.928 rows=50 loops=1)
Group Key: account_move_line_2.account_id, ((COALESCE((account_move_line__account_id_2.code_store -> '1'::text)) ->> 0))::character varying, (account_move_line__account_id_2.name ->> 'en_US'::text), account_move_line__account_id_2.account_type, account_account_account_tag_2.account_account_tag_id
Batches: 1 Memory Usage: 1585kB
Buffers: shared hit=143166
-> Hash Left Join (cost=320.08..71345.12 rows=47129 width=90) (actual time=7.557..119.457 rows=23165 loops=1)
Hash Cond: (account_move_line_2.account_id = account_account_account_tag_2.account_account_id)
Buffers: shared hit=143166
-> Hash Join (cost=317.29..70811.23 rows=47129 width=95) (actual time=7.539..111.472 rows=23165 loops=1)
Hash Cond: (account_move_line_2.account_id = account_move_line__account_id_2.id)
Buffers: shared hit=143164
-> Nested Loop (cost=277.55..70646.77 rows=47129 width=10) (actual time=7.314..106.772 rows=23165 loops=1)
Buffers: shared hit=143136
-> HashAggregate (cost=277.00..400.11 rows=12311 width=4) (actual time=7.291..11.487 rows=30307 loops=1)
Group Key: payment_move_ids_2.move_id
Batches: 1 Memory Usage: 3601kB
-> CTE Scan on payment_move_ids payment_move_ids_2 (cost=0.00..246.22 rows=12311 width=4) (actual time=0.001..1.610 rows=30307 loops=1)
-> Index Scan using account_move_line__move_id_index on account_move_line account_move_line_2 (cost=0.55..5.67 rows=4 width=14) (actual time=0.003..0.003 rows=1 loops=30307)
Index Cond: (move_id = payment_move_ids_2.move_id)
Filter: ((company_id = 1) AND (account_id <> ALL ('{1274,15,1941,1942,672,673,674,675,676,37,38,677,678,679,680,681,1079,63,64,65,66,67,68,69,70,71,72,73,1346,1353,1236,1111,1252,1254,1385,1898,1899,1900,1264,1908,1909,1018,1275}'::integer[])))
Rows Removed by Filter: 1
Buffers: shared hit=143136
-> Hash (cost=33.22..33.22 rows=522 width=89) (actual time=0.210..0.210 rows=522 loops=1)
Buckets: 1024 Batches: 1 Memory Usage: 72kB
Buffers: shared hit=28
-> Seq Scan on account_account account_move_line__account_id_2 (cost=0.00..33.22 rows=522 width=89) (actual time=0.024..0.130 rows=522 loops=1)
Buffers: shared hit=28
-> Hash (cost=2.77..2.77 rows=1 width=8) (actual time=0.008..0.008 rows=0 loops=1)
Buckets: 1024 Batches: 1 Memory Usage: 8kB
Buffers: shared hit=2
-> Index Only Scan using account_account_account_tag_account_account_tag_id_account__idx on account_account_account_tag account_account_account_tag_2 (cost=0.28..2.77 rows=1 width=8) (actual time=0.008..0.008 rows=0 loops=1)
Index Cond: (account_account_tag_id = ANY ('{1,3,2}'::integer[]))
Heap Fetches: 0
Buffers: shared hit=2
Planning:
Buffers: shared hit=1083
Planning Time: 12.195 ms
JIT:
Functions: 114
Options: Inlining false, Optimization false, Expressions true, Deforming true
Timing: Generation 6.501 ms (Deform 3.916 ms), Inlining 0.000 ms, Optimization 3.079 ms, Emission 50.027 ms, Total 59.607 ms
Execution Time: 668.775 ms
(137 rows)
```
</details>
UPDATED: In staging instance I have reproduced using the following code
<details>
<summary>code for odoo-bin shell</summary>
```python
report = env.ref("account_reports.cash_flow_report")
options = report.get_options({})
options["date"].update({"date_from": "2026-07-01", "date_to": "2026-07-31", "mode": "range", "filter": "custom"})
options = report.get_options(options)
handler = env[report.custom_handler_model_name] # -> account.cash.flow.report.handler
payment_account_ids = handler._get_account_ids(report, options)
print("PAYMENT_ACCOUNT_IDS=", sorted(payment_account_ids))
import logging
logging.getLogger("odoo.sql_db").setLevel(logging.DEBUG)
logging.basicConfig()
report._get_lines(options)
```
</details>
UPDATED2: The real solution was `vacuumdb --analyze`Odoo now checks whether an online or batch payment exceeds the maximum amount allowed by the connected financial institution before trying to start the payment. This helps prevent failed payment attempts and gives users earlier feedback when a bank-imposed limit applies.
Original PR description
Before trying to initiate payments through Odoo/Odoofin, we should check that the total amount for the (batch) payment does not exceed the maximum payment amount allowed by the institution (some Powens institutions introduced that limit). task-6310729 Forward-Port-Of: odoo/enterprise#126699 Forward-Port-Of: odoo/enterprise#121513
Bank account synchronization now recognizes a new type of provider error that should not stop the connection. This helps keep online banking links active when the issue is temporary or non-critical, reducing unnecessary disruption for users.
Original PR description
Odoofin now sends a 'non_blocking_error' error response to indicate that the state on account.online.link shouldn't be set to error. In this commit, we start using it. Task ID: 6358809 Forward-Port-Of: odoo/enterprise#123287
Resolved issues and error corrections
Shared Knowledge articles with transcript blocks now load correctly for portal users instead of showing a blank page. Portal users can view the transcript component, while recording remains unavailable to them.
Original PR description
Steps to reproduce: 1. Open knowledge 2. Share an article with a portal user 3. Use /transcript inside that article 4. Open it as a portal. ----> The page cannot load, it's a white screen with an error in the console Technical ---------- Voice transcription component can also be visible in Knowledge for portal users with permissions. Currently, the transcription component requested data from the server inaccessible to the portal. This change updates the transcription component such that portal users can view and use the transcript component while keeping the recording feature inaccessible. Task-6320461
Shipments sent through Sendcloud now always use the expected English VAT label in customs information. This prevents parcels from being rejected when a country uses a local tax label, such as Austria's USt, improving reliability for international shipping.
Original PR description
Issue ----- For some countries, the `vat_label` field is hardcoded to some value other than the English name `VAT`. This leads to shipments being rejected by Sendcloud as only the English name is accepted. For Austria for example, it is hardcoded as `USt`: https://github.com/odoo/odoo/blob/3aec1c317aad547b1ad0c85a21cc53f735c5cbfd/odoo/addons/base/data/res_country_data.xml#L79-L86 Sendcloud API doc: https://sendcloud.dev/api/v2/parcels/create-a-parcel-or-parcels#body-one-of-0-parcel-customs-information-tax-numbers-sender-items-name ----- Ticket: opw-6445962
The Timesheet and Planning Analysis report now includes planning slots for employees without a fixed working schedule. This ensures flexible employees are represented accurately in planning and workload reporting.
Original PR description
Steps to reproduce: ------------------- 1. Install project_timesheet_forecast. 2. Create a fully flexible employee (without a working schedule). 3. Create a planning slot. 4. Open the Timesheet/planning Analysis report. Issue: ------ Planning slots for fully flexible employees are not included in the report. Cause: ------ https://github.com/odoo/enterprise/blob/7d4b43cfa1934856d41992cbe8242eaf62575c2c/project_timesheet_forecast/report/timesheet_forecast_report.py#L142-L161 The report assumes every resource has a working schedule and only considers resources with a resource calendar. As a result, resources without a calendar are excluded from the report. Solution: --------- Handle resources without a working schedule separately so that planning slots for fully flexible employees are also included in the report. opw-6361571 Forward-Port-Of: odoo/enterprise#126682 Forward-Port-Of: odoo/enterprise#125072