explain.depesz.com

PostgreSQL's explain analyze made readable

Result: JRkG

Settings
# exclusive inclusive rows x rows loops node
1. 0.275 1,081.272 ↑ 16.7 6 1

Nested Loop Left Join (cost=30.65..10,100,257.84 rows=100 width=5,554) (actual time=1.213..1,081.272 rows=6 loops=1)

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

CTE uas

3. 0.018 0.031 ↓ 1.0 31 1

Bitmap Heap Scan on user_alert_settings (cost=1.62..30.36 rows=30 width=32) (actual time=0.017..0.031 rows=31 loops=1)

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

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

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

Nested Loop (cost=0.28..151.75 rows=100 width=5,214) (actual time=0.021..0.055 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.005..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.066 0.066 ↓ 1.0 31 6

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

9.          

SubPlan (for Nested Loop Left Join)

10. 1.860 48.276 ↑ 1.0 1 6

Aggregate (cost=6,249.32..6,249.33 rows=1 width=8) (actual time=8.046..8.046 rows=1 loops=6)

11. 4.477 46.416 ↓ 5,495.0 5,495 6

Hash Left Join (cost=164.83..6,249.32 rows=1 width=16) (actual time=1.728..7.736 rows=5,495 loops=6)

  • Hash Cond: (recommendations.id = recommendations_eav.recommendation)
  • Filter: (recommendations_eav.id IS NULL)
12. 34.950 41.928 ↓ 3.1 5,495 6

Bitmap Heap Scan on recommendations (cost=68.19..6,148.00 rows=1,782 width=16) (actual time=1.707..6.988 rows=5,495 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=26228
13. 6.978 6.978 ↑ 1.1 5,530 6

Bitmap Index Scan on recommendations_referring_objects (cost=0.00..67.74 rows=6,246 width=0) (actual time=1.163..1.163 rows=5,530 loops=6)

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

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

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

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

Limit (cost=94,744.30..94,744.30 rows=1 width=68) (actual time=172.081..172.083 rows=1 loops=6)

17. 117.468 1,032.480 ↑ 34,405.0 1 6

Sort (cost=94,744.30..94,830.31 rows=34,405 width=68) (actual time=172.080..172.080 rows=1 loops=6)

  • Sort Key: recommendations_1.updated_at DESC, photos.id
  • Sort Method: top-N heapsort Memory: 25kB
18. 96.834 915.012 ↓ 4.2 143,520 6

Nested Loop (cost=70.30..94,572.27 rows=34,405 width=68) (actual time=1.739..152.502 rows=143,520 loops=6)

19. 17.292 191.748 ↑ 1.1 5,495 6

Nested Loop (cost=69.74..21,956.99 rows=6,246 width=20) (actual time=1.730..31.958 rows=5,495 loops=6)

20. 35.688 42.576 ↑ 1.1 5,495 6

Bitmap Heap Scan on recommendations recommendations_1 (cost=69.31..6,117.89 rows=6,246 width=24) (actual time=1.711..7.096 rows=5,495 loops=6)

  • Recheck Cond: (ARRAY[alerts.id] <@ referring_objects)
  • Heap Blocks: exact=26228
21. 6.888 6.888 ↑ 1.1 5,530 6

Bitmap Index Scan on recommendations_referring_objects (cost=0.00..67.74 rows=6,246 width=0) (actual time=1.148..1.148 rows=5,530 loops=6)

  • Index Cond: (ARRAY[alerts.id] <@ referring_objects)
22. 131.880 131.880 ↑ 1.0 1 32,970

Index Scan using listings_pkey on listings (cost=0.43..2.54 rows=1 width=28) (actual time=0.004..0.004 rows=1 loops=32,970)

  • Index Cond: (id = recommendations_1.listing)
23. 626.430 626.430 ↑ 2.5 26 32,970

Index Scan using photos_listing_mui_idx on photos (cost=0.56..10.99 rows=64 width=72) (actual time=0.004..0.019 rows=26 loops=32,970)

  • Index Cond: (listing_mui = listings.matrix_unique_id)
  • Filter: ((url IS NOT NULL) AND (listings.mls = mls))
24. 0.060 0.102 ↑ 1.0 1 6

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

25. 0.042 0.042 ↑ 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.007 rows=2 loops=6)

  • Index Cond: (room = alerts.room)
Planning time : 2.553 ms
Execution time : 1,085.817 ms