explain.depesz.com

PostgreSQL's explain analyze made readable

Result: 1HTk

Settings
# exclusive inclusive rows x rows loops node
1. 0.639 194.380 ↓ 566.5 1,133 1

Unique (cost=8,180.90..8,181.02 rows=2 width=445) (actual time=193.656..194.380 rows=1,133 loops=1)

2.          

CTE data1

3. 0.393 1.439 ↓ 1.0 127 1

Hash Join (cost=42.50..140.09 rows=125 width=489) (actual time=0.210..1.439 rows=127 loops=1)

  • Hash Cond: (img.id = uiv.image_id)
4. 0.853 0.853 ↓ 1.1 1,204 1

Seq Scan on image img (cost=0.00..92.16 rows=1,116 width=473) (actual time=0.006..0.853 rows=1,204 loops=1)

5. 0.028 0.193 ↑ 1.3 127 1

Hash (cost=40.44..40.44 rows=165 width=24) (actual time=0.193..0.193 rows=127 loops=1)

  • Buckets: 1,024 Batches: 1 Memory Usage: 14kB
6. 0.165 0.165 ↑ 1.3 127 1

Seq Scan on user_image_version uiv (cost=0.00..40.44 rows=165 width=24) (actual time=0.006..0.165 rows=127 loops=1)

  • Filter: (user_id = 1)
  • Rows Removed by Filter: 1,059
7.          

CTE data2

8. 3.113 70.637 ↓ 1,010.0 1,010 1

Nested Loop (cost=1.31..8,034.15 rows=1 width=481) (actual time=0.111..70.637 rows=1,010 loops=1)

9. 0.586 0.964 ↓ 6.2 1,024 1

Hash Join (cost=1.04..43.48 rows=164 width=16) (actual time=0.019..0.964 rows=1,024 loops=1)

  • Hash Cond: (uiv_1.user_id = family_friend.user_id)
10. 0.370 0.370 ↑ 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.004..0.370 rows=1,186 loops=1)

  • Filter: (deleted IS NULL)
11. 0.004 0.008 ↓ 4.0 4 1

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

  • Buckets: 1,024 Batches: 1 Memory Usage: 9kB
12. 0.004 0.004 ↓ 4.0 4 1

Seq Scan on family_friend (cost=0.00..1.02 rows=1 width=16) (actual time=0.002..0.004 rows=4 loops=1)

  • Filter: (family_member_id = 1)
  • Rows Removed by Filter: 8
13. 4.462 66.560 ↑ 1.0 1 1,024

Index Scan using image_pk on image img_1 (cost=0.28..48.64 rows=1 width=473) (actual time=0.065..0.065 rows=1 loops=1,024)

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

SubPlan (for Index Scan)

15. 1.018 62.098 ↑ 1.0 1 1,018

Aggregate (cost=48.09..48.10 rows=1 width=8) (actual time=0.061..0.061 rows=1 loops=1,018)

16. 2.036 61.080 ↑ 11.0 1 1,018

Bitmap Heap Scan on image iimg (cost=16.09..48.06 rows=11 width=8) (actual time=0.060..0.060 rows=1 loops=1,018)

  • Recheck Cond: (version_history && img_1.version_history)
  • Heap Blocks: exact=1,057
17. 59.044 59.044 ↑ 11.0 1 1,018

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

  • Index Cond: (version_history && img_1.version_history)
18. 1.109 193.741 ↓ 568.5 1,137 1

Sort (cost=6.65..6.65 rows=2 width=445) (actual time=193.655..193.741 rows=1,137 loops=1)

  • Sort Key: data1.id, data1.object_key, data1.system, data1.thumbnail, data1.image_type, 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.uploaded_by, data1.user_id, (CASE WHEN (data2.version_date > data1.version_date) THEN data2.id ELSE NULL::bigint END)
  • Sort Method: quicksort Memory: 635kB
19. 0.144 192.632 ↓ 568.5 1,137 1

Append (cost=0.00..6.64 rows=2 width=445) (actual time=73.338..192.632 rows=1,137 loops=1)

20. 50.110 140.527 ↓ 127.0 127 1

Nested Loop Left Join (cost=0.00..2.54 rows=1 width=445) (actual time=73.338..140.527 rows=127 loops=1)

  • Join Filter: (data1.version_history && data2.version_history)
  • Rows Removed by Join Filter: 128,270
21. 1.644 1.644 ↓ 127.0 127 1

CTE Scan on data1 (cost=0.00..2.50 rows=1 width=437) (actual time=0.217..1.644 rows=127 loops=1)

  • Filter: (deleted IS NULL)
22. 88.773 88.773 ↓ 1,010.0 1,010 127

CTE Scan on data2 (cost=0.00..0.02 rows=1 width=48) (actual time=0.001..0.699 rows=1,010 loops=127)

23. 37.651 51.961 ↓ 1,010.0 1,010 1

Nested Loop Left Join (cost=0.00..4.08 rows=1 width=445) (actual time=0.065..51.961 rows=1,010 loops=1)

  • Join Filter: (data1_1.version_history && data2_1.version_history)
  • Rows Removed by Join Filter: 128,270
  • Filter: (data1_1.id IS NULL)
24. 0.170 0.170 ↓ 1,010.0 1,010 1

CTE Scan on data2 data2_1 (cost=0.00..0.02 rows=1 width=437) (actual time=0.000..0.170 rows=1,010 loops=1)

25. 14.140 14.140 ↓ 1.0 127 1,010

CTE Scan on data1 data1_1 (cost=0.00..2.50 rows=125 width=40) (actual time=0.000..0.014 rows=127 loops=1,010)

Planning time : 0.914 ms
Execution time : 194.819 ms