explain.depesz.com

PostgreSQL's explain analyze made readable

Result: vKVj

Settings
# exclusive inclusive rows x rows loops node
1. 0.029 1,871.158 ↓ 102.0 204 1

Unique (cost=113,450.76..113,450.79 rows=2 width=132) (actual time=1,871.121..1,871.158 rows=204 loops=1)

2. 0.626 1,871.129 ↓ 102.0 204 1

Sort (cost=113,450.76..113,450.76 rows=2 width=132) (actual time=1,871.120..1,871.129 rows=204 loops=1)

  • Sort Key: "*SELECT* 1"."Месяц", "*SELECT* 1"."Вид", "*SELECT* 1"."Категория", "*SELECT* 1"."Класс", "*SELECT* 1"."Часы
  • Sort Method: quicksort Memory: 56kB
3. 0.024 1,870.503 ↓ 102.0 204 1

Append (cost=20,627.17..113,450.75 rows=2 width=132) (actual time=699.583..1,870.503 rows=204 loops=1)

4. 0.020 712.678 ↓ 104.0 104 1

Subquery Scan on *SELECT* 1 (cost=20,627.17..20,652.11 rows=1 width=132) (actual time=699.582..712.678 rows=104 loops=1)

5. 10.346 712.658 ↓ 104.0 104 1

GroupAggregate (cost=20,627.17..20,652.10 rows=1 width=144) (actual time=699.581..712.658 rows=104 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. 50.221 701.480 ↓ 10,404.0 10,404 1

Sort (cost=20,627.17..20,627.17 rows=1 width=60) (actual time=699.495..701.480 rows=10,404 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: 1,197kB
7. 56.035 651.259 ↓ 10,404.0 10,404 1

Nested Loop (cost=17,924.52..20,627.16 rows=1 width=60) (actual time=339.909..651.259 rows=10,404 loops=1)

8. 19.894 544.620 ↓ 16,868.0 16,868 1

Merge Join (cost=17,924.23..20,622.05 rows=1 width=32) (actual time=339.871..544.620 rows=16,868 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. 10.031 437.943 ↓ 95.9 29,820 1

Subquery Scan on co1 (cost=11,672.49..13,697.17 rows=311 width=16) (actual time=312.364..437.943 rows=29,820 loops=1)

  • Filter: (co1.rn = 1)
  • Rows Removed by Filter: 32,495
10. 93.613 427.912 ↓ 1.0 62,315 1

WindowAgg (cost=11,672.49..12,918.45 rows=62,298 width=56) (actual time=312.357..427.912 rows=62,315 loops=1)

11. 272.281 334.299 ↓ 1.0 62,315 1

Sort (cost=11,672.49..11,828.23 rows=62,298 width=16) (actual time=312.345..334.299 rows=62,315 loops=1)

  • Sort Key: container_operation.container_id, container_operation.operation_date
  • Sort Method: external merge Disk: 1,584kB
12. 40.551 62.018 ↓ 1.0 62,444 1

Bitmap Heap Scan on container_operation (cost=1,720.12..6,711.42 rows=62,298 width=16) (actual time=23.371..62.018 rows=62,444 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-12-28 00:00:00+03'::timestamp with time zone))
  • Heap Blocks: exact=3,869
13. 21.467 21.467 ↓ 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=21.467..21.467 rows=62,445 loops=1)

  • Index Cond: (operation_type = 'CONTAINER_SORTING'::text)
14. 7.373 86.783 ↓ 200.0 20,603 1

Materialize (cost=6,251.74..6,923.80 rows=103 width=16) (actual time=27.399..86.783 rows=20,603 loops=1)

15. 5.142 79.410 ↓ 200.0 20,603 1

Subquery Scan on co2 (cost=6,251.74..6,923.55 rows=103 width=16) (actual time=27.394..79.410 rows=20,603 loops=1)

  • Filter: (co2.rn = 1)
  • Rows Removed by Filter: 6
16. 41.363 74.268 ↑ 1.0 20,609 1

WindowAgg (cost=6,251.74..6,665.16 rows=20,671 width=56) (actual time=27.388..74.268 rows=20,609 loops=1)

17. 20.496 32.905 ↑ 1.0 20,609 1

Sort (cost=6,251.74..6,303.42 rows=20,671 width=16) (actual time=27.377..32.905 rows=20,609 loops=1)

  • Sort Key: container_operation_1.container_id, container_operation_1.operation_date
  • Sort Method: quicksort Memory: 1,735kB
18. 10.124 12.409 ↑ 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=2.723..12.409 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. 2.285 2.285 ↑ 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=2.285..2.285 rows=20,610 loops=1)

  • Index Cond: (operation_type = ANY ('{CONTAINER_IS_SENT,CONTAINER_IS_SENT_INSIDE_CONTAINER}'::text[]))
20. 50.604 50.604 ↑ 1.0 1 16,868

Index Scan using session_container_pkey on session_container sc (cost=0.29..5.09 rows=1 width=20) (actual time=0.003..0.003 rows=1 loops=16,868)

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

SubPlan (for GroupAggregate)

22. 0.416 0.416 ↑ 1.0 1 104

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

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

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

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

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

  • Index Cond: ((dict_class_id = 4) AND (code = sc.class_id))
25. 0.010 1,157.801 ↓ 100.0 100 1

Subquery Scan on *SELECT* 2 (cost=92,782.00..92,798.64 rows=1 width=132) (actual time=1,151.749..1,157.801 rows=100 loops=1)

26. 4.861 1,157.791 ↓ 100.0 100 1

GroupAggregate (cost=92,782.00..92,798.63 rows=1 width=140) (actual time=1,151.748..1,157.791 rows=100 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. 16.691 1,152.730 ↓ 16,238.0 16,238 1

Sort (cost=92,782.00..92,782.00 rows=1 width=56) (actual time=1,151.708..1,152.730 rows=16,238 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,653kB
28. 28.966 1,136.039 ↓ 16,238.0 16,238 1

Nested Loop (cost=82,178.43..92,781.99 rows=1 width=56) (actual time=761.738..1,136.039 rows=16,238 loops=1)

29. 20.101 1,045.177 ↓ 20,632.0 20,632 1

Merge Join (cost=82,178.01..92,776.61 rows=1 width=32) (actual time=756.005..1,045.177 rows=20,632 loops=1)

  • Merge Cond: (ro1.rpi_id = ro2.rpi_id)
  • Join Filter: (ro1.operation_date < ro2.operation_date)
30. 24.980 960.494 ↓ 157.7 239,444 1

Subquery Scan on ro1 (cost=62,819.75..72,685.71 rows=1,518 width=16) (actual time=718.117..960.494 rows=239,444 loops=1)

  • Filter: (ro1.rn = 1)
  • Rows Removed by Filter: 69,767
31. 169.663 935.514 ↓ 1.0 309,211 1

WindowAgg (cost=62,819.75..68,891.11 rows=303,568 width=56) (actual time=718.111..935.514 rows=309,211 loops=1)

32. 393.634 765.851 ↓ 1.0 309,211 1

Sort (cost=62,819.75..63,578.67 rows=303,568 width=16) (actual time=718.102..765.851 rows=309,211 loops=1)

  • Sort Key: rpi_operation.rpi_id, rpi_operation.operation_date
  • Sort Method: external merge Disk: 7,888kB
33. 320.967 372.217 ↓ 1.0 310,659 1

Bitmap Heap Scan on rpi_operation (cost=8,657.28..29,986.87 rows=303,568 width=16) (actual time=54.347..372.217 rows=310,659 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-12-28 00:00:00+03'::timestamp with time zone))
  • Rows Removed by Filter: 3
  • Heap Blocks: exact=13,827
34. 51.250 51.250 ↓ 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=51.250..51.250 rows=310,662 loops=1)

  • Index Cond: (operation_type = 'RPO_SORTING'::text)
35. 2.961 64.582 ↓ 184.2 20,632 1

Materialize (cost=19,358.25..20,086.79 rows=112 width=16) (actual time=37.695..64.582 rows=20,632 loops=1)

36. 2.400 61.621 ↓ 184.2 20,632 1

Subquery Scan on ro2 (cost=19,358.25..20,086.51 rows=112 width=16) (actual time=37.692..61.621 rows=20,632 loops=1)

  • Filter: (ro2.rn = 1)
  • Rows Removed by Filter: 2
37. 18.687 59.221 ↑ 1.1 20,634 1

WindowAgg (cost=19,358.25..19,806.41 rows=22,408 width=56) (actual time=37.686..59.221 rows=20,634 loops=1)

38. 11.517 40.534 ↑ 1.1 20,634 1

Sort (cost=19,358.25..19,414.27 rows=22,408 width=16) (actual time=37.675..40.534 rows=20,634 loops=1)

  • Sort Key: rpi_operation_1.rpi_id, rpi_operation_1.operation_date
  • Sort Method: quicksort Memory: 1,736kB
39. 25.358 29.017 ↑ 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=4.011..29.017 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. 3.659 3.659 ↑ 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=3.659..3.659 rows=20,635 loops=1)

  • Index Cond: (operation_type = ANY ('{RPI_IS_SENT,RPI_IS_SENT_INSIDE_CONTAINER}'::text[]))
41. 61.896 61.896 ↑ 1.0 1 20,632

Index Scan using session_rpi_pkey on session_rpi sr (cost=0.42..5.37 rows=1 width=16) (actual time=0.003..0.003 rows=1 loops=20,632)

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

SubPlan (for GroupAggregate)

43. 0.100 0.100 ↑ 1.0 1 100

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

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

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=100)

  • Index Cond: ((dict_class_id = 9) AND (code = sr.rpi_category_id))
Planning time : 4.213 ms
Execution time : 1,890.536 ms