explain.depesz.com

PostgreSQL's explain analyze made readable

Result: yBTq

Settings

Optimization(s) for this plan:

# exclusive inclusive rows x rows loops node
1. 0.011 576.858 ↑ 1.0 25 1

Limit (cost=11,505.13..11,641.97 rows=25 width=5,301) (actual time=104.998..576.858 rows=25 loops=1)

2. 0.162 576.847 ↑ 304.0 25 1

Nested Loop Left Join (cost=11,505.13..53,102.93 rows=7,600 width=5,301) (actual time=104.998..576.847 rows=25 loops=1)

3. 0.033 576.310 ↑ 8.0 25 1

Nested Loop Left Join (cost=11,493.02..39,594.76 rows=200 width=5,894) (actual time=104.973..576.310 rows=25 loops=1)

4. 1.324 575.827 ↓ 25.0 25 1

Nested Loop Left Join (cost=11,459.42..33,716.03 rows=1 width=5,886) (actual time=104.956..575.827 rows=25 loops=1)

  • Join Filter: (contact_functional_expertise.contact_id = c.id)
  • Rows Removed by Join Filter: 33874
5. 0.071 561.003 ↓ 25.0 25 1

Nested Loop Left Join (cost=11,404.77..33,660.70 rows=1 width=5,854) (actual time=103.225..561.003 rows=25 loops=1)

6. 9.262 560.907 ↓ 25.0 25 1

Nested Loop Left Join (cost=11,404.48..33,653.37 rows=1 width=5,204) (actual time=103.215..560.907 rows=25 loops=1)

  • Join Filter: (c.id = ci.contact_id)
  • Rows Removed by Join Filter: 219587
7. 0.033 453.070 ↓ 25.0 25 1

Nested Loop Left Join (cost=11,051.49..33,292.89 rows=1 width=5,172) (actual time=92.780..453.070 rows=25 loops=1)

8. 0.061 452.687 ↓ 25.0 25 1

Nested Loop Left Join (cost=10,971.46..23,294.48 rows=1 width=5,156) (actual time=92.768..452.687 rows=25 loops=1)

9. 1.358 452.126 ↓ 25.0 25 1

Nested Loop Left Join (cost=10,914.90..23,237.88 rows=1 width=5,124) (actual time=92.735..452.126 rows=25 loops=1)

  • Join Filter: (ua.id = co.company_owner_id)
  • Rows Removed by Join Filter: 25975
10. 19.323 442.668 ↓ 25.0 25 1

Nested Loop Left Join (cost=10,914.90..23,142.25 rows=1 width=4,706) (actual time=92.217..442.668 rows=25 loops=1)

  • Join Filter: (c.id = position_description.contact_id)
  • Rows Removed by Join Filter: 401640
11. 9.234 143.195 ↓ 25.0 25 1

Nested Loop Left Join (cost=5,941.19..18,168.50 rows=1 width=4,698) (actual time=25.773..143.195 rows=25 loops=1)

  • Join Filter: (c.id = col.contact_id)
  • Rows Removed by Join Filter: 224912
12. 0.051 0.361 ↓ 25.0 25 1

Nested Loop Left Join (cost=0.87..12,220.68 rows=1 width=4,650) (actual time=0.025..0.361 rows=25 loops=1)

13. 0.061 0.235 ↓ 25.0 25 1

Nested Loop (cost=0.58..12,213.86 rows=1 width=4,578) (actual time=0.019..0.235 rows=25 loops=1)

14. 0.074 0.074 ↑ 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,026) (actual time=0.011..0.074 rows=25 loops=1)

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

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

  • Index Cond: (id = c.company_id)
  • Filter: (deleted_timestamp IS NULL)
16. 0.075 0.075 ↑ 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.003..0.003 rows=1 loops=25)

  • Index Cond: (c.id = contact_id)
17. 117.838 133.600 ↓ 45.0 8,997 25

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

  • Group Key: col.contact_id
18. 5.068 15.762 ↑ 1.0 17,443 1

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

  • Merge Cond: (col.company_location_id = cl.id)
19. 4.753 4.753 ↑ 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.008..4.753 rows=17,443 loops=1)

20. 5.941 5.941 ↑ 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.006..5.941 rows=28,938 loops=1)

21. 151.050 280.150 ↓ 16,066.0 16,066 25

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

  • Group Key: position_description.contact_id
22. 82.799 129.100 ↓ 53,130.0 53,130 25

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

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

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

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

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

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

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

26. 0.125 0.250 ↑ 10.0 1 25

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

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

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

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

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

  • Group Key: pd.contact_id
29. 0.168 0.300 ↓ 0.0 0 25

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

30. 0.025 0.100 ↓ 0.0 0 25

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

  • Recheck Cond: (contact_id = c.id)
  • Filter: (position_category = 1)
  • Heap Blocks: exact=4
31. 0.075 0.075 ↓ 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.003..0.003 rows=0 loops=25)

  • Index Cond: (contact_id = c.id)
32. 0.008 0.032 ↑ 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.007..0.008 rows=2 loops=4)

  • Recheck Cond: (position_description_id = pd.id)
  • Heap Blocks: exact=6
33. 0.024 0.024 ↑ 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.006..0.006 rows=2 loops=4)

  • Index Cond: (position_description_id = pd.id)
34. 95.295 98.575 ↓ 43.9 8,784 25

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

  • Group Key: ci.contact_id
35. 2.405 3.280 ↑ 1.0 15,150 1

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

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

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

37. 0.011 0.021 ↑ 1.0 50 1

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

  • Buckets: 1024 Batches: 1 Memory Usage: 11kB
38. 0.010 0.010 ↑ 1.0 50 1

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

39. 0.025 0.025 ↓ 0.0 0 25

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

  • Index Cond: (id = c.report_to)
  • Filter: (deleted_timestamp IS NULL)
40. 10.575 13.500 ↓ 84.7 1,355 25

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

  • Group Key: contact_functional_expertise.contact_id
41. 1.984 2.925 ↓ 96.7 1,547 25

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

  • Sort Key: contact_functional_expertise.contact_id
  • Sort Method: quicksort Memory: 169kB
42. 0.214 0.941 ↓ 96.7 1,547 1

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

  • Hash Cond: (contact_functional_expertise.functional_expertise_id = fex.id)
43. 0.572 0.705 ↓ 7.0 1,547 1

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

  • Group Key: contact_functional_expertise.contact_id, contact_functional_expertise.functional_expertise_id
44. 0.133 0.133 ↑ 1.0 2,200 1

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

45. 0.003 0.022 ↑ 1.0 15 1

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

  • Buckets: 1024 Batches: 1 Memory Usage: 9kB
46. 0.019 0.019 ↑ 1.0 15 1

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

47. 0.300 0.450 ↓ 0.0 0 25

GroupAggregate (cost=33.61..5,874.73 rows=200 width=12) (actual time=0.018..0.018 rows=0 loops=25)

  • Group Key: candidate.contact_id
48. 0.025 0.150 ↓ 0.0 0 25

Bitmap Heap Scan on candidate (cost=33.61..5,864.23 rows=1,701 width=4) (actual time=0.006..0.006 rows=0 loops=25)

  • Recheck Cond: (contact_id = c.id)
49. 0.125 0.125 ↓ 0.0 0 25

Bitmap Index Scan on candidate__contact_id__fkey (cost=0.00..33.18 rows=1,701 width=0) (actual time=0.005..0.005 rows=0 loops=25)

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

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

  • Group Key: cgc.contact_id
51. 0.171 0.325 ↓ 0.0 0 25

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

  • Hash Cond: (cgc.contact_group_id = cg.id)
52. 0.050 0.100 ↓ 0.0 0 25

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

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

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

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

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

  • Buckets: 1024 Batches: 1 Memory Usage: 21kB
55. 0.026 0.026 ↑ 1.0 200 1

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

Planning time : 3.071 ms
Execution time : 578.959 ms