explain.depesz.com

PostgreSQL's explain analyze made readable

Result: 0tlF

Settings
# exclusive inclusive rows x rows loops node
1. 0.150 3,011.372 ↑ 24.6 67 1

Sort (cost=1,305,026.50..1,305,030.61 rows=1,647 width=148) (actual time=3,011.362..3,011.372 rows=67 loops=1)

  • Sort Key: (to_char(date_trunc('MONTH'::text, co1.operation_date), 'YYYY, MONTH'::text)), ((SubPlan 3)), ((SubPlan 4)), ((SubPlan 5)), (avg((date_part('epoch'::text, (co2.operation_date - co1.operation_date)) / '3600'::double precision)))
  • Sort Method: quicksort Memory: 34kB
2.          

CTE container_sorting

3. 36.594 1,074.670 ↓ 214.8 253,840 1

Subquery Scan on t (cost=695,059.23..703,330.96 rows=1,182 width=42) (actual time=855.214..1,074.670 rows=253,840 loops=1)

  • Filter: (t.rn = 1)
  • Rows Removed by Filter: 49,783
4. 139.421 1,038.076 ↓ 1.3 303,623 1

WindowAgg (cost=695,059.23..700,376.77 rows=236,335 width=50) (actual time=855.200..1,038.076 rows=303,623 loops=1)

5. 151.492 898.655 ↓ 1.3 303,623 1

Sort (cost=695,059.23..695,650.07 rows=236,335 width=42) (actual time=855.189..898.655 rows=303,623 loops=1)

  • Sort Key: container_operation.container_id, container_operation.operation_date
  • Sort Method: quicksort Memory: 36,009kB
6. 103.715 747.163 ↓ 1.3 303,623 1

Bitmap Heap Scan on container_operation (cost=176,205.08..673,965.77 rows=236,335 width=42) (actual time=648.612..747.163 rows=303,623 loops=1)

  • Recheck Cond: ((operation_date >= '2019-02-01 00:00:00+03'::timestamp with time zone) AND (operation_date <= '2019-02-15 00:00:00+03'::timestamp with time zone) AND (operation_type = 'CONTAINER_SORTING'::text))
  • Heap Blocks: exact=29,734
7. 14.376 643.448 ↓ 0.0 0 1

BitmapAnd (cost=176,205.08..176,205.08 rows=236,335 width=0) (actual time=643.448..643.448 rows=0 loops=1)

8. 130.719 130.719 ↑ 1.0 1,767,462 1

Bitmap Index Scan on idx_container_operation_operation_date (cost=0.00..38,331.28 rows=1,768,272 width=0) (actual time=130.719..130.719 rows=1,767,462 loops=1)

  • Index Cond: ((operation_date >= '2019-02-01 00:00:00+03'::timestamp with time zone) AND (operation_date <= '2019-02-15 00:00:00+03'::timestamp with time zone))
9. 498.353 498.353 ↓ 1.0 4,265,835 1

Bitmap Index Scan on idx_container_operation_type (cost=0.00..137,755.38 rows=4,214,242 width=0) (actual time=498.353..498.353 rows=4,265,835 loops=1)

  • Index Cond: (operation_type = 'CONTAINER_SORTING'::text)
10.          

CTE container_sent

11. 36.160 797.277 ↓ 236.4 197,638 1

Subquery Scan on t_1 (cost=547,387.89..553,240.98 rows=836 width=42) (actual time=562.339..797.277 rows=197,638 loops=1)

  • Filter: (t_1.rn = 1)
  • Rows Removed by Filter: 43,572
12. 145.445 761.117 ↓ 1.4 241,210 1

WindowAgg (cost=547,387.89..551,150.59 rows=167,231 width=50) (actual time=562.325..761.117 rows=241,210 loops=1)

13. 138.397 615.672 ↓ 1.4 241,210 1

Sort (cost=547,387.89..547,805.97 rows=167,231 width=42) (actual time=562.313..615.672 rows=241,210 loops=1)

  • Sort Key: container_operation_1.container_id, container_operation_1.operation_type, container_operation_1.operation_date
  • Sort Method: quicksort Memory: 35,262kB
14. 55.851 477.275 ↓ 1.4 241,210 1

Bitmap Heap Scan on container_operation container_operation_1 (cost=132,505.30..532,879.36 rows=167,231 width=42) (actual time=423.279..477.275 rows=241,210 loops=1)

  • Recheck Cond: ((operation_date >= '2019-02-01 00:00:00+03'::timestamp with time zone) AND (operation_date <= '2019-02-15 00:00:00+03'::timestamp with time zone) AND (operation_type = ANY ('{CONTAINER_IS_SENT,CONTAINER_IS_SENT_INSIDE_CONTAINER}'::text[])))
  • Heap Blocks: exact=11,305
15. 13.388 421.424 ↓ 0.0 0 1

BitmapAnd (cost=132,505.30..132,505.30 rows=167,231 width=0) (actual time=421.424..421.424 rows=0 loops=1)

16. 130.806 130.806 ↑ 1.0 1,767,462 1

Bitmap Index Scan on idx_container_operation_operation_date (cost=0.00..38,331.28 rows=1,768,272 width=0) (actual time=130.806..130.806 rows=1,767,462 loops=1)

  • Index Cond: ((operation_date >= '2019-02-01 00:00:00+03'::timestamp with time zone) AND (operation_date <= '2019-02-15 00:00:00+03'::timestamp with time zone))
17. 277.230 277.230 ↑ 1.0 2,981,098 1

Bitmap Index Scan on idx_container_operation_type (cost=0.00..94,090.15 rows=2,982,005 width=0) (actual time=277.230..277.230 rows=2,981,098 loops=1)

  • Index Cond: (operation_type = ANY ('{CONTAINER_IS_SENT,CONTAINER_IS_SENT_INSIDE_CONTAINER}'::text[]))
18. 74.025 3,011.222 ↑ 24.6 67 1

GroupAggregate (cost=7,315.09..48,366.56 rows=1,647 width=148) (actual time=2,914.273..3,011.222 rows=67 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
19. 121.021 2,936.862 ↓ 107.5 177,103 1

Sort (cost=7,315.09..7,319.21 rows=1,647 width=60) (actual time=2,914.042..2,936.862 rows=177,103 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: 19,981kB
20. 289.086 2,815.841 ↓ 107.5 177,103 1

Hash Join (cost=38.85..7,227.09 rows=1,647 width=60) (actual time=1,750.579..2,815.841 rows=177,103 loops=1)

  • Hash Cond: (co2.container_id = co1.container_id)
  • Join Filter: (co1.operation_date < co2.operation_date)
  • Rows Removed by Join Filter: 7
21. 80.290 1,338.657 ↓ 236.4 197,638 1

Nested Loop (cost=0.43..6,993.28 rows=836 width=36) (actual time=562.368..1,338.657 rows=197,638 loops=1)

22. 863.091 863.091 ↓ 236.4 197,638 1

CTE Scan on container_sent co2 (cost=0.00..16.72 rows=836 width=16) (actual time=562.342..863.091 rows=197,638 loops=1)

23. 395.276 395.276 ↑ 1.0 1 197,638

Index Scan using session_container_pkey on session_container sc (cost=0.43..8.34 rows=1 width=20) (actual time=0.002..0.002 rows=1 loops=197,638)

  • Index Cond: (id = co2.container_id)
24. 40.614 1,188.098 ↓ 214.8 253,840 1

Hash (cost=23.64..23.64 rows=1,182 width=16) (actual time=1,188.098..1,188.098 rows=253,840 loops=1)

  • Buckets: 262,144 (originally 2048) Batches: 1 (originally 1) Memory Usage: 13,947kB
25. 1,147.484 1,147.484 ↓ 214.8 253,840 1

CTE Scan on container_sorting co1 (cost=0.00..23.64 rows=1,182 width=16) (actual time=855.217..1,147.484 rows=253,840 loops=1)

26.          

SubPlan (for GroupAggregate)

27. 0.201 0.201 ↑ 1.0 1 67

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

  • Index Cond: ((dict_class_id = 2) AND (code = sc.container_type_id))
28. 0.067 0.067 ↑ 1.0 1 67

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

  • Index Cond: ((dict_class_id = 3) AND (code = sc.container_category_id))
29. 0.067 0.067 ↑ 1.0 1 67

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

  • Index Cond: ((dict_class_id = 4) AND (code = sc.class_id))
Planning time : 0.758 ms
Execution time : 3,024.545 ms