explain.depesz.com

PostgreSQL's explain analyze made readable

Result: 19Di

Settings
# exclusive inclusive rows x rows loops node
1. 6.401 20,951.686 ↑ 45.5 2,649 1

Aggregate (cost=2,883,893.4..2,904,090.05 rows=120,577 width=493) (actual time=20,945.061..20,951.686 rows=2,649 loops=1)

  • Buffers: shared hit=16545 read=417001
2. 8.809 20,945.285 ↑ 45.5 2,649 1

Sort (cost=2,883,893.4..2,884,194.84 rows=120,577 width=465) (actual time=20,945.039..20,945.285 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=16545 read=417001
3. 0.563 20,936.476 ↑ 45.5 2,649 1

Result (cost=1,416,238.06..2,873,716.95 rows=120,577 width=465) (actual time=12,489.553..20,936.476 rows=2,649 loops=1)

  • Buffers: shared hit=16545 read=417001
4. 0.214 20,935.913 ↑ 45.5 2,649 1

Append (cost=1,416,238.06..2,872,511.18 rows=120,577 width=465) (actual time=12,489.551..20,935.913 rows=2,649 loops=1)

  • Buffers: shared hit=16545 read=417001
5. 0.073 12,490.526 ↑ 168.3 351 1

Subquery Scan on *SELECT* 1 (cost=1,416,238.06..1,426,868.32 rows=59,057 width=465) (actual time=12,489.551..12,490.526 rows=351 loops=1)

  • Buffers: shared hit=2934 read=207704
6. 0.899 12,490.453 ↑ 168.3 351 1

Aggregate (cost=1,416,238.06..1,426,277.75 rows=59,057 width=469) (actual time=12,489.548..12,490.453 rows=351 loops=1)

  • Buffers: shared hit=2934 read=207704
7. 0.891 12,489.554 ↑ 128.9 458 1

Sort (cost=1,416,238.06..1,416,385.7 rows=59,057 width=174) (actual time=12,489.516..12,489.554 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=2934 read=207704
8. 0.246 12,488.663 ↑ 128.9 458 1

Nested Loop (cost=653,067.7..1,411,557.84 rows=59,057 width=174) (actual time=10,675.451..12,488.663 rows=458 loops=1)

  • Buffers: shared hit=2934 read=207704
9. 1,835.495 12,485.669 ↑ 128.9 458 1

Hash Join (cost=653,067.14..976,192.86 rows=59,057 width=148) (actual time=10,675.421..12,485.669 rows=458 loops=1)

  • Buffers: shared hit=1255 read=207546
10. 1,455.015 1,455.015 ↑ 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.036..1,455.015 rows=15,886,343 loops=1)

  • Buffers: shared hit=618 read=103574
11. 0.442 9,195.159 ↑ 128.9 458 1

Hash (cost=652,328.93..652,328.93 rows=59,057 width=144) (actual time=9,195.159..9,195.159 rows=458 loops=1)

  • Buffers: shared hit=637 read=103972
12. 1,434.310 9,194.717 ↑ 128.9 458 1

Hash Join (cost=329,305.09..652,328.93 rows=59,057 width=144) (actual time=9,194.118..9,194.717 rows=458 loops=1)

  • Buffers: shared hit=637 read=103972
13. 7,360.064 7,360.064 ↑ 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=12.143..7,360.064 rows=15,886,343 loops=1)

  • Buffers: shared hit=585 read=103607
14. 0.599 400.343 ↑ 128.9 458 1

Hash (cost=328,566.88..328,566.88 rows=59,057 width=140) (actual time=400.343..400.343 rows=458 loops=1)

  • Buffers: shared hit=52 read=365
15. 0.234 399.744 ↑ 128.9 458 1

Nested Loop (cost=4.08..328,566.88 rows=59,057 width=140) (actual time=0.08..399.744 rows=458 loops=1)

  • Buffers: shared hit=52 read=365
16. 0.006 0.015 ↑ 100.0 1 1

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

17. 0.009 0.009 ↑ 100.0 1 1

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

18. 0.526 399.495 ↓ 4.6 458 1

Nested Loop (cost=2.32..3,284.64 rows=100 width=140) (actual time=0.065..399.495 rows=458 loops=1)

  • Buffers: shared hit=52 read=365
19. 0.002 0.005 ↑ 100.0 1 1

Aggregate (cost=1.76..2.76 rows=100 width=4) (actual time=0.003..0.005 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.002..0.003 rows=1 loops=1)

21. 398.964 398.964 ↓ 76.3 458 1

Index Scan using account_entry_operation_date_state1 on account_entry ae (cost=0.57..32.76 rows=6 width=140) (actual time=0.059..398.964 rows=458 loops=1)

  • Index Cond: ((ae.debet_account_id = (unnest('{197977}'::integer[]))) AND (ae.ent_id = (unnest('{1500}'::integer[]))) AND (ae.operation_date >= '2019-01-01'::date) AND (ae.operation_date <= '2019-08-31'::date))
  • Filter: (ae.state = 2)
  • Buffers: shared hit=52 read=365
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.426 8,445.173 ↑ 26.8 2,298 1

Subquery Scan on *SELECT* 2 (cost=1,434,569.26..1,445,642.86 rows=61,520 width=465) (actual time=8,438.207..8,445.173 rows=2,298 loops=1)

  • Buffers: shared hit=13611 read=209297
24. 6.192 8,444.747 ↑ 26.8 2,298 1

Aggregate (cost=1,434,569.26..1,445,027.66 rows=61,520 width=469) (actual time=8,438.205..8,444.747 rows=2,298 loops=1)

  • Buffers: shared hit=13611 read=209297
25. 10.422 8,438.555 ↑ 19.4 3,173 1

Sort (cost=1,434,569.26..1,434,723.06 rows=61,520 width=174) (actual time=8,438.166..8,438.555 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=13611 read=209297
26. 1.208 8,428.133 ↑ 19.4 3,173 1

Nested Loop (cost=653,023.35..1,429,675.72 rows=61,520 width=174) (actual time=5,828.728..8,428.133 rows=3,173 loops=1)

  • Buffers: shared hit=13611 read=209297
27. 1,919.095 8,147.701 ↑ 19.4 3,173 1

Hash Join (cost=653,022.78..976,153.64 rows=61,520 width=148) (actual time=5,817.182..8,147.701 rows=3,173 loops=1)

  • Buffers: shared hit=1331 read=208885
28. 1,501.429 1,501.429 ↑ 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.022..1,501.429 rows=15,886,343 loops=1)

  • Buffers: shared hit=643 read=103549
29. 1.229 4,727.177 ↑ 19.4 3,173 1

Hash (cost=652,253.78..652,253.78 rows=61,520 width=144) (actual time=4,727.177..4,727.177 rows=3,173 loops=1)

  • Buffers: shared hit=688 read=105336
30. 1,114.521 4,725.948 ↑ 19.4 3,173 1

Hash Join (cost=329,229.05..652,253.78 rows=61,520 width=144) (actual time=4,724.625..4,725.948 rows=3,173 loops=1)

  • Buffers: shared hit=688 read=105336
31. 1,472.462 1,472.462 ↑ 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.018..1,472.462 rows=15,886,343 loops=1)

  • Buffers: shared hit=611 read=103581
32. 3.522 2,138.965 ↑ 19.4 3,173 1

Hash (cost=328,460.05..328,460.05 rows=61,520 width=140) (actual time=2,138.965..2,138.965 rows=3,173 loops=1)

  • Buffers: shared hit=77 read=1755
33. 1.425 2,135.443 ↑ 19.4 3,173 1

Nested Loop (cost=4.08..328,460.05 rows=61,520 width=140) (actual time=0.098..2,135.443 rows=3,173 loops=1)

  • Buffers: shared hit=77 read=1755
34. 0.005 0.014 ↑ 100.0 1 1

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

35. 0.009 0.009 ↑ 100.0 1 1

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

36. 3.417 2,134.004 ↓ 31.7 3,173 1

Nested Loop (cost=2.32..3,283.57 rows=100 width=140) (actual time=0.08..2,134.004 rows=3,173 loops=1)

  • Buffers: shared hit=77 read=1755
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. 2,130.581 2,130.581 ↓ 528.8 3,173 1

Index Scan using account_entry_operation_date_state2 on account_entry ae_1 (cost=0.57..32.75 rows=6 width=140) (actual time=0.071..2,130.581 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.operation_date >= '2019-01-01'::date) AND (ae_1.operation_date <= '2019-08-31'::date))
  • Filter: (ae_1.state = 2)
  • Buffers: shared hit=77 read=1755
40. 279.224 279.224 ↓ 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.088..0.088 rows=0 loops=3,173)

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