explain.depesz.com

PostgreSQL's explain analyze made readable

Result: 2R3U

Settings
# exclusive inclusive rows x rows loops node
1. 0.246 62.207 ↑ 16.7 6 1

Nested Loop Left Join (cost=30.65..643,410.39 rows=100 width=5,554) (actual time=4.839..62.207 rows=6 loops=1)

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

CTE uas

3. 0.024 0.034 ↓ 1.0 31 1

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

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

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

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

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

6. 0.007 0.007 ↑ 16.7 6 1

Function Scan on unnest t (cost=0.00..1.00 rows=100 width=24) (actual time=0.005..0.007 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.060 0.060 ↓ 1.0 31 6

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

9.          

SubPlan (for Nested Loop Left Join)

10. 1.848 55.308 ↑ 1.0 1 6

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

11. 4.408 53.460 ↓ 5,544.0 5,544 6

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

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

Bitmap Heap Scan on recommendations (cost=156.91..6,318.02 rows=1,816 width=16) (actual time=3.088..8.173 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=27226
13. 15.162 15.162 ↑ 1.1 5,932 6

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

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

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

  • Buckets: 1024 Batches: 1 Memory Usage: 8kB
15. 0.013 0.013 ↓ 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.013..0.013 rows=0 loops=1)

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

Limit (cost=1.42..5.71 rows=1 width=52) (actual time=1.070..1.070 rows=1 loops=6)

17. 0.006 6.414 ↑ 34,572.0 1 6

Nested Loop (cost=1.42..148,245.31 rows=34,572 width=52) (actual time=1.069..1.069 rows=1 loops=6)

18. 0.030 6.366 ↑ 6,341.0 1 6

Nested Loop (cost=0.86..74,605.57 rows=6,341 width=20) (actual time=1.061..1.061 rows=1 loops=6)

19. 6.294 6.294 ↑ 6,341.0 1 6

Index Scan Backward using recommendations_updated_at on recommendations recommendations_1 (cost=0.43..58,531.22 rows=6,341 width=24) (actual time=1.049..1.049 rows=1 loops=6)

  • Filter: (ARRAY[alerts.id] <@ referring_objects)
  • Rows Removed by Filter: 1598
20. 0.042 0.042 ↑ 1.0 1 6

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

  • Index Cond: (id = recommendations_1.listing)
21. 0.042 0.042 ↑ 64.0 1 6

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

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

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

23. 0.048 0.048 ↑ 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.007..0.008 rows=2 loops=6)

  • Index Cond: (room = alerts.room)
Planning time : 2.593 ms
Execution time : 66.611 ms