explain.depesz.com

PostgreSQL's explain analyze made readable

Result: CBEc : Optimization for: Optimization for: plan #FBp9; plan #AsGu

Settings

Optimization path:

# exclusive inclusive rows x rows loops node
1. 0.023 5,254.522 ↑ 1.0 25 1

Limit (cost=14,906.08..127,264.22 rows=25 width=1,704) (actual time=139.335..5,254.522 rows=25 loops=1)

2. 0.251 5,254.499 ↑ 179,901.1 25 1

Nested Loop Left Join (cost=14,906.08..20,213,368,861.33 rows=4,497,528 width=1,704) (actual time=139.334..5,254.499 rows=25 loops=1)

3. 0.104 5,253.798 ↑ 89,950.6 25 1

Nested Loop Left Join (cost=14,905.38..20,198,269,394.32 rows=2,248,764 width=1,665) (actual time=139.281..5,253.798 rows=25 loops=1)

4. 0.085 1,583.994 ↑ 44,975.3 25 1

Nested Loop Left Join (cost=118.75..3,572,358,831.10 rows=1,124,382 width=1,633) (actual time=48.387..1,583.994 rows=25 loops=1)

5. 0.090 1,583.659 ↑ 44,975.3 25 1

Nested Loop Left Join (cost=118.33..3,570,585,118.50 rows=1,124,382 width=1,625) (actual time=48.364..1,583.659 rows=25 loops=1)

6. 0.077 1,581.344 ↑ 22,487.6 25 1

Nested Loop Left Join (cost=114.54..3,566,060,824.12 rows=562,191 width=1,593) (actual time=48.260..1,581.344 rows=25 loops=1)

7. 0.067 1,581.242 ↑ 22,487.6 25 1

Nested Loop Left Join (cost=114.12..3,565,759,293.80 rows=562,191 width=1,575) (actual time=48.252..1,581.242 rows=25 loops=1)

8. 0.121 1,580.975 ↑ 22,487.6 25 1

Nested Loop Left Join (cost=113.57..3,561,069,417.31 rows=562,191 width=1,559) (actual time=48.237..1,580.975 rows=25 loops=1)

9. 1.624 1,580.329 ↑ 22,487.6 25 1

Nested Loop Left Join (cost=103.28..3,555,269,257.06 rows=562,191 width=1,527) (actual time=48.206..1,580.329 rows=25 loops=1)

  • Join Filter: (ua.id = co.company_owner_id)
  • Rows Removed by Join Filter: 2,425
10. 0.090 1,578.280 ↑ 22,487.6 25 1

Nested Loop Left Join (cost=103.28..3,553,506,744.70 rows=562,191 width=1,511) (actual time=47.967..1,578.280 rows=25 loops=1)

11. 0.083 3.365 ↑ 22,487.6 25 1

Nested Loop Left Join (cost=102.86..3,550,261.58 rows=562,191 width=1,463) (actual time=1.072..3.365 rows=25 loops=1)

12. 0.868 3.032 ↑ 22,477.2 25 1

Nested Loop Left Join (cost=102.43..3,267,522.57 rows=561,929 width=1,394) (actual time=1.054..3.032 rows=25 loops=1)

  • Join Filter: (c.id = pc.contact_id)
  • Rows Removed by Join Filter: 7,350
13. 0.131 0.689 ↑ 22,477.2 25 1

Nested Loop (cost=0.84..325,720.01 rows=561,929 width=1,386) (actual time=0.054..0.689 rows=25 loops=1)

14. 0.283 0.283 ↑ 22,482.4 25 1

Index Scan using contact_insert_timestamp_desc__idx on contact c (cost=0.42..66,147.43 rows=562,059 width=1,347) (actual time=0.014..0.283 rows=25 loops=1)

  • Filter: ((deleted_timestamp IS NULL) AND ((board >= 1) OR (board IS NULL)))
  • Rows Removed by Filter: 188
15. 0.275 0.275 ↑ 1.0 1 25

Index Scan using company__pkey on company co (cost=0.42..0.46 rows=1 width=43) (actual time=0.011..0.011 rows=1 loops=25)

  • Index Cond: (id = c.company_id)
  • Filter: (deleted_timestamp IS NULL)
16. 0.569 1.475 ↑ 1.2 294 25

Materialize (cost=101.59..110.31 rows=349 width=12) (actual time=0.033..0.059 rows=294 loops=25)

17. 0.039 0.906 ↑ 1.2 294 1

Subquery Scan on pc (cost=101.59..108.57 rows=349 width=12) (actual time=0.810..0.906 rows=294 loops=1)

18. 0.201 0.867 ↑ 1.2 294 1

HashAggregate (cost=101.59..105.08 rows=349 width=12) (actual time=0.809..0.867 rows=294 loops=1)

  • Group Key: position_description.contact_id
19. 0.666 0.666 ↓ 1.0 421 1

Seq Scan on position_description (cost=0.00..99.49 rows=420 width=4) (actual time=0.010..0.666 rows=421 loops=1)

  • Filter: ((floated_job = 0) AND (position_category = 1))
  • Rows Removed by Filter: 146
20. 0.250 0.250 ↑ 1.0 1 25

Index Scan using contact_extension_pkey on contact_extension ce (cost=0.42..0.50 rows=1 width=73) (actual time=0.010..0.010 rows=1 loops=25)

  • Index Cond: (c.id = contact_id)
21. 0.175 1,574.825 ↓ 0.0 0 25

GroupAggregate (cost=0.42..6,314.48 rows=1 width=52) (actual time=62.993..62.993 rows=0 loops=25)

  • Group Key: col.contact_id
22. 0.171 1,574.650 ↓ 0.0 0 25

Nested Loop (cost=0.42..6,314.45 rows=1 width=57) (actual time=52.682..62.986 rows=0 loops=25)

23. 1,574.325 1,574.325 ↓ 0.0 0 25

Seq Scan on contact_location col (cost=0.00..6,311.81 rows=1 width=8) (actual time=52.670..62.973 rows=0 loops=25)

  • Filter: (contact_id = c.id)
  • Rows Removed by Filter: 334,449
24. 0.154 0.154 ↑ 1.0 1 11

Index Scan using company_location__pkey on company_location cl (cost=0.42..2.64 rows=1 width=57) (actual time=0.014..0.014 rows=1 loops=11)

  • Index Cond: (id = col.company_location_id)
25. 0.243 0.425 ↑ 2.2 97 25

Materialize (cost=0.00..44.09 rows=209 width=20) (actual time=0.002..0.017 rows=97 loops=25)

26. 0.182 0.182 ↑ 2.2 97 1

Seq Scan on user_account ua (cost=0.00..43.05 rows=209 width=20) (actual time=0.008..0.182 rows=97 loops=1)

  • Filter: ((deleted_timestamp IS NULL) AND (system_admin = 0))
  • Rows Removed by Filter: 54
27. 0.275 0.525 ↑ 1.0 1 25

Aggregate (cost=10.28..10.30 rows=1 width=32) (actual time=0.021..0.021 rows=1 loops=25)

28. 0.250 0.250 ↑ 10.0 1 25

Index Scan using client_account_pkey on user_account ua_1 (cost=0.15..10.26 rows=10 width=16) (actual time=0.008..0.010 rows=1 loops=25)

  • Index Cond: (id = ANY (c.contact_owner_ids))
29. 0.025 0.200 ↓ 0.0 0 25

GroupAggregate (cost=0.55..8.32 rows=1 width=20) (actual time=0.008..0.008 rows=0 loops=25)

  • Group Key: pd.contact_id
30. 0.050 0.175 ↓ 0.0 0 25

Nested Loop (cost=0.55..8.29 rows=2 width=10) (actual time=0.007..0.007 rows=0 loops=25)

31. 0.125 0.125 ↓ 0.0 0 25

Index Scan using position_description__contact_id__fkey on position_description pd (cost=0.28..2.50 rows=1 width=14) (actual time=0.005..0.005 rows=0 loops=25)

  • Index Cond: (contact_id = c.id)
  • Filter: (position_category = 1)
32. 0.000 0.000 ↓ 0.0 0

Index Scan using index_pc_pd on position_candidate pc_1 (cost=0.28..5.74 rows=5 width=8) (never executed)

  • Index Cond: (position_description_id = pd.id)
33. 0.025 0.025 ↓ 0.0 0 25

Index Scan using contact__pkey on contact (cost=0.42..0.54 rows=1 width=26) (actual time=0.001..0.001 rows=0 loops=25)

  • Index Cond: (id = c.report_to)
  • Filter: (deleted_timestamp IS NULL)
34. 0.350 2.225 ↑ 2.0 1 25

GroupAggregate (cost=3.78..8.01 rows=2 width=36) (actual time=0.089..0.089 rows=1 loops=25)

  • Group Key: ci.contact_id
35. 0.875 1.875 ↑ 1.0 2 25

Hash Join (cost=3.78..7.97 rows=2 width=19) (actual time=0.048..0.075 rows=2 loops=25)

  • Hash Cond: (v.id = ci.industry_id)
36. 0.600 0.600 ↑ 1.0 172 24

Seq Scan on vertical v (cost=0.00..3.72 rows=172 width=19) (actual time=0.007..0.025 rows=172 loops=24)

37. 0.075 0.400 ↑ 1.0 2 25

Hash (cost=3.76..3.76 rows=2 width=8) (actual time=0.016..0.016 rows=2 loops=25)

  • Buckets: 1,024 Batches: 1 Memory Usage: 9kB
38. 0.325 0.325 ↑ 1.0 2 25

Index Scan using contact_industry_contact_id_idx on contact_industry ci (cost=0.42..3.76 rows=2 width=8) (actual time=0.010..0.013 rows=2 loops=25)

  • Index Cond: (contact_id = c.id)
39. 0.025 0.250 ↓ 0.0 0 25

GroupAggregate (cost=0.42..1.56 rows=1 width=12) (actual time=0.010..0.010 rows=0 loops=25)

  • Group Key: candidate.contact_id
40. 0.225 0.225 ↓ 0.0 0 25

Index Only Scan using candidate__contact_id__fkey on candidate (cost=0.42..1.54 rows=1 width=4) (actual time=0.009..0.009 rows=0 loops=25)

  • Index Cond: (contact_id = c.id)
  • Heap Fetches: 2
41. 0.150 3,669.700 ↑ 2.0 1 25

GroupAggregate (cost=14,786.63..14,786.67 rows=2 width=36) (actual time=146.788..146.788 rows=1 loops=25)

  • Group Key: contact_functional_expertise.contact_id
42. 0.250 3,669.550 ↑ 2.0 1 25

Sort (cost=14,786.63..14,786.63 rows=2 width=520) (actual time=146.782..146.782 rows=1 loops=25)

  • Sort Key: contact_functional_expertise.contact_id
  • Sort Method: quicksort Memory: 25kB
43. 0.243 3,669.300 ↑ 2.0 1 25

Hash Join (cost=14,785.35..14,786.62 rows=2 width=520) (actual time=146.771..146.772 rows=1 loops=25)

  • Hash Cond: (fex.id = contact_functional_expertise.functional_expertise_id)
44. 0.132 0.132 ↑ 1.8 10 22

Seq Scan on functional_expertise fex (cost=0.00..1.18 rows=18 width=520) (actual time=0.005..0.006 rows=10 loops=22)

45. 0.075 3,668.925 ↑ 2.0 1 25

Hash (cost=14,785.33..14,785.33 rows=2 width=8) (actual time=146.757..146.757 rows=1 loops=25)

  • Buckets: 1,024 Batches: 1 Memory Usage: 9kB
46. 0.075 3,668.850 ↑ 2.0 1 25

Unique (cost=14,785.30..14,785.31 rows=2 width=8) (actual time=146.753..146.754 rows=1 loops=25)

47. 0.250 3,668.775 ↑ 2.0 1 25

Sort (cost=14,785.30..14,785.30 rows=2 width=8) (actual time=146.751..146.751 rows=1 loops=25)

  • Sort Key: contact_functional_expertise.functional_expertise_id
  • Sort Method: quicksort Memory: 25kB
48. 3,668.525 3,668.525 ↑ 2.0 1 25

Seq Scan on contact_functional_expertise (cost=0.00..14,785.29 rows=2 width=8) (actual time=146.120..146.741 rows=1 loops=25)

  • Filter: (contact_id = c.id)
  • Rows Removed by Filter: 783,400
49. 0.025 0.450 ↓ 0.0 0 25

GroupAggregate (cost=0.70..6.59 rows=2 width=36) (actual time=0.018..0.018 rows=0 loops=25)

  • Group Key: cgc.contact_id
50. 0.051 0.425 ↓ 0.0 0 25

Nested Loop (cost=0.70..6.55 rows=2 width=25) (actual time=0.017..0.017 rows=0 loops=25)

51. 0.325 0.325 ↓ 0.0 0 25

Index Only Scan using contact_group_contact_idx on contact_group_contact cgc (cost=0.42..1.56 rows=2 width=8) (actual time=0.013..0.013 rows=0 loops=25)

  • Index Cond: (contact_id = c.id)
  • Heap Fetches: 1
52. 0.049 0.049 ↑ 1.0 1 1

Index Scan using contact_group_pkey on contact_group cg (cost=0.28..2.49 rows=1 width=29) (actual time=0.049..0.049 rows=1 loops=1)

  • Index Cond: (id = cgc.contact_group_id)
Planning time : 4.726 ms
Execution time : 5,255.300 ms