explain.depesz.com

PostgreSQL's explain analyze made readable

Result: I9a

Settings
# exclusive inclusive rows x rows loops node
1. 69.796 253,259.041 ↓ 44,280.0 44,280 1

Sort (cost=233,949.75..233,949.76 rows=1 width=46) (actual time=253,251.528..253,259.041 rows=44,280 loops=1)

  • Sort Key: aml.date_maturity, aml.id
  • Sort Method: quicksort Memory: 7,398kB
  • Sort Key: aml.date_maturity, aml.id
  • Sort Method: quicksort Memory: 7,398kB
2.          

CTE jnl_precision

3. 0.054 0.886 ↑ 1.0 24 1

Hash Right Join (cost=4.43..10.18 rows=24 width=8) (actual time=0.861..0.886 rows=24 loops=1)

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

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

5. 0.006 0.291 ↑ 1.0 24 1

Hash (cost=4.13..4.13 rows=24 width=12) (actual time=0.287..0.291 rows=24 loops=1)

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

Hash Left Join (cost=1.14..4.13 rows=24 width=12) (actual time=0.021..0.285 rows=24 loops=1)

  • Hash Cond: (j.company_id = c.id)
7. 0.258 0.258 ↑ 1.0 24 1

Seq Scan on account_journal j (cost=0.00..2.89 rows=24 width=12) (actual time=0.005..0.258 rows=24 loops=1)

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

Hash (cost=1.06..1.06 rows=6 width=8) (actual time=0.008..0.008 rows=6 loops=1)

  • Buckets: 1,024 Batches: 1 Memory Usage: 9kB
9. 0.005 0.005 ↑ 1.0 6 1

Seq Scan on res_company c (cost=0.00..1.06 rows=6 width=8) (actual time=0.004..0.005 rows=6 loops=1)

10.          

CTE jnl_precision

11. 0.054 0.886 ↑ 1.0 24 1

Hash Right Join (cost=4.43..10.18 rows=24 width=8) (actual time=0.861..0.886 rows=24 loops=1)

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

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

13. 0.006 0.291 ↑ 1.0 24 1

Hash (cost=4.13..4.13 rows=24 width=12) (actual time=0.287..0.291 rows=24 loops=1)

  • Buckets: 1,024 Batches: 1 Memory Usage: 9kB
14. 0.019 0.285 ↑ 1.0 24 1

Hash Left Join (cost=1.14..4.13 rows=24 width=12) (actual time=0.021..0.285 rows=24 loops=1)

  • Hash Cond: (j.company_id = c.id)
15. 0.258 0.258 ↑ 1.0 24 1

Seq Scan on account_journal j (cost=0.00..2.89 rows=24 width=12) (actual time=0.005..0.258 rows=24 loops=1)

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

Hash (cost=1.06..1.06 rows=6 width=8) (actual time=0.008..0.008 rows=6 loops=1)

  • Buckets: 1,024 Batches: 1 Memory Usage: 9kB
17. 0.005 0.005 ↑ 1.0 6 1

Seq Scan on res_company c (cost=0.00..1.06 rows=6 width=8) (actual time=0.004..0.005 rows=6 loops=1)

18.          

CTE partners_table

19. 0.035 0.035 ↑ 1.0 131 1

Values Scan on "*VALUES*" (cost=0.00..1.64 rows=131 width=8) (actual time=0.003..0.035 rows=131 loops=1)

20. 12,576.522 253,188.987 ↓ 44,280.0 44,280 1

Nested Loop Left Join (cost=5.29..233,937.93 rows=1 width=46) (actual time=19.952..253,188.987 rows=44,280 loops=1)

  • Join Filter: (account.id = aml.account_id)
  • Rows Removed by Join Filter: 134,375,194
  • 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,804
21. 777.056 227,542.953 ↓ 726,084.0 726,084 1

Nested Loop Left Join (cost=5.29..233,851.84 rows=1 width=117) (actual time=3.237..227,542.953 rows=726,084 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))))))
22. 684.587 224,587.645 ↓ 11,170.5 726,084 1

Nested Loop Left Join (cost=4.86..233,817.22 rows=65 width=92) (actual time=3.216..224,587.645 rows=726,084 loops=1)

  • Join Filter: (company.id = st_line.company_id)
  • Rows Removed by Join Filter: 726,084
  • Filter: (COALESCE(st_line.currency_id, journal.currency_id, company.currency_id) = COALESCE(aml.currency_id, company.currency_id))
23. 138,472.707 223,903.058 ↓ 55.5 726,084 1

Nested Loop (cost=4.86..232,605.93 rows=13,083 width=100) (actual time=3.205..223,903.058 rows=726,084 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: 464,624,550
24. 0.473 11.675 ↓ 126.0 126 1

Nested Loop Left Join (cost=4.86..368.87 rows=1 width=58) (actual time=1.469..11.675 rows=126 loops=1)

  • Join Filter: (jnl_precision.journal_id = journal.id)
  • Rows Removed by Join Filter: 2,898
25. 0.932 9.690 ↓ 126.0 126 1

Nested Loop Left Join (cost=4.86..368.09 rows=1 width=62) (actual time=0.592..9.690 rows=126 loops=1)

  • Join Filter: (journal.id = st_line.journal_id)
  • Rows Removed by Join Filter: 2,268
26. 0.537 8.254 ↓ 126.0 126 1

Hash Join (cost=4.86..364.49 rows=1 width=50) (actual time=0.573..8.254 rows=126 loops=1)

  • Hash Cond: (st_line.id = line_partner.line_id)
27. 7.626 7.626 ↑ 1.0 131 1

Index Scan using account_bank_statement_line_pkey on account_bank_statement_line st_line (cost=0.29..359.42 rows=131 width=46) (actual time=0.458..7.626 rows=131 loops=1)

  • Index Cond: (id = ANY ('{23295,23294,23424,23423,23422,23421,23420,23419,23418,23417,23416,23415,23414,23413,23412,23411,23410,23409,23408,23407,23406,23405,23404,23403,23402,23401,23400,23399,23398,23397,23396,23395,23394,23393,23392,23391,23390,23389,23388,23387,23386,23385,23384,23383,23382,23381,23380,23379,23378,23377,23376,23375,23374,23373,23372,23371,23370,23369,23368,23367,23366,23365,23364,23363,23362,23361,23360,23359,23358,23357,23356,23355,23354,23353,23352,23351,23350,23349,23348,23347,23346,23345,23344,23343,23342,23341,23340,23339,23338,23337,23336,23335,23334,23333,23332,23331,23330,23329,23328,23327,23326,23325,23324,23323,23322,23321,23320,23319,23318,23317,23316,23315,23314,23313,23312,23311,23310,23309,23308,23307,23306,23305,23304,23303,23302,23301,23300,23299,23298,23297,23296}'::integer[]))
28. 0.017 0.091 ↑ 1.0 126 1

Hash (cost=2.95..2.95 rows=130 width=8) (actual time=0.090..0.091 rows=126 loops=1)

  • Buckets: 1,024 Batches: 1 Memory Usage: 13kB
29. 0.074 0.074 ↑ 1.0 126 1

CTE Scan on partners_table line_partner (cost=0.00..2.95 rows=130 width=8) (actual time=0.007..0.074 rows=126 loops=1)

  • Filter: (partner_id <> 0)
  • Rows Removed by Filter: 5
30. 0.504 0.504 ↑ 3.7 19 126

Seq Scan on account_journal journal (cost=0.00..2.71 rows=71 width=16) (actual time=0.003..0.004 rows=19 loops=126)

31. 1.512 1.512 ↑ 1.0 24 126

CTE Scan on jnl_precision (cost=0.00..0.48 rows=24 width=4) (actual time=0.007..0.012 rows=24 loops=126)

32. 85,418.676 85,418.676 ↑ 1.0 3,693,259 126

Seq Scan on account_move_line aml (cost=0.00..139,905.59 rows=3,693,259 width=52) (actual time=0.023..677.926 rows=3,693,259 loops=126)

33. 0.000 0.000 ↑ 3.0 2 726,084

Materialize (cost=0.00..1.09 rows=6 width=12) (actual time=0.000..0.000 rows=2 loops=726,084)

34. 0.006 0.006 ↑ 3.0 2 1

Seq Scan on res_company company (cost=0.00..1.06 rows=6 width=12) (actual time=0.004..0.006 rows=2 loops=1)

35. 2,178.252 2,178.252 ↑ 1.0 1 726,084

Index Scan using account_move_pkey on account_move move (cost=0.43..0.47 rows=1 width=41) (actual time=0.003..0.003 rows=1 loops=726,084)

  • Index Cond: (id = aml.move_id)
36. 13,069.512 13,069.512 ↑ 9.3 186 726,084

Seq Scan on account_account account (cost=0.00..51.35 rows=1,735 width=11) (actual time=0.001..0.018 rows=186 loops=726,084)

37. 0.000 0.258 ↑ 1.0 24 1

Execution time: 253266.543 msaccount_journal j (cost=0.00..2.89 rows=24 width=12) (actual time=0.005..0.258 rows=24 loops=1)

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

CTE partners_table

39. 0.035 0.035 ↑ 1.0 131 1

Values Scan on "*VALUES*" (cost=0.00..1.64 rows=131 width=8) (actual time=0.003..0.035 rows=131 loops=1)

40. 0.003 0.008 ↑ 1.0 6 1

Hash (cost=1.06..1.06 rows=6 width=8) (actual time=0.008..0.008 rows=6 loops=1)

  • Buckets: 1,024 Batches: 1 Memory Usage: 9kB
41. 0.005 0.005 ↑ 1.0 6 1

Seq Scan on res_company c (cost=0.00..1.06 rows=6 width=8) (actual time=0.004..0.005 rows=6 loops=1)

42. 12,576.522 253,188.987 ↓ 44,280.0 44,280 1

Nested Loop Left Join (cost=5.29..233,937.93 rows=1 width=46) (actual time=19.952..253,188.987 rows=44,280 loops=1)

  • Join Filter: (account.id = aml.account_id)
  • Rows Removed by Join Filter: 134,375,194
  • 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,804
43. 777.056 227,542.953 ↓ 726,084.0 726,084 1

Nested Loop Left Join (cost=5.29..233,851.84 rows=1 width=117) (actual time=3.237..227,542.953 rows=726,084 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))))))
44. 684.587 224,587.645 ↓ 11,170.5 726,084 1

Nested Loop Left Join (cost=4.86..233,817.22 rows=65 width=92) (actual time=3.216..224,587.645 rows=726,084 loops=1)

  • Join Filter: (company.id = st_line.company_id)
  • Rows Removed by Join Filter: 726,084
  • Filter: (COALESCE(st_line.currency_id, journal.currency_id, company.currency_id) = COALESCE(aml.currency_id, company.currency_id))
45. 138,472.707 223,903.058 ↓ 55.5 726,084 1

Nested Loop (cost=4.86..232,605.93 rows=13,083 width=100) (actual time=3.205..223,903.058 rows=726,084 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: 464,624,550
46. 0.473 11.675 ↓ 126.0 126 1

Nested Loop Left Join (cost=4.86..368.87 rows=1 width=58) (actual time=1.469..11.675 rows=126 loops=1)

  • Join Filter: (jnl_precision.journal_id = journal.id)
  • Rows Removed by Join Filter: 2,898
47. 0.932 9.690 ↓ 126.0 126 1

Nested Loop Left Join (cost=4.86..368.09 rows=1 width=62) (actual time=0.592..9.690 rows=126 loops=1)

  • Join Filter: (journal.id = st_line.journal_id)
  • Rows Removed by Join Filter: 2,268
48. 0.537 8.254 ↓ 126.0 126 1

Hash Join (cost=4.86..364.49 rows=1 width=50) (actual time=0.573..8.254 rows=126 loops=1)

  • Hash Cond: (st_line.id = line_partner.line_id)
49. 7.626 7.626 ↑ 1.0 131 1

Index Scan using account_bank_statement_line_pkey on account_bank_statement_line st_line (cost=0.29..359.42 rows=131 width=46) (actual time=0.458..7.626 rows=131 loops=1)

  • Index Cond: (id = ANY ('{23295,23294,23424,23423,23422,23421,23420,23419,23418,23417,23416,23415,23414,23413,23412,23411,23410,23409,23408,23407,23406,23405,23404,23403,23402,23401,23400,23399,23398,23397,23396,23395,23394,23393,23392,23391,23390,23389,23388,23387,23386,23385,23384,23383,23382,23381,23380,23379,23378,23377,23376,23375,23374,23373,23372,23371,23370,23369,23368,23367,23366,23365,23364,23363,23362,23361,23360,23359,23358,23357,23356,23355,23354,23353,23352,23351,23350,23349,23348,23347,23346,23345,23344,23343,23342,23341,23340,23339,23338,23337,23336,23335,23334,23333,23332,23331,23330,23329,23328,23327,23326,23325,23324,23323,23322,23321,23320,23319,23318,23317,23316,23315,23314,23313,23312,23311,23310,23309,23308,23307,23306,23305,23304,23303,23302,23301,23300,23299,23298,23297,23296}'::integer[]))
50. 0.017 0.091 ↑ 1.0 126 1

Hash (cost=2.95..2.95 rows=130 width=8) (actual time=0.090..0.091 rows=126 loops=1)

  • Buckets: 1,024 Batches: 1 Memory Usage: 13kB
51. 0.074 0.074 ↑ 1.0 126 1

CTE Scan on partners_table line_partner (cost=0.00..2.95 rows=130 width=8) (actual time=0.007..0.074 rows=126 loops=1)

  • Filter: (partner_id <> 0)
  • Rows Removed by Filter: 5
52. 0.504 0.504 ↑ 3.7 19 126

Seq Scan on account_journal journal (cost=0.00..2.71 rows=71 width=16) (actual time=0.003..0.004 rows=19 loops=126)

53. 1.512 1.512 ↑ 1.0 24 126

CTE Scan on jnl_precision (cost=0.00..0.48 rows=24 width=4) (actual time=0.007..0.012 rows=24 loops=126)

54. 85,418.676 85,418.676 ↑ 1.0 3,693,259 126

Seq Scan on account_move_line aml (cost=0.00..139,905.59 rows=3,693,259 width=52) (actual time=0.023..677.926 rows=3,693,259 loops=126)

55. 0.000 0.000 ↑ 3.0 2 726,084

Materialize (cost=0.00..1.09 rows=6 width=12) (actual time=0.000..0.000 rows=2 loops=726,084)

56. 0.006 0.006 ↑ 3.0 2 1

Seq Scan on res_company company (cost=0.00..1.06 rows=6 width=12) (actual time=0.004..0.006 rows=2 loops=1)

57. 2,178.252 2,178.252 ↑ 1.0 1 726,084

Index Scan using account_move_pkey on account_move move (cost=0.43..0.47 rows=1 width=41) (actual time=0.003..0.003 rows=1 loops=726,084)

  • Index Cond: (id = aml.move_id)
58. 13,069.512 13,069.512 ↑ 9.3 186 726,084

Seq Scan on account_account account (cost=0.00..51.35 rows=1,735 width=11) (actual time=0.001..0.018 rows=186 loops=726,084)

Planning time : 37.011 ms
Execution time : 253,266.543 ms