explain.depesz.com

PostgreSQL's explain analyze made readable

Result: 6GXu

Settings
# exclusive inclusive rows x rows loops node
1. 1.181 329,696.520 ↓ 10.0 10 1

GroupAggregate (cost=32,791.82..32,808.45 rows=1 width=144) (actual time=329,695.100..329,696.520 rows=10 loops=1)

  • Group Key: (to_char(date_trunc('MONTH'::text, ro1.operation_date), 'YYYY, MONTH'::text)), sr.rpi_type_id, sr.rpi_category_id
2.          

CTE rpi_operations

3. 32.907 188.903 ↑ 1.0 34,677 1

WindowAgg (cost=30,214.22..31,011.53 rows=35,436 width=419) (actual time=143.259..188.903 rows=34,677 loops=1)

4. 82.556 155.996 ↑ 1.0 34,677 1

Sort (cost=30,214.22..30,302.81 rows=35,436 width=411) (actual time=143.246..155.996 rows=34,677 loops=1)

  • Sort Key: rpi_operation.rpi_id, rpi_operation.operation_type, rpi_operation.operation_date
  • Sort Method: external sort Disk: 3,912kB
5. 58.870 73.440 ↑ 1.0 34,677 1

Bitmap Heap Scan on rpi_operation (cost=1,896.32..20,872.51 rows=35,436 width=411) (actual time=15.044..73.440 rows=34,677 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 ('{RPO_SORTING,RPI_IS_SENT,RPI_IS_SENT_INSIDE_CONTAINER}'::text[]))
  • Rows Removed by Filter: 51,992
  • Heap Blocks: exact=1,935
6. 14.570 14.570 ↑ 1.0 86,670 1

Bitmap Index Scan on idx_rpi_operation_operation_date (cost=0.00..1,887.46 rows=89,902 width=0) (actual time=14.570..14.570 rows=86,670 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. 6.072 329,695.219 ↓ 2,661.0 2,661 1

Sort (cost=1,780.29..1,780.30 rows=1 width=56) (actual time=329,695.040..329,695.219 rows=2,661 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: 304kB
8. 40.747 329,689.147 ↓ 2,661.0 2,661 1

Nested Loop (cost=0.42..1,780.28 rows=1 width=56) (actual time=252.962..329,689.147 rows=2,661 loops=1)

9. 6,618.823 329,584.536 ↓ 2,661.0 2,661 1

Nested Loop (cost=0.00..1,771.83 rows=1 width=32) (actual time=252.928..329,584.536 rows=2,661 loops=1)

  • Join Filter: ((ro1.operation_date < ro2.operation_date) AND (ro1.rpi_id = ro2.rpi_id))
  • Rows Removed by Join Filter: 67,793,520
10. 575.561 575.561 ↓ 24,291.0 24,291 1

CTE Scan on rpi_operations ro1 (cost=0.00..885.90 rows=1 width=16) (actual time=143.267..575.561 rows=24,291 loops=1)

  • Filter: ((operation_type = 'RPO_SORTING'::text) AND (rn = 1))
  • Rows Removed by Filter: 10,386
11. 322,390.152 322,390.152 ↓ 1,395.5 2,791 24,291

CTE Scan on rpi_operations ro2 (cost=0.00..885.90 rows=2 width=16) (actual time=0.015..13.272 rows=2,791 loops=24,291)

  • Filter: ((operation_type = ANY ('{RPI_IS_SENT,RPI_IS_SENT_INSIDE_CONTAINER}'::text[])) AND (rn = 1))
  • Rows Removed by Filter: 31,886
12. 63.864 63.864 ↑ 1.0 1 2,661

Index Scan using session_rpi_pkey on session_rpi sr (cost=0.42..8.44 rows=1 width=16) (actual time=0.023..0.024 rows=1 loops=2,661)

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

SubPlan (for GroupAggregate)

14. 0.090 0.090 ↑ 1.0 1 10

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

  • Index Cond: ((dict_class_id = 12) AND (code = sr.rpi_type_id))
15. 0.030 0.030 ↑ 1.0 1 10

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

  • Index Cond: ((dict_class_id = 9) AND (code = sr.rpi_category_id))
Planning time : 1.750 ms
Execution time : 329,714.425 ms