explain.depesz.com

PostgreSQL's explain analyze made readable

Result: O9GJ

Settings
# exclusive inclusive rows x rows loops node
1. 2.303 23,598.965 ↓ 0.0 0 1

Insert on money_movement_agg mma (cost=10,747.41..10,747.48 rows=2 width=38) (actual time=23,598.965..23,598.965 rows=0 loops=1)

  • Conflict Resolution: UPDATE
  • Conflict Arbiter Indexes: uq_money_movement_agg_supplier_contract_id
  • Tuples Inserted: 2
  • Conflicting Tuples: 5
2.          

CTE history

3. 190.173 401.583 ↓ 100.0 10,000 1

Insert on history (cost=3.27..4,013.75 rows=100 width=301) (actual time=75.732..401.583 rows=10,000 loops=1)

4. 10.068 211.410 ↓ 100.0 10,000 1

Nested Loop Left Join (cost=3.27..4,013.75 rows=100 width=301) (actual time=75.696..211.410 rows=10,000 loops=1)

5. 8.155 171.342 ↓ 100.0 10,000 1

Nested Loop Left Join (cost=2.25..3,908.25 rows=100 width=209) (actual time=75.662..171.342 rows=10,000 loops=1)

  • Join Filter: (c.currency_code = l.supplier_contract_currency_id)
  • Rows Removed by Join Filter: 120000
6. 13.393 153.187 ↓ 100.0 10,000 1

Nested Loop Left Join (cost=2.25..3,889.10 rows=100 width=209) (actual time=75.652..153.187 rows=10,000 loops=1)

  • Join Filter: (c.currency_code = l.currency_id)
  • Rows Removed by Join Filter: 110000
7. 13.534 129.794 ↓ 100.0 10,000 1

Hash Right Join (cost=2.25..3,869.95 rows=100 width=201) (actual time=75.625..129.794 rows=10,000 loops=1)

  • Hash Cond: (be.supplier_contract_id = l.supplier_contract_id)
8. 41.173 41.173 ↓ 1.0 61,557 1

Seq Scan on balance_entry be (cost=0.00..3,636.71 rows=61,329 width=12) (actual time=0.032..41.173 rows=61,557 loops=1)

  • Filter: (register_type_id IS NULL)
  • Rows Removed by Filter: 123114
9. 1.652 75.087 ↓ 100.0 10,000 1

Hash (cost=1.00..1.00 rows=100 width=193) (actual time=75.086..75.087 rows=10,000 loops=1)

  • Buckets: 16384 (originally 1024) Batches: 1 (originally 1) Memory Usage: 1076kB
10. 73.435 73.435 ↓ 100.0 10,000 1

Function Scan on json_to_recordset l (cost=0.00..1.00 rows=100 width=193) (actual time=72.663..73.435 rows=10,000 loops=1)

11. 9.962 10.000 ↑ 1.0 12 10,000

Materialize (cost=0.00..1.18 rows=12 width=8) (actual time=0.000..0.001 rows=12 loops=10,000)

12. 0.038 0.038 ↑ 1.0 12 1

Seq Scan on currency c (cost=0.00..1.12 rows=12 width=8) (actual time=0.020..0.038 rows=12 loops=1)

13. 9.996 10.000 ↑ 1.0 12 10,000

Materialize (cost=0.00..1.18 rows=12 width=4) (actual time=0.000..0.001 rows=12 loops=10,000)

14. 0.004 0.004 ↑ 1.0 12 1

Seq Scan on currency c1 (cost=0.00..1.12 rows=12 width=4) (actual time=0.002..0.004 rows=12 loops=1)

15. 10.000 30.000 ↑ 1.0 1 10,000

Limit (cost=1.01..1.02 rows=1 width=4) (actual time=0.002..0.003 rows=1 loops=10,000)

16. 10.000 20.000 ↑ 1.0 1 10,000

Aggregate (cost=1.01..1.02 rows=1 width=4) (actual time=0.002..0.002 rows=1 loops=10,000)

17. 10.000 10.000 ↓ 0.0 0 10,000

Seq Scan on revise r (cost=0.00..1.01 rows=1 width=4) (actual time=0.001..0.001 rows=0 loops=10,000)

  • Filter: (supplier_contract_id = l.supplier_contract_id)
  • Rows Removed by Filter: 1
18.          

CTE curr_docs

19. 12.554 3,587.343 ↓ 148.5 7,573 1

Nested Loop Left Join (cost=4,708.13..5,527.94 rows=51 width=79) (actual time=386.266..3,587.343 rows=7,573 loops=1)

20. 7.901 3,536.924 ↓ 148.5 7,573 1

Hash Left Join (cost=4,707.11..5,472.99 rows=51 width=67) (actual time=386.238..3,536.924 rows=7,573 loops=1)

  • Hash Cond: (mm_1.supplier_contract_id = be_1.supplier_contract_id)
21. 8.613 3,485.029 ↓ 148.5 7,573 1

Nested Loop (cost=3.79..466.84 rows=51 width=59) (actual time=342.117..3,485.029 rows=7,573 loops=1)

  • Join Filter: (cde.doc_edit_id = mm_1.doc_edit_id)
22. 5.155 3,415.832 ↓ 151.5 7,573 1

Nested Loop (cost=3.36..440.51 rows=50 width=41) (actual time=342.099..3,415.832 rows=7,573 loops=1)

23. 7.791 457.207 ↓ 151.5 7,573 1

Nested Loop (cost=3.36..314.51 rows=50 width=33) (actual time=341.825..457.207 rows=7,573 loops=1)

24. 11.731 411.551 ↓ 75.7 7,573 1

Nested Loop (cost=2.93..269.50 rows=100 width=25) (actual time=341.763..411.551 rows=7,573 loops=1)

25. 14.600 349.820 ↓ 100.0 10,000 1

HashAggregate (cost=2.50..3.50 rows=100 width=12) (actual time=341.699..349.820 rows=10,000 loops=1)

  • Group Key: h.doc_id, h.doc_type_id
26. 335.220 335.220 ↓ 100.0 10,000 1

CTE Scan on history h (cost=0.00..2.00 rows=100 width=12) (actual time=0.001..335.220 rows=10,000 loops=1)

27. 50.000 50.000 ↑ 1.0 1 10,000

Index Scan using ix_document_edition_doc_id_doc_type_id on document_edition de (cost=0.43..2.65 rows=1 width=25) (actual time=0.005..0.005 rows=1 loops=10,000)

  • Index Cond: ((doc_id = h.doc_id) AND (doc_type_id = h.doc_type_id))
28. 37.865 37.865 ↑ 1.0 1 7,573

Index Only Scan using pk_current_document_edition on current_document_edition cde (cost=0.43..0.45 rows=1 width=8) (actual time=0.005..0.005 rows=1 loops=7,573)

  • Index Cond: (doc_edit_id = de.doc_edit_id)
  • Heap Fetches: 7573
29. 7.573 2,953.470 ↑ 1.0 1 7,573

Limit (cost=0.00..2.50 rows=1 width=8) (actual time=0.390..0.390 rows=1 loops=7,573)

30. 2,945.897 2,945.897 ↑ 1.0 1 7,573

CTE Scan on history h_1 (cost=0.00..2.50 rows=1 width=8) (actual time=0.389..0.389 rows=1 loops=7,573)

  • Filter: ((doc_id = de.doc_id) AND (doc_type_id = de.doc_type_id))
  • Rows Removed by Filter: 4032
31. 60.584 60.584 ↑ 1.0 1 7,573

Index Scan using idx_money_movement_doc_edit_id on money_movement mm_1 (cost=0.43..0.51 rows=1 width=30) (actual time=0.007..0.008 rows=1 loops=7,573)

  • Index Cond: (doc_edit_id = de.doc_edit_id)
  • Filter: (NOT is_storno)
32. 12.216 43.994 ↓ 1.0 61,557 1

Hash (cost=3,636.71..3,636.71 rows=61,329 width=12) (actual time=43.994..43.994 rows=61,557 loops=1)

  • Buckets: 65536 Batches: 2 Memory Usage: 1843kB
33. 31.778 31.778 ↓ 1.0 61,557 1

Seq Scan on balance_entry be_1 (cost=0.00..3,636.71 rows=61,329 width=12) (actual time=0.014..31.778 rows=61,557 loops=1)

  • Filter: (register_type_id IS NULL)
  • Rows Removed by Filter: 123114
34. 7.573 37.865 ↑ 1.0 1 7,573

Limit (cost=1.01..1.02 rows=1 width=4) (actual time=0.005..0.005 rows=1 loops=7,573)

35. 15.146 30.292 ↑ 1.0 1 7,573

Aggregate (cost=1.01..1.02 rows=1 width=4) (actual time=0.004..0.004 rows=1 loops=7,573)

36. 15.146 15.146 ↓ 0.0 0 7,573

Seq Scan on revise r_1 (cost=0.00..1.01 rows=1 width=4) (actual time=0.002..0.002 rows=0 loops=7,573)

  • Filter: (supplier_contract_id = mm_1.supplier_contract_id)
  • Rows Removed by Filter: 1
37.          

CTE documents_temp

38. 13.552 22,832.639 ↓ 263.2 10,000 1

Nested Loop Left Join (cost=3.09..1,191.26 rows=38 width=237) (actual time=75.776..22,832.639 rows=10,000 loops=1)

39. 22,536.085 22,669.087 ↓ 263.2 10,000 1

Nested Loop Left Join (cost=0.43..1,088.56 rows=38 width=217) (actual time=75.759..22,669.087 rows=10,000 loops=1)

  • Filter: (((cde_1.doc_edit_id IS NULL) AND (NOT l_1.is_deleted)) OR (SubPlan 3))
40. 0.000 83.002 ↓ 133.3 10,000 1

CTE Scan on history l_1 (cost=0.00..509.00 rows=75 width=209) (actual time=75.735..83.002 rows=10,000 loops=1)

  • Filter: ((NOT is_deleted) OR (SubPlan 3))
41.          

SubPlan (forCTE Scan)

42. 15,089.752 21,764.802 ↑ 1.0 1 7,573

Hash Full Join (cost=1.91..5.07 rows=1 width=0) (actual time=2.874..2.874 rows=1 loops=7,573)

  • Hash Cond: ((lc.doc_id = cde1.doc_id) AND (lc.doc_type_id = cde1.doc_type_id) AND (lc.supplier_contract_id = cde1.supplier_contract_id))
  • Filter: ((((l_1.doc_id = lc.doc_id) AND (l_1.doc_type_id = lc.doc_type_id)) OR ((l_1.doc_id = cde1.doc_id) AND (l_1.doc_type_id = cde1.doc_type_id))) AND COALESCE(((lc.amount <> cde1.amount) OR (lc.rate <> cde1.rate) OR (lc.currency_id <> cde1.currency_id) OR (lc.is_deleted <> cde1.is_deleted) OR (lc.doc_dt <> cde1.doc_dt)), true))
  • Rows Removed by Filter: 4032
43. 3,067.065 3,067.065 ↓ 40.3 4,033 7,573

CTE Scan on history lc (cost=0.00..2.00 rows=100 width=67) (actual time=0.000..0.405 rows=4,033 loops=7,573)

44. 7.926 3,607.985 ↓ 148.5 7,573 1

Hash (cost=1.02..1.02 rows=51 width=67) (actual time=3,607.985..3,607.985 rows=7,573 loops=1)

  • Buckets: 8192 (originally 1024) Batches: 1 (originally 1) Memory Usage: 656kB
45. 3,600.059 3,600.059 ↓ 148.5 7,573 1

CTE Scan on curr_docs cde1 (cost=0.00..1.02 rows=51 width=67) (actual time=386.273..3,600.059 rows=7,573 loops=1)

46. 50.000 50.000 ↑ 1.0 1 10,000

Index Scan using uq_current_document_edition_doc_id_doc_type_id_supplier_contrac on current_document_edition cde_1 (cost=0.43..2.65 rows=1 width=20) (actual time=0.005..0.005 rows=1 loops=10,000)

  • Index Cond: ((l_1.doc_id = doc_id) AND (l_1.doc_type_id = doc_type_id))
  • Filter: (NOT is_deleted)
47. 20.000 150.000 ↑ 1.0 1 10,000

Limit (cost=2.66..2.67 rows=1 width=12) (actual time=0.014..0.015 rows=1 loops=10,000)

48. 40.000 130.000 ↑ 1.0 1 10,000

Sort (cost=2.66..2.67 rows=1 width=12) (actual time=0.013..0.013 rows=1 loops=10,000)

  • Sort Key: mm1.mm_id DESC
  • Sort Method: quicksort Memory: 25kB
49. 90.000 90.000 ↑ 1.0 1 10,000

Index Scan using idx_money_movement_doc_edit_id on money_movement mm1 (cost=0.43..2.65 rows=1 width=12) (actual time=0.009..0.009 rows=1 loops=10,000)

  • Index Cond: (doc_edit_id = cde_1.doc_edit_id)
  • Filter: (NOT is_storno)
50.          

CTE doc_edit

51. 130.643 23,030.948 ↓ 263.2 10,000 1

Insert on document_edition (cost=1.61..2.66 rows=38 width=135) (actual time=22,872.919..23,030.948 rows=10,000 loops=1)

52. 18.839 22,900.305 ↓ 263.2 10,000 1

Subquery Scan on *SELECT*_1 (cost=1.61..2.66 rows=38 width=135) (actual time=22,872.869..22,900.305 rows=10,000 loops=1)

53. 35.859 22,881.466 ↓ 263.2 10,000 1

HashAggregate (cost=1.61..1.99 rows=38 width=81) (actual time=22,872.849..22,881.466 rows=10,000 loops=1)

  • Group Key: documents_temp.doc_id, documents_temp.doc_type_id, documents_temp.doc_dt, documents_temp.edit_dt, documents_temp.old_doc_edit_id, documents_temp.is_deleted
54. 22,845.607 22,845.607 ↓ 263.2 10,000 1

CTE Scan on documents_temp (cost=0.00..0.76 rows=38 width=127) (actual time=75.786..22,845.607 rows=10,000 loops=1)

55.          

CTE curr_ups

56. 175.650 182.474 ↓ 0.0 0 1

Insert on current_document_edition (cost=0.00..0.85 rows=38 width=21) (actual time=182.474..182.474 rows=0 loops=1)

  • Conflict Resolution: UPDATE
  • Conflict Arbiter Indexes: uq_current_document_edition_doc_id_doc_type_id_supplier_contrac
  • Tuples Inserted: 2427
  • Conflicting Tuples: 7573
57. 6.824 6.824 ↓ 263.2 10,000 1

CTE Scan on doc_edit de_1 (cost=0.00..0.85 rows=38 width=21) (actual time=0.003..6.824 rows=10,000 loops=1)

58.          

CTE oper_with_doc_num

59. 2.479 23,098.494 ↓ 5,857.7 17,573 1

Append (cost=2.48..10.79 rows=3 width=97) (actual time=23,057.853..23,098.494 rows=17,573 loops=1)

60. 5.061 23,062.869 ↓ 7,573.0 7,573 1

Hash Join (cost=2.48..3.82 rows=1 width=97) (actual time=23,057.851..23,062.869 rows=7,573 loops=1)

  • Hash Cond: (dr.doc_type_id = od.doc_type_id)
61. 0.015 0.015 ↑ 1.0 24 1

Seq Scan on document_rule dr (cost=0.00..1.24 rows=24 width=9) (actual time=0.011..0.015 rows=24 loops=1)

62. 5.132 23,057.793 ↓ 7,573.0 7,573 1

Hash (cost=2.46..2.46 rows=1 width=102) (actual time=23,057.792..23,057.793 rows=7,573 loops=1)

  • Buckets: 8192 (originally 1024) Batches: 1 (originally 1) Memory Usage: 952kB
63. 9.179 23,052.661 ↓ 7,573.0 7,573 1

Hash Join (cost=1.41..2.46 rows=1 width=102) (actual time=22,877.360..23,052.661 rows=7,573 loops=1)

  • Hash Cond: ((de_2.doc_id = od.doc_id) AND (de_2.doc_type_id = od.doc_type_id))
64. 23,039.059 23,039.059 ↓ 263.2 10,000 1

CTE Scan on doc_edit de_2 (cost=0.00..0.76 rows=38 width=16) (actual time=22,872.920..23,039.059 rows=10,000 loops=1)

65. 2.197 4.423 ↓ 291.3 7,573 1

Hash (cost=1.02..1.02 rows=26 width=94) (actual time=4.422..4.423 rows=7,573 loops=1)

  • Buckets: 8192 (originally 1024) Batches: 1 (originally 1) Memory Usage: 893kB
66. 2.226 2.226 ↓ 291.3 7,573 1

CTE Scan on curr_docs od (cost=0.00..1.02 rows=26 width=94) (actual time=0.003..2.226 rows=7,573 loops=1)

  • Filter: (NOT is_deleted)
67. 8.611 30.682 ↓ 10,000.0 10,000 1

Hash Join (cost=2.11..3.46 rows=1 width=97) (actual time=22.110..30.682 rows=10,000 loops=1)

  • Hash Cond: (dr_1.doc_type_id = de_3.doc_type_id)
68. 0.012 0.012 ↑ 1.0 24 1

Seq Scan on document_rule dr_1 (cost=0.00..1.24 rows=24 width=9) (actual time=0.008..0.012 rows=24 loops=1)

69. 5.433 22.059 ↓ 10,000.0 10,000 1

Hash (cost=2.10..2.10 rows=1 width=94) (actual time=22.059..22.059 rows=10,000 loops=1)

  • Buckets: 16384 (originally 1024) Batches: 1 (originally 1) Memory Usage: 1222kB
70. 5.244 16.626 ↓ 10,000.0 10,000 1

Hash Join (cost=1.04..2.10 rows=1 width=94) (actual time=9.133..16.626 rows=10,000 loops=1)

  • Hash Cond: ((de_3.doc_id = d.doc_id) AND (de_3.doc_type_id = d.doc_type_id))
71. 2.268 2.268 ↓ 263.2 10,000 1

CTE Scan on doc_edit de_3 (cost=0.00..0.76 rows=38 width=16) (actual time=0.002..2.268 rows=10,000 loops=1)

72. 5.183 9.114 ↓ 526.3 10,000 1

Hash (cost=0.76..0.76 rows=19 width=86) (actual time=9.114..9.114 rows=10,000 loops=1)

  • Buckets: 16384 (originally 1024) Batches: 1 (originally 1) Memory Usage: 1144kB
73. 3.931 3.931 ↓ 526.3 10,000 1

CTE Scan on documents_temp d (cost=0.00..0.76 rows=19 width=86) (actual time=0.020..3.931 rows=10,000 loops=1)

  • Filter: (NOT is_deleted)
74. 0.035 2.464 ↓ 0.0 0 1

Hash Join (cost=2.11..3.46 rows=1 width=97) (actual time=2.464..2.464 rows=0 loops=1)

  • Hash Cond: (dr_2.doc_type_id = de_4.doc_type_id)
75. 0.008 0.008 ↑ 24.0 1 1

Seq Scan on document_rule dr_2 (cost=0.00..1.24 rows=24 width=9) (actual time=0.008..0.008 rows=1 loops=1)

76. 0.000 2.421 ↓ 0.0 0 1

Hash (cost=2.10..2.10 rows=1 width=94) (actual time=2.421..2.421 rows=0 loops=1)

  • Buckets: 1024 Batches: 1 Memory Usage: 8kB
77. 0.009 2.421 ↓ 0.0 0 1

Hash Join (cost=1.04..2.10 rows=1 width=94) (actual time=2.421..2.421 rows=0 loops=1)

  • Hash Cond: ((de_4.doc_id = d_1.doc_id) AND (de_4.doc_type_id = d_1.doc_type_id))
78. 0.002 0.002 ↑ 38.0 1 1

CTE Scan on doc_edit de_4 (cost=0.00..0.76 rows=38 width=16) (actual time=0.002..0.002 rows=1 loops=1)

79. 0.000 2.410 ↓ 0.0 0 1

Hash (cost=0.76..0.76 rows=19 width=86) (actual time=2.410..2.410 rows=0 loops=1)

  • Buckets: 1024 Batches: 1 Memory Usage: 8kB
80. 2.410 2.410 ↓ 0.0 0 1

CTE Scan on documents_temp d_1 (cost=0.00..0.76 rows=19 width=86) (actual time=2.410..2.410 rows=0 loops=1)

  • Filter: ((NOT is_deleted) AND (supplier_contract_currency_id IS NOT NULL))
  • Rows Removed by Filter: 10000
81.          

CTE mm_ins

82. 388.824 23,545.882 ↓ 8,786.5 17,573 1

Insert on money_movement (cost=0.00..0.08 rows=2 width=73) (actual time=23,057.912..23,545.882 rows=17,573 loops=1)

83. 23,157.058 23,157.058 ↓ 8,786.5 17,573 1

CTE Scan on oper_with_doc_num owdn (cost=0.00..0.08 rows=2 width=73) (actual time=23,057.880..23,157.058 rows=17,573 loops=1)

  • Filter: ((supplier_contract_id IS NOT NULL) AND (COALESCE(amount, '0'::numeric) <> '0'::numeric))
84. 0.044 23,596.662 ↓ 3.5 7 1

Subquery Scan on *SELECT* (cost=0.08..0.15 rows=2 width=38) (actual time=23,596.631..23,596.662 rows=7 loops=1)

85. 20.670 23,596.618 ↓ 3.5 7 1

HashAggregate (cost=0.08..0.11 rows=2 width=52) (actual time=23,596.606..23,596.618 rows=7 loops=1)

  • Group Key: date_trunc('month'::text, mm.dt), mm.supplier_contract_id, mm.currency_id, mm.register_type_id
86. 23,575.948 23,575.948 ↓ 8,786.5 17,573 1

CTE Scan on mm_ins mm (cost=0.00..0.04 rows=2 width=40) (actual time=23,057.926..23,575.948 rows=17,573 loops=1)

  • Filter: (supplier_contract_id IS NOT NULL)