explain.depesz.com

PostgreSQL's explain analyze made readable

Result: 4xDt

Settings
# exclusive inclusive rows x rows loops node
1. 0.003 4,531.187 ↓ 0.0 0 1

Merge Right Join (cost=531,908.01..584,189.74 rows=172 width=222) (actual time=4,531.187..4,531.187 rows=0 loops=1)

  • Merge Cond: (vehicle.request_id = ir.id)
2.          

CTE apt_audits

3. 0.116 0.558 ↓ 22.6 113 1

HashAggregate (cost=20.26..20.31 rows=5 width=4) (actual time=0.525..0.558 rows=113 loops=1)

  • Group Key: aa.reference_id
4. 0.442 0.442 ↓ 1.2 204 1

Index Scan using appointment_audit_pkey on appointment_audit aa (cost=0.43..19.84 rows=170 width=4) (actual time=0.030..0.442 rows=204 loops=1)

  • Index Cond: (id >= 13808501)
5.          

CTE insp_audits

6. 0.031 0.257 ↓ 23.0 23 1

HashAggregate (cost=34.49..34.50 rows=1 width=4) (actual time=0.250..0.257 rows=23 loops=1)

  • Group Key: ira.reference_id
7. 0.226 0.226 ↑ 1.0 68 1

Index Scan using inspection_request_audit_pkey on inspection_request_audit ira (cost=0.56..34.32 rows=69 width=4) (actual time=0.020..0.226 rows=68 loops=1)

  • Index Cond: (id >= 47851782)
8. 0.013 3,272.759 ↑ 1,275,740.0 1 1

GroupAggregate (cost=352,827.86..389,160.14 rows=1,275,740 width=30) (actual time=3,272.759..3,272.759 rows=1 loops=1)

  • Group Key: vehicle.request_id, vehicle.completed_at
9. 2,245.227 3,272.746 ↑ 392,914.7 6 1

Sort (cost=352,827.86..358,721.58 rows=2,357,488 width=30) (actual time=3,272.742..3,272.746 rows=6 loops=1)

  • Sort Key: vehicle.request_id, vehicle.completed_at
  • Sort Method: external merge Disk: 82952kB
10. 1,027.519 1,027.519 ↓ 1.0 2,357,825 1

Seq Scan on vehicle (cost=0.00..70,263.88 rows=2,357,488 width=30) (actual time=0.007..1,027.519 rows=2,357,825 loops=1)

11. 0.008 1,258.425 ↓ 0.0 0 1

Sort (cost=179,025.34..179,025.77 rows=172 width=190) (actual time=1,258.425..1,258.425 rows=0 loops=1)

  • Sort Key: ir.id
  • Sort Method: quicksort Memory: 25kB
12. 0.000 1,258.417 ↓ 0.0 0 1

Nested Loop (cost=0.99..179,018.95 rows=172 width=190) (actual time=1,258.417..1,258.417 rows=0 loops=1)

13. 0.007 1,258.417 ↓ 0.0 0 1

Nested Loop (cost=0.56..178,071.44 rows=398 width=33) (actual time=1,258.417..1,258.417 rows=0 loops=1)

14. 0.016 0.016 ↑ 1.0 1 1

Seq Scan on customer c (cost=0.00..1.07 rows=1 width=4) (actual time=0.013..0.016 rows=1 loops=1)

  • Filter: (name = 'NISSAN'::text)
  • Rows Removed by Filter: 7
15. 1,257.502 1,258.394 ↓ 0.0 0 1

Index Scan using ix_inreq_cust_id on inspection_request ir (cost=0.56..178,066.38 rows=398 width=37) (actual time=1,258.394..1,258.394 rows=0 loops=1)

  • Index Cond: (customer_id = c.id)
  • Filter: ((status = 3) AND ((hashed SubPlan 3) OR (hashed SubPlan 4)) AND ((inspection_location ->> 'state'::text) = 'NC'::text))
  • Rows Removed by Filter: 1428484
16.          

SubPlan (forIndex Scan)

17. 0.622 0.622 ↓ 22.6 113 1

CTE Scan on apt_audits (cost=0.00..0.10 rows=5 width=4) (actual time=0.527..0.622 rows=113 loops=1)

18. 0.270 0.270 ↓ 23.0 23 1

CTE Scan on insp_audits (cost=0.00..0.02 rows=1 width=4) (actual time=0.251..0.270 rows=23 loops=1)

19. 0.000 0.000 ↓ 0.0 0

Index Scan using appointment_request_id_idx on appointment a (cost=0.42..2.37 rows=1 width=161) (never executed)

  • Index Cond: (request_id = ir.id)