explain.depesz.com

PostgreSQL's explain analyze made readable

Result: 6muy

Settings
# exclusive inclusive rows x rows loops node
1. 0.163 15.215 ↓ 5.7 113 1

Sort (cost=833.04..833.09 rows=20 width=542) (actual time=15.193..15.215 rows=113 loops=1)

  • Sort Key: query_part.upload_date DESC
  • Sort Method: quicksort Memory: 95kB
2.          

CTE query_part

3. 0.023 1.342 ↑ 1.0 50 1

Limit (cost=17.00..18.75 rows=50 width=361) (actual time=1.144..1.342 rows=50 loops=1)

4. 0.149 1.319 ↑ 1.7 50 1

Unique (cost=17.00..20.01 rows=86 width=361) (actual time=1.143..1.319 rows=50 loops=1)

5. 0.803 1.170 ↓ 1.5 126 1

Sort (cost=17.00..17.21 rows=86 width=361) (actual time=1.142..1.170 rows=126 loops=1)

  • Sort Key: image.upload_date DESC, image.object_key, image.system, image.thumbnail, image.id, image.description, image.exif_orientation, image.location_id, image.user_id, image.exif_lat_long, image.date_id, image.bucket, image.metadata
  • Sort Method: quicksort Memory: 156kB
6. 0.182 0.367 ↓ 3.3 284 1

Nested Loop Left Join (cost=0.00..14.24 rows=86 width=361) (actual time=0.017..0.367 rows=284 loops=1)

  • Filter: ((image.user_id = 218) OR (image.user_id = family_friend.family_member_id))
  • Rows Removed by Filter: 43
7. 0.076 0.076 ↓ 1.0 109 1

Seq Scan on image (cost=0.00..11.31 rows=104 width=361) (actual time=0.007..0.076 rows=109 loops=1)

  • Filter: (image_type = 'photo'::text)
  • Rows Removed by Filter: 1
8. 0.103 0.109 ↓ 3.0 3 109

Materialize (cost=0.00..1.10 rows=1 width=8) (actual time=0.000..0.001 rows=3 loops=109)

9. 0.006 0.006 ↓ 3.0 3 1

Seq Scan on family_friend (cost=0.00..1.10 rows=1 width=8) (actual time=0.003..0.006 rows=3 loops=1)

  • Filter: (user_id = 218)
  • Rows Removed by Filter: 5
10. 0.165 15.052 ↓ 5.7 113 1

Nested Loop Left Join (cost=762.07..813.86 rows=20 width=542) (actual time=14.473..15.052 rows=113 loops=1)

  • Join Filter: (query_part.id = image_event.image_id)
  • Rows Removed by Join Filter: 326
11. 0.091 14.435 ↓ 5.7 113 1

Hash Left Join (cost=737.56..752.37 rows=20 width=518) (actual time=14.124..14.435 rows=113 loops=1)

  • Hash Cond: (f.id = upvf.face_id)
12. 0.124 1.909 ↓ 5.7 113 1

Hash Right Join (cost=10.16..24.88 rows=20 width=510) (actual time=1.670..1.909 rows=113 loops=1)

  • Hash Cond: (f.image_id = query_part.id)
13. 0.136 0.136 ↓ 1.0 260 1

Seq Scan on face f (cost=0.00..13.56 rows=256 width=68) (actual time=0.004..0.136 rows=260 loops=1)

14. 0.041 1.649 ↓ 6.2 50 1

Hash (cost=10.06..10.06 rows=8 width=442) (actual time=1.649..1.649 rows=50 loops=1)

  • Buckets: 1024 Batches: 1 Memory Usage: 26kB
15. 0.021 1.608 ↓ 6.2 50 1

Nested Loop Left Join (cost=3.56..10.06 rows=8 width=442) (actual time=1.516..1.608 rows=50 loops=1)

16. 0.041 1.537 ↓ 6.2 50 1

Hash Right Join (cost=3.42..4.93 rows=8 width=333) (actual time=1.508..1.537 rows=50 loops=1)

  • Hash Cond: (dates.id = query_part.date_id)
17. 0.012 0.012 ↑ 1.0 33 1

Seq Scan on dates (cost=0.00..1.33 rows=33 width=19) (actual time=0.005..0.012 rows=33 loops=1)

18. 0.039 1.484 ↓ 6.2 50 1

Hash (cost=3.32..3.32 rows=8 width=322) (actual time=1.484..1.484 rows=50 loops=1)

  • Buckets: 1024 Batches: 1 Memory Usage: 25kB
19. 0.049 1.445 ↓ 6.2 50 1

Hash Join (cost=2.18..3.32 rows=8 width=322) (actual time=1.176..1.445 rows=50 loops=1)

  • Hash Cond: (query_part.user_id = users.user_id)
20. 1.381 1.381 ↑ 1.0 50 1

CTE Scan on query_part (cost=0.00..1.00 rows=50 width=300) (actual time=1.146..1.381 rows=50 loops=1)

21. 0.006 0.015 ↑ 1.6 5 1

Hash (cost=2.08..2.08 rows=8 width=30) (actual time=0.015..0.015 rows=5 loops=1)

  • Buckets: 1024 Batches: 1 Memory Usage: 9kB
22. 0.009 0.009 ↑ 1.6 5 1

Seq Scan on users (cost=0.00..2.08 rows=8 width=30) (actual time=0.005..0.009 rows=5 loops=1)

23. 0.050 0.050 ↓ 0.0 0 50

Index Scan using location_pk on location (cost=0.14..0.64 rows=1 width=117) (actual time=0.001..0.001 rows=0 loops=50)

  • Index Cond: (query_part.location_id = id)
24. 0.067 12.435 ↓ 17.4 209 1

Hash (cost=727.25..727.25 rows=12 width=16) (actual time=12.435..12.435 rows=209 loops=1)

  • Buckets: 1024 Batches: 1 Memory Usage: 18kB
25. 0.087 12.368 ↓ 17.4 209 1

Subquery Scan on upvf (cost=727.01..727.25 rows=12 width=16) (actual time=12.192..12.368 rows=209 loops=1)

26. 0.370 12.281 ↓ 17.4 209 1

HashAggregate (cost=727.01..727.13 rows=12 width=208) (actual time=12.191..12.281 rows=209 loops=1)

  • Group Key: data1.person_version, data1.author_id, data1.previous_version, data1.version_history, data1.comment, data1.version_date, data1.sex, data1.person_thumbnail, data1.person_user_id, data1.user_id, pf.face_id
27.          

CTE data1

28. 0.112 0.228 ↑ 1.0 83 1

Hash Join (cost=2.93..14.26 rows=86 width=211) (actual time=0.078..0.228 rows=83 loops=1)

  • Hash Cond: (pvt.person_version = upv.person_version)
29. 0.055 0.055 ↑ 1.4 184 1

Seq Scan on person_version_table pvt (cost=0.00..9.52 rows=252 width=195) (actual time=0.002..0.055 rows=184 loops=1)

30. 0.032 0.061 ↑ 1.0 83 1

Hash (cost=1.86..1.86 rows=86 width=24) (actual time=0.061..0.061 rows=83 loops=1)

  • Buckets: 1024 Batches: 1 Memory Usage: 13kB
31. 0.029 0.029 ↑ 1.0 83 1

Seq Scan on user_person_version upv (cost=0.00..1.86 rows=86 width=24) (actual time=0.006..0.029 rows=83 loops=1)

32.          

CTE data2

33. 4.859 11.098 ↓ 115.0 115 1

Nested Loop Anti Join (cost=0.14..693.43 rows=1 width=203) (actual time=0.144..11.098 rows=115 loops=1)

  • Join Filter: ((pvt_1.version_history && data1_2.version_history) AND (data1_2.user_id = family_friend_1.family_member_id))
  • Rows Removed by Join Filter: 9877
34. 0.159 3.635 ↓ 124.0 124 1

Nested Loop (cost=0.14..690.42 rows=1 width=203) (actual time=0.074..3.635 rows=124 loops=1)

  • Join Filter: (upv_1.user_id = family_friend_1.user_id)
  • Rows Removed by Join Filter: 220
35. 0.072 3.347 ↓ 43.0 43 1

Nested Loop (cost=0.14..689.24 rows=1 width=203) (actual time=0.070..3.347 rows=43 loops=1)

36. 0.027 0.027 ↑ 1.1 58 1

Seq Scan on user_person_version upv_1 (cost=0.00..1.86 rows=62 width=16) (actual time=0.004..0.027 rows=58 loops=1)

  • Filter: (deleted IS NULL)
  • Rows Removed by Filter: 25
37. 0.116 3.248 ↑ 1.0 1 58

Index Scan using person_version_log_pkey on person_version_table pvt_1 (cost=0.14..10.91 rows=1 width=195) (actual time=0.056..0.056 rows=1 loops=58)

  • Index Cond: (person_version = upv_1.person_version)
  • Filter: ((SubPlan 3) = version_date)
  • Rows Removed by Filter: 0
38.          

SubPlan (for Index Scan)

39. 0.174 3.132 ↑ 1.0 1 58

Aggregate (cost=10.16..10.17 rows=1 width=8) (actual time=0.054..0.054 rows=1 loops=58)

40. 2.958 2.958 ↓ 1.7 5 58

Seq Scan on person_version_table ipvt (cost=0.00..10.15 rows=3 width=8) (actual time=0.017..0.051 rows=5 loops=58)

  • Filter: (version_history && pvt_1.version_history)
  • Rows Removed by Filter: 179
41. 0.129 0.129 ↑ 1.0 8 43

Seq Scan on family_friend family_friend_1 (cost=0.00..1.08 rows=8 width=16) (actual time=0.001..0.003 rows=8 loops=43)

42. 2.604 2.604 ↑ 1.1 80 124

CTE Scan on data1 data1_2 (cost=0.00..1.72 rows=86 width=40) (actual time=0.000..0.021 rows=80 loops=124)

43. 0.083 11.911 ↓ 17.4 209 1

Append (cost=4.32..18.99 rows=12 width=208) (actual time=0.102..11.911 rows=209 loops=1)

44. 0.108 0.619 ↓ 32.5 195 1

Nested Loop Left Join (cost=4.32..10.39 rows=6 width=208) (actual time=0.102..0.619 rows=195 loops=1)

45. 0.316 0.316 ↓ 39.0 39 1

CTE Scan on data1 (cost=0.00..1.93 rows=1 width=200) (actual time=0.083..0.316 rows=39 loops=1)

  • Filter: ((deleted IS NULL) AND (user_id = 218))
  • Rows Removed by Filter: 44
46. 0.117 0.195 ↑ 1.2 5 39

Bitmap Heap Scan on person_face pf (cost=4.32..8.40 rows=6 width=16) (actual time=0.003..0.005 rows=5 loops=39)

  • Recheck Cond: (person_version = data1.person_version)
  • Heap Blocks: exact=47
47. 0.078 0.078 ↑ 1.2 5 39

Bitmap Index Scan on person_face_pkey (cost=0.00..4.32 rows=6 width=0) (actual time=0.002..0.002 rows=5 loops=39)

  • Index Cond: (person_version = data1.person_version)
48. 0.009 11.209 ↓ 2.3 14 1

Nested Loop Left Join (cost=4.32..8.48 rows=6 width=208) (actual time=3.434..11.209 rows=14 loops=1)

49. 11.185 11.185 ↓ 3.0 3 1

CTE Scan on data2 (cost=0.00..0.02 rows=1 width=200) (actual time=3.428..11.185 rows=3 loops=1)

  • Filter: (user_id = 218)
  • Rows Removed by Filter: 112
50. 0.009 0.015 ↑ 1.2 5 3

Bitmap Heap Scan on person_face pf_1 (cost=4.32..8.40 rows=6 width=16) (actual time=0.003..0.005 rows=5 loops=3)

  • Recheck Cond: (person_version = data2.person_version)
  • Heap Blocks: exact=3
51. 0.006 0.006 ↑ 1.2 5 3

Bitmap Index Scan on person_face_pkey (cost=0.00..4.32 rows=6 width=0) (actual time=0.002..0.002 rows=5 loops=3)

  • Index Cond: (person_version = data2.person_version)
52. 0.110 0.452 ↑ 5.3 3 113

Materialize (cost=24.51..56.33 rows=16 width=48) (actual time=0.003..0.004 rows=3 loops=113)

53. 0.013 0.342 ↑ 5.3 3 1

Hash Join (cost=24.51..56.25 rows=16 width=48) (actual time=0.340..0.342 rows=3 loops=1)

  • Hash Cond: (image_event.event_id = uevv.id)
54. 0.004 0.004 ↑ 392.5 4 1

Seq Scan on image_event (cost=0.00..25.70 rows=1,570 width=16) (actual time=0.002..0.004 rows=4 loops=1)

55. 0.008 0.325 ↓ 7.0 14 1

Hash (cost=24.48..24.48 rows=2 width=40) (actual time=0.325..0.325 rows=14 loops=1)

  • Buckets: 1024 Batches: 1 Memory Usage: 9kB
56. 0.007 0.317 ↓ 7.0 14 1

Subquery Scan on uevv (cost=24.39..24.48 rows=2 width=40) (actual time=0.298..0.317 rows=14 loops=1)

57. 0.010 0.310 ↓ 7.0 14 1

Unique (cost=24.39..24.46 rows=2 width=224) (actual time=0.297..0.310 rows=14 loops=1)

58.          

CTE data1

59. 0.023 0.054 ↓ 1.1 14 1

Hash Join (cost=1.43..2.60 rows=13 width=201) (actual time=0.041..0.054 rows=14 loops=1)

  • Hash Cond: (uev.event_id = evt.id)
60. 0.007 0.007 ↓ 1.1 14 1

Seq Scan on user_event_version uev (cost=0.00..1.13 rows=13 width=24) (actual time=0.003..0.007 rows=14 loops=1)

61. 0.013 0.024 ↑ 1.0 19 1

Hash (cost=1.19..1.19 rows=19 width=185) (actual time=0.024..0.024 rows=19 loops=1)

  • Buckets: 1024 Batches: 1 Memory Usage: 11kB
62. 0.011 0.011 ↑ 1.0 19 1

Seq Scan on event evt (cost=0.00..1.19 rows=19 width=185) (actual time=0.004..0.011 rows=19 loops=1)

63.          

CTE data2

64. 0.036 0.190 ↓ 6.0 6 1

Nested Loop Anti Join (cost=0.14..21.44 rows=1 width=193) (actual time=0.076..0.190 rows=6 loops=1)

  • Join Filter: ((evt_1.version_history && data1_3.version_history) AND (data1_3.user_id = family_friend_2.family_member_id))
  • Rows Removed by Join Filter: 84
65. 0.007 0.130 ↓ 6.0 6 1

Nested Loop (cost=0.14..20.99 rows=1 width=193) (actual time=0.062..0.130 rows=6 loops=1)

  • Join Filter: (uev_1.user_id = family_friend_2.user_id)
  • Rows Removed by Join Filter: 10
66. 0.011 0.115 ↓ 2.0 2 1

Nested Loop (cost=0.14..19.81 rows=1 width=193) (actual time=0.059..0.115 rows=2 loops=1)

67. 0.006 0.006 ↓ 14.0 14 1

Seq Scan on user_event_version uev_1 (cost=0.00..1.13 rows=1 width=16) (actual time=0.002..0.006 rows=14 loops=1)

  • Filter: (deleted IS NULL)
68. 0.042 0.098 ↓ 0.0 0 14

Index Scan using event_pk on event evt_1 (cost=0.14..9.41 rows=1 width=185) (actual time=0.007..0.007 rows=0 loops=14)

  • Index Cond: (id = uev_1.event_id)
  • Filter: ((SubPlan 6) = version_date)
  • Rows Removed by Filter: 1
69.          

SubPlan (for Index Scan)

70. 0.014 0.056 ↑ 1.0 1 14

Aggregate (cost=1.24..1.25 rows=1 width=8) (actual time=0.004..0.004 rows=1 loops=14)

71. 0.042 0.042 ↓ 0.0 0 14

Seq Scan on event ievt (cost=0.00..1.24 rows=1 width=8) (actual time=0.003..0.003 rows=0 loops=14)

  • Filter: (version_history && evt_1.version_history)
  • Rows Removed by Filter: 19
72. 0.008 0.008 ↑ 1.0 8 2

Seq Scan on family_friend family_friend_2 (cost=0.00..1.08 rows=8 width=16) (actual time=0.001..0.004 rows=8 loops=2)

73. 0.024 0.024 ↓ 1.1 14 6

CTE Scan on data1 data1_3 (cost=0.00..0.26 rows=13 width=40) (actual time=0.000..0.004 rows=14 loops=6)

74. 0.029 0.300 ↓ 7.0 14 1

Sort (cost=0.34..0.35 rows=2 width=224) (actual time=0.296..0.300 rows=14 loops=1)

  • Sort Key: data1_1.id, data1_1.author_id, data1_1.previous_version, data1_1.version_history, data1_1.version_date, data1_1.user_id, data1_1.description, data1_1.name, data1_1.location_id, data1_1.start_date_id, data1_1.end_date_id, data1_1.event_type, data1_1.parent_event_id
  • Sort Method: quicksort Memory: 27kB
75. 0.006 0.271 ↓ 7.0 14 1

Append (cost=0.00..0.33 rows=2 width=224) (actual time=0.044..0.271 rows=14 loops=1)

76. 0.070 0.070 ↓ 14.0 14 1

CTE Scan on data1 data1_1 (cost=0.00..0.29 rows=1 width=224) (actual time=0.043..0.070 rows=14 loops=1)

  • Filter: ((deleted IS NULL) AND (id IS NOT NULL) AND (user_id = 218))
77. 0.195 0.195 ↓ 0.0 0 1

CTE Scan on data2 data2_1 (cost=0.00..0.02 rows=1 width=224) (actual time=0.195..0.195 rows=0 loops=1)

  • Filter: ((id IS NOT NULL) AND (user_id = 218))
  • Rows Removed by Filter: 6
Planning time : 2.935 ms
Execution time : 15.738 ms