explain.depesz.com

PostgreSQL's explain analyze made readable

Result: tMg5

Settings
# exclusive inclusive rows x rows loops node
1. 6.409 722.502 ↑ 2.0 1,542 1

Nested Loop Left Join (cost=3,968.27..250,075.87 rows=3,042 width=523) (actual time=120.113..722.502 rows=1,542 loops=1)

2. 2.128 663.665 ↓ 1.0 1,542 1

Hash Left Join (cost=3,956.06..228,824.37 rows=1,521 width=491) (actual time=120.023..663.665 rows=1,542 loops=1)

  • Hash Cond: (c.id = cfe.contact_id)
3. 236.664 659.860 ↓ 1.0 1,542 1

Nested Loop Left Join (cost=3,935.02..228,797.54 rows=1,521 width=459) (actual time=118.332..659.860 rows=1,542 loops=1)

  • Join Filter: (c.id = cr.contact_id)
  • Rows Removed by Join Filter: 456136
4. 2.521 210.400 ↓ 1.0 1,542 1

Nested Loop Left Join (cost=3,934.73..228,391.89 rows=1,521 width=451) (actual time=115.530..210.400 rows=1,542 loops=1)

5. 2.193 206.337 ↓ 1.0 1,542 1

Hash Left Join (cost=3,934.45..226,336.18 rows=1,521 width=440) (actual time=115.525..206.337 rows=1,542 loops=1)

  • Hash Cond: (c.id = ic.contact_id)
6. 3.261 202.381 ↓ 1.0 1,542 1

Nested Loop Left Join (cost=3,901.17..226,223.06 rows=1,521 width=408) (actual time=113.753..202.381 rows=1,542 loops=1)

7. 3.281 142.066 ↓ 1.0 1,542 1

Nested Loop Left Join (cost=3,896.54..99,385.94 rows=1,521 width=392) (actual time=113.675..142.066 rows=1,542 loops=1)

8. 1.891 124.907 ↓ 1.0 1,542 1

Hash Left Join (cost=3,833.82..3,939.78 rows=1,521 width=360) (actual time=113.659..124.907 rows=1,542 loops=1)

  • Hash Cond: (co.company_owner_id = ua.id)
9. 2.903 120.854 ↓ 1.0 1,542 1

Hash Join (cost=3,681.34..3,781.47 rows=1,521 width=343) (actual time=111.482..120.854 rows=1,542 loops=1)

  • Hash Cond: (c.company_id = co.id)
10. 3.973 63.508 ↓ 1.0 1,542 1

Hash Right Join (cost=1,845.32..1,924.53 rows=1,521 width=288) (actual time=56.775..63.508 rows=1,542 loops=1)

  • Hash Cond: (ce.contact_id = c.id)
11. 2.801 2.801 ↑ 1.0 3,208 1

Seq Scan on contact_extension ce (cost=0.00..51.73 rows=3,273 width=44) (actual time=0.021..2.801 rows=3,208 loops=1)

12. 1.489 56.734 ↓ 1.0 1,542 1

Hash (cost=1,826.30..1,826.30 rows=1,521 width=248) (actual time=56.734..56.734 rows=1,542 loops=1)

  • Buckets: 2048 Batches: 1 Memory Usage: 266kB
13. 1.649 55.245 ↓ 1.0 1,542 1

Hash Right Join (cost=1,795.29..1,826.30 rows=1,521 width=248) (actual time=51.825..55.245 rows=1,542 loops=1)

  • Hash Cond: (col.contact_id = c.id)
14. 1.450 40.702 ↓ 1.1 622 1

GroupAggregate (cost=1,208.46..1,228.54 rows=590 width=43) (actual time=38.917..40.702 rows=622 loops=1)

  • Group Key: col.contact_id
15. 0.867 39.252 ↓ 1.1 675 1

Sort (cost=1,208.46..1,210.06 rows=642 width=43) (actual time=38.893..39.252 rows=675 loops=1)

  • Sort Key: col.contact_id
  • Sort Method: quicksort Memory: 97kB
16. 0.960 38.385 ↓ 1.1 675 1

Hash Join (cost=1,158.27..1,178.52 rows=642 width=43) (actual time=37.235..38.385 rows=675 loops=1)

  • Hash Cond: (col.company_location_id = cl.id)
17. 0.347 0.347 ↓ 1.1 675 1

Seq Scan on contact_location col (cost=0.00..11.42 rows=642 width=8) (actual time=0.013..0.347 rows=675 loops=1)

18. 16.928 37.078 ↑ 1.0 27,982 1

Hash (cost=808.12..808.12 rows=28,012 width=43) (actual time=37.078..37.078 rows=27,982 loops=1)

  • Buckets: 32768 Batches: 1 Memory Usage: 1588kB
19. 20.150 20.150 ↑ 1.0 27,982 1

Seq Scan on company_location cl (cost=0.00..808.12 rows=28,012 width=43) (actual time=0.014..20.150 rows=27,982 loops=1)

20. 1.353 12.894 ↓ 1.0 1,542 1

Hash (cost=567.82..567.82 rows=1,521 width=200) (actual time=12.894..12.894 rows=1,542 loops=1)

  • Buckets: 2048 Batches: 1 Memory Usage: 237kB
21. 1.504 11.541 ↓ 1.0 1,542 1

Hash Right Join (cost=548.79..567.82 rows=1,521 width=200) (actual time=9.572..11.541 rows=1,542 loops=1)

  • Hash Cond: (position_description.contact_id = c.id)
22. 1.996 4.655 ↓ 1.0 681 1

HashAggregate (cost=141.96..148.63 rows=667 width=4) (actual time=4.170..4.655 rows=681 loops=1)

  • Group Key: position_description.contact_id
23. 2.659 2.659 ↓ 1.0 1,536 1

Seq Scan on position_description (cost=0.00..134.31 rows=1,530 width=4) (actual time=0.031..2.659 rows=1,536 loops=1)

  • Filter: ((floated_job = 0) AND (position_category = 1))
  • Rows Removed by Filter: 90
24. 1.421 5.382 ↓ 1.0 1,542 1

Hash (cost=387.81..387.81 rows=1,521 width=192) (actual time=5.382..5.382 rows=1,542 loops=1)

  • Buckets: 2048 Batches: 1 Memory Usage: 228kB
25. 3.961 3.961 ↓ 1.0 1,542 1

Seq Scan on contact c (cost=0.00..387.81 rows=1,521 width=192) (actual time=0.011..3.961 rows=1,542 loops=1)

  • Filter: (deleted_timestamp IS NULL)
  • Rows Removed by Filter: 1666
26. 23.041 54.443 ↑ 1.0 34,854 1

Hash (cost=1,399.90..1,399.90 rows=34,890 width=59) (actual time=54.443..54.443 rows=34,854 loops=1)

  • Buckets: 65536 Batches: 1 Memory Usage: 3418kB
27. 31.402 31.402 ↑ 1.0 34,854 1

Seq Scan on company co (cost=0.00..1,399.90 rows=34,890 width=59) (actual time=0.013..31.402 rows=34,854 loops=1)

28. 0.517 2.162 ↓ 1.0 864 1

Hash (cost=141.69..141.69 rows=863 width=21) (actual time=2.162..2.162 rows=864 loops=1)

  • Buckets: 1024 Batches: 1 Memory Usage: 56kB
29. 1.645 1.645 ↓ 1.0 864 1

Seq Scan on user_account ua (cost=0.00..141.69 rows=863 width=21) (actual time=0.016..1.645 rows=864 loops=1)

  • Filter: ((deleted_timestamp IS NULL) AND (system_admin = 0))
  • Rows Removed by Filter: 232
30. 6.168 13.878 ↑ 1.0 1 1,542

Aggregate (cost=62.72..62.73 rows=1 width=17) (actual time=0.008..0.009 rows=1 loops=1,542)

31. 4.626 7.710 ↑ 10.0 1 1,542

Bitmap Heap Scan on user_account ua_1 (cost=30.85..62.69 rows=10 width=17) (actual time=0.004..0.005 rows=1 loops=1,542)

  • Recheck Cond: (id = ANY (c.contact_owner_ids))
  • Heap Blocks: exact=1228
32. 3.084 3.084 ↑ 10.0 1 1,542

Bitmap Index Scan on client_account_pkey (cost=0.00..30.85 rows=10 width=0) (actual time=0.002..0.002 rows=1 loops=1,542)

  • Index Cond: (id = ANY (c.contact_owner_ids))
33. 12.336 57.054 ↓ 0.0 0 1,542

GroupAggregate (cost=4.63..83.37 rows=1 width=10) (actual time=0.037..0.037 rows=0 loops=1,542)

  • Group Key: pd.contact_id
34. 15.954 44.718 ↑ 2.0 7 1,542

Nested Loop (cost=4.63..83.19 rows=14 width=10) (actual time=0.008..0.029 rows=7 loops=1,542)

35. 6.168 6.168 ↑ 2.0 1 1,542

Index Scan using position_description__contact_id__fkey on position_description pd (cost=0.28..11.14 rows=2 width=14) (actual time=0.003..0.004 rows=1 loops=1,542)

  • Index Cond: (contact_id = c.id)
  • Filter: (position_category = 1)
36. 16.140 22.596 ↑ 1.3 7 1,614

Bitmap Heap Scan on position_candidate pc_1 (cost=4.35..35.93 rows=9 width=8) (actual time=0.006..0.014 rows=7 loops=1,614)

  • Recheck Cond: (position_description_id = pd.id)
  • Heap Blocks: exact=6061
37. 6.456 6.456 ↑ 1.3 7 1,614

Bitmap Index Scan on index_pc_pd (cost=0.00..4.35 rows=9 width=0) (actual time=0.004..0.004 rows=7 loops=1,614)

  • Index Cond: (position_description_id = pd.id)
38. 0.189 1.763 ↑ 1.3 313 1

Hash (cost=28.36..28.36 rows=394 width=36) (actual time=1.763..1.763 rows=313 loops=1)

  • Buckets: 1024 Batches: 1 Memory Usage: 28kB
39. 0.286 1.574 ↑ 1.3 313 1

Subquery Scan on ic (cost=18.51..28.36 rows=394 width=36) (actual time=1.011..1.574 rows=313 loops=1)

40. 0.651 1.288 ↑ 1.3 313 1

HashAggregate (cost=18.51..24.42 rows=394 width=422) (actual time=1.009..1.288 rows=313 loops=1)

  • Group Key: ci.contact_id
41. 0.414 0.637 ↑ 1.3 380 1

Hash Join (cost=1.45..16.06 rows=489 width=422) (actual time=0.049..0.637 rows=380 loops=1)

  • Hash Cond: (ci.industry_id = v.id)
42. 0.197 0.197 ↑ 1.3 380 1

Seq Scan on contact_industry ci (cost=0.00..7.89 rows=489 width=8) (actual time=0.011..0.197 rows=380 loops=1)

43. 0.015 0.026 ↑ 1.2 16 1

Hash (cost=1.20..1.20 rows=20 width=422) (actual time=0.026..0.026 rows=16 loops=1)

  • Buckets: 1024 Batches: 1 Memory Usage: 9kB
44. 0.011 0.011 ↑ 1.2 16 1

Seq Scan on vertical v (cost=0.00..1.20 rows=20 width=422) (actual time=0.004..0.011 rows=16 loops=1)

45. 1.542 1.542 ↓ 0.0 0 1,542

Index Scan using contact__pkey on contact c1 (cost=0.28..1.34 rows=1 width=19) (actual time=0.001..0.001 rows=0 loops=1,542)

  • Index Cond: (id = c.report_to)
  • Filter: (deleted_timestamp IS NULL)
  • Rows Removed by Filter: 0
46. 210.482 212.796 ↓ 29.6 296 1,542

Materialize (cost=0.29..177.52 rows=10 width=12) (actual time=0.001..0.138 rows=296 loops=1,542)

47. 0.283 2.314 ↓ 29.6 296 1

Subquery Scan on cr (cost=0.29..177.47 rows=10 width=12) (actual time=0.061..2.314 rows=296 loops=1)

48. 0.413 2.031 ↓ 29.6 296 1

GroupAggregate (cost=0.29..177.37 rows=10 width=4) (actual time=0.060..2.031 rows=296 loops=1)

  • Group Key: candidate.contact_id
49. 1.618 1.618 ↑ 1.0 344 1

Index Only Scan using candidate__contact_id__fkey on candidate (cost=0.29..175.55 rows=345 width=4) (actual time=0.054..1.618 rows=344 loops=1)

  • Index Cond: (contact_id IS NOT NULL)
  • Heap Fetches: 165
50. 0.072 1.677 ↓ 7.0 112 1

Hash (cost=20.85..20.85 rows=16 width=36) (actual time=1.677..1.677 rows=112 loops=1)

  • Buckets: 1024 Batches: 1 Memory Usage: 15kB
51. 0.104 1.605 ↓ 7.0 112 1

Subquery Scan on cfe (cost=20.49..20.85 rows=16 width=36) (actual time=1.432..1.605 rows=112 loops=1)

52. 0.337 1.501 ↓ 7.0 112 1

HashAggregate (cost=20.49..20.69 rows=16 width=520) (actual time=1.431..1.501 rows=112 loops=1)

  • Group Key: contact_functional_expertise.contact_id
53. 0.279 1.164 ↓ 14.2 228 1

Hash Join (cost=17.59..20.41 rows=16 width=520) (actual time=0.784..1.164 rows=228 loops=1)

  • Hash Cond: (contact_functional_expertise.functional_expertise_id = fex.id)
54. 0.504 0.858 ↓ 2.0 228 1

HashAggregate (cost=16.23..17.35 rows=112 width=8) (actual time=0.744..0.858 rows=228 loops=1)

  • Group Key: contact_functional_expertise.contact_id, contact_functional_expertise.functional_expertise_id
55. 0.354 0.354 ↑ 1.0 615 1

Seq Scan on contact_functional_expertise (cost=0.00..13.15 rows=615 width=8) (actual time=0.011..0.354 rows=615 loops=1)

56. 0.009 0.027 ↑ 1.6 10 1

Hash (cost=1.16..1.16 rows=16 width=520) (actual time=0.027..0.027 rows=10 loops=1)

  • Buckets: 1024 Batches: 1 Memory Usage: 9kB
57. 0.018 0.018 ↑ 1.6 10 1

Seq Scan on functional_expertise fex (cost=0.00..1.16 rows=16 width=520) (actual time=0.011..0.018 rows=10 loops=1)

58. 6.168 52.428 ↓ 0.0 0 1,542

GroupAggregate (cost=12.21..13.87 rows=2 width=520) (actual time=0.034..0.034 rows=0 loops=1,542)

  • Group Key: cgc.contact_id
59. 20.548 46.260 ↑ 3.0 1 1,542

Hash Join (cost=12.21..13.83 rows=3 width=520) (actual time=0.016..0.030 rows=1 loops=1,542)

  • Hash Cond: (cg.id = cgc.contact_group_id)
60. 13.376 13.376 ↓ 1.0 37 704

Seq Scan on contact_group cg (cost=0.00..1.36 rows=36 width=524) (actual time=0.002..0.019 rows=37 loops=704)

61. 4.626 12.336 ↑ 3.0 1 1,542

Hash (cost=12.17..12.17 rows=3 width=8) (actual time=0.008..0.008 rows=1 loops=1,542)

  • Buckets: 1024 Batches: 1 Memory Usage: 9kB
62. 4.626 7.710 ↑ 3.0 1 1,542

Bitmap Heap Scan on contact_group_contact cgc (cost=4.30..12.17 rows=3 width=8) (actual time=0.004..0.005 rows=1 loops=1,542)

  • Recheck Cond: (contact_id = c.id)
  • Heap Blocks: exact=1586
63. 3.084 3.084 ↑ 3.0 1 1,542

Bitmap Index Scan on contact_group_contact_idx (cost=0.00..4.30 rows=3 width=0) (actual time=0.002..0.002 rows=1 loops=1,542)

  • Index Cond: (contact_id = c.id)