explain.depesz.com

PostgreSQL's explain analyze made readable

Result: MnPM

Settings
# exclusive inclusive rows x rows loops node
1. 1,783.253 51,533.476 ↓ 49.3 1,437,253 1

Sort (cost=1,710,263.80..1,710,336.75 rows=29,178 width=178) (actual time=51,099.600..51,533.476 rows=1,437,253 loops=1)

  • Sort Key: last_operation.operation_date DESC
  • Sort Method: external merge Disk: 233,376kB
2.          

CTE filtered_containers

3. 0.000 2,503.220 ↓ 7.7 1,437,253 1

Gather (cost=1,000.43..433,347.82 rows=187,242 width=32) (actual time=0.452..2,503.220 rows=1,437,253 loops=1)

  • Workers Planned: 2
  • Workers Launched: 2
4. 349.812 3,012.281 ↓ 6.1 479,084 3 / 3

Nested Loop Semi Join (cost=0.43..413,623.62 rows=78,018 width=32) (actual time=0.077..3,012.281 rows=479,084 loops=3)

5. 167.978 167.978 ↑ 1.3 623,623 3 / 3

Parallel Seq Scan on container c (cost=0.00..43,597.30 rows=779,530 width=32) (actual time=0.019..167.978 rows=623,623 loops=3)

6. 2,494.491 2,494.491 ↑ 62.0 1 1,870,868 / 3

Index Scan using idx_container_operation_container_id on container_operation co (cost=0.43..2.57 rows=62 width=8) (actual time=0.004..0.004 rows=1 loops=1,870,868)

  • Index Cond: (container_id = c.id)
  • Filter: ((operation_date >= '2018-02-01 00:00:00+03'::timestamp with time zone) AND (operation_date <= '2020-02-01 00:00:00+03'::timestamp with time zone))
  • Rows Removed by Filter: 0
7. 2,242.843 49,750.223 ↓ 49.3 1,437,253 1

Merge Join (cost=806,746.80..1,274,752.05 rows=29,178 width=178) (actual time=31,999.784..49,750.223 rows=1,437,253 loops=1)

  • Merge Cond: (last_operation.container_id = fc.id)
8. 884.544 40,339.655 ↓ 49.3 1,437,253 1

Subquery Scan on last_operation (cost=786,604.66..976,263.87 rows=29,178 width=24) (actual time=30,859.394..40,339.655 rows=1,437,253 loops=1)

  • Filter: (last_operation.rn = 1)
  • Rows Removed by Filter: 10,221,480
9. 6,458.793 39,455.111 ↓ 2.0 11,658,733 1

WindowAgg (cost=786,604.66..903,318.02 rows=5,835,668 width=32) (actual time=30,859.392..39,455.111 rows=11,658,733 loops=1)

10. 9,418.139 32,996.318 ↓ 2.0 11,658,733 1

Sort (cost=786,604.66..801,193.83 rows=5,835,668 width=24) (actual time=30,859.379..32,996.318 rows=11,658,733 loops=1)

  • Sort Key: container_operation.container_id, container_operation.operation_date DESC
  • Sort Method: external merge Disk: 386,184kB
11. 2,186.912 23,578.179 ↓ 2.0 11,658,733 1

Nested Loop (cost=4,213.38..11,099.69 rows=5,835,668 width=24) (actual time=3,699.641..23,578.179 rows=11,658,733 loops=1)

12. 1,078.798 4,144.231 ↓ 7,186.3 1,437,253 1

HashAggregate (cost=4,212.94..4,214.94 rows=200 width=8) (actual time=3,699.627..4,144.231 rows=1,437,253 loops=1)

  • Group Key: filtered_containers.id
13. 3,065.433 3,065.433 ↓ 7.7 1,437,253 1

CTE Scan on filtered_containers (cost=0.00..3,744.84 rows=187,242 width=8) (actual time=0.455..3,065.433 rows=1,437,253 loops=1)

14. 17,247.036 17,247.036 ↑ 7.8 8 1,437,253

Index Scan using idx_container_operation_container_id on container_operation (cost=0.43..33.80 rows=62 width=24) (actual time=0.005..0.012 rows=8 loops=1,437,253)

  • Index Cond: (container_id = filtered_containers.id)
15. 1,212.747 1,418.713 ↓ 7.7 1,437,253 1

Sort (cost=20,142.13..20,610.24 rows=187,242 width=74) (actual time=1,140.305..1,418.713 rows=1,437,253 loops=1)

  • Sort Key: fc.id
  • Sort Method: external sort Disk: 63,776kB
16. 205.966 205.966 ↓ 7.7 1,437,253 1

CTE Scan on filtered_containers fc (cost=0.00..3,744.84 rows=187,242 width=74) (actual time=0.017..205.966 rows=1,437,253 loops=1)

17.          

SubPlan (for Merge Join)

18. 2,874.506 2,874.506 ↑ 1.0 1 1,437,253

Seq Scan on site (cost=0.00..1.20 rows=1 width=60) (actual time=0.001..0.002 rows=1 loops=1,437,253)

  • Filter: (id = last_operation.site_id)
  • Rows Removed by Filter: 23
19. 2,874.506 2,874.506 ↑ 1.0 1 1,437,253

Index Scan using operation_user_pkey on operation_user (cost=0.28..8.29 rows=1 width=51) (actual time=0.001..0.002 rows=1 loops=1,437,253)

  • Index Cond: (id = last_operation.user_id)
Planning time : 0.956 ms
Execution time : 51,784.042 ms