explain.depesz.com

PostgreSQL's explain analyze made readable

Result: DqEU : Optimization for: Optimization for: plan #OypZ; plan #TmGO

Settings

Optimization path:

# exclusive inclusive rows x rows loops node
1. 2.834 47,827.535 ↓ 0.0 0 1

Nested Loop (cost=37.78..34,733.97 rows=1 width=46) (actual time=47,827.535..47,827.535 rows=0 loops=1)

  • Output: 27, 31, st_line.id, aml.id, aml.currency_id, aml.date_maturity, aml.amount_residual, aml.amount_residual_currency, aml.balance, aml.amount_currency, account.internal_type, (regexp_split_to_array("substring"(regexp_replace(concat(move.name, ' ', move.ref), '[^0-9|^\s]'::text, ''::text, 'g'::text), '\S(?:.*\S)*'::text), '\s+'::text) && regexp_split_to_array("substring"(regexp_replace((st_line.name)::text, '[^0-9|^\s]'::text, ''::text, 'g'::text), '\S(?:.*\S)*'::text), '\s+'::text))
  • Inner Unique: true
  • Join Filter: ((aml.account_id = account.id) AND (((aml.account_id = ANY (ARRAY[journal.default_credit_account_id, journal.default_debit_account_id])) AND (aml.statement_id IS NULL) AND ((company.account_bank_reconciliation_start IS NULL) OR (aml.date > company.account_bank_reconciliation_start))) OR ((account.reconcile IS TRUE) AND (aml.reconciled IS FALSE))))
  • Rows Removed by Join Filter: 42,296
2.          

CTE jnl_precision

3. 0.030 0.291 ↑ 1.0 62 1

Hash Left Join (cost=22.20..35.16 rows=62 width=8) (actual time=0.175..0.291 rows=62 loops=1)

  • Output: j.id, currency.decimal_places
  • Inner Unique: true
  • Hash Cond: (COALESCE(j.currency_id, c.currency_id) = currency.id)
4. 0.041 0.168 ↑ 1.0 62 1

Hash Left Join (cost=9.24..22.04 rows=62 width=12) (actual time=0.070..0.168 rows=62 loops=1)

  • Output: j.id, j.currency_id, c.currency_id
  • Inner Unique: true
  • Hash Cond: (j.company_id = c.id)
5. 0.090 0.090 ↑ 1.0 62 1

Seq Scan on public.account_journal j (cost=0.00..12.53 rows=62 width=12) (actual time=0.009..0.090 rows=62 loops=1)

  • Output: j.id, j.company_id, j.currency_id
  • Filter: ((j.type)::text = ANY ('{bank,cash}'::text[]))
  • Rows Removed by Filter: 60
6. 0.009 0.037 ↓ 1.2 7 1

Hash (cost=9.16..9.16 rows=6 width=8) (actual time=0.037..0.037 rows=7 loops=1)

  • Output: c.id, c.currency_id
  • Buckets: 1,024 Batches: 1 Memory Usage: 9kB
7. 0.028 0.028 ↓ 1.2 7 1

Index Scan using res_company_pkey on public.res_company c (cost=0.13..9.16 rows=6 width=8) (actual time=0.006..0.028 rows=7 loops=1)

  • Output: c.id, c.currency_id
8. 0.031 0.093 ↑ 1.0 176 1

Hash (cost=10.76..10.76 rows=176 width=8) (actual time=0.093..0.093 rows=176 loops=1)

  • Output: currency.decimal_places, currency.id
  • Buckets: 1,024 Batches: 1 Memory Usage: 15kB
9. 0.062 0.062 ↑ 1.0 176 1

Seq Scan on public.res_currency currency (cost=0.00..10.76 rows=176 width=8) (actual time=0.005..0.062 rows=176 loops=1)

  • Output: currency.decimal_places, currency.id
10.          

CTE partners_table

11. 0.009 0.009 ↑ 1.0 5 1

Values Scan on "*VALUES*" (cost=0.00..0.06 rows=5 width=8) (actual time=0.004..0.009 rows=5 loops=1)

  • Output: "*VALUES*".column1, "*VALUES*".column2
12. 38,775.342 47,813.855 ↓ 17.0 17 1

Nested Loop (cost=2.56..34,519.31 rows=1 width=127) (actual time=7,350.960..47,813.855 rows=17 loops=1)

  • Output: st_line.id, st_line.name, journal.default_credit_account_id, journal.default_debit_account_id, company.account_bank_reconciliation_start, aml.id, aml.currency_id, aml.date_maturity, aml.amount_residual, aml.amount_residual_currency, aml.balance, aml.amount_currency, aml.account_id, aml.statement_id, aml.date, aml.reconciled, move.name, move.ref
  • Inner Unique: true
  • Join Filter: (((line_partner.partner_id <> 0) AND (aml.partner_id = line_partner.partner_id)) OR ((line_partner.partner_id = 0) AND ((st_line.name)::text ~* '[0-9]'::text) AND (regexp_split_to_array("substring"(regexp_replace(concat(move.name, ' ', move.ref), '[^0-9|^\s]'::text, ''::text, 'g'::text), '\S(?:.*\S)*'::text), '\s+'::text) && regexp_split_to_array("substring"(regexp_replace((st_line.name)::text, '[^0-9|^\s]'::text, ''::text, 'g'::text), '\S(?:.*\S)*'::text), '\s+'::text))))
  • Rows Removed by Join Filter: 763,803
13. 234.791 5,219.413 ↓ 76,382.0 763,820 1

Nested Loop (cost=2.13..34,514.37 rows=10 width=105) (actual time=0.367..5,219.413 rows=763,820 loops=1)

  • Output: st_line.id, st_line.name, journal.default_credit_account_id, journal.default_debit_account_id, company.account_bank_reconciliation_start, line_partner.partner_id, aml.id, aml.currency_id, aml.date_maturity, aml.amount_residual, aml.amount_residual_currency, aml.balance, aml.amount_currency, aml.move_id, aml.account_id, aml.partner_id, aml.statement_id, aml.date, aml.reconciled
  • Join Filter: (COALESCE(st_line.currency_id, journal.currency_id, company.currency_id) = COALESCE(aml.currency_id, company.currency_id))
  • Rows Removed by Join Filter: 960
14. 0.036 0.036 ↑ 1.0 1 1

Index Scan using account_journal_pkey on public.account_journal journal (cost=0.14..2.16 rows=1 width=16) (actual time=0.033..0.036 rows=1 loops=1)

  • Output: journal.id, journal.default_credit_account_id, journal.default_debit_account_id, journal.currency_id
  • Index Cond: (journal.id = 140)
15. 981.546 4,984.586 ↓ 396.3 764,780 1

Nested Loop (cost=1.99..34,488.09 rows=1,930 width=113) (actual time=0.332..4,984.586 rows=764,780 loops=1)

  • Output: st_line.id, st_line.name, st_line.journal_id, st_line.currency_id, company.account_bank_reconciliation_start, company.currency_id, jnl_precision.journal_id, line_partner.partner_id, aml.id, aml.currency_id, aml.date_maturity, aml.amount_residual, aml.amount_residual_currency, aml.balance, aml.amount_currency, aml.move_id, aml.account_id, aml.partner_id, aml.statement_id, aml.date, aml.reconciled
  • Join Filter: (sign(st_line.amount) = sign(aml.balance))
  • Rows Removed by Join Filter: 1,893,905
16. 0.011 0.665 ↓ 5.0 5 1

Nested Loop (cost=1.56..13.28 rows=1 width=79) (actual time=0.249..0.665 rows=5 loops=1)

  • Output: st_line.id, st_line.name, st_line.journal_id, st_line.company_id, st_line.amount, st_line.currency_id, company.id, company.account_bank_reconciliation_start, company.currency_id, jnl_precision.journal_id, line_partner.partner_id
  • Join Filter: (st_line.id = line_partner.line_id)
  • Rows Removed by Join Filter: 20
17. 0.009 0.629 ↓ 5.0 5 1

Nested Loop (cost=1.56..13.11 rows=1 width=75) (actual time=0.243..0.629 rows=5 loops=1)

  • Output: st_line.id, st_line.name, st_line.journal_id, st_line.company_id, st_line.amount, st_line.currency_id, company.id, company.account_bank_reconciliation_start, company.currency_id, jnl_precision.journal_id
18. 0.057 0.280 ↓ 5.0 5 1

Nested Loop (cost=1.56..11.71 rows=1 width=71) (actual time=0.061..0.280 rows=5 loops=1)

  • Output: st_line.id, st_line.name, st_line.journal_id, st_line.company_id, st_line.amount, st_line.currency_id, company.id, company.account_bank_reconciliation_start, company.currency_id
  • Inner Unique: true
19. 0.093 0.093 ↓ 5.0 5 1

Index Scan using account_bank_statement_line_pkey on public.account_bank_statement_line st_line (cost=0.42..9.27 rows=1 width=59) (actual time=0.021..0.093 rows=5 loops=1)

  • Output: st_line.id, st_line.name, st_line.journal_id, st_line.company_id, st_line.amount, st_line.currency_id
  • Index Cond: (st_line.id = ANY ('{189110,189111,189112,189113,189114}'::integer[]))
  • Filter: ((st_line.amount >= 0.0) AND (st_line.journal_id = 140))
20. 0.090 0.130 ↑ 1.0 1 5

Bitmap Heap Scan on public.res_company company (cost=1.14..2.15 rows=1 width=12) (actual time=0.026..0.026 rows=1 loops=5)

  • Output: company.id, company.account_bank_reconciliation_start, company.currency_id
  • Recheck Cond: (company.id = st_line.company_id)
  • Heap Blocks: exact=10
21. 0.040 0.040 ↓ 2.0 2 5

Bitmap Index Scan on res_company_pkey (cost=0.00..1.14 rows=1 width=0) (actual time=0.008..0.008 rows=2 loops=5)

  • Index Cond: (company.id = st_line.company_id)
22. 0.340 0.340 ↑ 1.0 1 5

CTE Scan on jnl_precision (cost=0.00..1.40 rows=1 width=4) (actual time=0.037..0.068 rows=1 loops=5)

  • Output: jnl_precision.journal_id, jnl_precision.dp
  • Filter: (jnl_precision.journal_id = 140)
  • Rows Removed by Filter: 61
23. 0.025 0.025 ↑ 1.0 5 5

CTE Scan on partners_table line_partner (cost=0.00..0.10 rows=5 width=8) (actual time=0.001..0.005 rows=5 loops=5)

  • Output: line_partner.line_id, line_partner.partner_id
24. 4,002.375 4,002.375 ↓ 1.4 531,737 5

Index Scan using account_move_line_company_id_partner_id_balance_statement_i_idx on public.account_move_line aml (cost=0.43..27,719.76 rows=386,003 width=52) (actual time=0.017..800.475 rows=531,737 loops=5)

  • Output: aml.id, aml.currency_id, aml.date_maturity, aml.amount_residual, aml.amount_residual_currency, aml.balance, aml.amount_currency, aml.move_id, aml.account_id, aml.company_id, aml.partner_id, aml.statement_id, aml.date, aml.reconciled
  • Index Cond: (aml.company_id = company.id)
25. 3,819.100 3,819.100 ↑ 1.0 1 763,820

Index Scan using account_move_pkey on public.account_move move (cost=0.42..0.45 rows=1 width=38) (actual time=0.005..0.005 rows=1 loops=763,820)

  • Output: move.name, move.ref, move.id
  • Index Cond: (move.id = aml.move_id)
26. 10.846 10.846 ↑ 1.0 2,488 17

Seq Scan on public.account_account account (cost=0.00..135.88 rows=2,488 width=11) (actual time=0.004..0.638 rows=2,488 loops=17)

  • Output: account.internal_type, account.id, account.reconcile
Planning time : 11.966 ms
Execution time : 47,827.734 ms