explain.depesz.com

PostgreSQL's explain analyze made readable

Result: kQPy

Settings
# exclusive inclusive rows x rows loops node
1. 162.280 818,540.531 ↓ 44,283.0 44,283 1

Sort (cost=307,202.31..307,202.31 rows=1 width=45) (actual time=818,529.525..818,540.531 rows=44,283 loops=1)

  • Sort Key: aml.date_maturity, aml.id
  • Sort Method: external merge Disk: 2,904kB
2.          

CTE jnl_precision

3. 0.051 0.305 ↑ 1.0 24 1

Hash Right Join (cost=6.82..12.57 rows=24 width=8) (actual time=0.293..0.305 rows=24 loops=1)

  • Hash Cond: (currency.id = COALESCE(j.currency_id, c.currency_id))
4. 0.087 0.087 ↑ 1.0 170 1

Seq Scan on res_currency currency (cost=0.00..4.70 rows=170 width=8) (actual time=0.014..0.087 rows=170 loops=1)

5. 0.013 0.167 ↑ 1.0 24 1

Hash (cost=6.52..6.52 rows=24 width=12) (actual time=0.167..0.167 rows=24 loops=1)

  • Buckets: 1,024 Batches: 1 Memory Usage: 9kB
6. 0.009 0.154 ↑ 1.0 24 1

Merge Right Join (cost=4.57..6.52 rows=24 width=12) (actual time=0.102..0.154 rows=24 loops=1)

  • Merge Cond: (c.id = j.company_id)
7. 0.046 0.046 ↑ 2.0 3 1

Index Scan using res_company_pkey on res_company c (cost=0.13..9.64 rows=6 width=8) (actual time=0.003..0.046 rows=3 loops=1)

8. 0.027 0.099 ↑ 1.0 24 1

Sort (cost=4.44..4.50 rows=24 width=12) (actual time=0.096..0.099 rows=24 loops=1)

  • Sort Key: j.company_id
  • Sort Method: quicksort Memory: 26kB
9. 0.072 0.072 ↑ 1.0 24 1

Seq Scan on account_journal j (cost=0.00..3.89 rows=24 width=12) (actual time=0.010..0.072 rows=24 loops=1)

  • Filter: ((type)::text = ANY ('{bank,cash}'::text[]))
  • Rows Removed by Filter: 47
10.          

CTE partners_table

11. 0.042 0.042 ↑ 1.0 126 1

Values Scan on "*VALUES*" (cost=0.00..1.58 rows=126 width=8) (actual time=0.003..0.042 rows=126 loops=1)

12. 192,716.746 818,378.251 ↓ 44,283.0 44,283 1

Nested Loop Left Join (cost=5.39..307,188.16 rows=1 width=45) (actual time=92.299..818,378.251 rows=44,283 loops=1)

  • Join Filter: (account.id = aml.account_id)
  • Rows Removed by Join Filter: 1,258,774,758
  • Filter: ((((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 Filter: 681,654
13. 1,013.256 453,614.436 ↓ 725,937.0 725,937 1

Nested Loop Left Join (cost=5.39..307,062.55 rows=1 width=116) (actual time=19.895..453,614.436 rows=725,937 loops=1)

  • Filter: (((line_partner.partner_id <> 0) AND (aml.partner_id = line_partner.partner_id)) OR ((line_partner.partner_id = 0) AND (btrim(regexp_replace((st_line.name)::text, '[^0-9|^\s]'::text, ''::text, 'g'::text)) <> ''::text) AND ((regexp_split_to_array(btrim(regexp_replace((move.name)::text, '[^0-9|^\s]'::text, ''::text, 'g'::text)), '\s+'::text) && regexp_split_to_array(btrim(regexp_replace((st_line.name)::text, '[^0-9|^\s]'::text, ''::text, 'g'::text)), '\s+'::text)) OR ((move.ref IS NOT NULL) AND (regexp_split_to_array(btrim(regexp_replace((move.ref)::text, '[^0-9|^\s]'::text, ''::text, 'g'::text)), '\s+'::text) && regexp_split_to_array(btrim(regexp_replace((st_line.name)::text, '[^0-9|^\s]'::text, ''::text, 'g'::text)), '\s+'::text))))))
14. 1,326.171 448,971.495 ↓ 11,342.8 725,937 1

Nested Loop Left Join (cost=4.96..307,029.28 rows=64 width=91) (actual time=19.870..448,971.495 rows=725,937 loops=1)

  • Join Filter: (company.id = st_line.company_id)
  • Rows Removed by Join Filter: 3,629,685
  • Filter: (COALESCE(st_line.currency_id, journal.currency_id, company.currency_id) = COALESCE(aml.currency_id, company.currency_id))
15. 116,946.929 446,919.387 ↓ 56.4 725,937 1

Nested Loop (cost=4.83..305,667.54 rows=12,877 width=99) (actual time=19.851..446,919.387 rows=725,937 loops=1)

  • Join Filter: ((st_line.company_id = aml.company_id) AND ((line_partner.partner_id = 0) OR (aml.partner_id = line_partner.partner_id)) AND CASE WHEN (st_line.amount > 0.0) THEN (aml.balance > '0'::numeric) ELSE (aml.balance < '0'::numeric) END)
  • Rows Removed by Join Filter: 453,663,246
16. 0.845 6.385 ↓ 123.0 123 1

Nested Loop Left Join (cost=4.83..162.47 rows=1 width=58) (actual time=0.526..6.385 rows=123 loops=1)

  • Join Filter: (jnl_precision.journal_id = journal.id)
  • Rows Removed by Join Filter: 2,829
17. 0.596 4.310 ↓ 123.0 123 1

Nested Loop Left Join (cost=4.83..161.69 rows=1 width=62) (actual time=0.223..4.310 rows=123 loops=1)

18. 0.589 2.730 ↓ 123.0 123 1

Hash Join (cost=4.69..161.48 rows=1 width=50) (actual time=0.204..2.730 rows=123 loops=1)

  • Hash Cond: (st_line.id = line_partner.line_id)
19. 2.009 2.009 ↑ 1.0 126 1

Index Scan using account_bank_statement_line_pkey on account_bank_statement_line st_line (cost=0.29..156.60 rows=126 width=46) (actual time=0.054..2.009 rows=126 loops=1)

  • Index Cond: (id = ANY ('{23121,23120,23250,23249,23248,23247,23246,23245,23244,23241,23240,23239,23238,23237,23236,23235,23234,23233,23232,23231,23230,23229,23228,23227,23224,23223,23221,23220,23219,23218,23217,23216,23215,23214,23213,23212,23211,23210,23209,23208,23207,23206,23205,23204,23203,23202,23201,23200,23199,23198,23197,23195,23194,23193,23192,23191,23190,23189,23188,23187,23186,23185,23184,23183,23182,23181,23180,23179,23178,23177,23176,23175,23174,23173,23172,23171,23170,23169,23168,23167,23166,23165,23164,23163,23162,23161,23160,23159,23158,23157,23156,23155,23154,23153,23152,23151,23150,23149,23148,23147,23146,23145,23144,23143,23142,23141,23140,23139,23138,23137,23136,23135,23134,23133,23132,23131,23130,23129,23128,23127,23126,23125,23124,23123,23122,23196}'::integer[]))
20. 0.027 0.132 ↑ 1.0 123 1

Hash (cost=2.83..2.83 rows=125 width=8) (actual time=0.132..0.132 rows=123 loops=1)

  • Buckets: 1,024 Batches: 1 Memory Usage: 13kB
21. 0.105 0.105 ↑ 1.0 123 1

CTE Scan on partners_table line_partner (cost=0.00..2.83 rows=125 width=8) (actual time=0.009..0.105 rows=123 loops=1)

  • Filter: (partner_id <> 0)
  • Rows Removed by Filter: 3
22. 0.984 0.984 ↑ 1.0 1 123

Index Scan using account_journal_pkey on account_journal journal (cost=0.14..0.20 rows=1 width=16) (actual time=0.005..0.008 rows=1 loops=123)

  • Index Cond: (id = st_line.journal_id)
23. 1.230 1.230 ↑ 1.0 24 123

CTE Scan on jnl_precision (cost=0.00..0.48 rows=24 width=4) (actual time=0.003..0.010 rows=24 loops=123)

24. 329,966.073 329,966.073 ↓ 1.1 3,694,221 123

Seq Scan on account_move_line aml (cost=0.00..218,623.59 rows=3,475,259 width=51) (actual time=0.363..2,682.651 rows=3,694,221 loops=123)

25. 725.876 725.937 ↑ 1.0 6 725,937

Materialize (cost=0.13..9.67 rows=6 width=12) (actual time=0.000..0.001 rows=6 loops=725,937)

26. 0.061 0.061 ↑ 1.0 6 1

Index Scan using res_company_pkey on res_company company (cost=0.13..9.64 rows=6 width=12) (actual time=0.009..0.061 rows=6 loops=1)

27. 3,629.685 3,629.685 ↑ 1.0 1 725,937

Index Scan using account_move_pkey on account_move move (cost=0.43..0.46 rows=1 width=41) (actual time=0.005..0.005 rows=1 loops=725,937)

  • Index Cond: (id = aml.move_id)
28. 172,047.069 172,047.069 ↓ 1.0 1,735 725,937

Seq Scan on account_account account (cost=0.00..91.19 rows=1,719 width=11) (actual time=0.003..0.237 rows=1,735 loops=725,937)

Planning time : 12.337 ms
Execution time : 818,545.408 ms