explain.depesz.com

PostgreSQL's explain analyze made readable

Result: DVHE

Settings
# exclusive inclusive rows x rows loops node
1. 6.974 4,738.267 ↓ 147.2 2,649 1

Aggregate (cost=553.11..556.12 rows=18 width=493) (actual time=4,731.038..4,738.267 rows=2,649 loops=1)

  • Buffers: shared hit=38190 read=2896
2. 8.791 4,731.293 ↓ 147.2 2,649 1

Sort (cost=553.11..553.15 rows=18 width=465) (actual time=4,731.023..4,731.293 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=38190 read=2896
3. 0.458 4,722.502 ↓ 147.2 2,649 1

Result (cost=274.66..552.73 rows=18 width=465) (actual time=1,384.978..4,722.502 rows=2,649 loops=1)

  • Buffers: shared hit=38190 read=2896
4. 0.189 4,722.044 ↓ 147.2 2,649 1

Append (cost=274.66..552.55 rows=18 width=465) (actual time=1,384.975..4,722.044 rows=2,649 loops=1)

  • Buffers: shared hit=38190 read=2896
5. 0.079 1,385.962 ↓ 39.0 351 1

Subquery Scan on *SELECT* 1 (cost=274.66..276.28 rows=9 width=465) (actual time=1,384.975..1,385.962 rows=351 loops=1)

  • Buffers: shared hit=4407 read=689
6. 0.899 1,385.883 ↓ 39.0 351 1

Aggregate (cost=274.66..276.19 rows=9 width=469) (actual time=1,384.974..1,385.883 rows=351 loops=1)

  • Buffers: shared hit=4407 read=689
7. 1.612 1,384.984 ↓ 50.9 458 1

Sort (cost=274.66..274.69 rows=9 width=174) (actual time=1,384.94..1,384.984 rows=458 loops=1)

  • Sort Key: ae.operation_date, 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=4407 read=689
8. 0.896 1,383.372 ↓ 50.9 458 1

Nested Loop (cost=2..274.52 rows=9 width=174) (actual time=51.416..1,383.372 rows=458 loops=1)

  • Buffers: shared hit=4407 read=689
9. 0.568 1,158.514 ↓ 50.9 458 1

Nested Loop (cost=1.44..197.17 rows=9 width=148) (actual time=51.384..1,158.514 rows=458 loops=1)

  • Buffers: shared hit=2773 read=486
10. 1.429 1,010.470 ↓ 50.9 458 1

Nested Loop (cost=1..121 rows=9 width=144) (actual time=51.37..1,010.47 rows=458 loops=1)

  • Buffers: shared hit=1437 read=445
11. 659.587 659.587 ↓ 50.9 458 1

Index Scan using account_entry_operation_date_state1 on account_entry ae (cost=0.57..44.84 rows=9 width=140) (actual time=51.342..659.587 rows=458 loops=1)

  • Index Cond: ((ae.debet_account_id = 197977) AND (ae.ent_id = 1500) AND (ae.operation_date >= '2019-01-01'::date) AND (ae.operation_date <= '2019-08-31'::date))
  • Filter: (ae.state = 2)
  • Buffers: shared hit=153 read=264
12. 349.454 349.454 ↓ 0.0 0 458

Index Scan using account_entry_contract_entry_id_key on account_entry_contract aec1 (cost=0.43..8.45 rows=1 width=8) (actual time=0.763..0.763 rows=0 loops=458)

  • Index Cond: (aec1.entry_id = ae.id)
  • Buffers: shared hit=1284 read=181
13. 147.476 147.476 ↓ 0.0 0 458

Index Scan using account_entry_contract_second_entry_id_key on account_entry_contract aec2 (cost=0.43..8.45 rows=1 width=8) (actual time=0.322..0.322 rows=0 loops=458)

  • Index Cond: (aec2.second_entry_id = ae.id)
  • Buffers: shared hit=1336 read=41
14. 223.962 223.962 ↓ 0.0 0 458

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

  • Index Cond: (ae.id = spec.entry_id)
  • Buffers: shared hit=1634 read=203
15. 0.430 3,335.893 ↓ 255.3 2,298 1

Subquery Scan on *SELECT* 2 (cost=274.65..276.27 rows=9 width=465) (actual time=3,329.274..3,335.893 rows=2,298 loops=1)

  • Buffers: shared hit=33783 read=2207
16. 5.939 3,335.463 ↓ 255.3 2,298 1

Aggregate (cost=274.65..276.18 rows=9 width=469) (actual time=3,329.273..3,335.463 rows=2,298 loops=1)

  • Buffers: shared hit=33783 read=2207
17. 11.430 3,329.524 ↓ 352.6 3,173 1

Sort (cost=274.65..274.67 rows=9 width=174) (actual time=3,329.231..3,329.524 rows=3,173 loops=1)

  • Sort Key: ae_1.operation_date, 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=33783 read=2207
18. 2.852 3,318.094 ↓ 352.6 3,173 1

Nested Loop (cost=2..274.5 rows=9 width=174) (actual time=11.127..3,318.094 rows=3,173 loops=1)

  • Buffers: shared hit=33783 read=2207
19. 3.889 2,379.207 ↓ 352.6 3,173 1

Nested Loop (cost=1.44..197.15 rows=9 width=148) (actual time=0.688..2,379.207 rows=3,173 loops=1)

  • Buffers: shared hit=21438 read=1860
20. 4.002 2,121.478 ↓ 352.6 3,173 1

Nested Loop (cost=1..120.99 rows=9 width=144) (actual time=0.677..2,121.478 rows=3,173 loops=1)

  • Buffers: shared hit=11992 read=1779
21. 1,425.762 1,425.762 ↓ 352.6 3,173 1

Index Scan using account_entry_operation_date_state2 on account_entry ae_1 (cost=0.57..44.82 rows=9 width=140) (actual time=0.246..1,425.762 rows=3,173 loops=1)

  • Index Cond: ((ae_1.credit_account_id = 197977) AND (ae_1.ent_id = 1500) AND (ae_1.operation_date >= '2019-01-01'::date) AND (ae_1.operation_date <= '2019-08-31'::date))
  • Filter: (ae_1.state = 2)
  • Buffers: shared hit=984 read=848
22. 691.714 691.714 ↑ 1.0 1 3,173

Index Scan using account_entry_contract_entry_id_key on account_entry_contract aec1_1 (cost=0.43..8.45 rows=1 width=8) (actual time=0.217..0.218 rows=1 loops=3,173)

  • Index Cond: (aec1_1.entry_id = ae_1.id)
  • Buffers: shared hit=11008 read=931
23. 253.840 253.840 ↓ 0.0 0 3,173

Index Scan using account_entry_contract_second_entry_id_key on account_entry_contract aec2_1 (cost=0.43..8.45 rows=1 width=8) (actual time=0.08..0.08 rows=0 loops=3,173)

  • Index Cond: (aec2_1.second_entry_id = ae_1.id)
  • Buffers: shared hit=9446 read=81
24. 936.035 936.035 ↓ 0.0 0 3,173

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

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