explain.depesz.com

PostgreSQL's explain analyze made readable

Result: wtgC

Settings
# exclusive inclusive rows x rows loops node
1. 0.669 20,555.454 ↑ 11.0 10 1

Nested Loop Left Join (cost=14,037.31..158,178.32 rows=110 width=315) (actual time=18,862.115..20,555.454 rows=10 loops=1)

2. 0.027 20,553.565 ↑ 1.0 10 1

Nested Loop Left Join (cost=13,774.48..156,004.20 rows=10 width=246) (actual time=18,860.515..20,553.565 rows=10 loops=1)

3. 0.084 20,553.208 ↑ 1.0 10 1

Hash Left Join (cost=13,774.05..155,872.32 rows=10 width=238) (actual time=18,860.185..20,553.208 rows=10 loops=1)

  • Hash Cond: (p.id = shortlisted_by_business.shortlistable_id)
4. 0.047 20,553.117 ↑ 1.0 10 1

Hash Left Join (cost=13,762.16..155,860.41 rows=10 width=230) (actual time=18,860.170..20,553.117 rows=10 loops=1)

  • Hash Cond: (p.id = liked_by_business.likeable_id)
5. 0.360 20,553.053 ↑ 1.0 10 1

Nested Loop (cost=13,749.35..155,847.56 rows=10 width=222) (actual time=18,860.140..20,553.053 rows=10 loops=1)

6. 0.012 20,552.583 ↑ 1.0 10 1

Limit (cost=13,744.08..155,714.61 rows=10 width=190) (actual time=18,860.102..20,552.583 rows=10 loops=1)

7. 0.166 20,552.571 ↑ 144.2 10 1

Nested Loop (cost=13,744.08..20,485,894.35 rows=1,442 width=190) (actual time=18,860.101..20,552.571 rows=10 loops=1)

8. 9,396.683 20,552.355 ↑ 144.2 10 1

Nested Loop (cost=13,743.79..20,485,857.19 rows=1,442 width=92) (actual time=18,860.042..20,552.355 rows=10 loops=1)

  • Join Filter: (p.id = privacy_settings.privatable_id)
  • Rows Removed by Join Filter: 41,580,045
9. 19.418 19.418 ↑ 14.0 106 1

Index Scan Backward using index_products_on_created_at on products p (cost=0.42..124,739.45 rows=1,485 width=85) (actual time=2.605..19.418 rows=106 loops=1)

  • Filter: ((deleted_at IS NULL) AND (business_id = 19,309))
  • Rows Removed by Filter: 21,901
10. 10,822.473 11,136.254 ↓ 1.0 392,265 106

Materialize (cost=13,743.37..8,896,778.46 rows=388,335 width=11) (actual time=0.003..105.059 rows=392,265 loops=106)

11. 303.552 313.781 ↓ 1.0 392,468 1

Index Scan using index_privacy_settings_on_privatable_id_and_privatable_type on privacy_settings (cost=13,743.37..8,892,939.78 rows=388,335 width=11) (actual time=0.011..313.781 rows=392,468 loops=1)

  • Index Cond: ((privatable_type)::text = 'Product'::text)
  • Filter: (((visibility)::text = 'public'::text) OR (permit_all_connections AND (hashed SubPlan 4)) OR (3512 = ANY (business_ids)) OR (SubPlan 5))
  • Rows Removed by Filter: 7,096
12.          

SubPlan (for Index Scan)

13. 1.621 10.229 ↑ 1.2 4,164 1

Append (cost=0.42..13,730.50 rows=4,976 width=4) (actual time=0.017..10.229 rows=4,164 loops=1)

14. 0.375 0.375 ↓ 13.3 292 1

Index Scan using index_connections_on_requestee_id on connections (cost=0.42..131.65 rows=22 width=4) (actual time=0.016..0.375 rows=292 loops=1)

  • Index Cond: (requestee_id = 19,309)
  • Filter: connected
  • Rows Removed by Filter: 45
15. 8.233 8.233 ↑ 1.3 3,872 1

Index Scan using index_connections_on_requester_id on connections connections_1 (cost=0.42..13,524.21 rows=4,954 width=4) (actual time=0.020..8.233 rows=3,872 loops=1)

  • Index Cond: (requester_id = 19,309)
  • Filter: connected
  • Rows Removed by Filter: 5,829
16. 0.000 0.000 ↓ 0.0 0 7,098

Index Scan using index_user_groups_on_group_id on user_groups (cost=0.28..44.61 rows=47 width=4) (actual time=0.000..0.000 rows=0 loops=7,098)

  • Index Cond: (group_id = ANY (privacy_settings.group_ids))
17. 0.018 0.050 ↑ 1.0 1 10

Materialize (cost=0.29..8.32 rows=1 width=48) (actual time=0.004..0.005 rows=1 loops=10)

18. 0.032 0.032 ↑ 1.0 1 1

Index Scan using businesses_pkey on businesses b (cost=0.29..8.31 rows=1 width=48) (actual time=0.031..0.032 rows=1 loops=1)

  • Index Cond: (id = 19,309)
19. 0.000 0.110 ↑ 1.0 1 10

Index Scan using images_pkey on images coverimage (cost=5.27..13.29 rows=1 width=40) (actual time=0.011..0.011 rows=1 loops=10)

  • Index Cond: (id = (SubPlan 3))
20.          

SubPlan (for Index Scan)

21. 0.060 0.250 ↑ 1.0 1 10

Limit (cost=0.42..4.84 rows=1 width=4) (actual time=0.025..0.025 rows=1 loops=10)

22. 0.190 0.190 ↑ 3.0 1 10

Index Scan using index_image_uploads_on_imageable_id_and_imageable_type on image_uploads (cost=0.42..13.68 rows=3 width=4) (actual time=0.019..0.019 rows=1 loops=10)

  • Index Cond: ((imageable_id = p.id) AND ((imageable_type)::text = 'Product'::text))
23. 0.004 0.017 ↑ 1.0 1 1

Hash (cost=12.80..12.80 rows=1 width=12) (actual time=0.016..0.017 rows=1 loops=1)

  • Buckets: 1,024 Batches: 1 Memory Usage: 9kB
24. 0.013 0.013 ↑ 1.0 1 1

Index Scan using index_likes_on_business_id on likes liked_by_business (cost=0.29..12.80 rows=1 width=12) (actual time=0.010..0.013 rows=1 loops=1)

  • Index Cond: (business_id = 3,512)
  • Filter: ((likeable_type)::text = 'Product'::text)
  • Rows Removed by Filter: 1
25. 0.000 0.007 ↓ 0.0 0 1

Hash (cost=11.85..11.85 rows=3 width=12) (actual time=0.007..0.007 rows=0 loops=1)

  • Buckets: 1,024 Batches: 1 Memory Usage: 8kB
26. 0.007 0.007 ↓ 0.0 0 1

Index Scan using index_shortlists_on_business_id on shortlists shortlisted_by_business (cost=0.29..11.85 rows=3 width=12) (actual time=0.006..0.007 rows=0 loops=1)

  • Index Cond: (business_id = 3,512)
  • Filter: ((shortlistable_type)::text = 'Product'::text)
27. 0.004 0.330 ↓ 0.0 0 10

Materialize (cost=0.42..131.76 rows=1 width=8) (actual time=0.033..0.033 rows=0 loops=10)

28. 0.326 0.326 ↓ 0.0 0 1

Index Scan using index_connections_on_requestee_id on connections c1 (cost=0.42..131.75 rows=1 width=8) (actual time=0.326..0.326 rows=0 loops=1)

  • Index Cond: (requestee_id = 19,309)
  • Filter: ((connected IS TRUE) AND (requester_id = 3,512))
  • Rows Removed by Filter: 337
29. 0.006 1.030 ↓ 0.0 0 10

Materialize (cost=262.83..340.99 rows=11 width=8) (actual time=0.103..0.103 rows=0 loops=10)

30. 0.011 1.024 ↓ 0.0 0 1

Bitmap Heap Scan on connections c2 (cost=262.83..340.94 rows=11 width=8) (actual time=1.024..1.024 rows=0 loops=1)

  • Recheck Cond: ((requestee_id = 3,512) AND (requester_id = 19,309))
  • Filter: (connected IS TRUE)
31. 0.098 1.013 ↓ 0.0 0 1

BitmapAnd (cost=262.83..262.83 rows=20 width=0) (actual time=1.013..1.013 rows=0 loops=1)

32. 0.324 0.324 ↑ 1.0 2,182 1

Bitmap Index Scan on index_connections_on_requestee_id (cost=0.00..53.05 rows=2,217 width=0) (actual time=0.324..0.324 rows=2,182 loops=1)

  • Index Cond: (requestee_id = 3,512)
33. 0.591 0.591 ↓ 1.1 9,778 1

Bitmap Index Scan on index_connections_on_requester_id (cost=0.00..209.52 rows=9,213 width=0) (actual time=0.591..0.591 rows=9,778 loops=1)

  • Index Cond: (requester_id = 19,309)
34.          

SubPlan (for Nested Loop Left Join)

35. 0.040 0.110 ↑ 1.0 1 10

Aggregate (cost=8.31..8.32 rows=1 width=8) (actual time=0.011..0.011 rows=1 loops=10)

36. 0.070 0.070 ↓ 0.0 0 10

Index Scan using index_likes_on_likeable_id_and_likeable_type_and_business_id on likes (cost=0.29..8.31 rows=1 width=8) (actual time=0.007..0.007 rows=0 loops=10)

  • Index Cond: ((likeable_id = p.id) AND ((likeable_type)::text = 'Product'::text))
37. 0.030 0.080 ↑ 1.0 1 10

Aggregate (cost=8.31..8.32 rows=1 width=8) (actual time=0.008..0.008 rows=1 loops=10)

38. 0.050 0.050 ↓ 0.0 0 10

Index Scan using unique_shortlists on shortlists (cost=0.29..8.30 rows=1 width=8) (actual time=0.005..0.005 rows=0 loops=10)

  • Index Cond: ((shortlistable_id = p.id) AND ((shortlistable_type)::text = 'Product'::text))
Planning time : 1.265 ms