explain.depesz.com

PostgreSQL's explain analyze made readable

Result: j3oE

Settings
# exclusive inclusive rows x rows loops node
1. 6.963 50,324.891 ↓ 147.2 2,649 1

Aggregate (cost=47,168.53..47,171.55 rows=18 width=493) (actual time=50,317.624..50,324.891 rows=2,649 loops=1)

  • Buffers: shared hit=41684 read=17078
2. 9.800 50,317.928 ↓ 147.2 2,649 1

Sort (cost=47,168.53..47,168.58 rows=18 width=465) (actual time=50,317.6..50,317.928 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=41684 read=17078
3. 0.656 50,308.128 ↓ 147.2 2,649 1

Result (cost=23,573.23..47,168.16 rows=18 width=465) (actual time=10,231.661..50,308.128 rows=2,649 loops=1)

  • Buffers: shared hit=41684 read=17078
4. 0.226 50,307.472 ↓ 147.2 2,649 1

Append (cost=23,573.23..47,167.98 rows=18 width=465) (actual time=10,231.658..50,307.472 rows=2,649 loops=1)

  • Buffers: shared hit=41684 read=17078
5. 0.090 10,232.753 ↓ 39.0 351 1

Subquery Scan on *SELECT* 1 (cost=23,573.23..23,574.85 rows=9 width=465) (actual time=10,231.657..10,232.753 rows=351 loops=1)

  • Buffers: shared hit=4226 read=6397
6. 0.996 10,232.663 ↓ 39.0 351 1

Aggregate (cost=23,573.23..23,574.76 rows=9 width=469) (actual time=10,231.656..10,232.663 rows=351 loops=1)

  • Buffers: shared hit=4226 read=6397
7. 1.993 10,231.667 ↓ 50.9 458 1

Sort (cost=23,573.23..23,573.25 rows=9 width=174) (actual time=10,231.611..10,231.667 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=4226 read=6397
8. 1.814 10,229.674 ↓ 50.9 458 1

Nested Loop (cost=22,677.08..23,573.09 rows=9 width=174) (actual time=652.672..10,229.674 rows=458 loops=1)

  • Buffers: shared hit=4226 read=6397
9. 1.278 9,882.070 ↓ 50.9 458 1

Nested Loop (cost=22,676.52..23,495.73 rows=9 width=148) (actual time=652.209..9,882.07 rows=458 loops=1)

  • Buffers: shared hit=2586 read=6200
10. 1.749 9,204.326 ↓ 50.9 458 1

Nested Loop (cost=22,676.08..23,419.57 rows=9 width=144) (actual time=619.272..9,204.326 rows=458 loops=1)

  • Buffers: shared hit=1355 read=6054
11. 6,814.001 7,401.721 ↓ 50.9 458 1

Bitmap Heap Scan on account_entry ae (cost=22,675.65..23,343.41 rows=9 width=140) (actual time=588.614..7,401.721 rows=458 loops=1)

  • Filter: ((ae.operation_date >= '2019-01-01'::date) AND (ae.operation_date <= '2019-08-31'::date) AND (ae.state = 2))
  • Buffers: shared hit=186 read=5758
12. 2.023 587.720 ↓ 0.0 0 1

BitmapAnd (cost=22,675.65..22,675.65 rows=167 width=0) (actual time=587.72..587.72 rows=0 loops=1)

  • Buffers: shared read=3332
13. 78.819 78.819 ↓ 2.3 23,998 1

Bitmap Index Scan on account_entry_debet_account_id (cost=0..199.29 rows=10,496 width=0) (actual time=78.819..78.819 rows=23,998 loops=1)

  • Index Cond: (ae.debet_account_id = 197977)
  • Buffers: shared read=69
14. 506.878 506.878 ↑ 1.0 1,191,767 1

Bitmap Index Scan on account_entry_ent_id (cost=0..22,476.11 rows=1,210,072 width=0) (actual time=506.878..506.878 rows=1,191,767 loops=1)

  • Index Cond: (ae.ent_id = 1500)
  • Buffers: shared read=3263
15. 1,800.856 1,800.856 ↓ 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=3.932..3.932 rows=0 loops=458)

  • Index Cond: (aec1.entry_id = ae.id)
  • Buffers: shared hit=1169 read=296
16. 676.466 676.466 ↓ 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=1.477..1.477 rows=0 loops=458)

  • Index Cond: (aec2.second_entry_id = ae.id)
  • Buffers: shared hit=1231 read=146
17. 345.790 345.790 ↓ 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.755..0.755 rows=0 loops=458)

  • Index Cond: (ae.id = spec.entry_id)
  • Buffers: shared hit=1640 read=197
18. 0.528 40,074.493 ↓ 255.3 2,298 1

Subquery Scan on *SELECT* 2 (cost=23,591.51..23,593.13 rows=9 width=465) (actual time=40,066.299..40,074.493 rows=2,298 loops=1)

  • Buffers: shared hit=37458 read=10681
19. 7.182 40,073.965 ↓ 255.3 2,298 1

Aggregate (cost=23,591.51..23,593.04 rows=9 width=469) (actual time=40,066.297..40,073.965 rows=2,298 loops=1)

  • Buffers: shared hit=37458 read=10681
20. 13.546 40,066.783 ↓ 352.6 3,173 1

Sort (cost=23,591.51..23,591.53 rows=9 width=174) (actual time=40,066.257..40,066.783 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=37458 read=10681
21. 5.011 40,053.237 ↓ 352.6 3,173 1

Nested Loop (cost=22,679.42..23,591.36 rows=9 width=174) (actual time=257.553..40,053.237 rows=3,173 loops=1)

  • Buffers: shared hit=37458 read=10681
22. 5.379 40,032.361 ↓ 352.6 3,173 1

Nested Loop (cost=22,678.85..23,514.01 rows=9 width=148) (actual time=257.526..40,032.361 rows=3,173 loops=1)

  • Buffers: shared hit=24766 read=10681
23. 5.147 39,512.956 ↓ 352.6 3,173 1

Nested Loop (cost=22,678.42..23,437.85 rows=9 width=144) (actual time=257.502..39,512.956 rows=3,173 loops=1)

  • Buffers: shared hit=15398 read=10522
24. 34,388.363 34,637.254 ↓ 352.6 3,173 1

Bitmap Heap Scan on account_entry ae_1 (cost=22,677.98..23,361.68 rows=9 width=140) (actual time=250.582..34,637.254 rows=3,173 loops=1)

  • Filter: ((ae_1.operation_date >= '2019-01-01'::date) AND (ae_1.operation_date <= '2019-08-31'::date) AND (ae_1.state = 2))
  • Buffers: shared hit=4836 read=9145
25. 2.559 248.891 ↓ 0.0 0 1

BitmapAnd (cost=22,677.98..22,677.98 rows=171 width=0) (actual time=248.891..248.891 rows=0 loops=1)

  • Buffers: shared hit=3263 read=156
26. 114.366 114.366 ↓ 5.1 55,637 1

Bitmap Index Scan on account_entry_credit_account_id (cost=0..201.62 rows=10,807 width=0) (actual time=114.366..114.366 rows=55,637 loops=1)

  • Index Cond: (ae_1.credit_account_id = 197977)
  • Buffers: shared read=156
27. 131.966 131.966 ↑ 1.0 1,191,767 1

Bitmap Index Scan on account_entry_ent_id (cost=0..22,476.11 rows=1,210,072 width=0) (actual time=131.966..131.966 rows=1,191,767 loops=1)

  • Index Cond: (ae_1.ent_id = 1500)
  • Buffers: shared hit=3263
28. 4,870.555 4,870.555 ↑ 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=1.534..1.535 rows=1 loops=3,173)

  • Index Cond: (aec1_1.entry_id = ae_1.id)
  • Buffers: shared hit=10562 read=1377
29. 514.026 514.026 ↓ 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.162..0.162 rows=0 loops=3,173)

  • Index Cond: (aec2_1.second_entry_id = ae_1.id)
  • Buffers: shared hit=9368 read=159
30. 15.865 15.865 ↓ 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.005..0.005 rows=0 loops=3,173)

  • Index Cond: (ae_1.id = spec_1.entry_id)
  • Buffers: shared hit=12692