explain.depesz.com

PostgreSQL's explain analyze made readable

Result: TmGO : Optimization for: plan #OypZ

Settings

Optimization path:

Optimization(s) for this plan:

# exclusive inclusive rows x rows loops node
1. 2.679 95,226.287 ↓ 0.0 0 1

Nested Loop (cost=37.78..34,742.09 rows=1 width=46) (actual time=95,226.287..95,226.287 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((move.name)::text, '[^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)) OR ((move.ref IS NOT NULL) AND (regexp_split_to_array("substring"(regexp_replace((move.ref)::text, '[^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 = journal.default_credit_account_id) OR (aml.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: 42296
  • aml.amount_residual_currency, aml.balance, aml.amount_currency, aml.account_id, aml.statement_id, aml.date, aml.reconciled, move.name, move.ref
2.          

CTE jnl_precision

3. 0.038 0.551 ↑ 1.0 62 1

Hash Left Join (cost=22.20..35.16 rows=62 width=8) (actual time=0.217..0.551 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.037 0.418 ↑ 1.0 62 1

Hash Left Join (cost=9.24..22.04 rows=62 width=12) (actual time=0.105..0.418 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.330 0.330 ↑ 1.0 62 1

Seq Scan on public.account_journal j (cost=0.00..12.53 rows=62 width=12) (actual time=0.038..0.330 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.005 0.051 ↓ 1.2 7 1

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

  • Output: c.id, c.currency_id
  • Buckets: 1024 Batches: 1 Memory Usage: 9kB
7. 0.046 0.046 ↓ 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.009..0.046 rows=7 loops=1)

  • Output: c.id, c.currency_id
8. 0.030 0.095 ↑ 1.0 176 1

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

  • Output: currency.decimal_places, currency.id
  • Buckets: 1024 Batches: 1 Memory Usage: 15kB
9. 0.065 0.065 ↑ 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.065 rows=176 loops=1)

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

CTE partners_table

11. 0.024 0.024 ↑ 1.0 5 1

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

  • Output: "*VALUES*".column1, "*VALUES*".column2
12. 84,809.650 95,211.300 ↓ 17.0 17 1

Nested Loop (cost=2.56..34,521.19 rows=1 width=127) (actual time=15,955.639..95,211.300 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,
  • 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 ("substring"(regexp_replace((st_line.name)::text, '[^0-9 ^\s]'::text, ''::text, 'g'::text), '\S(?:.*\S)*'::text) <> ''::text) AND ((regexp_split_to_array("substring"(regexp_replace((move.name)::text, '[^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)) OR ((move.ref IS NOT NULL) AND (regexp_split_to_array("substring"(regexp_replace((move.ref)::text, '[^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: 763803
13. 675.976 6,582.550 ↓ 381,910.0 763,820 1

Nested Loop (cost=2.13..34,520.16 rows=2 width=105) (actual time=0.607..6,582.550 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.partner_id, aml.move_id, aml.account_id, aml.statement_id, aml.date, aml.reconciled
  • Join Filter: ((st_line.id = line_partner.line_id) AND ((line_partner.partner_id = 0) OR (line_partner.partner_id = aml.partner_id)))
  • Rows Removed by Join Filter: 3055280
14. 0.044 0.044 ↑ 1.0 5 1

CTE Scan on partners_table line_partner (cost=0.00..0.10 rows=5 width=8) (actual time=0.006..0.044 rows=5 loops=1)

  • Output: line_partner.line_id, line_partner.partner_id
15. 1,288.182 5,906.530 ↓ 76,382.0 763,820 5

Materialize (cost=2.13..34,519.09 rows=10 width=101) (actual time=0.128..1,181.306 rows=763,820 loops=5)

  • 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.amou nt_residual, aml.amount_residual_currency, aml.balance, aml.amount_currency, aml.partner_id, aml.move_id, aml.account_id, aml.statement_id, aml.date, aml.reconciled
16. 282.316 4,618.348 ↓ 76,382.0 763,820 1

Nested Loop (cost=2.13..34,519.04 rows=10 width=101) (actual time=0.595..4,618.348 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, aml.id, aml.currency_id, aml.date_maturity, am l.amount_residual, aml.amount_residual_currency, aml.balance, aml.amount_currency, aml.partner_id, aml.move_id, aml.account_id, aml.statement_id, aml.date, aml.reconciled
  • Inner Unique: true
  • 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
17. 4,334.942 4,336.032 ↓ 396.3 764,780 1

Nested Loop (cost=1.99..34,487.93 rows=1,930 width=109) (actual time=0.570..4,336.032 rows=764,780 loops=1)

  • Output: st_line.id, st_line.name, st_line.journal_id, st_line.currency_id, jnl_precision.journal_id, company.account_bank_reconciliation_start, company.currency_id, aml.id, aml.currency_ id, aml.date_maturity, aml.amount_residual, aml.amount_residual_currency, aml.balance, aml.amount_currency, aml.partner_id, aml.move_id, aml.account_id, aml.statement_id, aml.date, aml.reconciled
  • Join Filter: (sign(st_line.amount) = sign(aml.balance))
  • Rows Removed by Join Filter: 1893905
  • -> Index Scan using account_move_line_company_id_partner_id_balance_statement_i_idx on public.account_move_line aml (cost=0.43..27719.76 rows=386003 width=52) (actual time=0.026..702.1 24 rows=531737 loops=5)
18. 0.057 1.090 ↓ 5.0 5 1

Nested Loop (cost=1.56..13.11 rows=1 width=75) (actual time=0.333..1.090 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, jnl_precision.journal_id, company.id, company.account_bank_reconcilia tion_start, company.currency_id
  • Inner Unique: true
  • Output: aml.id, aml.currency_id, aml.date_maturity, aml.amount_residual, aml.amount_residual_currency, aml.balance, aml.amount_currency, aml.company_id, aml.partner_id, aml.move_id , aml.account_id, aml.statement_id, aml.date, aml.reconciled Index Cond: (aml.company_id = company.id)
19. 0.283 0.918 ↓ 5.0 5 1

Nested Loop (cost=0.42..10.67 rows=1 width=63) (actual time=0.311..0.918 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, jnl_precision.journal_id
  • -> 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.084..0.270 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.635 0.635 ↑ 1.0 1 5

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

  • Output: jnl_precision.journal_id, jnl_precision.dp
  • Filter: (jnl_precision.journal_id = 140)
  • Rows Removed by Filter: 61
21. 0.065 0.115 ↑ 1.0 1 5

Bitmap Heap Scan on public.res_company company (cost=1.14..2.15 rows=1 width=12) (actual time=0.023..0.023 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
22. 0.050 0.050 ↓ 2.0 2 5

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

  • Index Cond: (company.id = st_line.company_id)
23. 0.000 0.000 ↑ 1.0 1 764,780

Materialize (cost=0.14..2.17 rows=1 width=16) (actual time=0.000..0.000 rows=1 loops=764,780)

  • Output: journal.id, journal.default_credit_account_id, journal.default_debit_account_id, journal.currency_id
24. 0.014 0.014 ↑ 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.013..0.014 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)
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. 12.308 12.308 ↑ 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.005..0.724 rows=2,488 loops=17)

  • Output: account.internal_type, account.id, account.reconcile
Planning time : 15.490 ms
Execution time : 95,245.658 ms