explain.depesz.com

PostgreSQL's explain analyze made readable

Result: 1gA

Settings
# exclusive inclusive rows x rows loops node
1. 11.088 18,771.979 ↑ 45.5 2,649 1

Aggregate (cost=2,803,365.08..2,823,561.73 rows=120,577 width=493) (actual time=18,760.343..18,771.979 rows=2,649 loops=1)

  • Buffers: shared hit=14764 read=418775
2. 8.957 18,760.891 ↑ 45.5 2,649 1

Sort (cost=2,803,365.08..2,803,666.52 rows=120,577 width=465) (actual time=18,760.31..18,760.891 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=14764 read=418775
3. 0.562 18,751.934 ↑ 45.5 2,649 1

Result (cost=1,375,964.99..2,793,188.63 rows=120,577 width=465) (actual time=9,556.914..18,751.934 rows=2,649 loops=1)

  • Buffers: shared hit=14764 read=418775
4. 0.222 18,751.372 ↑ 45.5 2,649 1

Append (cost=1,375,964.99..2,791,982.86 rows=120,577 width=465) (actual time=9,556.912..18,751.372 rows=2,649 loops=1)

  • Buffers: shared hit=14764 read=418775
5. 0.079 9,557.889 ↑ 168.3 351 1

Subquery Scan on *SELECT* 1 (cost=1,375,964.99..1,386,595.25 rows=59,057 width=465) (actual time=9,556.911..9,557.889 rows=351 loops=1)

  • Buffers: shared hit=1773 read=208866
6. 0.895 9,557.810 ↑ 168.3 351 1

Aggregate (cost=1,375,964.99..1,386,004.68 rows=59,057 width=469) (actual time=9,556.909..9,557.81 rows=351 loops=1)

  • Buffers: shared hit=1773 read=208866
7. 0.909 9,556.915 ↑ 128.9 458 1

Sort (cost=1,375,964.99..1,376,112.64 rows=59,057 width=174) (actual time=9,556.877..9,556.915 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=1773 read=208866
8. 0.386 9,556.006 ↑ 128.9 458 1

Nested Loop (cost=612,794.64..1,371,284.78 rows=59,057 width=174) (actual time=7,773.488..9,556.006 rows=458 loops=1)

  • Buffers: shared hit=1773 read=208866
9. 1,796.978 9,552.872 ↑ 128.9 458 1

Hash Join (cost=612,794.08..935,919.8 rows=59,057 width=148) (actual time=7,773.457..9,552.872 rows=458 loops=1)

  • Buffers: shared hit=94 read=208708
10. 1,499.758 1,499.758 ↑ 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.084..1,499.758 rows=15,886,343 loops=1)

  • Buffers: shared hit=35 read=104157
11. 0.212 6,256.136 ↑ 128.9 458 1

Hash (cost=612,055.87..612,055.87 rows=59,057 width=144) (actual time=6,256.136..6,256.136 rows=458 loops=1)

  • Buffers: shared hit=59 read=104551
12. 1,391.317 6,255.924 ↑ 128.9 458 1

Hash Join (cost=289,032.03..612,055.87 rows=59,057 width=144) (actual time=6,255.659..6,255.924 rows=458 loops=1)

  • Buffers: shared hit=59 read=104551
13. 4,508.988 4,508.988 ↑ 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.025..4,508.988 rows=15,886,343 loops=1)

  • Buffers: shared hit=2 read=104190
14. 0.488 355.619 ↑ 128.9 458 1

Hash (cost=288,293.82..288,293.82 rows=59,057 width=140) (actual time=355.619..355.619 rows=458 loops=1)

  • Buffers: shared hit=57 read=361
15. 0.261 355.131 ↑ 128.9 458 1

Nested Loop (cost=4.08..288,293.82 rows=59,057 width=140) (actual time=27.654..355.131 rows=458 loops=1)

  • Buffers: shared hit=57 read=361
16. 0.009 0.020 ↑ 100.0 1 1

Aggregate (cost=1.76..2.76 rows=100 width=4) (actual time=0.019..0.02 rows=1 loops=1)

17. 0.011 0.011 ↑ 100.0 1 1

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

18. 0.438 354.850 ↓ 4.6 458 1

Nested Loop (cost=2.32..2,881.91 rows=100 width=140) (actual time=27.631..354.85 rows=458 loops=1)

  • Buffers: shared hit=57 read=361
19. 0.004 0.007 ↑ 100.0 1 1

Aggregate (cost=1.76..2.76 rows=100 width=4) (actual time=0.005..0.007 rows=1 loops=1)

20. 0.003 0.003 ↑ 100.0 1 1

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

21. 354.405 354.405 ↓ 76.3 458 1

Index Scan using account_entry_operation_date_state1 on account_entry ae (cost=0.57..28.73 rows=6 width=140) (actual time=27.618..354.405 rows=458 loops=1)

  • Index Cond: ((ae.debet_account_id = (unnest('{197977}'::integer[]))) AND (ae.ent_id = (unnest('{1500}'::integer[]))) AND (ae.state = 2) AND (ae.operation_date >= '2019-01-01'::date) AND (ae.operation_date <= '2019-08-31'::date))
  • Buffers: shared hit=57 read=361
22. 2.748 2.748 ↓ 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=0.006..0.006 rows=0 loops=458)

  • Index Cond: (ae.id = spec.entry_id)
  • Buffers: shared hit=1679 read=158
23. 0.467 9,193.261 ↑ 26.8 2,298 1

Subquery Scan on *SELECT* 2 (cost=1,394,314..1,405,387.6 rows=61,520 width=465) (actual time=9,186.493..9,193.261 rows=2,298 loops=1)

  • Buffers: shared hit=12991 read=209909
24. 5.959 9,192.794 ↑ 26.8 2,298 1

Aggregate (cost=1,394,314..1,404,772.4 rows=61,520 width=469) (actual time=9,186.491..9,192.794 rows=2,298 loops=1)

  • Buffers: shared hit=12991 read=209909
25. 10.025 9,186.835 ↑ 19.4 3,173 1

Sort (cost=1,394,314..1,394,467.8 rows=61,520 width=174) (actual time=9,186.452..9,186.835 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=12991 read=209909
26. 3.443 9,176.810 ↑ 19.4 3,173 1

Nested Loop (cost=612,768.09..1,389,420.47 rows=61,520 width=174) (actual time=6,892.599..9,176.81 rows=3,173 loops=1)

  • Buffers: shared hit=12991 read=209909
27. 1,873.459 9,167.021 ↑ 19.4 3,173 1

Hash Join (cost=612,767.52..935,898.38 rows=61,520 width=148) (actual time=6,892.556..9,167.021 rows=3,173 loops=1)

  • Buffers: shared hit=299 read=209909
28. 1,494.089 1,494.089 ↑ 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.058..1,494.089 rows=15,886,343 loops=1)

  • Buffers: shared hit=99 read=104093
29. 1.124 5,799.473 ↑ 19.4 3,173 1

Hash (cost=611,998.52..611,998.52 rows=61,520 width=144) (actual time=5,799.473..5,799.473 rows=3,173 loops=1)

  • Buffers: shared hit=200 read=105816
30. 1,189.718 5,798.349 ↑ 19.4 3,173 1

Hash Join (cost=288,973.8..611,998.52 rows=61,520 width=144) (actual time=5,797.118..5,798.349 rows=3,173 loops=1)

  • Buffers: shared hit=200 read=105816
31. 1,595.052 1,595.052 ↑ 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.027..1,595.052 rows=15,886,343 loops=1)

  • Buffers: shared hit=67 read=104125
32. 5.095 3,013.579 ↑ 19.4 3,173 1

Hash (cost=288,204.8..288,204.8 rows=61,520 width=140) (actual time=3,013.579..3,013.579 rows=3,173 loops=1)

  • Buffers: shared hit=133 read=1691
33. 2.158 3,008.484 ↑ 19.4 3,173 1

Nested Loop (cost=4.08..288,204.8 rows=61,520 width=140) (actual time=0.099..3,008.484 rows=3,173 loops=1)

  • Buffers: shared hit=133 read=1691
34. 0.005 0.013 ↑ 100.0 1 1

Aggregate (cost=1.76..2.76 rows=100 width=4) (actual time=0.012..0.013 rows=1 loops=1)

35. 0.008 0.008 ↑ 100.0 1 1

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

36. 4.995 3,006.313 ↓ 31.7 3,173 1

Nested Loop (cost=2.32..2,881.02 rows=100 width=140) (actual time=0.084..3,006.313 rows=3,173 loops=1)

  • Buffers: shared hit=133 read=1691
37. 0.004 0.006 ↑ 100.0 1 1

Aggregate (cost=1.76..2.76 rows=100 width=4) (actual time=0.005..0.006 rows=1 loops=1)

38. 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)

39. 3,001.312 3,001.312 ↓ 528.8 3,173 1

Index Scan using account_entry_operation_date_state2 on account_entry ae_1 (cost=0.57..28.72 rows=6 width=140) (actual time=0.075..3,001.312 rows=3,173 loops=1)

  • Index Cond: ((ae_1.credit_account_id = (unnest('{197977}'::integer[]))) AND (ae_1.ent_id = (unnest('{1500}'::integer[]))) AND (ae_1.state = 2) AND (ae_1.operation_date >= '2019-01-01'::date) AND (ae_1.operation_date <= '2019-08-31'::date))
  • Buffers: shared hit=133 read=1691
40. 6.346 6.346 ↓ 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.002..0.002 rows=0 loops=3,173)

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