explain.depesz.com

PostgreSQL's explain analyze made readable

Result: PCw47

Settings
# exclusive inclusive rows x rows loops node
1. 24.882 47,073.441 ↑ 1.3 3 1

GroupAggregate (cost=315,854.08..315,884.04 rows=4 width=16) (actual time=47,028.741..47,073.441 rows=3 loops=1)

  • Group Key: ad.status
2. 103.819 47,048.559 ↓ 22.0 87,594 1

Sort (cost=315,854.08..315,864.05 rows=3,989 width=24) (actual time=47,025.407..47,048.559 rows=87,594 loops=1)

  • Sort Key: ad.status
  • Sort Method: external merge Disk: 3152kB
3. 186.424 46,944.740 ↓ 22.0 87,594 1

Nested Loop (cost=0.56..315,615.50 rows=3,989 width=24) (actual time=2.387..46,944.740 rows=87,594 loops=1)

4. 44,470.210 45,956.308 ↓ 9.8 133,668 1

Nested Loop (cost=0.00..282,361.29 rows=13,708 width=32) (actual time=2.370..45,956.308 rows=133,668 loops=1)

  • Join Filter: ((lower(((pd.info -> 'info'::text) ->> 'carrier'::text)))::text ~~ (('%'::text || (adc.condition ->> 'carrier'::text)) || '%'::text))
  • Rows Removed by Join Filter: 4143447
5. 228.123 228.123 ↓ 1.0 251,595 1

Seq Scan on passenger_device pd (cost=0.00..199,855.30 rows=249,230 width=648) (actual time=0.004..228.123 rows=251,595 loops=1)

6. 1,256.206 1,257.975 ↓ 1.5 17 251,595

Materialize (cost=0.00..260.12 rows=11 width=50) (actual time=0.000..0.005 rows=17 loops=251,595)

7. 1.769 1.769 ↓ 1.5 17 1

Seq Scan on ad_condition adc (cost=0.00..260.06 rows=11 width=50) (actual time=1.759..1.769 rows=17 loops=1)

  • Filter: (ad_id = '3a652fd1-2dad-4199-8057-f5fa4e64107b'::uuid)
  • Rows Removed by Filter: 9248
8. 802.008 802.008 ↑ 1.0 1 133,668

Index Scan using passenger_ad_passenger_id_ad_id_unique on passenger_ad ad (cost=0.56..2.42 rows=1 width=56) (actual time=0.006..0.006 rows=1 loops=133,668)

  • Index Cond: ((passenger_id = pd.passenger_id) AND (ad_id = '3a652fd1-2dad-4199-8057-f5fa4e64107b'::uuid))