explain.depesz.com

PostgreSQL's explain analyze made readable

Result: mPg5

Settings
# exclusive inclusive rows x rows loops node
1. 442.409 424,576.272 ↑ 66.6 31,248 1

Aggregate (cost=12,210,084.74..13,349,627.15 rows=2,081,356 width=477) (actual time=424,127.153..424,576.272 rows=31,248 loops=1)

  • Buffers: shared hit=155113 read=6443511
2. 335.839 424,133.863 ↑ 66.6 31,253 1

Sort (cost=12,210,084.74..12,215,288.13 rows=2,081,356 width=441) (actual time=424,126.854..424,133.863 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=155113 read=6443511
3. 7.366 423,798.024 ↑ 66.6 31,253 1

Result (cost=5,791,139.99..11,991,655.88 rows=2,081,356 width=441) (actual time=215,369.687..423,798.024 rows=31,253 loops=1)

  • Buffers: shared hit=155113 read=6443511
4. 2.859 423,790.658 ↑ 66.6 31,253 1

Append (cost=5,791,139.99..11,970,842.32 rows=2,081,356 width=441) (actual time=215,369.686..423,790.658 rows=31,253 loops=1)

  • Buffers: shared hit=155113 read=6443511
5. 26.805 215,400.543 ↑ 118.6 8,588 1

Aggregate (cost=5,791,139.99..5,966,791.56 rows=1,018,270 width=441) (actual time=215,369.685..215,400.543 rows=8,588 loops=1)

  • Buffers: shared hit=77418 read=3221894
6. 109.916 215,373.738 ↑ 52.8 19,269 1

Sort (cost=5,791,139.99..5,793,685.66 rows=1,018,270 width=184) (actual time=215,369.654..215,373.738 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.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: 3478kB
  • Buffers: shared hit=77418 read=3221894
7. 10,625.722 215,263.822 ↑ 52.8 19,269 1

Hash Join (cost=4,657,017.8..5,689,528.41 rows=1,018,270 width=184) (actual time=185,716.647..215,263.822 rows=19,269 loops=1)

  • Buffers: shared hit=77418 read=3221894
8. 25,648.052 25,648.052 ↑ 1.0 39,258,384 1

Seq Scan on account_entry_spec spec (cost=0..866,775.83 rows=39,399,083 width=25) (actual time=4.417..25,648.052 rows=39,258,384 loops=1)

  • Buffers: shared hit=2 read=472783
9. 11.121 178,990.048 ↑ 52.8 19,269 1

Hash (cost=4,644,289.42..4,644,289.42 rows=1,018,270 width=142) (actual time=178,990.048..178,990.048 rows=19,269 loops=1)

  • Buffers: shared hit=77416 read=2749111
10. 3,519.159 178,978.927 ↑ 52.8 19,269 1

Hash Join (cost=4,319,162.45..4,644,289.42 rows=1,018,270 width=142) (actual time=173,732.066..178,978.927 rows=19,269 loops=1)

  • Buffers: shared hit=77416 read=2749111
11. 1,765.897 1,765.897 ↑ 1.0 15,886,342 1

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

  • Buffers: shared hit=99 read=104093
12. 8.969 173,693.871 ↑ 52.8 19,269 1

Hash (cost=4,306,434.08..4,306,434.08 rows=1,018,270 width=138) (actual time=173,693.871..173,693.871 rows=19,269 loops=1)

  • Buffers: shared hit=77317 read=2645018
13. 1,901.822 173,684.902 ↑ 52.8 19,269 1

Hash Join (cost=3,983,063.49..4,306,434.08 rows=1,018,270 width=138) (actual time=173,673.037..173,684.902 rows=19,269 loops=1)

  • Buffers: shared hit=77317 read=2645018
14. 9,523.770 9,523.770 ↑ 1.0 15,886,342 1

Seq Scan on account_entry_contract aec2 (cost=0..263,326.9 rows=15,913,490 width=8) (actual time=0.275..9,523.77 rows=15,886,342 loops=1)

  • Buffers: shared hit=67 read=104125
15. 26.219 162,259.310 ↑ 52.8 19,269 1

Hash (cost=3,970,335.12..3,970,335.12 rows=1,018,270 width=134) (actual time=162,259.31..162,259.31 rows=19,269 loops=1)

  • Buffers: shared hit=77250 read=2540893
16. 9.022 162,233.091 ↑ 52.8 19,269 1

Hash Join (cost=5.51..3,970,335.12 rows=1,018,270 width=134) (actual time=81.329..162,233.091 rows=19,269 loops=1)

  • Buffers: shared hit=77250 read=2540893
17. 6,331.640 162,224.042 ↑ 71.3 19,269 1

Hash Join (cost=2.76..3,955,395.61 rows=1,374,665 width=134) (actual time=81.276..162,224.042 rows=19,269 loops=1)

  • Buffers: shared hit=77250 read=2540893
18. 155,892.396 155,892.396 ↑ 1.0 67,418,462 1

Seq Scan on account_entry ae (cost=0..3,762,251.8 rows=67,751,582 width=134) (actual time=0.034..155,892.396 rows=67,418,462 loops=1)

  • Filter: ((ae.operation_date >= '0001-01-01'::date) AND (ae.state = 2))
  • Buffers: shared hit=77250 read=2540893
19. 0.002 0.006 ↑ 100.0 1 1

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

20. 0.004 0.004 ↑ 100.0 1 1

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

21. 0.006 0.027 ↑ 100.0 1 1

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

22. 0.021 0.021 ↑ 100.0 1 1

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

23. 79.615 208,387.256 ↑ 46.9 22,665 1

Aggregate (cost=5,799,854.86..5,983,237.19 rows=1,063,086 width=441) (actual time=208,293.591..208,387.256 rows=22,665 loops=1)

  • Buffers: shared hit=77695 read=3221617
24. 431.555 208,307.641 ↑ 21.7 48,945 1

Sort (cost=5,799,854.86..5,802,512.57 rows=1,063,086 width=184) (actual time=208,293.519..208,307.641 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.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: 8419kB
  • Buffers: shared hit=77695 read=3221617
25. 11,749.592 207,876.086 ↑ 21.7 48,945 1

Hash Join (cost=4,660,138.56..5,693,440.87 rows=1,063,086 width=184) (actual time=184,671.814..207,876.086 rows=48,945 loops=1)

  • Buffers: shared hit=77695 read=3221617
26. 22,018.604 22,018.604 ↑ 1.0 39,258,384 1

Seq Scan on account_entry_spec spec_1 (cost=0..866,775.83 rows=39,399,083 width=25) (actual time=5.836..22,018.604 rows=39,258,384 loops=1)

  • Buffers: shared hit=36 read=472749
27. 53.804 174,107.890 ↑ 21.7 48,945 1

Hash (cost=4,646,849.99..4,646,849.99 rows=1,063,086 width=142) (actual time=174,107.89..174,107.89 rows=48,945 loops=1)

  • Buffers: shared hit=77659 read=2748868
28. 3,808.099 174,054.086 ↑ 21.7 48,945 1

Hash Join (cost=4,321,629.52..4,646,849.99 rows=1,063,086 width=142) (actual time=168,615.095..174,054.086 rows=48,945 loops=1)

  • Buffers: shared hit=77659 read=2748868
29. 1,664.079 1,664.079 ↑ 1.0 15,886,342 1

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

  • Buffers: shared hit=163 read=104029
30. 34.047 168,581.908 ↑ 21.7 48,945 1

Hash (cost=4,308,340.94..4,308,340.94 rows=1,063,086 width=138) (actual time=168,581.908..168,581.908 rows=48,945 loops=1)

  • Buffers: shared hit=77496 read=2644839
31. 1,527.348 168,547.861 ↑ 21.7 48,945 1

Hash Join (cost=3,984,954.16..4,308,340.94 rows=1,063,086 width=138) (actual time=168,515.369..168,547.861 rows=48,945 loops=1)

  • Buffers: shared hit=77496 read=2644839
32. 1,597.572 1,597.572 ↑ 1.0 15,886,342 1

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

  • Buffers: shared hit=131 read=104061
33. 52.252 165,422.941 ↑ 21.7 48,945 1

Hash (cost=3,971,665.58..3,971,665.58 rows=1,063,086 width=134) (actual time=165,422.941..165,422.941 rows=48,945 loops=1)

  • Buffers: shared hit=77365 read=2540778
34. 31.966 165,370.689 ↑ 21.7 48,945 1

Hash Join (cost=5.51..3,971,665.58 rows=1,063,086 width=134) (actual time=10.138..165,370.689 rows=48,945 loops=1)

  • Buffers: shared hit=77365 read=2540778
35. 6,933.427 165,338.693 ↑ 29.3 48,945 1

Hash Join (cost=2.76..3,956,068.68 rows=1,435,166 width=134) (actual time=10.082..165,338.693 rows=48,945 loops=1)

  • Buffers: shared hit=77365 read=2540778
36. 158,405.259 158,405.259 ↑ 1.0 67,418,462 1

Seq Scan on account_entry ae_1 (cost=0..3,762,251.8 rows=67,751,582 width=134) (actual time=0.037..158,405.259 rows=67,418,462 loops=1)

  • Filter: ((ae_1.operation_date >= '0001-01-01'::date) AND (ae_1.state = 2))
  • Buffers: shared hit=77365 read=2540778
37. 0.002 0.007 ↑ 100.0 1 1

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

38. 0.005 0.005 ↑ 100.0 1 1

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

39. 0.004 0.030 ↑ 100.0 1 1

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

40. 0.026 0.026 ↑ 100.0 1 1

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