explain.depesz.com

PostgreSQL's explain analyze made readable

Result: J2xp

Settings
# exclusive inclusive rows x rows loops node
1. 0.082 719,286.744 ↑ 1.0 1 1

Aggregate (cost=144,959.00..144,959.01 rows=1 width=32) (actual time=719,286.744..719,286.744 rows=1 loops=1)

2. 0.008 719,286.662 ↓ 40.0 40 1

Limit (cost=144,958.98..144,958.99 rows=1 width=56) (actual time=719,286.653..719,286.662 rows=40 loops=1)

3. 16.897 719,286.654 ↓ 40.0 40 1

Sort (cost=144,958.98..144,958.99 rows=1 width=56) (actual time=719,286.652..719,286.654 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: 92kB
4. 1,331.108 719,269.757 ↓ 6,060.0 6,060 1

Nested Loop Left Join (cost=1.09..144,958.97 rows=1 width=56) (actual time=118.187..719,269.757 rows=6,060 loops=1)

  • Join Filter: (di.ext_id = iv.atm_id)
  • Rows Removed by Join Filter: 11114037
5. 143.769 143.769 ↓ 6,060.0 6,060 1

Seq Scan on device_info di (cost=0.00..80,698.66 rows=1 width=350) (actual time=0.016..143.769 rows=6,060 loops=1)

  • Filter: (is_monitoring AND (COALESCE(business_statuses, to_jsonb('{}'::uuid[])) ?| '{35b6a966-bafe-4da8-b1cb-7db85e0aea06}'::text[
  • Rows Removed by Filter: 93954
6. 2,405.820 717,770.640 ↑ 1.4 1,834 6,060

Merge Left Join (cost=1.09..64,228.73 rows=2,525 width=40) (actual time=2.134..118.444 rows=1,834 loops=6,060)

  • Merge Cond: (iv.atm_id = iv_1.atm_id)
7. 2,884.560 561,925.620 ↑ 1.4 1,834 6,060

Unique (cost=0.55..34,439.00 rows=2,525 width=32) (actual time=0.040..92.727 rows=1,834 loops=6,060)

8. 559,041.060 559,041.060 ↓ 1.0 2,768 6,060

WindowAgg (cost=0.55..34,432.29 rows=2,686 width=32) (actual time=0.040..92.251 rows=2,768 loops=6,060)

  • -> Index Only Scan using incident_active_id on incident iv (cost=0.55..34385.28 rows=2686 width=24) (actual time=0.0
  • Filter: ((status)::text <> 'CLOSED'::text)
  • Rows Removed by Filter: 393429
  • Heap Fetches: 86494180
9. 18.180 153,439.200 ↑ 1.5 2 6,060

Unique (cost=0.55..29,758.10 rows=3 width=32) (actual time=2.092..25.320 rows=2 loops=6,060)

10. 153,421.020 153,421.020 ↑ 1.5 2 6,060

WindowAgg (cost=0.55..29,758.09 rows=3 width=32) (actual time=2.091..25.317 rows=2 loops=6,060)

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

SubPlan (for Nested Loop Left Join)

12. 24.240 24.240 ↑ 1.0 1 6,060

Result (cost=0.00..0.02 rows=1 width=32) (actual time=0.004..0.004 rows=1 loops=6,060)

Planning time : 0.838 ms
Execution time : 719,287.327 ms