explain.depesz.com

PostgreSQL's explain analyze made readable

Result: FBp9

Settings

Optimization(s) for this plan:

# exclusive inclusive rows x rows loops node
1. 0.023 7,049.827 ↑ 1.0 25 1

Limit (cost=14,904.88..127,260.28 rows=25 width=1,705) (actual time=202.752..7,049.827 rows=25 loops=1)

2. 0.255 7,049.804 ↑ 179,901.1 25 1

Nested Loop Left Join (cost=14,904.88..20,212,877,030.35 rows=4,497,528 width=1,705) (actual time=202.751..7,049.804 rows=25 loops=1)

3. 0.097 7,049.149 ↑ 89,950.6 25 1

Nested Loop Left Join (cost=14,904.18..20,197,777,563.34 rows=2,248,764 width=1,666) (actual time=202.707..7,049.149 rows=25 loops=1)

4. 0.080 2,251.752 ↑ 44,975.3 25 1

Nested Loop Left Join (cost=118.82..3,571,883,865.85 rows=1,124,382 width=1,634) (actual time=88.349..2,251.752 rows=25 loops=1)

5. 0.091 2,251.447 ↑ 44,975.3 25 1

Nested Loop Left Join (cost=118.40..3,570,110,153.25 rows=1,124,382 width=1,626) (actual time=88.313..2,251.447 rows=25 loops=1)

6. 0.151 2,248.756 ↑ 22,487.6 25 1

Nested Loop Left Join (cost=114.61..3,565,585,858.87 rows=562,191 width=1,594) (actual time=88.182..2,248.756 rows=25 loops=1)

7. 0.073 2,248.580 ↑ 22,487.6 25 1

Nested Loop Left Join (cost=114.19..3,565,284,328.56 rows=562,191 width=1,576) (actual time=88.174..2,248.580 rows=25 loops=1)

8. 0.104 2,248.307 ↑ 22,487.6 25 1

Nested Loop Left Join (cost=113.64..3,560,594,452.06 rows=562,191 width=1,560) (actual time=88.158..2,248.307 rows=25 loops=1)

9. 0.385 2,247.703 ↑ 22,487.6 25 1

Nested Loop Left Join (cost=103.28..3,554,754,850.98 rows=562,191 width=1,528) (actual time=88.114..2,247.703 rows=25 loops=1)

  • Join Filter: (ua.id = co.company_owner_id)
  • Rows Removed by Join Filter: 2400
10. 0.102 2,246.918 ↑ 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=87.895..2,246.918 rows=25 loops=1)

11. 0.077 4.916 ↑ 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.766..4.916 rows=25 loops=1)

12. 0.886 4.164 ↑ 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.735..4.164 rows=25 loops=1)

  • Join Filter: (c.id = pc.contact_id)
  • Rows Removed by Join Filter: 7325
13. 0.128 1.428 ↑ 22,477.2 25 1

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

14. 0.750 0.750 ↑ 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.383..0.750 rows=25 loops=1)

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

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

  • Index Cond: (id = c.company_id)
  • Filter: (deleted_timestamp IS NULL)
16. 0.624 1.850 ↑ 1.2 293 25

Materialize (cost=101.59..110.31 rows=349 width=12) (actual time=0.046..0.074 rows=293 loops=25)

17. 0.038 1.226 ↑ 1.2 293 1

Subquery Scan on pc (cost=101.59..108.57 rows=349 width=12) (actual time=1.132..1.226 rows=293 loops=1)

18. 0.195 1.188 ↑ 1.2 293 1

HashAggregate (cost=101.59..105.08 rows=349 width=12) (actual time=1.132..1.188 rows=293 loops=1)

  • Group Key: position_description.contact_id
19. 0.993 0.993 ↑ 1.0 420 1

Seq Scan on position_description (cost=0.00..99.49 rows=420 width=4) (actual time=0.017..0.993 rows=420 loops=1)

  • Filter: ((floated_job = 0) AND (position_category = 1))
  • Rows Removed by Filter: 146
20. 0.675 0.675 ↑ 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.027..0.027 rows=1 loops=25)

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

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

  • Group Key: col.contact_id
22. 0.197 2,241.725 ↓ 0.0 0 25

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

23. 2,241.275 2,241.275 ↓ 0.0 0 25

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

  • Filter: (contact_id = c.id)
  • Rows Removed by Filter: 334449
24. 0.253 0.253 ↑ 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.023..0.023 rows=1 loops=11)

  • Index Cond: (id = col.company_location_id)
25. 0.241 0.400 ↑ 1.5 96 25

Materialize (cost=0.00..42.63 rows=148 width=21) (actual time=0.002..0.016 rows=96 loops=25)

26. 0.159 0.159 ↑ 1.5 96 1

Seq Scan on user_account ua (cost=0.00..41.89 rows=148 width=21) (actual time=0.015..0.159 rows=96 loops=1)

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

Aggregate (cost=10.35..10.37 rows=1 width=32) (actual time=0.020..0.020 rows=1 loops=25)

28. 0.200 0.200 ↑ 10.0 1 25

Index Scan using client_account_pkey on user_account ua_1 (cost=0.14..10.33 rows=10 width=17) (actual time=0.008..0.008 rows=1 loops=25)

  • Index Cond: (id = ANY (c.contact_owner_ids))
29. 0.050 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.025 0.150 ↓ 0.0 0 25

Nested Loop (cost=0.55..8.29 rows=2 width=10) (actual time=0.006..0.006 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.600 ↑ 2.0 1 25

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

  • Group Key: ci.contact_id
35. 1.025 2.250 ↓ 1.5 3 25

Hash Join (cost=3.78..7.97 rows=2 width=19) (actual time=0.061..0.090 rows=3 loops=25)

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

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

37. 0.100 0.600 ↓ 1.5 3 25

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

  • Buckets: 1024 Batches: 1 Memory Usage: 9kB
38. 0.500 0.500 ↓ 1.5 3 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.017..0.020 rows=3 loops=25)

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

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

  • Group Key: candidate.contact_id
40. 0.175 0.175 ↓ 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.007..0.007 rows=0 loops=25)

  • Index Cond: (contact_id = c.id)
  • Heap Fetches: 0
41. 0.250 4,797.300 ↑ 2.0 1 25

GroupAggregate (cost=14,785.35..14,786.65 rows=2 width=36) (actual time=191.892..191.892 rows=1 loops=25)

  • Group Key: contact_functional_expertise.contact_id
42. 0.250 4,797.050 ↑ 2.0 1 25

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

  • Hash Cond: (fex.id = contact_functional_expertise.functional_expertise_id)
43. 0.225 0.225 ↑ 1.8 10 25

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

44. 0.075 4,796.575 ↑ 2.0 1 25

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

  • Buckets: 1024 Batches: 1 Memory Usage: 9kB
45. 0.100 4,796.500 ↑ 2.0 1 25

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

46. 0.275 4,796.400 ↑ 2.0 1 25

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

  • Sort Key: contact_functional_expertise.functional_expertise_id
  • Sort Method: quicksort Memory: 25kB
47. 4,796.125 4,796.125 ↑ 2.0 1 25

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

  • Filter: (contact_id = c.id)
  • Rows Removed by Filter: 783400
48. 0.050 0.400 ↓ 0.0 0 25

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

  • Group Key: cgc.contact_id
49. 0.025 0.350 ↓ 0.0 0 25

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

50. 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: 0
51. 0.000 0.000 ↓ 0.0 0

Index Scan using contact_group_pkey on contact_group cg (cost=0.28..2.49 rows=1 width=29) (never executed)

  • Index Cond: (id = cgc.contact_group_id)
Planning time : 17.806 ms
Execution time : 7,050.664 ms