explain.depesz.com

PostgreSQL's explain analyze made readable

Result: yyQ

Settings
# exclusive inclusive rows x rows loops node
1. 7.204 129,329.049 ↑ 46.3 2,649 1

Aggregate (cost=8,020,158.17..8,040,698.69 rows=122,630 width=493) (actual time=129,321.604..129,329.049 rows=2,649 loops=1)

  • Buffers: shared hit=13820 read=897886 written=24
2. 16.270 129,321.845 ↑ 46.3 2,649 1

Sort (cost=8,020,158.17..8,020,464.74 rows=122,630 width=465) (actual time=129,321.576..129,321.845 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=13820 read=897886 written=24
3. 0.888 129,305.575 ↑ 46.3 2,649 1

Result (cost=3,983,326.15..8,009,793.51 rows=122,630 width=465) (actual time=115,938.652..129,305.575 rows=2,649 loops=1)

  • Buffers: shared hit=13803 read=897886 written=24
4. 0.329 129,304.687 ↑ 46.3 2,649 1

Append (cost=3,983,326.15..8,008,567.21 rows=122,630 width=465) (actual time=115,938.65..129,304.687 rows=2,649 loops=1)

  • Buffers: shared hit=13803 read=897886 written=24
5. 0.162 115,940.309 ↑ 170.9 351 1

Subquery Scan on *SELECT* 1 (cost=3,983,326.15..3,994,125.25 rows=59,995 width=465) (actual time=115,938.649..115,940.309 rows=351 loops=1)

  • Buffers: shared hit=1586 read=448831
6. 1.466 115,940.147 ↑ 170.9 351 1

Aggregate (cost=3,983,326.15..3,993,525.3 rows=59,995 width=469) (actual time=115,938.647..115,940.147 rows=351 loops=1)

  • Buffers: shared hit=1586 read=448831
7. 2.167 115,938.681 ↑ 131.0 458 1

Sort (cost=3,983,326.15..3,983,476.13 rows=59,995 width=174) (actual time=115,938.599..115,938.681 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=1586 read=448831
8. 2.316 115,936.514 ↑ 131.0 458 1

Nested Loop (cost=3,213,163.38..3,978,564.78 rows=59,995 width=174) (actual time=112,523.578..115,936.514 rows=458 loops=1)

  • Buffers: shared hit=1586 read=448831
9. 1,906.148 114,322.954 ↑ 131.0 458 1

Hash Join (cost=3,213,162.81..3,536,290.49 rows=59,995 width=148) (actual time=112,507.006..114,322.954 rows=458 loops=1)

  • Buffers: shared hit=49 read=448531
10. 1,533.800 1,533.800 ↑ 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.019..1,533.8 rows=15,886,343 loops=1)

  • Buffers: shared hit=35 read=104157
11. 0.220 110,883.006 ↑ 131.0 458 1

Hash (cost=3,212,412.87..3,212,412.87 rows=59,995 width=144) (actual time=110,883.006..110,883.006 rows=458 loops=1)

  • Buffers: shared hit=14 read=344374
12. 1,392.681 110,882.786 ↑ 131.0 458 1

Hash Join (cost=2,889,388.7..3,212,412.87 rows=59,995 width=144) (actual time=110,882.481..110,882.786 rows=458 loops=1)

  • Buffers: shared hit=14 read=344374
13. 8,113.743 8,113.743 ↑ 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=0.389..8,113.743 rows=15,886,343 loops=1)

  • Buffers: shared hit=2 read=104190
14. 1.496 101,376.362 ↑ 131.0 458 1

Hash (cost=2,888,638.76..2,888,638.76 rows=59,995 width=140) (actual time=101,376.362..101,376.362 rows=458 loops=1)

  • Buffers: shared hit=12 read=240184
15. 1.547 101,374.866 ↑ 131.0 458 1

Hash Join (cost=95,711.43..2,888,638.76 rows=59,995 width=140) (actual time=2,429.683..101,374.866 rows=458 loops=1)

  • Buffers: shared hit=12 read=240184
16. 726.279 101,373.308 ↑ 176.8 458 1

Hash Join (cost=95,708.67..2,887,755.95 rows=80,993 width=140) (actual time=2,429.661..101,373.308 rows=458 loops=1)

  • Buffers: shared hit=12 read=240184
17. 98,529.149 100,647.026 ↓ 1.1 4,335,077 1

Bitmap Heap Scan on account_entry ae (cost=95,705.91..2,876,373.63 rows=3,991,816 width=140) (actual time=2,236.91..100,647.026 rows=4,335,077 loops=1)

  • Filter: (ae.state = 2)
  • Heap Blocks: exact=227219
  • Buffers: shared hit=12 read=240184
18. 2,117.877 2,117.877 ↓ 1.1 4,720,782 1

Bitmap Index Scan on account_entry_operation_date (cost=0..94,707.96 rows=4,493,939 width=0) (actual time=2,117.877..2,117.877 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 read=12977
19. 0.001 0.003 ↑ 100.0 1 1

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

20. 0.002 0.002 ↑ 100.0 1 1

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

21. 0.002 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)

22. 0.009 0.009 ↑ 100.0 1 1

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

23. 1,611.244 1,611.244 ↓ 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=3.518..3.518 rows=0 loops=458)

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

Subquery Scan on *SELECT* 2 (cost=4,003,167.66..4,014,441.96 rows=62,635 width=465) (actual time=13,352.693..13,364.049 rows=2,298 loops=1)

  • Buffers: shared hit=12217 read=449055 written=24
25. 9.994 13,363.323 ↑ 27.3 2,298 1

Aggregate (cost=4,003,167.66..4,013,815.61 rows=62,635 width=469) (actual time=13,352.69..13,363.323 rows=2,298 loops=1)

  • Buffers: shared hit=12217 read=449055 written=24
26. 18.706 13,353.329 ↑ 19.7 3,173 1

Sort (cost=4,003,167.66..4,003,324.25 rows=62,635 width=174) (actual time=13,352.643..13,353.329 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=12217 read=449055 written=24
27. 5.771 13,334.623 ↑ 19.7 3,173 1

Nested Loop (cost=3,213,308.73..3,998,177.32 rows=62,635 width=174) (actual time=8,993.447..13,334.623 rows=3,173 loops=1)

  • Buffers: shared hit=12217 read=449055 written=24
28. 1,998.082 11,336.208 ↑ 19.7 3,173 1

Hash Join (cost=3,213,308.16..3,536,441.34 rows=62,635 width=148) (actual time=8,970.103..11,336.208 rows=3,173 loops=1)

  • Buffers: shared hit=35 read=448545 written=24
29. 1,559.064 1,559.064 ↑ 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.027..1,559.064 rows=15,886,343 loops=1)

  • Buffers: shared hit=33 read=104159
30. 1.259 7,779.062 ↑ 19.7 3,173 1

Hash (cost=3,212,525.22..3,212,525.22 rows=62,635 width=144) (actual time=7,779.062..7,779.062 rows=3,173 loops=1)

  • Buffers: shared hit=2 read=344386 written=24
31. 1,294.655 7,777.803 ↑ 19.7 3,173 1

Hash Join (cost=2,889,500.1..3,212,525.22 rows=62,635 width=144) (actual time=7,776.455..7,777.803 rows=3,173 loops=1)

  • Buffers: shared hit=2 read=344386 written=24
32. 1,590.460 1,590.460 ↑ 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.015..1,590.46 rows=15,886,343 loops=1)

  • Buffers: shared read=104192
33. 4.330 4,892.688 ↑ 19.7 3,173 1

Hash (cost=2,888,717.16..2,888,717.16 rows=62,635 width=140) (actual time=4,892.688..4,892.688 rows=3,173 loops=1)

  • Buffers: shared hit=2 read=240194 written=24
34. 2.880 4,888.358 ↑ 19.7 3,173 1

Hash Join (cost=95,711.43..2,888,717.16 rows=62,635 width=140) (actual time=901.349..4,888.358 rows=3,173 loops=1)

  • Buffers: shared hit=2 read=240194 written=24
35. 735.166 4,885.453 ↑ 26.6 3,173 1

Hash Join (cost=95,708.67..2,887,795.61 rows=84,558 width=140) (actual time=901.297..4,885.453 rows=3,173 loops=1)

  • Buffers: shared hit=2 read=240194 written=24
36. 3,347.943 4,150.277 ↓ 1.1 4,335,077 1

Bitmap Heap Scan on account_entry ae_1 (cost=95,705.91..2,876,373.63 rows=3,991,816 width=140) (actual time=899.575..4,150.277 rows=4,335,077 loops=1)

  • Filter: (ae_1.state = 2)
  • Heap Blocks: exact=227219
  • Buffers: shared hit=2 read=240194 written=24
37. 802.334 802.334 ↓ 1.1 4,720,782 1

Bitmap Index Scan on account_entry_operation_date (cost=0..94,707.96 rows=4,493,939 width=0) (actual time=802.334..802.334 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 read=12977
38. 0.003 0.010 ↑ 100.0 1 1

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

39. 0.007 0.007 ↑ 100.0 1 1

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

40. 0.006 0.025 ↑ 100.0 1 1

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

41. 0.019 0.019 ↑ 100.0 1 1

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

42. 1,992.644 1,992.644 ↓ 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.628..0.628 rows=0 loops=3,173)

  • Index Cond: (ae_1.id = spec_1.entry_id)
  • Buffers: shared hit=12182 read=510
Planning time : 1,595.136 ms
Execution time : 129,329.786 ms