explain.depesz.com

PostgreSQL's explain analyze made readable

Result: p24K

Settings
# exclusive inclusive rows x rows loops node
1. 0.014 41.547 ↑ 1.0 20 1

Limit (cost=21.65..398.61 rows=20 width=8,537) (actual time=2.353..41.547 rows=20 loops=1)

2. 0.160 41.533 ↑ 84,395.1 20 1

Merge Left Join (cost=21.65..31,813,084.70 rows=1,687,902 width=8,537) (actual time=2.352..41.533 rows=20 loops=1)

  • Merge Cond: (files.public_id = f.public_id)
3. 0.059 40.660 ↑ 84,395.1 20 1

Nested Loop (cost=5.48..3,696,840.93 rows=1,687,902 width=1,471) (actual time=1.710..40.660 rows=20 loops=1)

4. 0.047 33.381 ↑ 84,395.1 20 1

Nested Loop (cost=5.05..2,841,201.52 rows=1,687,902 width=1,274) (actual time=1.277..33.381 rows=20 loops=1)

  • Join Filter: (files.public_id = file_public_attributes.file_public_id)
5. 0.022 25.614 ↑ 84,395.1 20 1

Merge Left Join (cost=4.62..1,777,518.71 rows=1,687,902 width=299) (actual time=0.730..25.614 rows=20 loops=1)

  • Merge Cond: (files.public_id = files_external_data.file_public_id)
6. 0.024 25.567 ↑ 84,395.1 20 1

Nested Loop Left Join (cost=4.20..1,688,385.98 rows=1,687,902 width=251) (actual time=0.702..25.567 rows=20 loops=1)

7. 0.028 25.523 ↑ 84,395.1 20 1

Nested Loop (cost=3.92..1,194,575.70 rows=1,687,902 width=240) (actual time=0.686..25.523 rows=20 loops=1)

8. 0.058 25.435 ↑ 84,395.1 20 1

Nested Loop (cost=3.65..700,803.56 rows=1,687,902 width=229) (actual time=0.654..25.435 rows=20 loops=1)

9. 0.077 23.157 ↑ 84,395.1 20 1

Merge Join (cost=3.37..207,031.43 rows=1,687,902 width=214) (actual time=0.045..23.157 rows=20 loops=1)

  • Merge Cond: (files.public_id = file_buckets_join.file_public_id)
10. 15.841 15.841 ↑ 84,395.1 20 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.029..15.841 rows=20 loops=1)

  • Filter: ((version_id IS NULL) AND (deleted_at IS NULL))
  • Rows Removed by Filter: 330
11. 7.239 7.239 ↑ 5,007.4 349 1

Index Only Scan using file_buckets_join_file_public_id_index on file_buckets_join (cost=0.43..53,126.90 rows=1,747,575 width=4) (actual time=0.009..7.239 rows=349 loops=1)

  • Heap Fetches: 349
12. 2.220 2.220 ↑ 1.0 1 20

Index Scan using users_pkey on users users_created_by (cost=0.28..0.29 rows=1 width=19) (actual time=0.111..0.111 rows=1 loops=20)

  • Index Cond: (id = files.created_by)
13. 0.060 0.060 ↑ 1.0 1 20

Index Scan using users_pkey on users users_updated_by (cost=0.28..0.29 rows=1 width=19) (actual time=0.003..0.003 rows=1 loops=20)

  • Index Cond: (id = files.updated_by)
14. 0.020 0.020 ↓ 0.0 0 20

Index Scan using users_pkey on users users_deleted_by (cost=0.28..0.29 rows=1 width=19) (actual time=0.001..0.001 rows=0 loops=20)

  • Index Cond: (id = files.deleted_by)
15. 0.025 0.025 ↑ 1,722,722.0 1 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.025..0.025 rows=1 loops=1)

16. 7.720 7.720 ↑ 1.0 1 20

Index Scan using file_public_attributes_file_public_id_index on file_public_attributes (cost=0.43..0.62 rows=1 width=975) (actual time=0.385..0.386 rows=1 loops=20)

  • Index Cond: (file_public_id = file_buckets_join.file_public_id)
17. 7.220 7.220 ↑ 1.0 1 20

Index Scan using file_curations_file_public_id_index on file_curations (cost=0.43..0.50 rows=1 width=209) (actual time=0.360..0.361 rows=1 loops=20)

  • Index Cond: (file_public_id = files.public_id)
18. 0.001 0.053 ↓ 0.0 0 1

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

19. 0.039 0.052 ↓ 0.0 0 1

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

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

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

  • Join Filter: (fv_title.file_version_id = fv.id)
21. 0.001 0.013 ↓ 0.0 0 1

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

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

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

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

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

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

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

25. 0.000 0.012 ↓ 0.0 0 1

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

26. 0.001 0.012 ↓ 0.0 0 1

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

27. 0.000 0.011 ↓ 0.0 0 1

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

28. 0.011 0.011 ↓ 0.0 0 1

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

  • Filter: ((version_id IS NULL) AND (entity_type = '-1'::integer) AND (entity_id = '-1'::integer))
  • Rows Removed by Filter: 17
29. 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)
30. 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
31. 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)
32. 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)
33. 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)
34. 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)
35. 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)
36. 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)
37.          

SubPlan (for Merge Left Join)

38. 0.620 0.620 ↑ 1.0 1 20

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

  • Index Cond: (id = file_public_attributes.curated_by)
39. 0.040 0.040 ↑ 1.0 1 20

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

  • Index Cond: (id = file_curations.comment_by)
Planning time : 26.781 ms
Execution time : 41.881 ms