explain.depesz.com

PostgreSQL's explain analyze made readable

Result: Y7LI

Settings
# exclusive inclusive rows x rows loops node
1. 0.238 635.721 ↑ 16.7 6 1

Nested Loop Left Join (cost=30.65..643,330.01 rows=100 width=5,554) (actual time=236.606..635.721 rows=6 loops=1)

  • Join Filter: (uas.alert = alerts.id)
  • Rows Removed by Join Filter: 180
2.          

CTE uas

3. 0.026 0.035 ↓ 1.0 31 1

Bitmap Heap Scan on user_alert_settings (cost=1.62..30.37 rows=30 width=32) (actual time=0.013..0.035 rows=31 loops=1)

  • Recheck Cond: ("user" = '34316c82-b070-11e8-a7ce-0a95998482ac'::uuid)
  • Heap Blocks: exact=11
4. 0.009 0.009 ↓ 1.0 31 1

Bitmap Index Scan on unique_user_alert (cost=0.00..1.61 rows=30 width=0) (actual time=0.009..0.009 rows=31 loops=1)

  • Index Cond: ("user" = '34316c82-b070-11e8-a7ce-0a95998482ac'::uuid)
5. 0.021 0.059 ↑ 16.7 6 1

Nested Loop (cost=0.28..151.75 rows=100 width=5,214) (actual time=0.017..0.059 rows=6 loops=1)

6. 0.008 0.008 ↑ 16.7 6 1

Function Scan on unnest t (cost=0.00..1.00 rows=100 width=24) (actual time=0.004..0.008 rows=6 loops=1)

7. 0.030 0.030 ↑ 1.0 1 6

Index Scan using alerts_pkey on alerts (cost=0.28..1.51 rows=1 width=5,206) (actual time=0.005..0.005 rows=1 loops=6)

  • Index Cond: (id = t.aid)
8. 0.072 0.072 ↓ 1.0 31 6

CTE Scan on uas (cost=0.00..0.60 rows=30 width=32) (actual time=0.003..0.012 rows=31 loops=6)

9.          

SubPlan (for Nested Loop Left Join)

10. 1.806 56.190 ↑ 1.0 1 6

Aggregate (cost=6,419.43..6,419.44 rows=1 width=8) (actual time=9.365..9.365 rows=1 loops=6)

11. 4.430 54.384 ↓ 5,544.0 5,544 6

Hash Left Join (cost=253.55..6,419.43 rows=1 width=16) (actual time=3.312..9.064 rows=5,544 loops=6)

  • Hash Cond: (recommendations.id = recommendations_eav.recommendation)
  • Filter: (recommendations_eav.id IS NULL)
12. 33.666 49.926 ↓ 3.1 5,544 6

Bitmap Heap Scan on recommendations (cost=156.91..6,318.02 rows=1,816 width=16) (actual time=3.277..8.321 rows=5,544 loops=6)

  • Recheck Cond: (ARRAY[alerts.id] <@ referring_objects)
  • Filter: ((deleted_at IS NULL) AND (hidden IS FALSE) AND (COALESCE(array_length(referring_objects, 1), 0) > 0))
  • Heap Blocks: exact=27224
13. 16.260 16.260 ↑ 1.1 5,931 6

Bitmap Index Scan on recommendations_referring_objects (cost=0.00..156.45 rows=6,341 width=0) (actual time=2.710..2.710 rows=5,931 loops=6)

  • Index Cond: (ARRAY[alerts.id] <@ referring_objects)
14. 0.000 0.028 ↓ 0.0 0 1

Hash (cost=95.58..95.58 rows=85 width=32) (actual time=0.028..0.028 rows=0 loops=1)

  • Buckets: 1024 Batches: 1 Memory Usage: 8kB
15. 0.028 0.028 ↓ 0.0 0 1

Index Scan using recommendations_eav_user_recommendation_action_key on recommendations_eav (cost=0.42..95.58 rows=85 width=32) (actual time=0.027..0.028 rows=0 loops=1)

  • Index Cond: (("user" = '34316c82-b070-11e8-a7ce-0a95998482ac'::uuid) AND (action = 'Read'::recommendation_eav_action))
16. 0.012 579.072 ↑ 1.0 1 6

Limit (cost=1.00..4.91 rows=1 width=44) (actual time=96.511..96.512 rows=1 loops=6)

17. 0.010 579.060 ↑ 34,572.0 1 6

Nested Loop (cost=1.00..135,233.47 rows=34,572 width=44) (actual time=96.510..96.510 rows=1 loops=6)

18. 0.031 578.994 ↑ 6,341.0 1 6

Nested Loop (cost=0.43..61,593.74 rows=6,341 width=12) (actual time=75.784..96.499 rows=1 loops=6)

19. 578.886 578.886 ↑ 6,341.0 1 6

Seq Scan on recommendations recommendations_1 (cost=0.00..45,519.39 rows=6,341 width=16) (actual time=75.769..96.481 rows=1 loops=6)

  • Filter: (ARRAY[alerts.id] <@ referring_objects)
  • Rows Removed by Filter: 363987
20. 0.077 0.077 ↑ 1.0 1 7

Index Scan using listings_pkey on listings (cost=0.43..2.53 rows=1 width=28) (actual time=0.011..0.011 rows=1 loops=7)

  • Index Cond: (id = recommendations_1.listing)
21. 0.056 0.056 ↑ 64.0 1 7

Index Scan using photos_listing_mui_idx on photos (cost=0.56..10.97 rows=64 width=56) (actual time=0.008..0.008 rows=1 loops=7)

  • Index Cond: (listing_mui = listings.matrix_unique_id)
  • Filter: ((url IS NOT NULL) AND (listings.mls = mls))
22. 0.054 0.090 ↑ 1.0 1 6

Aggregate (cost=3.76..3.77 rows=1 width=32) (actual time=0.015..0.015 rows=1 loops=6)

23. 0.036 0.036 ↑ 1.0 2 6

Index Scan using rooms_users_room_idx on rooms_users (cost=0.42..3.76 rows=2 width=16) (actual time=0.006..0.006 rows=2 loops=6)

  • Index Cond: (room = alerts.room)
Planning time : 1.234 ms
Execution time : 635.886 ms