explain.depesz.com

PostgreSQL's explain analyze made readable

Result: AsGu : Optimization for: plan #FBp9

Settings

Optimization path:

Optimization(s) for this plan:

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

Limit (cost=14,906.12..127,263.89 rows=25 width=1,705) (actual time=263.563..5,029.348 rows=25 loops=1)

2. 0.252 5,029.325 ↑ 179,901.1 25 1

Nested Loop Left Join (cost=14,906.12..20,213,303,584.81 rows=4,497,528 width=1,705) (actual time=263.562..5,029.325 rows=25 loops=1)

3. 0.089 5,028.723 ↑ 89,950.6 25 1

Nested Loop Left Join (cost=14,905.42..20,198,204,117.81 rows=2,248,764 width=1,666) (actual time=263.512..5,028.723 rows=25 loops=1)

4. 0.077 1,553.034 ↑ 44,975.3 25 1

Nested Loop Left Join (cost=118.79..3,572,293,554.59 rows=1,124,382 width=1,634) (actual time=86.036..1,553.034 rows=25 loops=1)

5. 0.077 1,552.732 ↑ 44,975.3 25 1

Nested Loop Left Join (cost=118.36..3,570,519,841.98 rows=1,124,382 width=1,626) (actual time=86.015..1,552.732 rows=25 loops=1)

6. 0.073 1,550.355 ↑ 22,487.6 25 1

Nested Loop Left Join (cost=114.58..3,565,995,547.60 rows=562,191 width=1,594) (actual time=85.907..1,550.355 rows=25 loops=1)

7. 0.079 1,550.257 ↑ 22,487.6 25 1

Nested Loop Left Join (cost=114.15..3,565,694,017.29 rows=562,191 width=1,576) (actual time=85.900..1,550.257 rows=25 loops=1)

8. 0.097 1,549.978 ↑ 22,487.6 25 1

Nested Loop Left Join (cost=113.60..3,561,004,140.79 rows=562,191 width=1,560) (actual time=85.883..1,549.978 rows=25 loops=1)

9. 0.376 1,549.381 ↑ 22,487.6 25 1

Nested Loop Left Join (cost=103.28..3,555,184,928.21 rows=562,191 width=1,528) (actual time=85.856..1,549.381 rows=25 loops=1)

  • Join Filter: (ua.id = co.company_owner_id)
  • Rows Removed by Join Filter: 2,425
10. 0.094 1,548.630 ↑ 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=85.649..1,548.630 rows=25 loops=1)

11. 0.081 3.311 ↑ 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.029..3.311 rows=25 loops=1)

12. 0.910 3.005 ↑ 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.012..3.005 rows=25 loops=1)

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

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

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

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

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

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

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

17. 0.039 0.867 ↑ 1.2 294 1

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

18. 0.190 0.828 ↑ 1.2 294 1

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

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

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

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

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

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

  • Group Key: col.contact_id
22. 0.206 1,545.050 ↓ 0.0 0 25

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

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

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

  • Filter: (contact_id = c.id)
  • Rows Removed by Filter: 334,448
24. 0.144 0.144 ↑ 1.0 1 12

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

  • Index Cond: (id = col.company_location_id)
25. 0.218 0.375 ↑ 2.1 97 25

Materialize (cost=0.00..43.87 rows=199 width=21) (actual time=0.002..0.015 rows=97 loops=25)

26. 0.157 0.157 ↑ 2.1 97 1

Seq Scan on user_account ua (cost=0.00..42.88 rows=199 width=21) (actual time=0.008..0.157 rows=97 loops=1)

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

Aggregate (cost=10.32..10.33 rows=1 width=32) (actual time=0.019..0.020 rows=1 loops=25)

28. 0.225 0.225 ↑ 10.0 1 25

Index Scan using client_account_pkey on user_account ua_1 (cost=0.15..10.29 rows=10 width=17) (actual time=0.007..0.009 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.025 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.150 0.150 ↓ 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.006..0.006 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.300 ↑ 2.0 1 25

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

  • Group Key: ci.contact_id
35. 0.925 1.950 ↑ 1.0 2 25

Hash Join (cost=3.78..7.97 rows=2 width=19) (actual time=0.049..0.078 rows=2 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.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.011..0.013 rows=2 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.175 3,475.600 ↑ 2.0 1 25

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

  • Group Key: contact_functional_expertise.contact_id
42. 0.300 3,475.425 ↑ 2.0 1 25

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

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

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

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

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

45. 0.100 3,474.725 ↑ 2.0 1 25

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

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

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

47. 0.250 3,474.575 ↑ 2.0 1 25

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

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

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

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

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

  • Group Key: cgc.contact_id
50. 0.050 0.325 ↓ 0.0 0 25

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

51. 0.275 0.275 ↓ 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.011..0.011 rows=0 loops=25)

  • Index Cond: (contact_id = c.id)
  • Heap Fetches: 0
52. 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 : 3.737 ms
Execution time : 5,030.096 ms