explain.depesz.com

PostgreSQL's explain analyze made readable

Result: GlAf

Settings
# exclusive inclusive rows x rows loops node
1. 423.258 48,552.271 ↓ 100.8 31,248 1

Aggregate (cost=56,058.85..56,228.58 rows=310 width=477) (actual time=48,122.02..48,552.271 rows=31,248 loops=1)

  • Buffers: shared hit=717962 read=33738
2. 402.479 48,129.013 ↓ 100.8 31,253 1

Sort (cost=56,058.85..56,059.63 rows=310 width=441) (actual time=48,121.744..48,129.013 rows=31,253 loops=1)

  • Sort Key: (make_timestamp((date_part('year'::text, (ae.operation_date)::timestamp without time zone))::integer, 12, 31, 23, 59, '59'::double precision)), ('9999-12-31 23:59:59'::timestamp without time zone), ('0001-12-31 23:59:59'::timestamp without time zone), 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: 9070kB
  • Buffers: shared hit=717962 read=33738
3. 7.290 47,726.534 ↓ 100.8 31,253 1

Result (cost=27,896.93..56,046.03 rows=310 width=441) (actual time=23,606.654..47,726.534 rows=31,253 loops=1)

  • Buffers: shared hit=717962 read=33738
4. 2.764 47,719.244 ↓ 100.8 31,253 1

Append (cost=27,896.93..56,042.93 rows=310 width=441) (actual time=23,606.651..47,719.244 rows=31,253 loops=1)

  • Buffers: shared hit=717962 read=33738
5. 28.588 23,638.398 ↓ 56.5 8,588 1

Aggregate (cost=27,896.93..27,923.15 rows=152 width=441) (actual time=23,606.65..23,638.398 rows=8,588 loops=1)

  • Buffers: shared hit=195388 read=12380
6. 127.993 23,609.810 ↓ 126.8 19,269 1

Sort (cost=27,896.93..27,897.31 rows=152 width=184) (actual time=23,606.621..23,609.81 rows=19,269 loops=1)

  • Sort Key: (make_timestamp((date_part('year'::text, (ae.operation_date)::timestamp without time zone))::integer, 12, 31, 23, 59, '59'::double precision)), 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: 3478kB
  • Buffers: shared hit=195388 read=12380
7. 57.271 23,481.817 ↓ 126.8 19,269 1

Nested Loop (cost=23,329.03..27,891.43 rows=152 width=184) (actual time=650.307..23,481.817 rows=19,269 loops=1)

  • Buffers: shared hit=195388 read=12380
8. 27.188 13,269.783 ↓ 126.8 19,269 1

Nested Loop (cost=23,328.46..26,583.47 rows=152 width=142) (actual time=639.692..13,269.783 rows=19,269 loops=1)

  • Buffers: shared hit=121508 read=9117
9. 20.641 9,003.415 ↓ 126.8 19,269 1

Nested Loop (cost=23,328.03..25,297.17 rows=152 width=138) (actual time=620.402..9,003.415 rows=19,269 loops=1)

  • Buffers: shared hit=65241 read=7520
10. 704.118 1,294.443 ↓ 126.8 19,269 1

Bitmap Heap Scan on account_entry ae (cost=23,327.59..24,010.87 rows=152 width=134) (actual time=591.396..1,294.443 rows=19,269 loops=1)

  • Filter: ((ae.operation_date >= '0001-01-01'::date) AND (ae.state = 2))
  • Buffers: shared hit=1154 read=4790
11. 0.861 590.325 ↓ 0.0 0 1

BitmapAnd (cost=23,327.59..23,327.59 rows=171 width=0) (actual time=590.325..590.325 rows=0 loops=1)

  • Buffers: shared read=3332
12. 52.741 52.741 ↓ 2.3 23,998 1

Bitmap Index Scan on account_entry_debet_account_id (cost=0..195.14 rows=10,477 width=0) (actual time=52.741..52.741 rows=23,998 loops=1)

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

Bitmap Index Scan on account_entry_ent_id (cost=0..23,132.12 rows=1,245,807 width=0) (actual time=536.723..536.723 rows=1,191,767 loops=1)

  • Index Cond: (ae.ent_id = 1500)
  • Buffers: shared read=3263
14. 7,688.331 7,688.331 ↓ 0.0 0 19,269

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.399..0.399 rows=0 loops=19,269)

  • Index Cond: (aec1.entry_id = ae.id)
  • Buffers: shared hit=64087 read=2730
15. 4,239.180 4,239.180 ↓ 0.0 0 19,269

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.22..0.22 rows=0 loops=19,269)

  • Index Cond: (aec2.second_entry_id = ae.id)
  • Buffers: shared hit=56267 read=1597
16. 10,154.763 10,154.763 ↓ 0.0 0 19,269

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.527..0.527 rows=0 loops=19,269)

  • Index Cond: (ae.id = spec.entry_id)
  • Buffers: shared hit=73880 read=3263
17. 73.142 24,078.082 ↓ 143.4 22,665 1

Aggregate (cost=28,089.42..28,116.67 rows=158 width=441) (actual time=23,995.234..24,078.082 rows=22,665 loops=1)

  • Buffers: shared hit=522574 read=21358
18. 484.976 24,004.940 ↓ 309.8 48,945 1

Sort (cost=28,089.42..28,089.81 rows=158 width=184) (actual time=23,995.186..24,004.94 rows=48,945 loops=1)

  • Sort Key: (make_timestamp((date_part('year'::text, (ae_1.operation_date)::timestamp without time zone))::integer, 12, 31, 23, 59, '59'::double precision)), 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: 8419kB
  • Buffers: shared hit=522574 read=21358
19. 122.287 23,519.964 ↓ 309.8 48,945 1

Nested Loop (cost=23,340.19..28,083.65 rows=158 width=184) (actual time=352.679..23,519.964 rows=48,945 loops=1)

  • Buffers: shared hit=522574 read=21358
20. 19.568 14,440.742 ↓ 309.8 48,945 1

Nested Loop (cost=23,339.62..26,724.06 rows=158 width=142) (actual time=352.644..14,440.742 rows=48,945 loops=1)

  • Buffers: shared hit=329704 read=18331
21. 66.554 11,827.089 ↓ 309.8 48,945 1

Nested Loop (cost=23,339.19..25,386.98 rows=158 width=138) (actual time=352.631..11,827.089 rows=48,945 loops=1)

  • Buffers: shared hit=183969 read=17196
22. 5,243.067 5,593.465 ↓ 309.8 48,945 1

Bitmap Heap Scan on account_entry ae_1 (cost=23,338.75..24,049.91 rows=158 width=134) (actual time=352.592..5,593.465 rows=48,945 loops=1)

  • Filter: ((ae_1.operation_date >= '0001-01-01'::date) AND (ae_1.state = 2))
  • Buffers: shared hit=8455 read=5526
23. 2.972 350.398 ↓ 0.0 0 1

BitmapAnd (cost=23,338.75..23,338.75 rows=178 width=0) (actual time=350.398..350.398 rows=0 loops=1)

  • Buffers: shared hit=3265 read=154
24. 80.713 80.713 ↓ 5.1 55,637 1

Bitmap Index Scan on account_entry_credit_account_id (cost=0..206.3 rows=10,898 width=0) (actual time=80.713..80.713 rows=55,637 loops=1)

  • Index Cond: (ae_1.credit_account_id = 197977)
  • Buffers: shared hit=2 read=154
25. 266.713 266.713 ↑ 1.0 1,191,767 1

Bitmap Index Scan on account_entry_ent_id (cost=0..23,132.12 rows=1,245,807 width=0) (actual time=266.713..266.713 rows=1,191,767 loops=1)

  • Index Cond: (ae_1.ent_id = 1500)
  • Buffers: shared hit=3263
26. 6,167.070 6,167.070 ↑ 1.0 1 48,945

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.126..0.126 rows=1 loops=48,945)

  • Index Cond: (aec1_1.entry_id = ae_1.id)
  • Buffers: shared hit=175514 read=11670
27. 2,594.085 2,594.085 ↓ 0.0 0 48,945

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.053..0.053 rows=0 loops=48,945)

  • Index Cond: (aec2_1.second_entry_id = ae_1.id)
  • Buffers: shared hit=145735 read=1135
28. 8,956.935 8,956.935 ↓ 0.0 0 48,945

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.183..0.183 rows=0 loops=48,945)

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