explain.depesz.com

PostgreSQL's explain analyze made readable

Result: jIqk

Settings
# exclusive inclusive rows x rows loops node
1. 0.033 4,139.741 ↓ 4.0 4 1

GroupAggregate (cost=8,501.39..8,526.31 rows=1 width=148) (actual time=4,139.721..4,139.741 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. 1.149 38.430 ↓ 133.2 6,125 1

Subquery Scan on t (cost=8,168.40..8,490.96 rows=46 width=582) (actual time=25.384..38.430 rows=6,125 loops=1)

  • Filter: (t.rn = 1)
  • Rows Removed by Filter: 3,589
4. 9.988 37.281 ↓ 1.1 9,714 1

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

5. 15.874 27.293 ↓ 1.1 9,714 1

Sort (cost=8,168.40..8,191.44 rows=9,216 width=574) (actual time=25.364..27.293 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
6. 8.111 11.419 ↓ 1.1 9,714 1

Bitmap Heap Scan on container_operation (cost=502.92..5,199.03 rows=9,216 width=574) (actual time=3.466..11.419 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
7. 3.308 3.308 ↓ 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=3.308..3.308 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)))
8. 0.055 4,139.672 ↓ 23.0 23 1

Sort (cost=10.43..10.44 rows=1 width=60) (actual time=4,139.671..4,139.672 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
9. 3.050 4,139.617 ↓ 23.0 23 1

Nested Loop (cost=0.29..10.42 rows=1 width=60) (actual time=1,185.684..4,139.617 rows=23 loops=1)

10. 725.390 4,128.727 ↓ 1,960.0 1,960 1

Nested Loop (cost=0.00..2.10 rows=1 width=32) (actual time=96.441..4,128.727 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
11. 28.067 28.067 ↓ 3,645.0 3,645 1

CTE Scan on container_operations co1 (cost=0.00..1.03 rows=1 width=16) (actual time=25.628..28.067 rows=3,645 loops=1)

  • Filter: (operation_type = 'CONTAINER_SORTING'::text)
  • Rows Removed by Filter: 2,480
12. 3,375.270 3,375.270 ↓ 1,240.0 2,480 3,645

CTE Scan on container_operations co2 (cost=0.00..1.03 rows=2 width=16) (actual time=0.000..0.926 rows=2,480 loops=3,645)

  • Filter: (operation_type = ANY ('{CONTAINER_IS_SENT,CONTAINER_IS_SENT_INSIDE_CONTAINER}'::text[]))
  • Rows Removed by Filter: 3,645
13. 7.840 7.840 ↓ 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.004..0.004 rows=0 loops=1,960)

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

SubPlan (for GroupAggregate)

15. 0.020 0.020 ↑ 1.0 1 4

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

  • Index Cond: ((dict_class_id = 2) AND (code = sc.container_type_id))
16. 0.004 0.004 ↑ 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.001..0.001 rows=1 loops=4)

  • Index Cond: ((dict_class_id = 3) AND (code = sc.container_category_id))
17. 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.002..0.003 rows=1 loops=4)

  • Index Cond: ((dict_class_id = 4) AND (code = sc.class_id))
Planning time : 2.066 ms
Execution time : 4,141.110 ms