explain.depesz.com

PostgreSQL's explain analyze made readable

Result: Lg8D

Settings
# exclusive inclusive rows x rows loops node
1. 0.012 13,033.853 ↑ 1.0 20 1

Limit (cost=568.28..3,956.53 rows=20 width=8,541) (actual time=13,033.487..13,033.853 rows=20 loops=1)

2. 276.790 13,033.841 ↑ 163.2 20 1

Hash Join (cost=568.28..553,530.60 rows=3,264 width=8,541) (actual time=13,033.486..13,033.841 rows=20 loops=1)

  • Hash Cond: (files.public_id = file_buckets_join.file_public_id)
3. 729.252 12,754.454 ↑ 1.0 1,683,329 1

Hash Left Join (cost=154.07..494,323.71 rows=1,687,902 width=3,763) (actual time=0.944..12,754.454 rows=1,683,329 loops=1)

  • Hash Cond: (files.deleted_by = users_deleted_by.id)
4. 805.251 12,025.020 ↑ 1.0 1,683,329 1

Hash Join (cost=117.12..489,855.36 rows=1,687,902 width=3,752) (actual time=0.752..12,025.020 rows=1,683,329 loops=1)

  • Hash Cond: (files.updated_by = users_updated_by.id)
5. 841.262 11,219.553 ↑ 1.0 1,683,329 1

Hash Join (cost=80.18..485,362.73 rows=1,687,902 width=3,741) (actual time=0.529..11,219.553 rows=1,683,329 loops=1)

  • Hash Cond: (files.created_by = users_created_by.id)
6. 740.497 10,378.101 ↑ 1.0 1,683,329 1

Merge Left Join (cost=43.24..480,870.11 rows=1,687,902 width=3,726) (actual time=0.330..10,378.101 rows=1,683,329 loops=1)

  • Merge Cond: (files.public_id = f.public_id)
7. 986.277 9,637.315 ↑ 1.0 1,683,329 1

Merge Left Join (cost=27.07..476,634.15 rows=1,687,902 width=1,446) (actual time=0.038..9,637.315 rows=1,683,329 loops=1)

  • Merge Cond: (files.public_id = files_external_data.file_public_id)
8. 1,020.542 7,471.198 ↑ 1.0 1,683,329 1

Merge Join (cost=26.64..387,501.42 rows=1,687,902 width=1,398) (actual time=0.028..7,471.198 rows=1,683,329 loops=1)

  • Merge Cond: (file_public_attributes.file_public_id = files.public_id)
9. 1,171.270 4,508.829 ↑ 1.0 1,727,808 1

Merge Join (cost=13.95..233,778.40 rows=1,734,354 width=1,188) (actual time=0.018..4,508.829 rows=1,727,808 loops=1)

  • Merge Cond: (file_public_attributes.file_public_id = file_curations.file_public_id)
10. 2,443.300 2,443.300 ↑ 1.0 1,740,956 1

Index Scan using file_public_attributes_file_public_id_index on file_public_attributes (cost=0.43..131,030.10 rows=1,747,502 width=979) (actual time=0.008..2,443.300 rows=1,740,956 loops=1)

11. 894.259 894.259 ↑ 1.0 1,727,808 1

Index Scan using file_curations_file_public_id_index on file_curations (cost=0.43..76,703.49 rows=1,734,354 width=209) (actual time=0.007..894.259 rows=1,727,808 loops=1)

12. 1,941.827 1,941.827 ↑ 1.0 1,683,380 1

Index Scan using files_public_id_index on files (cost=0.43..128,443.64 rows=1,687,902 width=210) (actual time=0.007..1,941.827 rows=1,683,380 loops=1)

  • Filter: ((deleted_at IS NULL) AND (version_id IS NULL))
  • Rows Removed by Filter: 44251
13. 1,179.840 1,179.840 ↑ 1.0 1,716,176 1

Index Scan using files_external_data_file_public_id_unique on files_external_data (cost=0.43..63,847.18 rows=1,722,722 width=52) (actual time=0.007..1,179.840 rows=1,716,176 loops=1)

14. 0.000 0.289 ↓ 0.0 0 1

Unique (cost=16.17..16.18 rows=1 width=2,830) (actual time=0.289..0.289 rows=0 loops=1)

15. 0.004 0.289 ↓ 0.0 0 1

Sort (cost=16.17..16.18 rows=1 width=2,830) (actual time=0.289..0.289 rows=0 loops=1)

  • Sort Key: f.public_id
  • Sort Method: quicksort Memory: 25kB
16. 0.000 0.285 ↓ 0.0 0 1

Nested Loop Left Join (cost=1.40..16.16 rows=1 width=2,830) (actual time=0.285..0.285 rows=0 loops=1)

  • Join Filter: (fv_title.file_version_id = fv.id)
17. 0.000 0.285 ↓ 0.0 0 1

Nested Loop Left Join (cost=1.40..14.85 rows=1 width=1,768) (actual time=0.285..0.285 rows=0 loops=1)

  • Join Filter: (fv_subtext.file_version_id = fv.id)
18. 0.000 0.285 ↓ 0.0 0 1

Nested Loop Left Join (cost=1.40..13.54 rows=1 width=1,252) (actual time=0.285..0.285 rows=0 loops=1)

  • Join Filter: (fv_alternative_text.file_version_id = fv.id)
19. 0.000 0.285 ↓ 0.0 0 1

Nested Loop (cost=1.40..12.23 rows=1 width=736) (actual time=0.285..0.285 rows=0 loops=1)

  • Join Filter: (fv.id = fv_caption.file_version_id)
20. 0.000 0.285 ↓ 0.0 0 1

Nested Loop (cost=1.40..10.91 rows=1 width=220) (actual time=0.285..0.285 rows=0 loops=1)

21. 0.000 0.285 ↓ 0.0 0 1

Nested Loop (cost=1.13..10.62 rows=1 width=209) (actual time=0.285..0.285 rows=0 loops=1)

22. 0.001 0.285 ↓ 0.0 0 1

Nested Loop (cost=0.85..10.33 rows=1 width=198) (actual time=0.285..0.285 rows=0 loops=1)

23. 0.000 0.284 ↓ 0.0 0 1

Nested Loop (cost=0.43..9.70 rows=1 width=198) (actual time=0.284..0.284 rows=0 loops=1)

24. 0.284 0.284 ↓ 0.0 0 1

Seq Scan on file_versions fv (cost=0.00..1.25 rows=1 width=8) (actual time=0.284..0.284 rows=0 loops=1)

  • Filter: ((version_id IS NULL) AND (entity_type = '-1'::integer) AND (entity_id = '-1'::integer))
  • Rows Removed by Filter: 17
25. 0.000 0.000 ↓ 0.0 0

Index Scan using files_pkey on files f (cost=0.43..8.45 rows=1 width=194) (never executed)

  • Index Cond: (id = fv.file_id)
26. 0.000 0.000 ↓ 0.0 0

Index Only Scan using file_public_attributes_file_public_id_index on file_public_attributes fpa (cost=0.43..0.62 rows=1 width=4) (never executed)

  • Index Cond: (file_public_id = f.public_id)
  • Heap Fetches: 0
27. 0.000 0.000 ↓ 0.0 0

Index Scan using users_pkey on users users_created_by_1 (cost=0.28..0.29 rows=1 width=19) (never executed)

  • Index Cond: (id = f.created_by)
28. 0.000 0.000 ↓ 0.0 0

Index Scan using users_pkey on users users_updated_by_1 (cost=0.28..0.29 rows=1 width=19) (never executed)

  • Index Cond: (id = f.updated_by)
29. 0.000 0.000 ↓ 0.0 0

Seq Scan on file_version_values fv_caption (cost=0.00..1.30 rows=1 width=520) (never executed)

  • Filter: (type = 200)
30. 0.000 0.000 ↓ 0.0 0

Seq Scan on file_version_values fv_alternative_text (cost=0.00..1.30 rows=1 width=520) (never executed)

  • Filter: (type = 300)
31. 0.000 0.000 ↓ 0.0 0

Seq Scan on file_version_values fv_subtext (cost=0.00..1.30 rows=1 width=520) (never executed)

  • Filter: (type = 400)
32. 0.000 0.000 ↓ 0.0 0

Seq Scan on file_version_values fv_title (cost=0.00..1.30 rows=1 width=520) (never executed)

  • Filter: (type = 500)
33. 0.115 0.190 ↑ 1.0 753 1

Hash (cost=27.53..27.53 rows=753 width=19) (actual time=0.190..0.190 rows=753 loops=1)

  • Buckets: 1024 Batches: 1 Memory Usage: 47kB
34. 0.075 0.075 ↑ 1.0 753 1

Seq Scan on users users_created_by (cost=0.00..27.53 rows=753 width=19) (actual time=0.002..0.075 rows=753 loops=1)

35. 0.087 0.216 ↑ 1.0 753 1

Hash (cost=27.53..27.53 rows=753 width=19) (actual time=0.216..0.216 rows=753 loops=1)

  • Buckets: 1024 Batches: 1 Memory Usage: 47kB
36. 0.129 0.129 ↑ 1.0 753 1

Seq Scan on users users_updated_by (cost=0.00..27.53 rows=753 width=19) (actual time=0.013..0.129 rows=753 loops=1)

37. 0.104 0.182 ↑ 1.0 753 1

Hash (cost=27.53..27.53 rows=753 width=19) (actual time=0.182..0.182 rows=753 loops=1)

  • Buckets: 1024 Batches: 1 Memory Usage: 47kB
38. 0.078 0.078 ↑ 1.0 753 1

Seq Scan on users users_deleted_by (cost=0.00..27.53 rows=753 width=19) (actual time=0.003..0.078 rows=753 loops=1)

39. 0.403 2.597 ↑ 1.4 2,414 1

Hash (cost=371.97..371.97 rows=3,379 width=4) (actual time=2.597..2.597 rows=2,414 loops=1)

  • Buckets: 4096 Batches: 1 Memory Usage: 117kB
40. 2.194 2.194 ↑ 1.4 2,414 1

Index Scan using file_buckets_join_bucket_id_index on file_buckets_join (cost=0.43..371.97 rows=3,379 width=4) (actual time=0.748..2.194 rows=2,414 loops=1)

  • Index Cond: (bucket_id = 1)
41.          

SubPlan (for Hash Join)

42. 0.000 0.000 ↓ 0.0 0 20

Index Scan using users_pkey on users (cost=0.28..8.29 rows=1 width=15) (actual time=0.000..0.000 rows=0 loops=20)

  • Index Cond: (id = file_public_attributes.curated_by)
43. 0.000 0.000 ↓ 0.0 0 20

Index Scan using users_pkey on users users_1 (cost=0.28..8.29 rows=1 width=15) (actual time=0.000..0.000 rows=0 loops=20)

  • Index Cond: (id = file_curations.comment_by)
Planning time : 23.417 ms
Execution time : 13,034.091 ms