explain.depesz.com

PostgreSQL's explain analyze made readable

Result: 8KXZ

Settings
# exclusive inclusive rows x rows loops node
1. 14.314 1,169.819 ↑ 1.0 13,978 1

Hash Left Join (cost=875.29..24,911.96 rows=14,310 width=436) (actual time=17.903..1,169.819 rows=13,978 loops=1)

  • Hash Cond: (fb_pages.id = fb_pages_users.fb_page_id)
2. 11.568 1,155.044 ↑ 1.0 13,978 1

Hash Left Join (cost=850.69..24,688.80 rows=14,310 width=233) (actual time=17.432..1,155.044 rows=13,978 loops=1)

  • Hash Cond: (distributors.fb_page_id = fb_pages.id)
3. 180.586 1,143.327 ↑ 1.0 13,978 1

Nested Loop Left Join (cost=845.14..24,610.91 rows=14,310 width=225) (actual time=17.276..1,143.327 rows=13,978 loops=1)

  • Join Filter: ((links.distributor_id = distributors.id) OR (distributors.fb_page_id = fb_posts.from_fb_profile_id))
  • Rows Removed by Join Filter: 389943
4. 10.771 822.961 ↑ 1.0 13,978 1

Hash Left Join (cost=845.14..18,967.78 rows=14,017 width=225) (actual time=17.221..822.961 rows=13,978 loops=1)

  • Hash Cond: (creatives.campaign_id = cs.id)
5. 412.385 812.155 ↑ 1.0 13,978 1

Nested Loop Left Join (cost=843.51..18,773.42 rows=14,017 width=207) (actual time=17.177..812.155 rows=13,978 loops=1)

  • Join Filter: ((links.creative_id = creatives.id) OR (fb_videos.video_id = creatives.id))
  • Rows Removed by Join Filter: 978435
6. 11.497 50.320 ↓ 1.0 13,978 1

Hash Left Join (cost=843.51..2,395.27 rows=13,967 width=207) (actual time=17.053..50.320 rows=13,978 loops=1)

  • Hash Cond: (fb_posts.object_id = fb_videos2.id)
7. 13.877 30.928 ↓ 1.0 13,978 1

Hash Full Join (cost=410.61..1,886.15 rows=13,967 width=207) (actual time=9.130..30.928 rows=13,978 loops=1)

  • Hash Cond: (fb_posts.object_id = fb_videos.id)
8. 7.930 7.930 ↑ 1.0 13,967 1

Seq Scan on fb_posts (cost=0.00..1,420.67 rows=13,967 width=122) (actual time=0.004..7.930 rows=13,967 loops=1)

9. 0.017 9.121 ↑ 40.5 25 1

Hash (cost=397.96..397.96 rows=1,012 width=93) (actual time=9.121..9.121 rows=25 loops=1)

  • Buckets: 1024 Batches: 1 Memory Usage: 12kB
10. 5.201 9.104 ↑ 40.5 25 1

Hash Right Join (cost=3.14..397.96 rows=1,012 width=93) (actual time=0.349..9.104 rows=25 loops=1)

  • Hash Cond: ((fb_videos.id)::text = "substring"(links.url, '(?:.*/(?:videos)/)(.*)/$'::text))
11. 3.590 3.590 ↑ 1.0 9,240 1

Seq Scan on fb_videos (cost=0.00..312.40 rows=9,640 width=53) (actual time=0.002..3.590 rows=9,240 loops=1)

12. 0.281 0.313 ↓ 1.2 25 1

Hash (cost=2.88..2.88 rows=21 width=40) (actual time=0.313..0.313 rows=25 loops=1)

  • Buckets: 1024 Batches: 1 Memory Usage: 11kB
13. 0.032 0.032 ↓ 1.2 25 1

Seq Scan on campaign_social_links links (cost=0.00..2.88 rows=21 width=40) (actual time=0.010..0.032 rows=25 loops=1)

  • Filter: (platform = 'facebook'::social_platforms_single)
  • Rows Removed by Filter: 50
14. 4.048 7.895 ↑ 1.0 9,240 1

Hash (cost=312.40..312.40 rows=9,640 width=8) (actual time=7.895..7.895 rows=9,240 loops=1)

  • Buckets: 16384 Batches: 1 Memory Usage: 489kB
15. 3.847 3.847 ↑ 1.0 9,240 1

Seq Scan on fb_videos fb_videos2 (cost=0.00..312.40 rows=9,640 width=8) (actual time=0.002..3.847 rows=9,240 loops=1)

16. 349.415 349.450 ↓ 1.0 70 13,978

Materialize (cost=0.00..2.00 rows=67 width=8) (actual time=0.000..0.025 rows=70 loops=13,978)

17. 0.035 0.035 ↓ 1.0 70 1

Seq Scan on creatives (cost=0.00..1.67 rows=67 width=8) (actual time=0.006..0.035 rows=70 loops=1)

18. 0.027 0.035 ↑ 1.0 28 1

Hash (cost=1.28..1.28 rows=28 width=22) (actual time=0.035..0.035 rows=28 loops=1)

  • Buckets: 1024 Batches: 1 Memory Usage: 10kB
19. 0.008 0.008 ↑ 1.0 28 1

Seq Scan on campaigns cs (cost=0.00..1.28 rows=28 width=22) (actual time=0.003..0.008 rows=28 loops=1)

20. 139.763 139.780 ↓ 1.2 28 13,978

Materialize (cost=0.00..1.34 rows=23 width=12) (actual time=0.000..0.010 rows=28 loops=13,978)

21. 0.017 0.017 ↓ 1.2 28 1

Seq Scan on distributors (cost=0.00..1.23 rows=23 width=12) (actual time=0.005..0.017 rows=28 loops=1)

22. 0.074 0.149 ↑ 1.0 158 1

Hash (cost=3.58..3.58 rows=158 width=16) (actual time=0.149..0.149 rows=158 loops=1)

  • Buckets: 1024 Batches: 1 Memory Usage: 15kB
23. 0.075 0.075 ↑ 1.0 158 1

Seq Scan on fb_pages (cost=0.00..3.58 rows=158 width=16) (actual time=0.007..0.075 rows=158 loops=1)

24. 0.023 0.461 ↓ 21.5 43 1

Hash (cost=24.57..24.57 rows=2 width=219) (actual time=0.461..0.461 rows=43 loops=1)

  • Buckets: 1024 Batches: 1 Memory Usage: 20kB
25. 0.079 0.438 ↓ 21.5 43 1

Nested Loop (cost=12.36..24.57 rows=2 width=219) (actual time=0.174..0.438 rows=43 loops=1)

  • Join Filter: (one_user.fb_page_id = fb_profiles.id)
26. 0.085 0.273 ↓ 21.5 43 1

Hash Join (cost=12.22..23.88 rows=2 width=212) (actual time=0.163..0.273 rows=43 loops=1)

  • Hash Cond: ((fb_pages_users.user_id = one_user.user_id) AND (fb_pages_users.fb_page_id = one_user.fb_page_id))
27. 0.036 0.036 ↑ 1.0 94 1

Seq Scan on fb_pages_users (cost=0.00..10.94 rows=94 width=204) (actual time=0.002..0.036 rows=94 loops=1)

28. 0.028 0.152 ↓ 1.4 43 1

Hash (cost=11.77..11.77 rows=30 width=12) (actual time=0.152..0.152 rows=43 loops=1)

  • Buckets: 1024 Batches: 1 Memory Usage: 11kB
29. 0.025 0.124 ↓ 1.4 43 1

Subquery Scan on one_user (cost=11.17..11.77 rows=30 width=12) (actual time=0.077..0.124 rows=43 loops=1)

30. 0.053 0.099 ↓ 1.4 43 1

HashAggregate (cost=11.17..11.47 rows=30 width=12) (actual time=0.076..0.099 rows=43 loops=1)

  • Group Key: fb_pages_users_1.fb_page_id
31. 0.046 0.046 ↑ 1.0 45 1

Seq Scan on fb_pages_users fb_pages_users_1 (cost=0.00..10.94 rows=45 width=12) (actual time=0.002..0.046 rows=45 loops=1)

  • Filter: ((access_token IS NOT NULL) AND can_advertise)
  • Rows Removed by Filter: 49
32. 0.086 0.086 ↑ 1.0 1 43

Index Scan using fb_profiles_pkey on fb_profiles (cost=0.14..0.33 rows=1 width=23) (actual time=0.001..0.002 rows=1 loops=43)

  • Index Cond: (id = fb_pages_users.fb_page_id)