explain.depesz.com

PostgreSQL's explain analyze made readable

Result: FvSi

Settings
# exclusive inclusive rows x rows loops node
1. 968.444 124,844.239 ↓ 1,217,760.0 1,217,760 1

Sort (cost=42,510.01..42,510.01 rows=1 width=48) (actual time=124,657.701..124,844.239 rows=1,217,760 loops=1)

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

CTE jnl_precision

3. 0.024 0.141 ↑ 1.0 157 1

Hash Left Join (cost=7.85..16.49 rows=157 width=8) (actual time=0.069..0.141 rows=157 loops=1)

  • Hash Cond: (COALESCE(j.currency_id, c.currency_id) = currency.id)
4. 0.035 0.082 ↑ 1.0 157 1

Hash Left Join (cost=1.02..9.24 rows=157 width=12) (actual time=0.029..0.082 rows=157 loops=1)

  • Hash Cond: (j.company_id = c.id)
5. 0.038 0.038 ↑ 1.0 157 1

Seq Scan on account_journal j (cost=0.00..6.06 rows=157 width=12) (actual time=0.013..0.038 rows=157 loops=1)

  • Filter: ((type)::text = ANY ('{bank,cash}'::text[]))
  • Rows Removed by Filter: 8
6. 0.003 0.009 ↑ 1.0 1 1

Hash (cost=1.01..1.01 rows=1 width=8) (actual time=0.008..0.009 rows=1 loops=1)

  • Buckets: 1024 Batches: 1 Memory Usage: 9kB
7. 0.006 0.006 ↑ 1.0 1 1

Seq Scan on res_company c (cost=0.00..1.01 rows=1 width=8) (actual time=0.006..0.006 rows=1 loops=1)

8. 0.017 0.035 ↑ 1.0 170 1

Hash (cost=4.70..4.70 rows=170 width=8) (actual time=0.035..0.035 rows=170 loops=1)

  • Buckets: 1024 Batches: 1 Memory Usage: 15kB
9. 0.018 0.018 ↑ 1.0 170 1

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

10.          

CTE partners_table

11. 0.019 0.019 ↑ 1.0 98 1

Values Scan on "*VALUES*" (cost=0.00..1.23 rows=98 width=8) (actual time=0.001..0.019 rows=98 loops=1)

12. 54,850.397 123,875.795 ↓ 1,217,760.0 1,217,760 1

Nested Loop Left Join (cost=4.27..42,492.28 rows=1 width=48) (actual time=0.261..123,875.795 rows=1,217,760 loops=1)

  • Join Filter: (account.id = aml.account_id)
  • Rows Removed by Join Filter: 684285216
  • 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: 7168608
13. 5,381.660 35,479.926 ↓ 8,386,368.0 8,386,368 1

Nested Loop Left Join (cost=4.27..42,478.06 rows=1 width=103) (actual time=0.188..35,479.926 rows=8,386,368 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. 2,199.220 21,711.898 ↓ 254,132.4 8,386,368 1

Nested Loop Left Join (cost=3.85..42,461.39 rows=33 width=84) (actual time=0.181..21,711.898 rows=8,386,368 loops=1)

  • Join Filter: (company.id = st_line.company_id)
  • Filter: (COALESCE(st_line.currency_id, journal.currency_id, company.currency_id) = COALESCE(aml.currency_id, company.currency_id))
15. 14,146.742 19,512.678 ↓ 1,288.6 8,386,368 1

Nested Loop (cost=3.85..42,346.48 rows=6,508 width=92) (actual time=0.175..19,512.678 rows=8,386,368 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: 54398496
16. 0.750 3.088 ↓ 96.0 96 1

Nested Loop Left Join (cost=3.85..203.17 rows=1 width=50) (actual time=0.168..3.088 rows=96 loops=1)

  • Join Filter: (jnl_precision.journal_id = journal.id)
  • Rows Removed by Join Filter: 14976
17. 0.185 1.378 ↓ 96.0 96 1

Nested Loop Left Join (cost=3.85..198.07 rows=1 width=54) (actual time=0.073..1.378 rows=96 loops=1)

18. 0.262 0.905 ↓ 96.0 96 1

Hash Join (cost=3.71..197.83 rows=1 width=42) (actual time=0.066..0.905 rows=96 loops=1)

  • Hash Cond: (st_line.id = line_partner.line_id)
19. 0.598 0.598 ↓ 1.1 98 1

Index Scan using account_bank_statement_line_pkey on account_bank_statement_line st_line (cost=0.29..194.05 rows=93 width=38) (actual time=0.015..0.598 rows=98 loops=1)

  • Index Cond: (id = ANY ('{61927,63772,63993,64568,64587,74953,75102,75167,76275,76278,76317,76665,76716,76719,76718,76750,76779,76809,76826,76803,78217,78218,78220,76777,78219,78222,76962,78168,78171,78175,78177,78178,78181,78182,78183,78184,78186,78187,78188,78189,78190,78191,78192,78193,78195,78196,78197,78198,78199,78200,78201,78202,78203,78204,78205,78208,78209,78210,78211,78212,78213,78214,78215,78216,78221,79186,79185,91851,92215,92223,92229,92231,92248,92395,92452,92467,92470,92525,92542,92554,92556,92557,92558,92559,92587,92589,92592,92593,92595,92600,92608,92708,92747,92754,92764,92777,92785,92786}'::integer[]))
  • Filter: (journal_id = ANY ('{511,513,556,507,508,509,510,527,526,520,559,560,419,8,562,563,564,515,506,519,525,501,505,521}'::integer[]))
20. 0.009 0.045 ↑ 1.0 96 1

Hash (cost=2.21..2.21 rows=97 width=8) (actual time=0.045..0.045 rows=96 loops=1)

  • Buckets: 1024 Batches: 1 Memory Usage: 12kB
21. 0.036 0.036 ↑ 1.0 96 1

CTE Scan on partners_table line_partner (cost=0.00..2.21 rows=97 width=8) (actual time=0.002..0.036 rows=96 loops=1)

  • Filter: (partner_id <> 0)
  • Rows Removed by Filter: 2
22. 0.288 0.288 ↑ 1.0 1 96

Index Scan using account_journal_pkey on account_journal journal (cost=0.14..0.23 rows=1 width=16) (actual time=0.003..0.003 rows=1 loops=96)

  • Index Cond: (id = st_line.journal_id)
23. 0.960 0.960 ↑ 1.0 157 96

CTE Scan on jnl_precision (cost=0.00..3.14 rows=157 width=4) (actual time=0.001..0.010 rows=157 loops=96)

24. 5,362.848 5,362.848 ↑ 1.0 654,009 96

Seq Scan on account_move_line aml (cost=0.00..25,793.09 rows=654,009 width=52) (actual time=0.002..55.863 rows=654,009 loops=96)

25. 0.000 0.000 ↑ 1.0 1 8,386,368

Materialize (cost=0.00..1.01 rows=1 width=12) (actual time=0.000..0.000 rows=1 loops=8,386,368)

26. 0.001 0.001 ↑ 1.0 1 1

Seq Scan on res_company company (cost=0.00..1.01 rows=1 width=12) (actual time=0.001..0.001 rows=1 loops=1)

27. 8,386.368 8,386.368 ↑ 1.0 1 8,386,368

Index Scan using account_move_pkey on account_move move (cost=0.42..0.45 rows=1 width=35) (actual time=0.001..0.001 rows=1 loops=8,386,368)

  • Index Cond: (id = aml.move_id)
28. 33,545.472 33,545.472 ↑ 3.3 83 8,386,368

Seq Scan on account_account account (cost=0.00..8.73 rows=273 width=13) (actual time=0.001..0.004 rows=83 loops=8,386,368)

Planning time : 4.125 ms
Execution time : 124,877.908 ms