explain.depesz.com

PostgreSQL's explain analyze made readable

Result: HRyT

Settings
# exclusive inclusive rows x rows loops node
1. 0.002 26,136.446 ↓ 0.0 0 1

Aggregate (cost=6,748,009.9..6,748,010.24 rows=2 width=493) (actual time=26,136.446..26,136.446 rows=0 loops=1)

  • Buffers: shared hit=423 read=688593
2.          

CTE document_contract_temp

3. 25.710 26,107.132 ↑ 93.4 14,872 1

Aggregate (cost=6,667,998.57..6,681,890.53 rows=1,389,196 width=134) (actual time=26,099.198..26,107.132 rows=14,872 loops=1)

  • Buffers: shared hit=112 read=688580
4. 1.653 26,081.422 ↑ 93.4 14,877 1

Append (cost=2,920,002.97..6,650,633.62 rows=1,389,196 width=134) (actual time=9,273.301..26,081.422 rows=14,877 loops=1)

  • Buffers: shared hit=112 read=688580
5. 76.132 15,539.437 ↑ 65.8 10,559 1

Hash Join (cost=2,920,002.97..3,322,247 rows=694,598 width=22) (actual time=9,273.3..15,539.437 rows=10,559 loops=1)

  • Buffers: shared hit=75 read=344271
6. 7,116.435 15,463.273 ↑ 1.1 892,324 1

Hash Join (cost=2,920,000.21..3,312,055.36 rows=937,707 width=26) (actual time=7,225.153..15,463.273 rows=892,324 loops=1)

  • Buffers: shared hit=75 read=344271
7. 1,691.928 1,691.928 ↑ 1.0 15,886,342 1

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

  • Buffers: shared read=104192
8. 1,982.529 6,654.910 ↓ 1.0 4,703,972 1

Hash (cost=2,863,819.42..2,863,819.42 rows=4,494,463 width=22) (actual time=6,654.91..6,654.91 rows=4,703,972 loops=1)

  • Buffers: shared hit=75 read=240079
9. 3,969.991 4,672.381 ↓ 1.0 4,703,972 1

Bitmap Heap Scan on account_entry ae_2 (cost=95,804.81..2,863,819.42 rows=4,494,463 width=22) (actual time=784.836..4,672.381 rows=4,703,972 loops=1)

  • Buffers: shared hit=75 read=240079
10. 702.390 702.390 ↓ 1.1 4,719,908 1

Bitmap Index Scan on account_entry_operation_date (cost=0..94,681.2 rows=4,494,463 width=0) (actual time=702.39..702.39 rows=4,719,908 loops=1)

  • Index Cond: ((ae_2.operation_date >= '2019-01-01'::date) AND (ae_2.operation_date <= '2019-08-31'::date))
  • Buffers: shared hit=5 read=12968
11. 0.015 0.032 ↑ 100.0 1 1

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

12. 0.017 0.017 ↑ 100.0 1 1

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

13. 25.441 10,540.332 ↑ 160.9 4,318 1

Hash Join (cost=2,920,002.97..3,314,494.66 rows=694,598 width=22) (actual time=7,750.121..10,540.332 rows=4,318 loops=1)

  • Buffers: shared hit=37 read=344309
14. 2,350.899 10,514.869 ↑ 3.2 295,888 1

Hash Join (cost=2,920,000.21..3,304,303.02 rows=937,707 width=26) (actual time=7,726.727..10,514.869 rows=295,888 loops=1)

  • Buffers: shared hit=37 read=344309
15. 1,629.160 1,629.160 ↑ 1.0 15,886,342 1

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

  • Buffers: shared read=104192
16. 2,045.838 6,534.810 ↓ 1.0 4,703,972 1

Hash (cost=2,863,819.42..2,863,819.42 rows=4,494,463 width=22) (actual time=6,534.81..6,534.81 rows=4,703,972 loops=1)

  • Buffers: shared hit=37 read=240117
17. 3,800.584 4,488.972 ↓ 1.0 4,703,972 1

Bitmap Heap Scan on account_entry ae_3 (cost=95,804.81..2,863,819.42 rows=4,494,463 width=22) (actual time=769.424..4,488.972 rows=4,703,972 loops=1)

  • Buffers: shared hit=37 read=240117
18. 688.388 688.388 ↓ 1.1 4,719,908 1

Bitmap Index Scan on account_entry_operation_date (cost=0..94,681.2 rows=4,494,463 width=0) (actual time=688.388..688.388 rows=4,719,908 loops=1)

  • Index Cond: ((ae_3.operation_date >= '2019-01-01'::date) AND (ae_3.operation_date <= '2019-08-31'::date))
  • Buffers: shared hit=5 read=12968
19. 0.004 0.022 ↑ 100.0 1 1

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

20. 0.018 0.018 ↑ 100.0 1 1

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

21. 0.046 26,136.444 ↓ 0.0 0 1

Sort (cost=66,119.37..66,119.38 rows=2 width=465) (actual time=26,136.444..26,136.444 rows=0 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: 25kB
  • Buffers: shared hit=423 read=688593
22. 0.002 26,136.398 ↓ 0.0 0 1

Result (cost=33,059.49..66,119.36 rows=2 width=465) (actual time=26,136.398..26,136.398 rows=0 loops=1)

  • Buffers: shared hit=423 read=688593
23. 0.002 26,136.396 ↓ 0.0 0 1

Append (cost=33,059.49..66,119.34 rows=2 width=465) (actual time=26,136.396..26,136.396 rows=0 loops=1)

  • Buffers: shared hit=423 read=688593
24. 0.001 26,123.630 ↓ 0.0 0 1

Subquery Scan on *SELECT* 1 (cost=33,059.49..33,059.67 rows=1 width=465) (actual time=26,123.63..26,123.63 rows=0 loops=1)

  • Buffers: shared hit=274 read=688580
25. 0.003 26,123.629 ↓ 0.0 0 1

Aggregate (cost=33,059.49..33,059.66 rows=1 width=469) (actual time=26,123.629..26,123.629 rows=0 loops=1)

  • Buffers: shared hit=274 read=688580
26. 0.024 26,123.626 ↓ 0.0 0 1

Sort (cost=33,059.49..33,059.49 rows=1 width=174) (actual time=26,123.626..26,123.626 rows=0 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, con.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: 25kB
  • Buffers: shared hit=274 read=688580
27. 0.000 26,123.602 ↓ 0.0 0 1

Nested Loop (cost=41.27..33,059.48 rows=1 width=174) (actual time=26,123.602..26,123.602 rows=0 loops=1)

  • Buffers: shared hit=274 read=688580
28. 0.001 26,123.602 ↓ 0.0 0 1

Nested Loop (cost=41.27..33,056.72 rows=1 width=165) (actual time=26,123.602..26,123.602 rows=0 loops=1)

  • Buffers: shared hit=274 read=688580
29. 5.979 26,123.601 ↓ 0.0 0 1

Nested Loop (cost=40.7..33,048.13 rows=1 width=144) (actual time=26,123.601..26,123.601 rows=0 loops=1)

  • Buffers: shared hit=274 read=688580
30. 1.872 26,114.202 ↓ 47.5 380 1

Hash Join (cost=40.7..33,034.12 rows=8 width=144) (actual time=26,114.059..26,114.202 rows=380 loops=1)

  • Buffers: shared hit=274 read=688580
31. 26,111.513 26,111.513 ↑ 93.4 14,872 1

CTE Scan on document_contract_temp con (cost=0..27,783.92 rows=1,389,196 width=8) (actual time=26,099.203..26,111.513 rows=14,872 loops=1)

  • Buffers: shared hit=112 read=688580
32. 0.181 0.817 ↓ 47.5 380 1

Hash (cost=40.6..40.6 rows=8 width=140) (actual time=0.817..0.817 rows=380 loops=1)

  • Buffers: shared hit=162
33. 0.636 0.636 ↓ 47.5 380 1

Index Scan using account_entry_operation_date on account_entry ae (cost=0.57..40.6 rows=8 width=140) (actual time=0.042..0.636 rows=380 loops=1)

  • Index Cond: ((ae.operation_date >= '2019-08-01'::date) AND (ae.operation_date <= '2019-08-31'::date))
  • Filter: (ae.state = 2)
  • Buffers: shared hit=162
34. 3.384 3.420 ↓ 1.6 165 380

Materialize (cost=0..2.01 rows=100 width=4) (actual time=0..0.009 rows=165 loops=380)

35. 0.036 0.036 ↓ 1.6 165 1

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

36. 0.000 0.000 ↓ 0.0 0 0

Index Scan using account_entry_spec_entry_id on account_entry_spec spec (cost=0.56..8.58 rows=1 width=25) (never executed)

  • Index Cond: (ae.id = spec.entry_id)
37. 0.000 0.000 ↓ 0.0 0 0

Result (cost=0..0.51 rows=100 width=4) (never executed)

38. 0.002 12.764 ↓ 0.0 0 1

Subquery Scan on *SELECT* 2 (cost=33,059.49..33,059.67 rows=1 width=465) (actual time=12.764..12.764 rows=0 loops=1)

  • Buffers: shared hit=149 read=13
39. 0.003 12.762 ↓ 0.0 0 1

Aggregate (cost=33,059.49..33,059.66 rows=1 width=469) (actual time=12.762..12.762 rows=0 loops=1)

  • Buffers: shared hit=149 read=13
40. 0.056 12.759 ↓ 0.0 0 1

Sort (cost=33,059.49..33,059.49 rows=1 width=174) (actual time=12.759..12.759 rows=0 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, con_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: 25kB
  • Buffers: shared hit=149 read=13
41. 0.001 12.703 ↓ 0.0 0 1

Nested Loop (cost=41.27..33,059.48 rows=1 width=174) (actual time=12.703..12.703 rows=0 loops=1)

  • Buffers: shared hit=149 read=13
42. 0.002 12.702 ↓ 0.0 0 1

Nested Loop (cost=41.27..33,056.72 rows=1 width=165) (actual time=12.702..12.702 rows=0 loops=1)

  • Buffers: shared hit=149 read=13
43. 5.736 12.700 ↓ 0.0 0 1

Nested Loop (cost=40.7..33,048.13 rows=1 width=144) (actual time=12.7..12.7 rows=0 loops=1)

  • Buffers: shared hit=149 read=13
44. 1.685 3.924 ↓ 47.5 380 1

Hash Join (cost=40.7..33,034.12 rows=8 width=144) (actual time=3.795..3.924 rows=380 loops=1)

  • Buffers: shared hit=149 read=13
45. 1.364 1.364 ↑ 93.4 14,872 1

CTE Scan on document_contract_temp con_1 (cost=0..27,783.92 rows=1,389,196 width=8) (actual time=0.003..1.364 rows=14,872 loops=1)

46. 0.169 0.875 ↓ 47.5 380 1

Hash (cost=40.6..40.6 rows=8 width=140) (actual time=0.875..0.875 rows=380 loops=1)

  • Buffers: shared hit=149 read=13
47. 0.706 0.706 ↓ 47.5 380 1

Index Scan using account_entry_operation_date on account_entry ae_1 (cost=0.57..40.6 rows=8 width=140) (actual time=0.075..0.706 rows=380 loops=1)

  • Index Cond: ((ae_1.operation_date >= '2019-08-01'::date) AND (ae_1.operation_date <= '2019-08-31'::date))
  • Filter: (ae_1.state = 2)
  • Buffers: shared hit=149 read=13
48. 3.011 3.040 ↓ 1.6 165 380

Materialize (cost=0..2.01 rows=100 width=4) (actual time=0..0.008 rows=165 loops=380)

49. 0.029 0.029 ↓ 1.6 165 1

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

50. 0.000 0.000 ↓ 0.0 0 0

Index Scan using account_entry_spec_entry_id on account_entry_spec spec_1 (cost=0.56..8.58 rows=1 width=25) (never executed)

  • Index Cond: (ae_1.id = spec_1.entry_id)
51. 0.000 0.000 ↓ 0.0 0 0

Result (cost=0..0.51 rows=100 width=4) (never executed)