explain.depesz.com

PostgreSQL's explain analyze made readable

Result: iRoV

Settings
# exclusive inclusive rows x rows loops node
1. 9.083 163,389.496 ↑ 46.3 2,649 1

Aggregate (cost=8,020,366.81..8,040,918.72 rows=122,698 width=493) (actual time=163,379.967..163,389.496 rows=2,649 loops=1)

  • Buffers: shared hit=29662 read=882047 dirtied=7
2. 11.789 163,380.413 ↑ 46.3 2,649 1

Sort (cost=8,020,366.81..8,020,673.55 rows=122,698 width=465) (actual time=163,379.932..163,380.413 rows=2,649 loops=1)

  • Sort Key: "*SELECT* 1".date, "*SELECT* 1".date_next, "*SELECT* 1".date_prev, "*SELECT* 1".ent_id, "*SELECT* 1".account_id, "*SELECT* 1".kbk_id, "*SELECT* 1".analytic1, "*SELECT* 1".analytic2, "*SELECT* 1".analytic3, "*SELECT* 1".analytic4, "*SELECT* 1".analytic5, "*SELECT* 1".analytic6, "*SELECT* 1".analytic7, "*SELECT* 1".analytic8, "*SELECT* 1".analytic9, "*SELECT* 1".analytic10, "*SELECT* 1".analytic11, "*SELECT* 1".analytic12, "*SELECT* 1".analytic13, "*SELECT* 1".analytic14, "*SELECT* 1".analytic15, "*SELECT* 1".analytic16, "*SELECT* 1".analytic17, "*SELECT* 1".analytic18, "*SELECT* 1".analytic19, "*SELECT* 1".analytic20, "*SELECT* 1".kvd_id, "*SELECT* 1".kosgu_id, "*SELECT* 1".kvd_analytic_id, "*SELECT* 1".closing, "*SELECT* 1".contract_id, "*SELECT* 1".document_type_id, "*SELECT* 1".past_years_return, "*SELECT* 1".currency_id, "*SELECT* 1".product_id, "*SELECT* 1".unit_id, "*SELECT* 1".fact_accounting, "*SELECT* 1".average_accounting, "*SELECT* 1".cost, "*SELECT* 1".internal, "*SELECT* 1".journal, "*SELECT* 1".transition
  • Sort Method: quicksort Memory: 800kB
  • Buffers: shared hit=29662 read=882047 dirtied=7
3. 0.640 163,368.624 ↑ 46.3 2,649 1

Result (cost=3,983,413.37..8,009,995.91 rows=122,698 width=465) (actual time=151,236.678..163,368.624 rows=2,649 loops=1)

  • Buffers: shared hit=29642 read=882047 dirtied=7
4. 0.250 163,367.984 ↑ 46.3 2,649 1

Append (cost=3,983,413.37..8,008,768.93 rows=122,698 width=465) (actual time=151,236.676..163,367.984 rows=2,649 loops=1)

  • Buffers: shared hit=29642 read=882047 dirtied=7
5. 0.064 151,237.834 ↑ 171.0 351 1

Subquery Scan on *SELECT* 1 (cost=3,983,413.37..3,994,218.41 rows=60,028 width=465) (actual time=151,236.675..151,237.834 rows=351 loops=1)

  • Buffers: shared hit=2560 read=447857 dirtied=7
6. 1.110 151,237.770 ↑ 171.0 351 1

Aggregate (cost=3,983,413.37..3,993,618.13 rows=60,028 width=469) (actual time=151,236.671..151,237.77 rows=351 loops=1)

  • Buffers: shared hit=2560 read=447857 dirtied=7
7. 2.152 151,236.660 ↑ 131.1 458 1

Sort (cost=3,983,413.37..3,983,563.44 rows=60,028 width=174) (actual time=151,236.615..151,236.66 rows=458 loops=1)

  • Sort Key: ae.operation_date, ae.ent_id, ae.debet_account_id, ae.debet_kbk_id, ae.debet_analytic1_id, ae.debet_analytic2_id, ae.debet_analytic3_id, ae.debet_analytic4_id, ae.debet_analytic5_id, ae.debet_analytic6_id, ae.debet_analytic7_id, ae.debet_analytic8_id, ae.debet_analytic9_id, ae.debet_analytic10_id, ae.debet_analytic11_id, ae.debet_analytic12_id, ae.debet_analytic13_id, ae.debet_analytic14_id, ae.debet_analytic15_id, ae.debet_analytic16_id, ae.debet_analytic17_id, ae.debet_analytic18_id, ae.debet_analytic19_id, ae.debet_analytic20_id, ae.kvd_id, ae.debet_kosgu_id, ae.debet_kvd_analytic_id, ae.closing, (COALESCE(aec1.contract_id, aec2.contract_id)), ae.document_type_id, ae.past_years_return, ae.currency_id, spec.product_id, spec.unit_id, spec.fact_accounting, spec.average_accounting, (COALESCE(spec.cost, 0.00::numeric(16,2))), (CASE WHEN (ae.debet_account_id = ae.credit_account_id) THEN true ELSE false END), ae.debet_journal, ae.transition
  • Sort Method: quicksort Memory: 89kB
  • Buffers: shared hit=2560 read=447857 dirtied=7
8. 2.214 151,234.508 ↑ 131.1 458 1

Nested Loop (cost=3,213,004.71..3,978,649.15 rows=60,028 width=174) (actual time=147,813.432..151,234.508 rows=458 loops=1)

  • Buffers: shared hit=2560 read=447857 dirtied=7
9. 2,129.181 149,946.688 ↑ 131.1 458 1

Hash Join (cost=3,213,004.14..3,536,131.81 rows=60,028 width=148) (actual time=147,789.034..149,946.688 rows=458 loops=1)

  • Buffers: shared hit=1023 read=447557 dirtied=7
10. 1,749.957 1,749.957 ↑ 1.0 15,886,343 1

Seq Scan on account_entry_contract aec1 (cost=0..263,326.9 rows=15,913,490 width=8) (actual time=0.088..1,749.957 rows=15,886,343 loops=1)

  • Buffers: shared hit=310 read=103882
11. 4.036 146,067.550 ↑ 131.1 458 1

Hash (cost=3,212,253.79..3,212,253.79 rows=60,028 width=144) (actual time=146,067.55..146,067.55 rows=458 loops=1)

  • Buffers: shared hit=713 read=343675 dirtied=7
12. 1,530.183 146,063.514 ↑ 131.1 458 1

Hash Join (cost=2,889,229.61..3,212,253.79 rows=60,028 width=144) (actual time=146,062.845..146,063.514 rows=458 loops=1)

  • Buffers: shared hit=713 read=343675 dirtied=7
13. 6,421.369 6,421.369 ↑ 1.0 15,886,343 1

Seq Scan on account_entry_contract aec2 (cost=0..263,326.9 rows=15,913,490 width=8) (actual time=16.448..6,421.369 rows=15,886,343 loops=1)

  • Buffers: shared hit=277 read=103915
14. 2.031 138,111.962 ↑ 131.1 458 1

Hash (cost=2,888,479.26..2,888,479.26 rows=60,028 width=140) (actual time=138,111.962..138,111.962 rows=458 loops=1)

  • Buffers: shared hit=436 read=239760 dirtied=7
15. 2.140 138,109.931 ↑ 131.1 458 1

Hash Join (cost=95,737.03..2,888,479.26 rows=60,028 width=140) (actual time=2,155.967..138,109.931 rows=458 loops=1)

  • Buffers: shared hit=436 read=239760 dirtied=7
16. 928.281 138,107.765 ↑ 176.9 458 1

Hash Join (cost=95,734.28..2,887,595.97 rows=81,038 width=140) (actual time=2,155.92..138,107.765 rows=458 loops=1)

  • Buffers: shared hit=436 read=239760 dirtied=7
17. 135,331.231 137,179.473 ↓ 1.1 4,335,077 1

Bitmap Heap Scan on account_entry ae (cost=95,731.52..2,876,207.31 rows=3,994,041 width=140) (actual time=1,949.222..137,179.473 rows=4,335,077 loops=1)

  • Filter: (ae.state = 2)
  • Buffers: shared hit=436 read=239760 dirtied=7
18. 1,848.242 1,848.242 ↓ 1.0 4,720,782 1

Bitmap Index Scan on account_entry_operation_date (cost=0..94,733.01 rows=4,496,444 width=0) (actual time=1,848.242..1,848.242 rows=4,720,782 loops=1)

  • Index Cond: ((ae.operation_date >= '2019-01-01'::date) AND (ae.operation_date <= '2019-08-31'::date))
  • Buffers: shared hit=4 read=12973
19. 0.004 0.011 ↑ 100.0 1 1

Hash (cost=1.51..1.51 rows=100 width=4) (actual time=0.011..0.011 rows=1 loops=1)

20. 0.007 0.007 ↑ 100.0 1 1

Result (cost=0..0.51 rows=100 width=4) (actual time=0.007..0.007 rows=1 loops=1)

21. 0.010 0.026 ↑ 100.0 1 1

Hash (cost=1.51..1.51 rows=100 width=4) (actual time=0.026..0.026 rows=1 loops=1)

22. 0.016 0.016 ↑ 100.0 1 1

Result (cost=0..0.51 rows=100 width=4) (actual time=0.015..0.016 rows=1 loops=1)

23. 1,285.606 1,285.606 ↓ 0.0 0 458

Index Scan using account_entry_spec_entry_id on account_entry_spec spec (cost=0.56..7.36 rows=1 width=25) (actual time=2.807..2.807 rows=0 loops=458)

  • Index Cond: (ae.id = spec.entry_id)
  • Buffers: shared hit=1537 read=300
24. 0.570 12,129.900 ↑ 27.3 2,298 1

Subquery Scan on *SELECT* 2 (cost=4,003,269.92..4,014,550.52 rows=62,670 width=465) (actual time=12,119.369..12,129.9 rows=2,298 loops=1)

  • Buffers: shared hit=27082 read=434190
25. 9.515 12,129.330 ↑ 27.3 2,298 1

Aggregate (cost=4,003,269.92..4,013,923.82 rows=62,670 width=469) (actual time=12,119.367..12,129.33 rows=2,298 loops=1)

  • Buffers: shared hit=27082 read=434190
26. 14.202 12,119.815 ↑ 19.8 3,173 1

Sort (cost=4,003,269.92..4,003,426.59 rows=62,670 width=174) (actual time=12,119.294..12,119.815 rows=3,173 loops=1)

  • Sort Key: ae_1.operation_date, ae_1.ent_id, ae_1.credit_account_id, ae_1.credit_kbk_id, ae_1.credit_analytic1_id, ae_1.credit_analytic2_id, ae_1.credit_analytic3_id, ae_1.credit_analytic4_id, ae_1.credit_analytic5_id, ae_1.credit_analytic6_id, ae_1.credit_analytic7_id, ae_1.credit_analytic8_id, ae_1.credit_analytic9_id, ae_1.credit_analytic10_id, ae_1.credit_analytic11_id, ae_1.credit_analytic12_id, ae_1.credit_analytic13_id, ae_1.credit_analytic14_id, ae_1.credit_analytic15_id, ae_1.credit_analytic16_id, ae_1.credit_analytic17_id, ae_1.credit_analytic18_id, ae_1.credit_analytic19_id, ae_1.credit_analytic20_id, ae_1.kvd_id, ae_1.credit_kosgu_id, ae_1.credit_kvd_analytic_id, ae_1.closing, (COALESCE(aec1_1.contract_id, aec2_1.contract_id)), ae_1.document_type_id, ae_1.past_years_return, ae_1.currency_id, spec_1.product_id, spec_1.unit_id, spec_1.fact_accounting, spec_1.average_accounting, (COALESCE(spec_1.cost, 0.00::numeric(16,2))), (CASE WHEN (ae_1.debet_account_id = ae_1.credit_account_id) THEN true ELSE false END), ae_1.credit_journal, ae_1.transition
  • Sort Method: quicksort Memory: 543kB
  • Buffers: shared hit=27082 read=434190
27. 4.653 12,105.613 ↑ 19.8 3,173 1

Nested Loop (cost=3,213,150.15..3,998,276.53 rows=62,670 width=174) (actual time=8,292.874..12,105.613 rows=3,173 loops=1)

  • Buffers: shared hit=27082 read=434190
28. 2,251.514 10,949.161 ↑ 19.8 3,173 1

Hash Join (cost=3,213,149.59..3,536,282.77 rows=62,670 width=148) (actual time=8,273.334..10,949.161 rows=3,173 loops=1)

  • Buffers: shared hit=14889 read=433691
29. 1,715.762 1,715.762 ↑ 1.0 15,886,343 1

Seq Scan on account_entry_contract aec1_1 (cost=0..263,326.9 rows=15,913,490 width=8) (actual time=0.033..1,715.762 rows=15,886,343 loops=1)

  • Buffers: shared hit=97 read=104095
30. 1.325 6,981.885 ↑ 19.8 3,173 1

Hash (cost=3,212,366.21..3,212,366.21 rows=62,670 width=144) (actual time=6,981.885..6,981.885 rows=3,173 loops=1)

  • Buffers: shared hit=14792 read=329596
31. 1,240.999 6,980.560 ↑ 19.8 3,173 1

Hash Join (cost=2,889,341.08..3,212,366.21 rows=62,670 width=144) (actual time=6,979.248..6,980.56 rows=3,173 loops=1)

  • Buffers: shared hit=14792 read=329596
32. 1,615.930 1,615.930 ↑ 1.0 15,886,343 1

Seq Scan on account_entry_contract aec2_1 (cost=0..263,326.9 rows=15,913,490 width=8) (actual time=0.044..1,615.93 rows=15,886,343 loops=1)

  • Buffers: shared hit=65 read=104127
33. 4.923 4,123.631 ↑ 19.8 3,173 1

Hash (cost=2,888,557.7..2,888,557.7 rows=62,670 width=140) (actual time=4,123.631..4,123.631 rows=3,173 loops=1)

  • Buffers: shared hit=14727 read=225469
34. 3.036 4,118.708 ↑ 19.8 3,173 1

Hash Join (cost=95,737.03..2,888,557.7 rows=62,670 width=140) (actual time=821.092..4,118.708 rows=3,173 loops=1)

  • Buffers: shared hit=14727 read=225469
35. 763.005 4,115.649 ↑ 26.7 3,173 1

Hash Join (cost=95,734.28..2,887,635.65 rows=84,605 width=140) (actual time=821.048..4,115.649 rows=3,173 loops=1)

  • Buffers: shared hit=14727 read=225469
36. 2,612.578 3,352.636 ↓ 1.1 4,335,077 1

Bitmap Heap Scan on account_entry ae_1 (cost=95,731.52..2,876,207.31 rows=3,994,041 width=140) (actual time=819.92..3,352.636 rows=4,335,077 loops=1)

  • Filter: (ae_1.state = 2)
  • Buffers: shared hit=14727 read=225469
37. 740.058 740.058 ↓ 1.0 4,720,782 1

Bitmap Index Scan on account_entry_operation_date (cost=0..94,733.01 rows=4,496,444 width=0) (actual time=740.058..740.058 rows=4,720,782 loops=1)

  • Index Cond: ((ae_1.operation_date >= '2019-01-01'::date) AND (ae_1.operation_date <= '2019-08-31'::date))
  • Buffers: shared hit=4 read=12973
38. 0.002 0.008 ↑ 100.0 1 1

Hash (cost=1.51..1.51 rows=100 width=4) (actual time=0.008..0.008 rows=1 loops=1)

39. 0.006 0.006 ↑ 100.0 1 1

Result (cost=0..0.51 rows=100 width=4) (actual time=0.005..0.006 rows=1 loops=1)

40. 0.006 0.023 ↑ 100.0 1 1

Hash (cost=1.51..1.51 rows=100 width=4) (actual time=0.023..0.023 rows=1 loops=1)

41. 0.017 0.017 ↑ 100.0 1 1

Result (cost=0..0.51 rows=100 width=4) (actual time=0.015..0.017 rows=1 loops=1)

42. 1,151.799 1,151.799 ↓ 0.0 0 3,173

Index Scan using account_entry_spec_entry_id on account_entry_spec spec_1 (cost=0.56..7.36 rows=1 width=25) (actual time=0.363..0.363 rows=0 loops=3,173)

  • Index Cond: (ae_1.id = spec_1.entry_id)
  • Buffers: shared hit=12193 read=499