explain.depesz.com

PostgreSQL's explain analyze made readable

Result: T8Yq

Settings

Optimization(s) for this plan:

# exclusive inclusive rows x rows loops node
1. 0.014 9,245.172 ↑ 1.0 25 1

Limit (cost=67,972.80..82,660.17 rows=25 width=5,311) (actual time=7,970.816..9,245.172 rows=25 loops=1)

2. 0.279 9,245.158 ↑ 1.5 25 1

Nested Loop Left Join (cost=67,972.80..90,297.60 rows=38 width=5,311) (actual time=7,970.815..9,245.158 rows=25 loops=1)

3. 0.082 9,243.879 ↓ 25.0 25 1

Nested Loop Left Join (cost=67,960.69..90,230.06 rows=1 width=5,904) (actual time=7,970.748..9,243.879 rows=25 loops=1)

4. 4.080 9,243.572 ↓ 25.0 25 1

Nested Loop Left Join (cost=67,960.54..90,226.66 rows=1 width=5,896) (actual time=7,970.691..9,243.572 rows=25 loops=1)

  • Join Filter: (contact_functional_expertise.contact_id = c.id)
  • Rows Removed by Join Filter: 33874
5. 0.443 9,206.692 ↓ 25.0 25 1

Nested Loop Left Join (cost=67,905.90..90,171.33 rows=1 width=5,864) (actual time=7,966.170..9,206.692 rows=25 loops=1)

  • Join Filter: (c.id = candidate.contact_id)
  • Rows Removed by Join Filter: 2975
6. 0.122 1,605.024 ↓ 25.0 25 1

Nested Loop Left Join (cost=11,404.77..33,660.70 rows=1 width=5,856) (actual time=366.461..1,605.024 rows=25 loops=1)

7. 31.681 1,604.852 ↓ 25.0 25 1

Nested Loop Left Join (cost=11,404.48..33,653.37 rows=1 width=5,206) (actual time=366.442..1,604.852 rows=25 loops=1)

  • Join Filter: (c.id = ci.contact_id)
  • Rows Removed by Join Filter: 219587
8. 0.060 1,338.146 ↓ 25.0 25 1

Nested Loop Left Join (cost=11,051.49..33,292.89 rows=1 width=5,174) (actual time=348.392..1,338.146 rows=25 loops=1)

9. 0.112 1,336.436 ↓ 25.0 25 1

Nested Loop Left Join (cost=10,971.46..23,294.48 rows=1 width=5,158) (actual time=348.336..1,336.436 rows=25 loops=1)

10. 9.148 1,335.399 ↓ 25.0 25 1

Nested Loop Left Join (cost=10,914.90..23,237.88 rows=1 width=5,126) (actual time=348.263..1,335.399 rows=25 loops=1)

  • Join Filter: (ua.id = co.company_owner_id)
  • Rows Removed by Join Filter: 25975
11. 60.053 1,306.551 ↓ 25.0 25 1

Nested Loop Left Join (cost=10,914.90..23,142.25 rows=1 width=4,708) (actual time=345.544..1,306.551 rows=25 loops=1)

  • Join Filter: (c.id = position_description.contact_id)
  • Rows Removed by Join Filter: 401640
12. 33.806 476.573 ↓ 25.0 25 1

Nested Loop Left Join (cost=5,941.19..18,168.50 rows=1 width=4,700) (actual time=157.183..476.573 rows=25 loops=1)

  • Join Filter: (c.id = col.contact_id)
  • Rows Removed by Join Filter: 224912
13. 0.078 8.142 ↓ 25.0 25 1

Nested Loop Left Join (cost=0.87..12,220.68 rows=1 width=4,652) (actual time=1.331..8.142 rows=25 loops=1)

14. 0.125 7.114 ↓ 25.0 25 1

Nested Loop (cost=0.58..12,213.86 rows=1 width=4,580) (actual time=0.928..7.114 rows=25 loops=1)

15. 1.464 1.464 ↑ 5.4 25 1

Index Scan using contact_insert_timestamp_desc__idx on contact c (cost=0.29..11,360.92 rows=134 width=4,028) (actual time=0.443..1.464 rows=25 loops=1)

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

Index Scan using company__pkey on company co (cost=0.29..6.36 rows=1 width=556) (actual time=0.221..0.221 rows=1 loops=25)

  • Index Cond: (id = c.company_id)
  • Filter: (deleted_timestamp IS NULL)
17. 0.950 0.950 ↑ 1.0 1 25

Index Scan using contact_extension_pkey on contact_extension ce (cost=0.29..6.82 rows=1 width=76) (actual time=0.038..0.038 rows=1 loops=25)

  • Index Cond: (c.id = contact_id)
18. 301.644 434.625 ↓ 45.0 8,997 25

HashAggregate (cost=5,940.32..5,943.32 rows=200 width=52) (actual time=6.122..17.385 rows=8,997 loops=25)

  • Group Key: col.contact_id
19. 13.743 132.981 ↑ 1.0 17,443 1

Merge Join (cost=0.57..5,678.67 rows=17,443 width=536) (actual time=0.524..132.981 rows=17,443 loops=1)

  • Merge Cond: (col.company_location_id = cl.id)
20. 16.296 16.296 ↑ 1.0 17,443 1

Index Scan using contact_location_company_location_id_idx on contact_location col (cost=0.29..909.93 rows=17,443 width=8) (actual time=0.105..16.296 rows=17,443 loops=1)

21. 102.942 102.942 ↑ 1.0 28,938 1

Index Scan using company_location__pkey on company_location cl (cost=0.29..4,478.36 rows=28,938 width=536) (actual time=0.414..102.942 rows=28,938 loops=1)

22. 430.950 769.925 ↓ 16,066.0 16,066 25

GroupAggregate (cost=4,973.70..4,973.72 rows=1 width=12) (actual time=7.082..30.797 rows=16,066 loops=25)

  • Group Key: position_description.contact_id
23. 182.122 338.975 ↓ 53,130.0 53,130 25

Sort (cost=4,973.70..4,973.71 rows=1 width=4) (actual time=7.068..13.559 rows=53,130 loops=25)

  • Sort Key: position_description.contact_id
  • Sort Method: quicksort Memory: 3856kB
24. 156.853 156.853 ↓ 53,130.0 53,130 1

Seq Scan on position_description (cost=0.00..4,973.69 rows=1 width=4) (actual time=0.403..156.853 rows=53,130 loops=1)

  • Filter: ((floated_job = 0) AND (position_category = 1))
  • Rows Removed by Filter: 183
25. 19.700 19.700 ↓ 1,039.0 1,039 25

Seq Scan on user_account ua (cost=0.00..95.63 rows=1 width=422) (actual time=0.015..0.788 rows=1,039 loops=25)

  • Filter: ((deleted_timestamp IS NULL) AND (system_admin = 0))
  • Rows Removed by Filter: 131
26. 0.450 0.925 ↑ 1.0 1 25

Aggregate (cost=56.56..56.58 rows=1 width=32) (actual time=0.036..0.037 rows=1 loops=25)

27. 0.200 0.475 ↑ 10.0 1 25

Bitmap Heap Scan on user_account ua_1 (cost=26.85..56.54 rows=10 width=418) (actual time=0.018..0.019 rows=1 loops=25)

  • Recheck Cond: (id = ANY (c.contact_owner_ids))
  • Heap Blocks: exact=25
28. 0.275 0.275 ↑ 10.0 1 25

Bitmap Index Scan on client_account_pkey (cost=0.00..26.85 rows=10 width=0) (actual time=0.011..0.011 rows=1 loops=25)

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

GroupAggregate (cost=80.03..9,998.38 rows=1 width=20) (actual time=0.066..0.066 rows=0 loops=25)

  • Group Key: pd.contact_id
30. 0.258 1.600 ↓ 0.0 0 25

Nested Loop (cost=80.03..9,998.20 rows=14 width=10) (actual time=0.053..0.064 rows=0 loops=25)

31. 0.175 0.350 ↓ 0.0 0 25

Bitmap Heap Scan on position_description pd (cost=6.29..852.75 rows=1 width=14) (actual time=0.014..0.014 rows=0 loops=25)

  • Recheck Cond: (contact_id = c.id)
  • Filter: (position_category = 1)
  • Heap Blocks: exact=4
32. 0.175 0.175 ↓ 0.0 0 25

Bitmap Index Scan on position_description__contact_id__fkey (cost=0.00..6.29 rows=267 width=0) (actual time=0.007..0.007 rows=0 loops=25)

  • Index Cond: (contact_id = c.id)
33. 0.388 0.992 ↑ 1,891.0 2 4

Bitmap Heap Scan on position_candidate pc_1 (cost=73.74..9,107.63 rows=3,782 width=8) (actual time=0.179..0.248 rows=2 loops=4)

  • Recheck Cond: (position_description_id = pd.id)
  • Heap Blocks: exact=6
34. 0.604 0.604 ↑ 1,891.0 2 4

Bitmap Index Scan on index_pc_pd (cost=0.00..72.79 rows=3,782 width=0) (actual time=0.151..0.151 rows=2 loops=4)

  • Index Cond: (position_description_id = pd.id)
35. 227.695 235.025 ↓ 43.9 8,784 25

HashAggregate (cost=352.99..355.99 rows=200 width=36) (actual time=0.657..9.401 rows=8,784 loops=25)

  • Group Key: ci.contact_id
36. 3.558 7.330 ↑ 1.0 15,150 1

Hash Join (cost=2.13..277.24 rows=15,150 width=422) (actual time=0.080..7.330 rows=15,150 loops=1)

  • Hash Cond: (ci.industry_id = v.id)
37. 3.726 3.726 ↑ 1.0 15,150 1

Seq Scan on contact_industry ci (cost=0.00..234.50 rows=15,150 width=8) (actual time=0.026..3.726 rows=15,150 loops=1)

38. 0.016 0.046 ↑ 1.0 50 1

Hash (cost=1.50..1.50 rows=50 width=422) (actual time=0.045..0.046 rows=50 loops=1)

  • Buckets: 1024 Batches: 1 Memory Usage: 11kB
39. 0.030 0.030 ↑ 1.0 50 1

Seq Scan on vertical v (cost=0.00..1.50 rows=50 width=422) (actual time=0.025..0.030 rows=50 loops=1)

40. 0.050 0.050 ↓ 0.0 0 25

Index Scan using contact__pkey on contact c1 (cost=0.29..7.30 rows=1 width=658) (actual time=0.002..0.002 rows=0 loops=25)

  • Index Cond: (id = c.report_to)
  • Filter: (deleted_timestamp IS NULL)
41. 1.400 7,601.225 ↑ 1.7 119 25

Finalize GroupAggregate (cost=56,501.13..56,506.13 rows=200 width=12) (actual time=303.988..304.049 rows=119 loops=25)

  • Group Key: candidate.contact_id
42. 0.222 7,599.825 ↑ 3.4 119 25

Sort (cost=56,501.13..56,502.13 rows=400 width=12) (actual time=303.985..303.993 rows=119 loops=25)

  • Sort Key: candidate.contact_id
  • Sort Method: quicksort Memory: 30kB
43. 52.212 7,599.603 ↑ 3.4 119 1

Gather (cost=56,441.84..56,483.84 rows=400 width=12) (actual time=7,598.677..7,599.603 rows=119 loops=1)

  • Workers Planned: 2
  • Workers Launched: 2
44. 0.179 7,547.391 ↑ 5.0 40 3 / 3

Partial HashAggregate (cost=55,441.84..55,443.84 rows=200 width=12) (actual time=7,547.379..7,547.391 rows=40 loops=3)

  • Group Key: candidate.contact_id
45. 7,547.212 7,547.212 ↑ 3,526.2 40 3 / 3

Parallel Seq Scan on candidate (cost=0.00..54,736.59 rows=141,050 width=4) (actual time=51.920..7,547.212 rows=40 loops=3)

  • Filter: (contact_id IS NOT NULL)
  • Rows Removed by Filter: 113368
46. 25.675 32.800 ↓ 84.7 1,355 25

GroupAggregate (cost=54.65..54.97 rows=16 width=36) (actual time=0.152..1.312 rows=1,355 loops=25)

  • Group Key: contact_functional_expertise.contact_id
47. 3.984 7.125 ↓ 96.7 1,547 25

Sort (cost=54.65..54.69 rows=16 width=520) (actual time=0.148..0.285 rows=1,547 loops=25)

  • Sort Key: contact_functional_expertise.contact_id
  • Sort Method: quicksort Memory: 169kB
48. 0.461 3.141 ↓ 96.7 1,547 1

Hash Join (cost=49.34..54.33 rows=16 width=520) (actual time=2.354..3.141 rows=1,547 loops=1)

  • Hash Cond: (contact_functional_expertise.functional_expertise_id = fex.id)
49. 1.267 2.424 ↓ 7.0 1,547 1

HashAggregate (cost=48.00..50.20 rows=220 width=8) (actual time=2.089..2.424 rows=1,547 loops=1)

  • Group Key: contact_functional_expertise.contact_id, contact_functional_expertise.functional_expertise_id
50. 1.157 1.157 ↑ 1.0 2,200 1

Seq Scan on contact_functional_expertise (cost=0.00..37.00 rows=2,200 width=8) (actual time=0.046..1.157 rows=2,200 loops=1)

51. 0.011 0.256 ↑ 1.0 15 1

Hash (cost=1.15..1.15 rows=15 width=520) (actual time=0.256..0.256 rows=15 loops=1)

  • Buckets: 1024 Batches: 1 Memory Usage: 9kB
52. 0.245 0.245 ↑ 1.0 15 1

Seq Scan on functional_expertise fex (cost=0.00..1.15 rows=15 width=520) (actual time=0.238..0.245 rows=15 loops=1)

53. 0.225 0.225 ↓ 0.0 0 25

Index Only Scan using branch_record_branch_id_record_id_record_type_key on branch_record br (cost=0.15..3.39 rows=1 width=16) (actual time=0.009..0.009 rows=0 loops=25)

  • Index Cond: ((record_id = c.id) AND (record_type = 'contact'::text))
  • Heap Fetches: 0
54. 0.125 1.000 ↓ 0.0 0 25

GroupAggregate (cost=12.11..65.17 rows=38 width=36) (actual time=0.040..0.040 rows=0 loops=25)

  • Group Key: cgc.contact_id
55. 0.455 0.875 ↓ 0.0 0 25

Hash Join (cost=12.11..64.39 rows=42 width=520) (actual time=0.035..0.035 rows=0 loops=25)

  • Hash Cond: (cgc.contact_group_id = cg.id)
56. 0.075 0.275 ↓ 0.0 0 25

Bitmap Heap Scan on contact_group_contact cgc (cost=4.61..56.78 rows=42 width=8) (actual time=0.011..0.011 rows=0 loops=25)

  • Recheck Cond: (contact_id = c.id)
  • Heap Blocks: exact=7
57. 0.200 0.200 ↓ 0.0 0 25

Bitmap Index Scan on contact_group_contact_idx (cost=0.00..4.60 rows=42 width=0) (actual time=0.008..0.008 rows=0 loops=25)

  • Index Cond: (contact_id = c.id)
58. 0.054 0.145 ↑ 1.0 200 1

Hash (cost=5.00..5.00 rows=200 width=524) (actual time=0.144..0.145 rows=200 loops=1)

  • Buckets: 1024 Batches: 1 Memory Usage: 21kB
59. 0.091 0.091 ↑ 1.0 200 1

Seq Scan on contact_group cg (cost=0.00..5.00 rows=200 width=524) (actual time=0.026..0.091 rows=200 loops=1)

Planning time : 8.527 ms
Execution time : 9,249.094 ms