explain.depesz.com

PostgreSQL's explain analyze made readable

Result: Rhuu

Settings
# exclusive inclusive rows x rows loops node
1. 0.004 1,949.771 ↑ 1.9 26 1

Limit (cost=16,325.15..16,325.28 rows=50 width=684) (actual time=1,949.767..1,949.771 rows=26 loops=1)

2. 0.028 1,949.767 ↑ 4.4 26 1

Sort (cost=16,325.15..16,325.44 rows=115 width=684) (actual time=1,949.766..1,949.767 rows=26 loops=1)

  • Sort Key: sd.date_normalized DESC
  • Sort Method: quicksort Memory: 34kB
3. 0.018 1,949.739 ↑ 4.4 26 1

Unique (cost=16,311.84..16,321.33 rows=115 width=684) (actual time=1,949.720..1,949.739 rows=26 loops=1)

4. 0.064 1,949.721 ↑ 4.4 26 1

Sort (cost=16,311.84..16,312.13 rows=115 width=684) (actual time=1,949.719..1,949.721 rows=26 loops=1)

  • Sort Key: uevv.author_id, uevv.location_id, uevv.id, uevv.name, uevv.start_date_id, uevv.end_date_id, uevv.event_type, uevv.parent_event_id, uevv.description, uevv.previous_version, uevv.version_history, uevv.version_date, uevv.data, sd.date_string, ed.date_string, (NULL::text), (NULL::text), (NULL::jsonb), (NULL::text), (NULL::bigint), (NULL::text), (NULL::integer), (NULL::bigint), (NULL::bigint), (NULL::geography), (NULL::bigint), (NULL::text), (NULL::timestamp with time zone), (NULL::jsonb), (NULL::bigint), (NULL::text), sd.date_normalized
  • Sort Method: quicksort Memory: 34kB
5. 0.005 1,949.657 ↑ 4.4 26 1

Append (cost=48.59..16,307.91 rows=115 width=684) (actual time=23.531..1,949.657 rows=26 loops=1)

6. 0.012 23.543 ↑ 6.7 17 1

Hash Left Join (cost=48.59..61.55 rows=114 width=622) (actual time=23.530..23.543 rows=17 loops=1)

  • Hash Cond: (uevv.end_date_id = ed.id)
7. 0.032 23.396 ↑ 6.7 17 1

Hash Right Join (cost=35.45..46.85 rows=114 width=272) (actual time=23.390..23.396 rows=17 loops=1)

  • Hash Cond: (image_event.event_id = uevv.id)
8. 0.023 0.023 ↑ 1.0 169 1

Seq Scan on image_event (cost=0.00..7.69 rows=169 width=8) (actual time=0.006..0.023 rows=169 loops=1)

9. 0.009 23.341 ↑ 6.7 17 1

Hash (cost=34.02..34.02 rows=114 width=272) (actual time=23.341..23.341 rows=17 loops=1)

  • Buckets: 1,024 Batches: 1 Memory Usage: 12kB
10. 0.064 23.332 ↑ 6.7 17 1

Hash Join (cost=11.34..34.02 rows=114 width=272) (actual time=23.273..23.332 rows=17 loops=1)

  • Hash Cond: (uevv.start_date_id = sd.id)
  • Join Filter: (ROW(sd.date_normalized, uevv.id) < ROW('[1971-01-01,1971-01-02)'::daterange, NULL::bigint))
  • Rows Removed by Join Filter: 25
11. 23.144 23.144 ↑ 5.0 200 1

Function Scan on user_event_version_set uevv (cost=0.25..10.25 rows=1,000 width=248) (actual time=23.131..23.144 rows=200 loops=1)

12. 0.046 0.124 ↓ 1.1 257 1

Hash (cost=8.06..8.06 rows=242 width=32) (actual time=0.124..0.124 rows=257 loops=1)

  • Buckets: 1,024 Batches: 1 Memory Usage: 27kB
13. 0.078 0.078 ↓ 1.1 257 1

Seq Scan on dates sd (cost=0.00..8.06 rows=242 width=32) (actual time=0.013..0.078 rows=257 loops=1)

  • Filter: (date_normalized IS NOT NULL)
  • Rows Removed by Filter: 174
14. 0.080 0.135 ↓ 1.1 431 1

Hash (cost=8.06..8.06 rows=406 width=18) (actual time=0.135..0.135 rows=431 loops=1)

  • Buckets: 1,024 Batches: 1 Memory Usage: 32kB
15. 0.055 0.055 ↓ 1.1 431 1

Seq Scan on dates ed (cost=0.00..8.06 rows=406 width=18) (actual time=0.002..0.055 rows=431 loops=1)

16. 0.008 1,926.109 ↓ 9.0 9 1

Nested Loop Left Join (cost=16,228.20..16,245.21 rows=1 width=644) (actual time=1,925.974..1,926.109 rows=9 loops=1)

  • Filter: (image_event_1.event_id IS NULL)
17. 0.101 1,926.083 ↓ 9.0 9 1

Hash Join (cost=16,228.05..16,237.04 rows=1 width=332) (actual time=1,925.960..1,926.083 rows=9 loops=1)

  • Hash Cond: (sd_1.id = image.date_id)
  • Join Filter: (ROW(sd_1.date_normalized, image.id) < ROW('[1971-01-01,1971-01-02)'::daterange, NULL::bigint))
  • Rows Removed by Join Filter: 258
18. 0.053 0.053 ↓ 1.1 257 1

Seq Scan on dates sd_1 (cost=0.00..8.06 rows=242 width=32) (actual time=0.002..0.053 rows=257 loops=1)

  • Filter: (date_normalized IS NOT NULL)
  • Rows Removed by Filter: 174
19. 0.233 1,925.929 ↓ 196.0 392 1

Hash (cost=16,228.03..16,228.03 rows=2 width=308) (actual time=1,925.929..1,925.929 rows=392 loops=1)

  • Buckets: 1,024 Batches: 1 Memory Usage: 133kB
20. 0.322 1,925.696 ↓ 562.0 1,124 1

Subquery Scan on image (cost=16,227.89..16,228.03 rows=2 width=308) (actual time=1,924.775..1,925.696 rows=1,124 loops=1)

21. 0.520 1,925.374 ↓ 562.0 1,124 1

Unique (cost=16,227.89..16,228.01 rows=2 width=437) (actual time=1,924.773..1,925.374 rows=1,124 loops=1)

22.          

CTE data1

23. 0.629 2.413 ↓ 1.1 1,180 1

Hash Join (cost=106.11..146.74 rows=1,116 width=489) (actual time=1.663..2.413 rows=1,180 loops=1)

  • Hash Cond: (uiv.image_id = image_1.id)
24. 0.140 0.140 ↑ 1.2 1,186 1

Seq Scan on user_image_version uiv (cost=0.00..36.75 rows=1,475 width=24) (actual time=0.005..0.140 rows=1,186 loops=1)

25. 1.135 1.644 ↓ 1.1 1,204 1

Hash (cost=92.16..92.16 rows=1,116 width=473) (actual time=1.644..1.644 rows=1,204 loops=1)

  • Buckets: 2,048 Batches: 1 Memory Usage: 513kB
26. 0.509 0.509 ↓ 1.1 1,204 1

Seq Scan on image image_1 (cost=0.00..92.16 rows=1,116 width=473) (actual time=0.003..0.509 rows=1,204 loops=1)

27.          

CTE data2

28. 1,233.485 1,910.308 ↓ 3,492.0 3,492 1

Nested Loop Anti Join (cost=1.32..16,053.19 rows=1 width=481) (actual time=0.603..1,910.308 rows=3,492 loops=1)

  • Join Filter: ((image_2.version_history && data1_1.version_history) AND (data1_1.author_id = family_friend.family_member_id))
  • Rows Removed by Join Filter: 4,126,519
29. 3.170 200.959 ↓ 3,499.0 3,499 1

Nested Loop (cost=1.32..16,014.13 rows=1 width=481) (actual time=0.110..200.959 rows=3,499 loops=1)

30. 1.542 2.154 ↓ 10.8 3,557 1

Hash Join (cost=1.04..46.61 rows=328 width=16) (actual time=0.024..2.154 rows=3,557 loops=1)

  • Hash Cond: (uiv_1.user_id = family_friend.user_id)
31. 0.604 0.604 ↑ 1.2 1,186 1

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

  • Filter: (deleted IS NULL)
32. 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
33. 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)

34. 18.685 195.635 ↑ 1.0 1 3,557

Index Scan using image_pk on image image_2 (cost=0.28..48.64 rows=1 width=473) (actual time=0.055..0.055 rows=1 loops=3,557)

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

SubPlan (for Index Scan)

36. 3.539 176.950 ↑ 1.0 1 3,539

Aggregate (cost=48.09..48.10 rows=1 width=8) (actual time=0.050..0.050 rows=1 loops=3,539)

37. 7.078 173.411 ↑ 11.0 1 3,539

Bitmap Heap Scan on image iimage (cost=16.09..48.06 rows=11 width=8) (actual time=0.049..0.049 rows=1 loops=3,539)

  • Recheck Cond: (version_history && image_2.version_history)
  • Heap Blocks: exact=3,730
38. 166.333 166.333 ↑ 11.0 1 3,539

Bitmap Index Scan on idx_image_version_history (cost=0.00..16.08 rows=11 width=0) (actual time=0.047..0.047 rows=1 loops=3,539)

  • Index Cond: (version_history && image_2.version_history)
39. 475.864 475.864 ↓ 1.1 1,179 3,499

CTE Scan on data1 data1_1 (cost=0.00..22.32 rows=1,116 width=40) (actual time=0.000..0.136 rows=1,179 loops=3,499)

40. 4.852 1,924.854 ↓ 563.5 1,127 1

Sort (cost=27.96..27.96 rows=2 width=437) (actual time=1,924.772..1,924.854 rows=1,127 loops=1)

  • Sort Key: data1.object_key, data1.system, data1.thumbnail, data1.image_type, data1.id, data1.description, data1.exif_date_time, data1.exif_lat_long, data1.exif_orientation, data1.location_id, data1.bucket, data1.has_people, data1.file_size, data1.author_id, data1.date_id, data1.upload_date, data1.metadata, data1.previous_version, data1.version_history, data1.version_date, data1.hash, data1.user_id, data1.uploaded_by
  • Sort Method: quicksort Memory: 630kB
41. 0.489 1,920.002 ↓ 563.5 1,127 1

Append (cost=0.00..27.95 rows=2 width=437) (actual time=1.671..1,920.002 rows=1,127 loops=1)

42. 3.762 3.762 ↓ 125.0 125 1

CTE Scan on data1 (cost=0.00..27.90 rows=1 width=437) (actual time=1.670..3.762 rows=125 loops=1)

  • Filter: ((deleted IS NULL) AND (image_type = ANY ('{photo,video}'::text[])) AND (user_id = 1))
  • Rows Removed by Filter: 1,055
43. 1,915.751 1,915.751 ↓ 1,002.0 1,002 1

CTE Scan on data2 (cost=0.00..0.03 rows=1 width=437) (actual time=12.583..1,915.751 rows=1,002 loops=1)

  • Filter: ((image_type = ANY ('{photo,video}'::text[])) AND (user_id = 1))
  • Rows Removed by Filter: 2,490
44. 0.018 0.018 ↓ 0.0 0 9

Index Only Scan using image_event_pk on image_event image_event_1 (cost=0.14..8.16 rows=1 width=16) (actual time=0.002..0.002 rows=0 loops=9)

  • Index Cond: (image_id = image.id)
  • Heap Fetches: 0
Planning time : 3.206 ms
Execution time : 1,950.761 ms