explain.depesz.com

PostgreSQL's explain analyze made readable

Result: 761y

Settings
# exclusive inclusive rows x rows loops node
1. 0.112 728,939.521 ↑ 1.0 1 1

Aggregate (cost=142,323.28..142,323.29 rows=1 width=32) (actual time=728,939.521..728,939.521 rows=1 loops=1)

2. 3.163 728,939.409 ↓ 40.0 40 1

Limit (cost=142,323.26..142,323.26 rows=1 width=56) (actual time=728,936.244..728,939.409 rows=40 loops=1)

3. 33.064 728,936.246 ↓ 40.0 40 1

Sort (cost=142,323.26..142,323.26 rows=1 width=56) (actual time=728,936.243..728,936.246 rows=40 loops=1)

  • Sort Key: (COALESCE((min(iv_1.opened_at) OVER (?)), (min(iv.opened_at) OVER (?)))), di.ext_id
  • Sort Method: top-N heapsort Memory: 93kB
4. 1,405.445 728,903.182 ↓ 6,606.0 6,606 1

Nested Loop Left Join (cost=1,001.17..142,323.25 rows=1 width=56) (actual time=102.704..728,903.182 rows=6,606 loops=1)

  • Join Filter: (di.ext_id = iv.atm_id)
  • Rows Removed by Join Filter: 12128613
5. 177.137 177.137 ↓ 6,606.0 6,606 1

Seq Scan on device_info di (cost=0.00..80,698.68 rows=1 width=350) (actual time=0.016..177.137 rows=6,606 loops=1)

  • Filter: (is_monitoring AND (COALESCE(business_statuses, to_jsonb('{}'::uuid[])) ?| '{35b6a966-bafe-4da8-b1cb-7db85e0aea06}'::text[
  • Rows Removed by Filter: 93408
6. 2,972.700 727,280.964 ↑ 1.4 1,836 6,606

Merge Left Join (cost=1,001.17..61,592.99 rows=2,525 width=40) (actual time=19.193..110.094 rows=1,836 loops=6,606)

  • Merge Cond: (iv.atm_id = iv_1.atm_id)
7. 3,177.486 544,433.490 ↑ 1.4 1,836 6,606

Unique (cost=1,000.63..31,803.26 rows=2,525 width=32) (actual time=14.060..82.415 rows=1,836 loops=6,606)

8. 14,268.960 541,256.004 ↓ 1.0 2,776 6,606

WindowAgg (cost=1,000.63..31,796.54 rows=2,686 width=32) (actual time=14.059..81.934 rows=2,776 loops=6,606)

9. 526,987.044 526,987.044 ↓ 1.0 2,776 6,606

Gather Merge (cost=1,000.63..31,749.54 rows=2,686 width=24) (actual time=13.373..79.774 rows=2,776 loops=6,606)

  • Workers Planned: 5
  • Workers Launched: 5
  • -> Parallel Index Only Scan using incident_active_id on incident iv (cost=0.55..30426.00 rows=537 width=24) (a
  • Filter: ((status)::text <> 'CLOSED'::text)
  • Rows Removed by Filter: 65531
  • Heap Fetches: 31962945
10. 39.636 179,874.774 ↓ 1.7 5 6,606

Unique (cost=0.55..29,758.10 rows=3 width=32) (actual time=5.128..27.229 rows=5 loops=6,606)

11. 179,835.138 179,835.138 ↓ 1.7 5 6,606

WindowAgg (cost=0.55..29,758.09 rows=3 width=32) (actual time=5.128..27.223 rows=5 loops=6,606)

  • -> Index Only Scan using incident_active_id on incident iv_1 (cost=0.55..29758.04 rows=3 width=24) (actual time=1.13
  • Index Cond: (status = 'WAITING_MANUAL'::text)
  • Heap Fetches: 106354
12.          

SubPlan (for Nested Loop Left Join)

13. 39.636 39.636 ↑ 1.0 1 6,606

Result (cost=0.00..0.02 rows=1 width=32) (actual time=0.005..0.006 rows=1 loops=6,606)

Planning time : 0.894 ms
Execution time : 728,941.460 ms