explain.depesz.com

PostgreSQL's explain analyze made readable

Result: 4xte

Settings
# exclusive inclusive rows x rows loops node
1. 0.002 96,920.821 ↓ 25.0 25 1

Limit (cost=20,709.76..20,709.77 rows=1 width=5,301) (actual time=96,920.819..96,920.821 rows=25 loops=1)

2. 37.900 96,920.819 ↓ 25.0 25 1

Sort (cost=20,709.76..20,709.77 rows=1 width=5,301) (actual time=96,920.818..96,920.819 rows=25 loops=1)

  • Sort Key: c.insert_timestamp DESC
  • Sort Method: top-N heapsort Memory: 44kB
3. 72.743 96,882.919 ↓ 11,449.0 11,449 1

Nested Loop Left Join (cost=16,579.63..20,709.75 rows=1 width=5,301) (actual time=561.304..96,882.919 rows=11,449 loops=1)

4. 823.785 96,283.522 ↓ 11,449.0 11,449 1

Nested Loop Left Join (cost=16,574.97..20,703.82 rows=1 width=5,894) (actual time=558.334..96,283.522 rows=11,449 loops=1)

  • Join Filter: (contact_functional_expertise.contact_id = c.id)
  • Rows Removed by Join Filter: 14979220
5. 124.706 89,666.543 ↓ 11,449.0 11,449 1

Nested Loop Left Join (cost=16,536.32..20,664.58 rows=1 width=5,862) (actual time=553.942..89,666.543 rows=11,449 loops=1)

  • Join Filter: (c.id = candidate.contact_id)
  • Rows Removed by Join Filter: 2213445
6. 33.727 88,820.550 ↓ 11,449.0 11,449 1

Nested Loop Left Join (cost=3,870.70..7,992.46 rows=1 width=5,854) (actual time=121.387..88,820.550 rows=11,449 loops=1)

7. 2,505.031 88,741.027 ↓ 11,449.0 11,449 1

Nested Loop Left Join (cost=3,870.42..7,984.69 rows=1 width=5,204) (actual time=121.378..88,741.027 rows=11,449 loops=1)

  • Join Filter: (c.id = ci.contact_id)
  • Rows Removed by Join Filter: 48123421
8. 21.313 61,700.789 ↓ 11,449.0 11,449 1

Nested Loop Left Join (cost=3,677.61..7,784.38 rows=1 width=5,172) (actual time=112.654..61,700.789 rows=11,449 loops=1)

9. 30.478 57,557.836 ↓ 11,449.0 11,449 1

Nested Loop Left Join (cost=3,646.41..4,372.81 rows=1 width=5,156) (actual time=112.177..57,557.836 rows=11,449 loops=1)

10. 257.489 57,355.623 ↓ 11,449.0 11,449 1

Nested Loop Left Join (cost=3,614.92..4,341.29 rows=1 width=5,124) (actual time=112.122..57,355.623 rows=11,449 loops=1)

  • Join Filter: (ua.id = co.company_owner_id)
  • Rows Removed by Join Filter: 1919444
11. 2,483.647 55,976.132 ↓ 11,449.0 11,449 1

Nested Loop Left Join (cost=3,614.92..4,305.67 rows=1 width=4,706) (actual time=110.027..55,976.132 rows=11,449 loops=1)

  • Join Filter: (c.id = position_description.contact_id)
  • Rows Removed by Join Filter: 45477465
12. 1,907.347 19,832.425 ↓ 11,449.0 11,449 1

Nested Loop Left Join (cost=323.27..1,013.98 rows=1 width=4,698) (actual time=11.586..19,832.425 rows=11,449 loops=1)

  • Join Filter: (c.id = col.contact_id)
  • Rows Removed by Join Filter: 37823528
13. 16.969 1,072.150 ↓ 11,449.0 11,449 1

Nested Loop Left Join (cost=0.57..683.79 rows=1 width=4,650) (actual time=1.981..1,072.150 rows=11,449 loops=1)

14. 35.352 963.589 ↓ 11,449.0 11,449 1

Nested Loop (cost=0.29..676.82 rows=1 width=4,578) (actual time=1.318..963.589 rows=11,449 loops=1)

15. 92.460 92.460 ↓ 545.2 11,449 1

Seq Scan on contact c (cost=0.00..510.30 rows=21 width=4,026) (actual time=0.585..92.460 rows=11,449 loops=1)

  • Filter: ((deleted_timestamp IS NULL) AND ((board >= 1) OR (board IS NULL)))
  • Rows Removed by Filter: 1295
16. 835.777 835.777 ↑ 1.0 1 11,449

Index Scan using company__pkey on company co (cost=0.29..7.92 rows=1 width=556) (actual time=0.073..0.073 rows=1 loops=11,449)

  • Index Cond: (id = c.company_id)
  • Filter: (deleted_timestamp IS NULL)
17. 91.592 91.592 ↑ 1.0 1 11,449

Index Scan using contact_extension_pkey on contact_extension ce (cost=0.29..6.97 rows=1 width=76) (actual time=0.008..0.008 rows=1 loops=11,449)

  • Index Cond: (c.id = contact_id)
18. 16,846.859 16,852.928 ↓ 16.5 3,304 11,449

HashAggregate (cost=322.70..325.70 rows=200 width=52) (actual time=0.003..1.472 rows=3,304 loops=11,449)

  • Group Key: col.contact_id
19. 0.794 6.069 ↑ 1.0 4,163 1

Hash Join (cost=180.68..260.25 rows=4,163 width=536) (actual time=4.854..6.069 rows=4,163 loops=1)

  • Hash Cond: (col.company_location_id = cl.id)
20. 0.763 0.763 ↑ 1.0 4,163 1

Seq Scan on contact_location col (cost=0.00..68.63 rows=4,163 width=8) (actual time=0.303..0.763 rows=4,163 loops=1)

21. 0.622 4.512 ↑ 1.0 4,297 1

Hash (cost=126.97..126.97 rows=4,297 width=536) (actual time=4.512..4.512 rows=4,297 loops=1)

  • Buckets: 8192 Batches: 1 Memory Usage: 356kB
22. 3.890 3.890 ↑ 1.0 4,297 1

Seq Scan on company_location cl (cost=0.00..126.97 rows=4,297 width=536) (actual time=0.378..3.890 rows=4,297 loops=1)

23. 21,844.692 33,660.060 ↓ 3,973.0 3,973 11,449

GroupAggregate (cost=3,291.65..3,291.67 rows=1 width=12) (actual time=0.011..2.940 rows=3,973 loops=11,449)

  • Group Key: position_description.contact_id
24. 11,724.868 11,815.368 ↓ 18,738.0 18,738 11,449

Sort (cost=3,291.65..3,291.66 rows=1 width=4) (actual time=0.009..1.032 rows=18,738 loops=11,449)

  • Sort Key: position_description.contact_id
  • Sort Method: quicksort Memory: 1855kB
25. 90.500 90.500 ↓ 23,176.0 23,176 1

Seq Scan on position_description (cost=0.00..3,291.64 rows=1 width=4) (actual time=0.306..90.500 rows=23,176 loops=1)

  • Filter: ((floated_job = 0) AND (position_category = 1))
  • Rows Removed by Filter: 200
26. 1,122.002 1,122.002 ↓ 168.0 168 11,449

Seq Scan on user_account ua (cost=0.00..35.60 rows=1 width=422) (actual time=0.010..0.098 rows=168 loops=11,449)

  • Filter: ((deleted_timestamp IS NULL) AND (system_admin = 0))
  • Rows Removed by Filter: 107
27. 103.041 171.735 ↑ 1.0 1 11,449

Aggregate (cost=31.49..31.50 rows=1 width=32) (actual time=0.015..0.015 rows=1 loops=11,449)

28. 34.347 68.694 ↑ 10.0 1 11,449

Bitmap Heap Scan on user_account ua_1 (cost=9.55..31.46 rows=10 width=418) (actual time=0.006..0.006 rows=1 loops=11,449)

  • Recheck Cond: (id = ANY (c.contact_owner_ids))
  • Heap Blocks: exact=7864
29. 34.347 34.347 ↑ 10.0 1 11,449

Bitmap Index Scan on client_account_pkey (cost=0.00..9.55 rows=10 width=0) (actual time=0.003..0.003 rows=1 loops=11,449)

  • Index Cond: (id = ANY (c.contact_owner_ids))
30. 103.041 4,121.640 ↓ 0.0 0 11,449

GroupAggregate (cost=31.20..3,411.56 rows=1 width=20) (actual time=0.360..0.360 rows=0 loops=11,449)

  • Group Key: pd.contact_id
31. 237.203 4,018.599 ↓ 2.0 22 11,449

Nested Loop (cost=31.20..3,411.41 rows=11 width=10) (actual time=0.053..0.351 rows=22 loops=11,449)

32. 91.592 160.286 ↓ 2.0 2 11,449

Bitmap Heap Scan on position_description pd (cost=5.17..398.70 rows=1 width=14) (actual time=0.009..0.014 rows=2 loops=11,449)

  • Recheck Cond: (contact_id = c.id)
  • Filter: (position_category = 1)
  • Rows Removed by Filter: 0
  • Heap Blocks: exact=21704
33. 68.694 68.694 ↑ 58.5 2 11,449

Bitmap Index Scan on position_description__contact_id__fkey (cost=0.00..5.16 rows=117 width=0) (actual time=0.006..0.006 rows=2 loops=11,449)

  • Index Cond: (contact_id = c.id)
34. 3,177.232 3,621.110 ↑ 112.8 11 23,362

Bitmap Heap Scan on position_candidate pc_1 (cost=26.04..3,000.30 rows=1,241 width=8) (actual time=0.042..0.155 rows=11 loops=23,362)

  • Recheck Cond: (position_description_id = pd.id)
  • Heap Blocks: exact=99648
35. 443.878 443.878 ↑ 112.8 11 23,362

Bitmap Index Scan on index_pc_pd (cost=0.00..25.73 rows=1,241 width=0) (actual time=0.019..0.019 rows=11 loops=23,362)

  • Index Cond: (position_description_id = pd.id)
36. 24,531.420 24,535.207 ↓ 21.0 4,204 11,449

HashAggregate (cost=192.81..195.81 rows=200 width=36) (actual time=0.004..2.143 rows=4,204 loops=11,449)

  • Group Key: ci.contact_id
37. 1.323 3.787 ↑ 1.0 8,023 1

Hash Join (cost=5.96..152.70 rows=8,023 width=520) (actual time=1.587..3.787 rows=8,023 loops=1)

  • Hash Cond: (ci.industry_id = v.id)
38. 1.450 1.450 ↑ 1.0 8,023 1

Seq Scan on contact_industry ci (cost=0.00..125.23 rows=8,023 width=8) (actual time=0.563..1.450 rows=8,023 loops=1)

39. 0.028 1.014 ↑ 1.0 176 1

Hash (cost=3.76..3.76 rows=176 width=520) (actual time=1.014..1.014 rows=176 loops=1)

  • Buckets: 1024 Batches: 1 Memory Usage: 23kB
40. 0.986 0.986 ↑ 1.0 176 1

Seq Scan on vertical v (cost=0.00..3.76 rows=176 width=520) (actual time=0.411..0.986 rows=176 loops=1)

41. 45.796 45.796 ↓ 0.0 0 11,449

Index Scan using contact__pkey on contact c1 (cost=0.29..7.54 rows=1 width=658) (actual time=0.004..0.004 rows=0 loops=11,449)

  • Index Cond: (id = c.report_to)
  • Filter: (deleted_timestamp IS NULL)
  • Rows Removed by Filter: 0
42. 289.121 721.287 ↑ 1.0 193 11,449

HashAggregate (cost=12,665.62..12,667.62 rows=200 width=12) (actual time=0.038..0.063 rows=193 loops=11,449)

  • Group Key: candidate.contact_id
43. 432.166 432.166 ↑ 365.8 195 1

Seq Scan on candidate (cost=0.00..12,308.94 rows=71,336 width=4) (actual time=2.592..432.166 rows=195 loops=1)

  • Filter: (contact_id IS NOT NULL)
  • Rows Removed by Filter: 71499
44. 4,945.968 5,793.194 ↓ 93.4 1,308 11,449

GroupAggregate (cost=38.65..38.93 rows=14 width=36) (actual time=0.003..0.506 rows=1,308 loops=11,449)

  • Group Key: contact_functional_expertise.contact_id
45. 843.593 847.226 ↓ 97.5 1,365 11,449

Sort (cost=38.65..38.68 rows=14 width=520) (actual time=0.001..0.074 rows=1,365 loops=11,449)

  • Sort Key: contact_functional_expertise.contact_id
  • Sort Method: quicksort Memory: 179kB
46. 0.181 3.633 ↓ 103.1 1,444 1

Hash Join (cost=33.84..38.38 rows=14 width=520) (actual time=3.327..3.633 rows=1,444 loops=1)

  • Hash Cond: (contact_functional_expertise.functional_expertise_id = fex.id)
47. 0.419 2.505 ↓ 7.2 1,444 1

HashAggregate (cost=32.53..34.53 rows=200 width=8) (actual time=2.368..2.505 rows=1,444 loops=1)

  • Group Key: contact_functional_expertise.contact_id, contact_functional_expertise.functional_expertise_id
48. 2.086 2.086 ↑ 1.0 1,502 1

Seq Scan on contact_functional_expertise (cost=0.00..25.02 rows=1,502 width=8) (actual time=0.983..2.086 rows=1,502 loops=1)

49. 0.009 0.947 ↑ 1.0 14 1

Hash (cost=1.14..1.14 rows=14 width=520) (actual time=0.947..0.947 rows=14 loops=1)

  • Buckets: 1024 Batches: 1 Memory Usage: 9kB
50. 0.938 0.938 ↑ 1.0 14 1

Seq Scan on functional_expertise fex (cost=0.00..1.14 rows=14 width=520) (actual time=0.936..0.938 rows=14 loops=1)

51. 160.286 526.654 ↓ 0.0 0 11,449

GroupAggregate (cost=4.66..5.87 rows=1 width=36) (actual time=0.046..0.046 rows=0 loops=11,449)

  • Group Key: cgc.contact_id
52. 78.931 366.368 ↓ 0.0 0 11,449

Hash Join (cost=4.66..5.85 rows=1 width=520) (actual time=0.032..0.032 rows=0 loops=11,449)

  • Hash Cond: (cg.id = cgc.contact_group_id)
53. 1.212 1.212 ↑ 1.0 13 202

Seq Scan on contact_group cg (cost=0.00..1.13 rows=13 width=524) (actual time=0.006..0.006 rows=13 loops=202)

54. 68.694 286.225 ↓ 0.0 0 11,449

Hash (cost=4.65..4.65 rows=1 width=8) (actual time=0.025..0.025 rows=0 loops=11,449)

  • Buckets: 1024 Batches: 1 Memory Usage: 8kB
55. 217.531 217.531 ↓ 0.0 0 11,449

Seq Scan on contact_group_contact cgc (cost=0.00..4.65 rows=1 width=8) (actual time=0.019..0.019 rows=0 loops=11,449)

  • Filter: (contact_id = c.id)
  • Rows Removed by Filter: 212
Planning time : 84.437 ms
Execution time : 96,923.184 ms