explain.depesz.com

PostgreSQL's explain analyze made readable

Result: xMTl

Settings
# exclusive inclusive rows x rows loops node
1. 0.256 60,105.129 ↑ 16.7 6 1

Nested Loop Left Join (cost=30.65..10,260,278.62 rows=100 width=5,554) (actual time=20.455..60,105.129 rows=6 loops=1)

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

CTE uas

3. 0.017 0.032 ↓ 1.0 31 1

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

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

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

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

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

6. 0.009 0.009 ↑ 16.7 6 1

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

7. 0.036 0.036 ↑ 1.0 1 6

Index Scan using alerts_pkey on alerts (cost=0.28..1.51 rows=1 width=5,206) (actual time=0.006..0.006 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.003..0.011 rows=31 loops=6)

9.          

SubPlan (for Nested Loop Left Join)

10. 2.358 63.984 ↑ 1.0 1 6

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

11. 5.215 61.626 ↓ 5,544.0 5,544 6

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

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

Bitmap Heap Scan on recommendations (cost=156.91..6,318.02 rows=1,816 width=16) (actual time=3.068..9.400 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. 14.982 14.982 ↑ 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.497..2.497 rows=5,931 loops=6)

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

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

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

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

Limit (cost=96,174.39..96,174.40 rows=1 width=68) (actual time=10,006.772..10,006.774 rows=1 loops=6)

17. 157.512 60,040.620 ↑ 34,572.0 1 6

Sort (cost=96,174.39..96,260.82 rows=34,572 width=68) (actual time=10,006.770..10,006.770 rows=1 loops=6)

  • Sort Key: recommendations_1.updated_at DESC, photos.id
  • Sort Method: top-N heapsort Memory: 25kB
18. 90.726 59,883.108 ↓ 4.2 144,608 6

Nested Loop (cost=159.04..96,001.53 rows=34,572 width=68) (actual time=3.795..9,980.518 rows=144,608 loops=6)

19. 14.328 1,513.854 ↑ 1.1 5,544 6

Nested Loop (cost=158.47..22,361.80 rows=6,341 width=20) (actual time=3.704..252.309 rows=5,544 loops=6)

20. 51.012 69.174 ↑ 1.1 5,544 6

Bitmap Heap Scan on recommendations recommendations_1 (cost=158.04..6,287.45 rows=6,341 width=24) (actual time=3.689..11.529 rows=5,544 loops=6)

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

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

  • Index Cond: (ARRAY[alerts.id] <@ referring_objects)
22. 1,430.352 1,430.352 ↑ 1.0 1 33,264

Index Scan using listings_pkey on listings (cost=0.43..2.53 rows=1 width=28) (actual time=0.043..0.043 rows=1 loops=33,264)

  • Index Cond: (id = recommendations_1.listing)
23. 58,278.528 58,278.528 ↑ 2.5 26 33,264

Index Scan using photos_listing_mui_idx on photos (cost=0.56..10.97 rows=64 width=72) (actual time=0.242..1.752 rows=26 loops=33,264)

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

Aggregate (cost=3.76..3.77 rows=1 width=32) (actual time=0.019..0.019 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.007..0.007 rows=2 loops=6)

  • Index Cond: (room = alerts.room)
Planning time : 1.462 ms
Execution time : 60,105.291 ms