explain.depesz.com

PostgreSQL's explain analyze made readable

Result: amslM

Settings
# exclusive inclusive rows x rows loops node
1. 0.055 10,710.137 ↓ 4.0 4 1

GroupAggregate (cost=8,844.90..8,869.83 rows=1 width=148) (actual time=10,710.086..10,710.137 rows=4 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
2.          

CTE container_operations

3. 10.143 47.199 ↓ 1.1 9,714 1

WindowAgg (cost=8,168.40..8,375.76 rows=9,216 width=582) (actual time=35.153..47.199 rows=9,714 loops=1)

4. 13.698 37.056 ↓ 1.1 9,714 1

Sort (cost=8,168.40..8,191.44 rows=9,216 width=574) (actual time=35.144..37.056 rows=9,714 loops=1)

  • Sort Key: container_operation.container_id, container_operation.operation_type, container_operation.operation_date
  • Sort Method: quicksort Memory: 2,913kB
5. 16.312 23.358 ↓ 1.1 9,714 1

Bitmap Heap Scan on container_operation (cost=502.92..5,199.03 rows=9,216 width=574) (actual time=7.379..23.358 rows=9,714 loops=1)

  • Recheck Cond: ((operation_date >= date_trunc('MONTH'::text, ('2018-05-27'::date)::timestamp with time zone)) AND (operation_date <= ((date_trunc('month'::text, ('2018-05-28'::date)::timestamp with time zone) + '1 mon'::interval) - '00:00:01'::interval)))
  • Filter: (operation_type = ANY ('{CONTAINER_SORTING,CONTAINER_IS_SENT,CONTAINER_IS_SENT_INSIDE_CONTAINER}'::text[]))
  • Rows Removed by Filter: 14,264
  • Heap Blocks: exact=818
6. 7.046 7.046 ↓ 1.0 23,978 1

Bitmap Index Scan on idx_container_operation_operation_date (cost=0.00..500.62 rows=23,618 width=0) (actual time=7.046..7.046 rows=23,978 loops=1)

  • Index Cond: ((operation_date >= date_trunc('MONTH'::text, ('2018-05-27'::date)::timestamp with time zone)) AND (operation_date <= ((date_trunc('month'::text, ('2018-05-28'::date)::timestamp with time zone) + '1 mon'::interval) - '00:00:01'::interval)))
7. 0.082 10,710.026 ↓ 23.0 23 1

Sort (cost=469.14..469.15 rows=1 width=60) (actual time=10,710.024..10,710.026 rows=23 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: 26kB
8. 4.762 10,709.944 ↓ 23.0 23 1

Nested Loop (cost=0.29..469.13 rows=1 width=60) (actual time=2,540.307..10,709.944 rows=23 loops=1)

9. 844.973 10,685.582 ↓ 1,960.0 1,960 1

Nested Loop (cost=0.00..460.82 rows=1 width=32) (actual time=251.143..10,685.582 rows=1,960 loops=1)

  • Join Filter: ((co1.operation_date < co2.operation_date) AND (co1.container_id = co2.container_id))
  • Rows Removed by Join Filter: 9,037,640
10. 42.849 42.849 ↓ 3,645.0 3,645 1

CTE Scan on container_operations co1 (cost=0.00..230.40 rows=1 width=16) (actual time=35.212..42.849 rows=3,645 loops=1)

  • Filter: ((operation_type = 'CONTAINER_SORTING'::text) AND (rn = 1))
  • Rows Removed by Filter: 6,069
11. 9,797.760 9,797.760 ↓ 2,480.0 2,480 3,645

CTE Scan on container_operations co2 (cost=0.00..230.40 rows=1 width=16) (actual time=0.001..2.688 rows=2,480 loops=3,645)

  • Filter: ((operation_type = ANY ('{CONTAINER_IS_SENT,CONTAINER_IS_SENT_INSIDE_CONTAINER}'::text[])) AND (rn = 1))
  • Rows Removed by Filter: 7,234
12. 19.600 19.600 ↓ 0.0 0 1,960

Index Scan using session_container_pkey on session_container sc (cost=0.29..8.30 rows=1 width=20) (actual time=0.010..0.010 rows=0 loops=1,960)

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

SubPlan (for GroupAggregate)

14. 0.032 0.032 ↑ 1.0 1 4

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

  • Index Cond: ((dict_class_id = 2) AND (code = sc.container_type_id))
15. 0.012 0.012 ↑ 1.0 1 4

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

  • Index Cond: ((dict_class_id = 3) AND (code = sc.container_category_id))
16. 0.012 0.012 ↑ 1.0 1 4

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

  • Index Cond: ((dict_class_id = 4) AND (code = sc.class_id))
Planning time : 2.432 ms
Execution time : 10,712.792 ms