explain.depesz.com

PostgreSQL's explain analyze made readable

Result: CdSu : Optimization for: plan #yBTq

Settings

Optimization path:

Optimization(s) for this plan:

# exclusive inclusive rows x rows loops node
1. 0.012 1,108.747 ↑ 1.0 25 1

Limit (cost=18,948.31..19,009.84 rows=25 width=5,301) (actual time=136.492..1,108.747 rows=25 loops=1)

2. 0.259 1,108.735 ↑ 4,160.0 25 1

Nested Loop Left Join (cost=18,948.31..274,889.09 rows=104,000 width=5,301) (actual time=136.491..1,108.735 rows=25 loops=1)

3. 0.059 1,108.076 ↑ 104.0 25 1

Nested Loop Left Join (cost=18,932.86..75,257.52 rows=2,600 width=5,894) (actual time=136.473..1,108.076 rows=25 loops=1)

4. 0.062 1,086.692 ↑ 8.0 25 1

Nested Loop Left Join (cost=18,798.53..48,171.42 rows=200 width=5,862) (actual time=136.005..1,086.692 rows=25 loops=1)

5. 0.112 1,085.980 ↓ 25.0 25 1

Nested Loop Left Join (cost=18,764.68..42,103.56 rows=1 width=5,854) (actual time=135.978..1,085.980 rows=25 loops=1)

6. 39.859 1,085.818 ↓ 25.0 25 1

Nested Loop Left Join (cost=18,764.39..42,096.20 rows=1 width=5,204) (actual time=135.963..1,085.818 rows=25 loops=1)

  • Join Filter: (c.id = ci.contact_id)
  • Rows Removed by Join Filter: 410111
7. 0.062 769.859 ↓ 25.0 25 1

Nested Loop Left Join (cost=18,123.00..41,447.31 rows=1 width=5,172) (actual time=108.810..769.859 rows=25 loops=1)

8. 0.096 769.422 ↓ 25.0 25 1

Nested Loop Left Join (cost=18,097.07..38,365.47 rows=1 width=5,156) (actual time=108.788..769.422 rows=25 loops=1)

9. 1.395 768.651 ↓ 25.0 25 1

Nested Loop Left Join (cost=18,049.85..38,318.21 rows=1 width=5,124) (actual time=108.759..768.651 rows=25 loops=1)

  • Join Filter: (ua.id = co.company_owner_id)
  • Rows Removed by Join Filter: 9871
10. 25.112 760.331 ↓ 25.0 25 1

Nested Loop Left Join (cost=18,049.85..38,247.25 rows=1 width=4,706) (actual time=108.502..760.331 rows=25 loops=1)

  • Join Filter: (c.id = position_description.contact_id)
  • Rows Removed by Join Filter: 224849
11. 56.677 524.419 ↓ 25.0 25 1

Nested Loop Left Join (cost=14,012.43..34,209.79 rows=1 width=4,698) (actual time=78.857..524.419 rows=25 loops=1)

  • Join Filter: (c.id = col.contact_id)
  • Rows Removed by Join Filter: 538036
12. 0.053 0.492 ↓ 25.0 25 1

Nested Loop Left Join (cost=1.00..20,190.87 rows=1 width=4,650) (actual time=0.030..0.492 rows=25 loops=1)

13. 0.079 0.339 ↓ 25.0 25 1

Nested Loop (cost=0.71..20,184.05 rows=1 width=4,578) (actual time=0.023..0.339 rows=25 loops=1)

14. 0.085 0.085 ↑ 6.2 25 1

Index Scan using contact_insert_timestamp_desc__idx on contact c (cost=0.29..18,972.22 rows=155 width=4,026) (actual time=0.010..0.085 rows=25 loops=1)

  • Filter: ((deleted_timestamp IS NULL) AND ((board >= 1) OR (board IS NULL)))
15. 0.175 0.175 ↑ 1.0 1 25

Index Scan using company__pkey on company co (cost=0.42..7.82 rows=1 width=556) (actual time=0.007..0.007 rows=1 loops=25)

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

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

  • Index Cond: (c.id = contact_id)
17. 422.430 467.250 ↓ 107.6 21,522 25

HashAggregate (cost=14,011.42..14,014.42 rows=200 width=52) (actual time=2.489..18.690 rows=21,522 loops=25)

  • Group Key: col.contact_id
18. 11.285 44.820 ↑ 1.0 31,455 1

Merge Join (cost=0.58..13,539.60 rows=31,455 width=536) (actual time=0.014..44.820 rows=31,455 loops=1)

  • Merge Cond: (col.company_location_id = cl.id)
19. 8.298 8.298 ↑ 1.0 31,455 1

Index Scan using contact_location_company_location_id_idx on contact_location col (cost=0.29..1,628.11 rows=31,455 width=8) (actual time=0.007..8.298 rows=31,455 loops=1)

20. 25.237 25.237 ↑ 1.0 83,541 1

Index Scan using company_location__pkey on company_location cl (cost=0.29..11,309.44 rows=83,543 width=536) (actual time=0.006..25.237 rows=83,541 loops=1)

21. 131.175 210.800 ↓ 8,994.0 8,994 25

GroupAggregate (cost=4,037.42..4,037.44 rows=1 width=12) (actual time=0.969..8.432 rows=8,994 loops=25)

  • Group Key: position_description.contact_id
22. 60.834 79.625 ↓ 29,307.0 29,307 25

Sort (cost=4,037.42..4,037.43 rows=1 width=4) (actual time=0.966..3.185 rows=29,307 loops=25)

  • Sort Key: position_description.contact_id
  • Sort Method: quicksort Memory: 2142kB
23. 18.791 18.791 ↓ 29,307.0 29,307 1

Seq Scan on position_description (cost=0.00..4,037.41 rows=1 width=4) (actual time=0.042..18.791 rows=29,307 loops=1)

  • Filter: ((floated_job = 0) AND (position_category = 1))
  • Rows Removed by Filter: 4587
24. 6.925 6.925 ↓ 395.0 395 25

Seq Scan on user_account ua (cost=0.00..70.95 rows=1 width=422) (actual time=0.010..0.277 rows=395 loops=25)

  • Filter: ((deleted_timestamp IS NULL) AND (system_admin = 0))
  • Rows Removed by Filter: 154
25. 0.350 0.675 ↑ 1.0 1 25

Aggregate (cost=47.22..47.23 rows=1 width=32) (actual time=0.027..0.027 rows=1 loops=25)

26. 0.150 0.325 ↑ 10.0 1 25

Bitmap Heap Scan on user_account ua_1 (cost=18.83..47.19 rows=10 width=418) (actual time=0.013..0.013 rows=1 loops=25)

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

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

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

GroupAggregate (cost=25.93..3,081.82 rows=1 width=20) (actual time=0.015..0.015 rows=0 loops=25)

  • Group Key: pd.contact_id
29. 0.190 0.350 ↓ 0.0 0 25

Nested Loop (cost=25.93..3,081.73 rows=6 width=10) (actual time=0.014..0.014 rows=0 loops=25)

30. 0.050 0.150 ↓ 0.0 0 25

Bitmap Heap Scan on position_description pd (cost=5.56..564.08 rows=1 width=14) (actual time=0.006..0.006 rows=0 loops=25)

  • Recheck Cond: (contact_id = c.id)
  • Filter: (position_category = 1)
  • Rows Removed by Filter: 0
  • Heap Blocks: exact=3
31. 0.100 0.100 ↓ 0.0 0 25

Bitmap Index Scan on position_description__contact_id__fkey (cost=0.00..5.56 rows=169 width=0) (actual time=0.004..0.004 rows=0 loops=25)

  • Index Cond: (contact_id = c.id)
32. 0.002 0.010 ↑ 1,026.0 1 1

Bitmap Heap Scan on position_candidate pc_1 (cost=20.37..2,507.39 rows=1,026 width=8) (actual time=0.010..0.010 rows=1 loops=1)

  • Recheck Cond: (position_description_id = pd.id)
  • Heap Blocks: exact=1
33. 0.008 0.008 ↑ 1,026.0 1 1

Bitmap Index Scan on index_pc_pd (cost=0.00..20.12 rows=1,026 width=0) (actual time=0.008..0.008 rows=1 loops=1)

  • Index Cond: (position_description_id = pd.id)
34. 270.017 276.100 ↓ 82.0 16,405 25

HashAggregate (cost=641.39..644.39 rows=200 width=36) (actual time=0.660..11.044 rows=16,405 loops=25)

  • Group Key: ci.contact_id
35. 4.241 6.083 ↑ 1.0 27,625 1

Hash Join (cost=1.97..503.27 rows=27,625 width=422) (actual time=0.042..6.083 rows=27,625 loops=1)

  • Hash Cond: (ci.industry_id = v.id)
36. 1.819 1.819 ↑ 1.0 27,625 1

Seq Scan on contact_industry ci (cost=0.00..427.25 rows=27,625 width=8) (actual time=0.009..1.819 rows=27,625 loops=1)

37. 0.014 0.023 ↑ 1.0 43 1

Hash (cost=1.43..1.43 rows=43 width=422) (actual time=0.023..0.023 rows=43 loops=1)

  • Buckets: 1024 Batches: 1 Memory Usage: 11kB
38. 0.009 0.009 ↑ 1.0 43 1

Seq Scan on vertical v (cost=0.00..1.43 rows=43 width=422) (actual time=0.006..0.009 rows=43 loops=1)

39. 0.050 0.050 ↓ 0.0 0 25

Index Scan using contact__pkey on contact c1 (cost=0.29..7.33 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)
40. 0.400 0.650 ↓ 0.0 0 25

GroupAggregate (cost=33.85..6,063.87 rows=200 width=12) (actual time=0.026..0.026 rows=0 loops=25)

  • Group Key: candidate.contact_id
41. 0.025 0.250 ↓ 0.0 0 25

Bitmap Heap Scan on candidate (cost=33.85..6,053.20 rows=1,733 width=4) (actual time=0.010..0.010 rows=0 loops=25)

  • Recheck Cond: (contact_id = c.id)
42. 0.225 0.225 ↓ 0.0 0 25

Bitmap Index Scan on candidate__contact_id__fkey (cost=0.00..33.42 rows=1,733 width=0) (actual time=0.009..0.009 rows=0 loops=25)

  • Index Cond: (contact_id = c.id)
43. 0.025 21.325 ↓ 0.0 0 25

GroupAggregate (cost=134.33..135.17 rows=13 width=36) (actual time=0.853..0.853 rows=0 loops=25)

  • Group Key: contact_functional_expertise.contact_id
44. 0.034 21.300 ↓ 0.0 0 25

Hash Join (cost=134.33..134.94 rows=13 width=520) (actual time=0.852..0.852 rows=0 loops=25)

  • Hash Cond: (contact_functional_expertise.functional_expertise_id = fex.id)
45. 0.125 21.250 ↓ 0.0 0 25

Unique (cost=133.04..133.21 rows=34 width=8) (actual time=0.850..0.850 rows=0 loops=25)

46. 0.425 21.125 ↓ 0.0 0 25

Sort (cost=133.04..133.12 rows=35 width=8) (actual time=0.845..0.845 rows=0 loops=25)

  • Sort Key: contact_functional_expertise.functional_expertise_id
  • Sort Method: quicksort Memory: 25kB
47. 20.700 20.700 ↓ 0.0 0 25

Seq Scan on contact_functional_expertise (cost=0.00..132.14 rows=35 width=8) (actual time=0.828..0.828 rows=0 loops=25)

  • Filter: (contact_id = c.id)
  • Rows Removed by Filter: 6971
48. 0.005 0.016 ↑ 1.0 13 1

Hash (cost=1.13..1.13 rows=13 width=520) (actual time=0.016..0.016 rows=13 loops=1)

  • Buckets: 1024 Batches: 1 Memory Usage: 9kB
49. 0.011 0.011 ↑ 1.0 13 1

Seq Scan on functional_expertise fex (cost=0.00..1.13 rows=13 width=520) (actual time=0.009..0.011 rows=13 loops=1)

50. 0.000 0.400 ↓ 0.0 0 25

GroupAggregate (cost=15.45..74.28 rows=40 width=36) (actual time=0.016..0.016 rows=0 loops=25)

  • Group Key: cgc.contact_id
51. 0.250 0.400 ↓ 0.0 0 25

Hash Join (cost=15.45..73.46 rows=45 width=520) (actual time=0.016..0.016 rows=0 loops=25)

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

Bitmap Heap Scan on contact_group_contact cgc (cost=4.63..62.52 rows=45 width=8) (actual time=0.006..0.006 rows=0 loops=25)

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

Bitmap Index Scan on contact_group_contact_idx (cost=0.00..4.62 rows=45 width=0) (actual time=0.005..0.005 rows=0 loops=25)

  • Index Cond: (contact_id = c.id)
54. 0.000 0.000 ↓ 0.0 0

Hash (cost=7.03..7.03 rows=303 width=524) (never executed)

55. 0.000 0.000 ↓ 0.0 0

Seq Scan on contact_group cg (cost=0.00..7.03 rows=303 width=524) (never executed)

Planning time : 3.082 ms
Execution time : 1,111.864 ms