explain.depesz.com

PostgreSQL's explain analyze made readable

Result: 3mYQ

Settings
# exclusive inclusive rows x rows loops node
1. 0.195 2,095.135 ↓ 8.5 161 1

Sort (cost=16,246.12..16,246.17 rows=19 width=535) (actual time=2,095.117..2,095.135 rows=161 loops=1)

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

CTE query_part

3. 0.006 2,059.421 ↓ 25.0 50 1

Limit (cost=14,914.68..14,914.76 rows=2 width=308) (actual time=2,059.375..2,059.421 rows=50 loops=1)

4. 0.037 2,059.415 ↓ 25.0 50 1

Unique (cost=14,914.68..14,914.76 rows=2 width=308) (actual time=2,059.374..2,059.415 rows=50 loops=1)

5. 0.822 2,059.378 ↓ 25.0 50 1

Sort (cost=14,914.68..14,914.69 rows=2 width=308) (actual time=2,059.373..2,059.378 rows=50 loops=1)

  • Sort Key: image_2.upload_date DESC, image_2.object_key, image_2.system, image_2.thumbnail, image_2.image_type, image_2.id, image_2.description, image_2.exif_orientation, image_2.location_id, image_2.author_id, image_2.exif_lat_long, image_2.date_id, image_2.bucket, image_2.metadata, image_2.uploaded_by
  • Sort Method: quicksort Memory: 603kB
6. 0.281 2,058.556 ↓ 561.5 1,123 1

Subquery Scan on image_2 (cost=14,914.53..14,914.67 rows=2 width=308) (actual time=2,057.719..2,058.556 rows=1,123 loops=1)

7. 0.483 2,058.275 ↓ 561.5 1,123 1

Unique (cost=14,914.53..14,914.65 rows=2 width=437) (actual time=2,057.717..2,058.275 rows=1,123 loops=1)

8.          

CTE data1

9. 0.470 1.841 ↓ 1.1 1,183 1

Hash Join (cost=106.06..146.70 rows=1,114 width=489) (actual time=1.157..1.841 rows=1,183 loops=1)

  • Hash Cond: (uiv.image_id = image.id)
10. 0.227 0.227 ↑ 1.2 1,190 1

Seq Scan on user_image_version uiv (cost=0.00..36.75 rows=1,475 width=24) (actual time=0.004..0.227 rows=1,190 loops=1)

11. 0.668 1.144 ↓ 1.1 1,207 1

Hash (cost=92.14..92.14 rows=1,114 width=473) (actual time=1.144..1.144 rows=1,207 loops=1)

  • Buckets: 2,048 Batches: 1 Memory Usage: 516kB
12. 0.476 0.476 ↓ 1.1 1,207 1

Seq Scan on image (cost=0.00..92.14 rows=1,114 width=473) (actual time=0.008..0.476 rows=1,207 loops=1)

13.          

CTE data2

14. 1,467.683 2,032.916 ↓ 3,489.0 3,489 1

Nested Loop Anti Join (cost=1.32..14,739.93 rows=1 width=481) (actual time=0.500..2,032.916 rows=3,489 loops=1)

  • Join Filter: ((image_1.version_history && data1.version_history) AND (data1.author_id = family_friend.family_member_id))
  • Rows Removed by Join Filter: 4,130,769
15. 6.024 58.603 ↓ 3,494.0 3,494 1

Nested Loop (cost=1.32..14,700.94 rows=1 width=481) (actual time=0.068..58.603 rows=3,494 loops=1)

16. 1.976 2.669 ↓ 10.9 3,565 1

Hash Join (cost=1.04..46.61 rows=328 width=16) (actual time=0.023..2.669 rows=3,565 loops=1)

  • Hash Cond: (uiv_1.user_id = family_friend.user_id)
17. 0.685 0.685 ↑ 1.2 1,190 1

Seq Scan on user_image_version uiv_1 (cost=0.00..36.75 rows=1,475 width=16) (actual time=0.005..0.685 rows=1,190 loops=1)

  • Filter: (deleted IS NULL)
18. 0.005 0.008 ↓ 6.0 12 1

Hash (cost=1.02..1.02 rows=2 width=16) (actual time=0.008..0.008 rows=12 loops=1)

  • Buckets: 1,024 Batches: 1 Memory Usage: 9kB
19. 0.003 0.003 ↓ 6.0 12 1

Seq Scan on family_friend (cost=0.00..1.02 rows=2 width=16) (actual time=0.002..0.003 rows=12 loops=1)

20. 18.014 49.910 ↑ 1.0 1 3,565

Index Scan using image_pk on image image_1 (cost=0.28..44.64 rows=1 width=473) (actual time=0.014..0.014 rows=1 loops=3,565)

  • Index Cond: (id = uiv_1.image_id)
  • Filter: ((SubPlan 2) = version_date)
  • Rows Removed by Filter: 0
21.          

SubPlan (for Index Scan)

22. 3.544 31.896 ↑ 1.0 1 3,544

Aggregate (cost=44.09..44.10 rows=1 width=8) (actual time=0.009..0.009 rows=1 loops=3,544)

23. 7.088 28.352 ↑ 11.0 1 3,544

Bitmap Heap Scan on image iimage (cost=12.09..44.06 rows=11 width=8) (actual time=0.008..0.008 rows=1 loops=3,544)

  • Recheck Cond: (version_history && image_1.version_history)
  • Heap Blocks: exact=3,776
24. 21.264 21.264 ↑ 11.0 1 3,544

Bitmap Index Scan on idx_image_version_history (cost=0.00..12.08 rows=11 width=0) (actual time=0.006..0.006 rows=1 loops=3,544)

  • Index Cond: (version_history && image_1.version_history)
25. 506.630 506.630 ↓ 1.1 1,182 3,494

CTE Scan on data1 (cost=0.00..22.28 rows=1,114 width=40) (actual time=0.000..0.145 rows=1,182 loops=3,494)

26. 5.336 2,057.792 ↓ 563.0 1,126 1

Sort (cost=27.91..27.91 rows=2 width=437) (actual time=2,057.716..2,057.792 rows=1,126 loops=1)

  • Sort Key: data1_1.object_key, data1_1.system, data1_1.thumbnail, data1_1.image_type, data1_1.id, data1_1.description, data1_1.exif_date_time, data1_1.exif_lat_long, data1_1.exif_orientation, data1_1.location_id, data1_1.bucket, data1_1.has_people, data1_1.file_size, data1_1.author_id, data1_1.date_id, data1_1.upload_date, data1_1.metadata, data1_1.previous_version, data1_1.version_history, data1_1.version_date, data1_1.hash, data1_1.user_id, data1_1.uploaded_by
  • Sort Method: quicksort Memory: 632kB
27. 0.287 2,052.456 ↓ 563.0 1,126 1

Append (cost=0.00..27.90 rows=2 width=437) (actual time=1.166..2,052.456 rows=1,126 loops=1)

28. 3.159 3.159 ↓ 127.0 127 1

CTE Scan on data1 data1_1 (cost=0.00..27.85 rows=1 width=437) (actual time=1.166..3.159 rows=127 loops=1)

  • Filter: ((deleted IS NULL) AND (image_type = ANY ('{photo,video}'::text[])) AND (user_id = 1))
  • Rows Removed by Filter: 1,056
29. 2,049.010 2,049.010 ↓ 999.0 999 1

CTE Scan on data2 (cost=0.00..0.03 rows=1 width=437) (actual time=4.896..2,049.010 rows=999 loops=1)

  • Filter: ((image_type = ANY ('{photo,video}'::text[])) AND (user_id = 1))
  • Rows Removed by Filter: 2,490
30. 0.247 2,094.940 ↓ 8.5 161 1

Nested Loop Left Join (cost=80.13..1,330.96 rows=19 width=535) (actual time=2,069.989..2,094.940 rows=161 loops=1)

31. 0.062 2,093.244 ↓ 8.5 161 1

Hash Left Join (cost=30.58..388.76 rows=19 width=517) (actual time=2,069.840..2,093.244 rows=161 loops=1)

  • Hash Cond: (query_part.id = image_event.image_id)
32. 0.090 2,093.116 ↓ 8.8 158 1

Hash Left Join (cost=20.78..378.68 rows=18 width=501) (actual time=2,069.767..2,093.116 rows=158 loops=1)

  • Hash Cond: (query_part.date_id = dates.id)
33. 0.090 2,092.880 ↓ 8.8 158 1

Nested Loop Left Join (cost=6.08..363.74 rows=18 width=490) (actual time=2,069.598..2,092.880 rows=158 loops=1)

34. 18.004 2,092.474 ↓ 8.8 158 1

Nested Loop Left Join (cost=5.80..356.62 rows=18 width=490) (actual time=2,069.583..2,092.474 rows=158 loops=1)

  • Join Filter: (upv.version_history && person_face.version_history)
  • Rows Removed by Join Filter: 80,500
  • Filter: (upv.deleted IS NULL)
35. 1.005 2,070.102 ↓ 39.0 156 1

Nested Loop (cost=5.80..270.24 rows=4 width=534) (actual time=2,069.559..2,070.102 rows=156 loops=1)

  • Join Filter: (query_part.uploaded_by = users.user_id)
  • Rows Removed by Join Filter: 6,240
36. 0.032 0.032 ↓ 1.1 41 1

Seq Scan on users (cost=0.00..6.39 rows=39 width=26) (actual time=0.005..0.032 rows=41 loops=1)

37. 0.467 2,069.065 ↓ 39.0 156 41

Materialize (cost=5.80..261.52 rows=4 width=516) (actual time=50.246..50.465 rows=156 loops=41)

38. 0.088 2,068.598 ↓ 39.0 156 1

Nested Loop Left Join (cost=5.80..261.50 rows=4 width=516) (actual time=2,060.082..2,068.598 rows=156 loops=1)

39. 0.055 2,060.454 ↓ 38.0 152 1

Nested Loop Left Join (cost=5.52..103.05 rows=4 width=464) (actual time=2,060.010..2,060.454 rows=152 loops=1)

40. 0.282 2,060.095 ↓ 38.0 152 1

Hash Right Join (cost=5.24..100.88 rows=4 width=404) (actual time=2,059.993..2,060.095 rows=152 loops=1)

  • Hash Cond: (ifv.image_id = query_part.id)
41. 0.248 0.248 ↑ 1.1 2,543 1

Seq Scan on image_face_version ifv (cost=0.00..85.35 rows=2,735 width=16) (actual time=0.005..0.248 rows=2,543 loops=1)

42. 0.036 2,059.565 ↓ 25.0 50 1

Hash (cost=5.21..5.21 rows=2 width=396) (actual time=2,059.565..2,059.565 rows=50 loops=1)

  • Buckets: 1,024 Batches: 1 Memory Usage: 27kB
43. 0.038 2,059.529 ↓ 25.0 50 1

Hash Right Join (cost=0.07..5.21 rows=2 width=396) (actual time=2,059.487..2,059.529 rows=50 loops=1)

  • Hash Cond: (location.id = query_part.location_id)
44. 0.024 0.024 ↓ 1.5 125 1

Seq Scan on location (cost=0.00..4.82 rows=82 width=96) (actual time=0.009..0.024 rows=125 loops=1)

45. 0.024 2,059.467 ↓ 25.0 50 1

Hash (cost=0.04..0.04 rows=2 width=308) (actual time=2,059.467..2,059.467 rows=50 loops=1)

  • Buckets: 1,024 Batches: 1 Memory Usage: 25kB
46. 2,059.443 2,059.443 ↓ 25.0 50 1

CTE Scan on query_part (cost=0.00..0.04 rows=2 width=308) (actual time=2,059.377..2,059.443 rows=50 loops=1)

47. 0.304 0.304 ↑ 1.0 1 152

Index Scan using face_id_uindex on face f (cost=0.28..0.54 rows=1 width=68) (actual time=0.002..0.002 rows=1 loops=152)

  • Index Cond: (id = ifv.face_id)
48. 8.056 8.056 ↑ 1.0 1 152

Index Scan using person_face_pkey on person_face (cost=0.28..39.60 rows=1 width=60) (actual time=0.032..0.053 rows=1 loops=152)

  • Index Cond: (f.id = face_id)
49. 4.139 4.368 ↑ 1.0 517 156

Materialize (cost=0.00..56.65 rows=517 width=47) (actual time=0.000..0.028 rows=517 loops=156)

50. 0.229 0.229 ↑ 1.0 517 1

Seq Scan on user_person_version upv (cost=0.00..54.06 rows=517 width=47) (actual time=0.010..0.229 rows=517 loops=1)

  • Filter: (user_id = 1)
  • Rows Removed by Filter: 1,648
51. 0.316 0.316 ↑ 1.0 1 158

Index Only Scan using person_version_table_pkey on person_version_table upvf (cost=0.28..0.40 rows=1 width=8) (actual time=0.002..0.002 rows=1 loops=158)

  • Index Cond: (person_version = upv.person_version)
  • Heap Fetches: 151
52. 0.081 0.146 ↓ 1.0 450 1

Hash (cost=9.31..9.31 rows=431 width=19) (actual time=0.146..0.146 rows=450 loops=1)

  • Buckets: 1,024 Batches: 1 Memory Usage: 33kB
53. 0.065 0.065 ↓ 1.0 450 1

Seq Scan on dates (cost=0.00..9.31 rows=431 width=19) (actual time=0.005..0.065 rows=450 loops=1)

54. 0.032 0.066 ↓ 1.2 203 1

Hash (cost=7.69..7.69 rows=169 width=24) (actual time=0.066..0.066 rows=203 loops=1)

  • Buckets: 1,024 Batches: 1 Memory Usage: 18kB
55. 0.034 0.034 ↓ 1.2 203 1

Seq Scan on image_event (cost=0.00..7.69 rows=169 width=24) (actual time=0.007..0.034 rows=203 loops=1)

56. 0.000 1.449 ↓ 0.0 0 161

Limit (cost=49.55..49.55 rows=1 width=266) (actual time=0.009..0.009 rows=0 loops=161)

57. 0.322 1.449 ↓ 0.0 0 161

Sort (cost=49.55..49.58 rows=14 width=266) (actual time=0.009..0.009 rows=0 loops=161)

  • Sort Key: uev.event_id, ievent.version_date DESC
  • Sort Method: quicksort Memory: 25kB
58. 0.049 1.127 ↓ 0.0 0 161

Result (cost=16.53..49.48 rows=14 width=266) (actual time=0.007..0.007 rows=0 loops=161)

  • One-Time Filter: (image_event.deleted IS NULL)
59. 0.103 1.078 ↓ 0.0 0 154

Nested Loop Left Join (cost=16.53..49.48 rows=14 width=266) (actual time=0.007..0.007 rows=0 loops=154)

60. 0.000 0.000 ↓ 0.0 0 154

Index Scan using event_pk on event (cost=0.28..8.29 rows=1 width=31) (actual time=0.000..0.000 rows=0 loops=154)

  • Index Cond: (image_event.event_id = id)
61. 0.042 0.975 ↑ 14.0 1 25

Nested Loop Left Join (cost=16.25..41.04 rows=14 width=97) (actual time=0.038..0.039 rows=1 loops=25)

62. 0.050 0.875 ↑ 14.0 1 25

Bitmap Heap Scan on event ievent (cost=16.11..38.39 rows=14 width=89) (actual time=0.035..0.035 rows=1 loops=25)

  • Recheck Cond: (event.version_history && version_history)
  • Heap Blocks: exact=29
63. 0.825 0.825 ↑ 14.0 1 25

Bitmap Index Scan on event_version_history_idx (cost=0.00..16.10 rows=14 width=0) (actual time=0.033..0.033 rows=1 loops=25)

  • Index Cond: (event.version_history && version_history)
64. 0.058 0.058 ↓ 0.0 0 29

Index Scan using user_event_version_pk on user_event_version uev (cost=0.14..0.19 rows=1 width=8) (actual time=0.002..0.002 rows=0 loops=29)

  • Index Cond: ((user_id = 1) AND (ievent.id = event_id))
  • Filter: (deleted IS NULL)
Planning time : 2.424 ms
Execution time : 2,096.143 ms