explain.depesz.com

PostgreSQL's explain analyze made readable

Result: OMHe

Settings
# exclusive inclusive rows x rows loops node
1. 0.035 958.793 ↓ 60.0 120 1

Unique (cost=98,186.69..98,186.72 rows=2 width=132) (actual time=958.742..958.793 rows=120 loops=1)

2. 1.536 958.758 ↓ 60.0 120 1

Sort (cost=98,186.69..98,186.69 rows=2 width=132) (actual time=958.739..958.758 rows=120 loops=1)

  • Sort Key: "*SELECT* 1"."Месяц", "*SELECT* 1"."Вид", "*SELECT* 1"."Категория", "*SELECT* 1"."Класс", "*SELECT* 1"."Часы
  • Sort Method: quicksort Memory: 43kB
3. 0.011 957.222 ↓ 60.0 120 1

Append (cost=18,199.56..98,186.68 rows=2 width=132) (actual time=145.823..957.222 rows=120 loops=1)

4. 0.005 147.754 ↓ 58.0 58 1

Subquery Scan on *SELECT* 1 (cost=18,199.56..18,224.50 rows=1 width=132) (actual time=145.822..147.754 rows=58 loops=1)

5. 1.572 147.749 ↓ 58.0 58 1

GroupAggregate (cost=18,199.56..18,224.49 rows=1 width=144) (actual time=145.821..147.749 rows=58 loops=1)

  • Group Key: (to_char(date_trunc('MONTH'::text, co1.operation_date), 'YYYY, MONTH'::text)), sc.container_type_id, sc.container_category_id, sc.class_id
6. 4.487 146.003 ↓ 4,980.0 4,980 1

Sort (cost=18,199.56..18,199.56 rows=1 width=60) (actual time=145.773..146.003 rows=4,980 loops=1)

  • Sort Key: (to_char(date_trunc('MONTH'::text, co1.operation_date), 'YYYY, MONTH'::text)), sc.container_type_id, sc.container_category_id, sc.class_id
  • Sort Method: quicksort Memory: 582kB
7. 9.901 141.516 ↓ 4,980.0 4,980 1

Nested Loop (cost=16,163.69..18,199.55 rows=1 width=60) (actual time=80.105..141.516 rows=4,980 loops=1)

8. 4.619 120.172 ↓ 11,443.0 11,443 1

Merge Join (cost=16,163.41..18,193.62 rows=1 width=32) (actual time=79.993..120.172 rows=11,443 loops=1)

  • Merge Cond: (co1.container_id = co2.container_id)
  • Join Filter: (co1.operation_date < co2.operation_date)
  • Rows Removed by Join Filter: 1
9. 2.739 88.741 ↓ 97.1 20,301 1

Subquery Scan on co1 (cost=9,911.67..11,269.00 rows=209 width=16) (actual time=66.371..88.741 rows=20,301 loops=1)

  • Filter: (co1.rn = 1)
  • Rows Removed by Filter: 21,743
10. 16.257 86.002 ↓ 1.0 42,044 1

WindowAgg (cost=9,911.67..10,746.95 rows=41,764 width=56) (actual time=66.366..86.002 rows=42,044 loops=1)

11. 19.442 69.745 ↓ 1.0 42,044 1

Sort (cost=9,911.67..10,016.08 rows=41,764 width=16) (actual time=66.350..69.745 rows=42,044 loops=1)

  • Sort Key: container_operation.container_id, container_operation.operation_date
  • Sort Method: quicksort Memory: 3,337kB
12. 26.703 50.303 ↓ 1.0 42,044 1

Bitmap Heap Scan on container_operation (cost=1,714.99..6,706.29 rows=41,764 width=16) (actual time=25.144..50.303 rows=42,044 loops=1)

  • Recheck Cond: (operation_type = 'CONTAINER_SORTING'::text)
  • Filter: ((operation_date >= '2018-02-01 00:00:00+03'::timestamp with time zone) AND (operation_date <= '2018-07-28 00:00:00+03'::timestamp with time zone))
  • Rows Removed by Filter: 20,400
  • Heap Blocks: exact=3,869
13. 23.600 23.600 ↓ 1.0 62,445 1

Bitmap Index Scan on idx_container_operation_type (cost=0.00..1,704.55 rows=62,417 width=0) (actual time=23.600..23.600 rows=62,445 loops=1)

  • Index Cond: (operation_type = 'CONTAINER_SORTING'::text)
14. 1.538 26.812 ↓ 135.9 14,000 1

Materialize (cost=6,251.74..6,923.80 rows=103 width=16) (actual time=13.588..26.812 rows=14,000 loops=1)

15. 1.251 25.274 ↓ 135.9 14,000 1

Subquery Scan on co2 (cost=6,251.74..6,923.55 rows=103 width=16) (actual time=13.584..25.274 rows=14,000 loops=1)

  • Filter: (co2.rn = 1)
  • Rows Removed by Filter: 3
16. 8.966 24.023 ↑ 1.5 14,003 1

WindowAgg (cost=6,251.74..6,665.16 rows=20,671 width=56) (actual time=13.578..24.023 rows=14,003 loops=1)

17. 7.220 15.057 ↑ 1.5 14,003 1

Sort (cost=6,251.74..6,303.42 rows=20,671 width=16) (actual time=13.570..15.057 rows=14,003 loops=1)

  • Sort Key: container_operation_1.container_id, container_operation_1.operation_date
  • Sort Method: quicksort Memory: 1,735kB
18. 6.416 7.837 ↑ 1.0 20,609 1

Bitmap Heap Scan on container_operation container_operation_1 (cost=561.05..4,770.11 rows=20,671 width=16) (actual time=1.639..7.837 rows=20,609 loops=1)

  • Recheck Cond: (operation_type = ANY ('{CONTAINER_IS_SENT,CONTAINER_IS_SENT_INSIDE_CONTAINER}'::text[]))
  • Filter: (operation_date >= '2018-02-01 00:00:00+03'::timestamp with time zone)
  • Heap Blocks: exact=2,084
19. 1.421 1.421 ↑ 1.0 20,610 1

Bitmap Index Scan on idx_container_operation_type (cost=0.00..555.88 rows=20,671 width=0) (actual time=1.421..1.421 rows=20,610 loops=1)

  • Index Cond: (operation_type = ANY ('{CONTAINER_IS_SENT,CONTAINER_IS_SENT_INSIDE_CONTAINER}'::text[]))
20. 11.443 11.443 ↓ 0.0 0 11,443

Index Scan using session_container_pkey on session_container sc (cost=0.29..5.91 rows=1 width=20) (actual time=0.001..0.001 rows=0 loops=11,443)

  • Index Cond: (id = co1.container_id)
21.          

SubPlan (for GroupAggregate)

22. 0.058 0.058 ↑ 1.0 1 58

Index Scan using pk_dict on dict dict_2 (cost=0.28..8.29 rows=1 width=31) (actual time=0.001..0.001 rows=1 loops=58)

  • Index Cond: ((dict_class_id = 2) AND (code = sc.container_type_id))
23. 0.058 0.058 ↑ 1.0 1 58

Index Scan using pk_dict on dict dict_3 (cost=0.28..8.29 rows=1 width=31) (actual time=0.001..0.001 rows=1 loops=58)

  • Index Cond: ((dict_class_id = 3) AND (code = sc.container_category_id))
24. 0.058 0.058 ↑ 1.0 1 58

Index Scan using pk_dict on dict dict_4 (cost=0.28..8.29 rows=1 width=31) (actual time=0.001..0.001 rows=1 loops=58)

  • Index Cond: ((dict_class_id = 4) AND (code = sc.class_id))
25. 0.007 809.457 ↓ 62.0 62 1

Subquery Scan on *SELECT* 2 (cost=79,945.53..79,962.18 rows=1 width=132) (actual time=804.577..809.457 rows=62 loops=1)

26. 3.736 809.450 ↓ 62.0 62 1

GroupAggregate (cost=79,945.53..79,962.17 rows=1 width=140) (actual time=804.576..809.450 rows=62 loops=1)

  • Group Key: (to_char(date_trunc('MONTH'::text, ro1.operation_date), 'YYYY, MONTH'::text)), sr.rpi_type_id, sr.rpi_category_id
27. 14.148 805.528 ↓ 10,339.0 10,339 1

Sort (cost=79,945.53..79,945.54 rows=1 width=56) (actual time=804.531..805.528 rows=10,339 loops=1)

  • Sort Key: (to_char(date_trunc('MONTH'::text, ro1.operation_date), 'YYYY, MONTH'::text)), sr.rpi_type_id, sr.rpi_category_id
  • Sort Method: quicksort Memory: 1,192kB
28. 8.232 791.380 ↓ 10,339.0 10,339 1

Nested Loop (cost=72,180.65..79,945.52 rows=1 width=56) (actual time=522.765..791.380 rows=10,339 loops=1)

29. 13.510 725.756 ↓ 14,348.0 14,348 1

Merge Join (cost=72,180.23..79,939.44 rows=1 width=32) (actual time=519.581..725.756 rows=14,348 loops=1)

  • Merge Cond: (ro1.rpi_id = ro2.rpi_id)
  • Join Filter: (ro1.operation_date < ro2.operation_date)
30. 18.423 667.605 ↓ 159.0 171,933 1

Subquery Scan on ro1 (cost=52,821.97..59,849.64 rows=1,081 width=16) (actual time=491.841..667.605 rows=171,933 loops=1)

  • Filter: (ro1.rn = 1)
  • Rows Removed by Filter: 48,993
31. 122.748 649.182 ↓ 1.0 220,926 1

WindowAgg (cost=52,821.97..57,146.69 rows=216,236 width=56) (actual time=491.836..649.182 rows=220,926 loops=1)

32. 155.664 526.434 ↓ 1.0 220,926 1

Sort (cost=52,821.97..53,362.56 rows=216,236 width=16) (actual time=491.826..526.434 rows=220,926 loops=1)

  • Sort Key: rpi_operation.rpi_id, rpi_operation.operation_date
  • Sort Method: external merge Disk: 5,608kB
33. 296.383 370.770 ↓ 1.0 220,926 1

Bitmap Heap Scan on rpi_operation (cost=8,635.45..29,965.03 rows=216,236 width=16) (actual time=76.583..370.770 rows=220,926 loops=1)

  • Recheck Cond: (operation_type = 'RPO_SORTING'::text)
  • Filter: ((operation_date >= '2018-02-01 00:00:00+03'::timestamp with time zone) AND (operation_date <= '2018-07-28 00:00:00+03'::timestamp with time zone))
  • Rows Removed by Filter: 89,736
  • Heap Blocks: exact=13,827
34. 74.387 74.387 ↓ 1.0 310,662 1

Bitmap Index Scan on idx_rpi_operation_type (cost=0.00..8,581.39 rows=307,862 width=0) (actual time=74.386..74.387 rows=310,662 loops=1)

  • Index Cond: (operation_type = 'RPO_SORTING'::text)
35. 1.841 44.641 ↓ 128.1 14,349 1

Materialize (cost=19,358.25..20,086.79 rows=112 width=16) (actual time=27.502..44.641 rows=14,349 loops=1)

36. 1.534 42.800 ↓ 128.1 14,349 1

Subquery Scan on ro2 (cost=19,358.25..20,086.51 rows=112 width=16) (actual time=27.500..42.800 rows=14,349 loops=1)

  • Filter: (ro2.rn = 1)
37. 12.068 41.266 ↑ 1.6 14,349 1

WindowAgg (cost=19,358.25..19,806.41 rows=22,408 width=56) (actual time=27.496..41.266 rows=14,349 loops=1)

38. 6.688 29.198 ↑ 1.6 14,349 1

Sort (cost=19,358.25..19,414.27 rows=22,408 width=16) (actual time=27.489..29.198 rows=14,349 loops=1)

  • Sort Key: rpi_operation_1.rpi_id, rpi_operation_1.operation_date
  • Sort Method: quicksort Memory: 1,736kB
39. 16.610 22.510 ↑ 1.1 20,634 1

Bitmap Heap Scan on rpi_operation rpi_operation_1 (cost=635.56..17,739.08 rows=22,408 width=16) (actual time=6.120..22.510 rows=20,634 loops=1)

  • Recheck Cond: (operation_type = ANY ('{RPI_IS_SENT,RPI_IS_SENT_INSIDE_CONTAINER}'::text[]))
  • Filter: (operation_date >= '2018-02-01 00:00:00+03'::timestamp with time zone)
  • Rows Removed by Filter: 1
  • Heap Blocks: exact=1,938
40. 5.900 5.900 ↑ 1.1 20,635 1

Bitmap Index Scan on idx_rpi_operation_type (cost=0.00..629.96 rows=22,549 width=0) (actual time=5.900..5.900 rows=20,635 loops=1)

  • Index Cond: (operation_type = ANY ('{RPI_IS_SENT,RPI_IS_SENT_INSIDE_CONTAINER}'::text[]))
41. 57.392 57.392 ↑ 1.0 1 14,348

Index Scan using session_rpi_pkey on session_rpi sr (cost=0.42..6.07 rows=1 width=16) (actual time=0.004..0.004 rows=1 loops=14,348)

  • Index Cond: (id = ro1.rpi_id)
42.          

SubPlan (for GroupAggregate)

43. 0.124 0.124 ↑ 1.0 1 62

Index Scan using pk_dict on dict (cost=0.28..8.29 rows=1 width=31) (actual time=0.002..0.002 rows=1 loops=62)

  • Index Cond: ((dict_class_id = 12) AND (code = sr.rpi_type_id))
44. 0.062 0.062 ↑ 1.0 1 62

Index Scan using pk_dict on dict dict_1 (cost=0.28..8.29 rows=1 width=31) (actual time=0.001..0.001 rows=1 loops=62)

  • Index Cond: ((dict_class_id = 9) AND (code = sr.rpi_category_id))
Planning time : 2.750 ms
Execution time : 974.604 ms