explain.depesz.com

PostgreSQL's explain analyze made readable

Result: QkUx : Optimization for: Optimization for: plan #T8Yq; plan #DFJ9

Settings

Optimization path:

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

Limit (cost=11,509.45..11,646.21 rows=25 width=5,311) (actual time=112.009..585.582 rows=25 loops=1)

2. 0.185 585.571 ↑ 304.0 25 1

Nested Loop Left Join (cost=11,509.45..53,084.73 rows=7,600 width=5,311) (actual time=112.008..585.571 rows=25 loops=1)

3. 0.040 585.036 ↑ 8.0 25 1

Nested Loop Left Join (cost=11,497.34..39,576.56 rows=200 width=5,904) (actual time=111.988..585.036 rows=25 loops=1)

4. 0.051 584.571 ↓ 25.0 25 1

Nested Loop Left Join (cost=11,459.56..33,719.43 rows=1 width=5,896) (actual time=111.964..584.571 rows=25 loops=1)

5. 1.840 584.445 ↓ 25.0 25 1

Nested Loop Left Join (cost=11,459.42..33,716.03 rows=1 width=5,888) (actual time=111.950..584.445 rows=25 loops=1)

  • Join Filter: (contact_functional_expertise.contact_id = c.id)
  • Rows Removed by Join Filter: 33874
6. 0.074 567.430 ↓ 25.0 25 1

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

7. 9.975 567.331 ↓ 25.0 25 1

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

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

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

9. 0.071 459.159 ↓ 25.0 25 1

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

10. 1.407 458.538 ↓ 25.0 25 1

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

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

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

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

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

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

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

14. 0.060 0.279 ↓ 25.0 25 1

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

15. 0.094 0.094 ↑ 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.009..0.094 rows=25 loops=1)

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

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

  • Index Cond: (id = c.company_id)
  • Filter: (deleted_timestamp IS NULL)
17. 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)
18. 117.947 135.225 ↓ 45.0 8,997 25

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

  • Group Key: col.contact_id
19. 5.375 17.278 ↑ 1.0 17,443 1

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

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

21. 6.878 6.878 ↑ 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..6.878 rows=28,938 loops=1)

22. 148.100 283.325 ↓ 16,066.0 16,066 25

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

  • Group Key: position_description.contact_id
23. 87.557 135.225 ↓ 53,130.0 53,130 25

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

  • Sort Key: position_description.contact_id
  • Sort Method: quicksort Memory: 3856kB
24. 47.668 47.668 ↓ 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..47.668 rows=53,130 loops=1)

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

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

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

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

27. 0.150 0.275 ↑ 10.0 1 25

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

  • Recheck Cond: (id = ANY (c.contact_owner_ids))
  • Heap Blocks: exact=25
28. 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))
29. 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
30. 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)

31. 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
32. 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)
33. 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
34. 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)
35. 94.349 97.800 ↓ 43.9 8,784 25

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

  • Group Key: ci.contact_id
36. 2.383 3.451 ↑ 1.0 15,150 1

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

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

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

38. 0.014 0.024 ↑ 1.0 50 1

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

  • Buckets: 1024 Batches: 1 Memory Usage: 11kB
39. 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)

40. 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)
41. 11.525 15.175 ↓ 84.7 1,355 25

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

  • Group Key: contact_functional_expertise.contact_id
42. 2.374 3.650 ↓ 96.7 1,547 25

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

  • Sort Key: contact_functional_expertise.contact_id
  • Sort Method: quicksort Memory: 169kB
43. 0.416 1.276 ↓ 96.7 1,547 1

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

  • Hash Cond: (contact_functional_expertise.functional_expertise_id = fex.id)
44. 0.692 0.837 ↓ 7.0 1,547 1

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

  • Group Key: contact_functional_expertise.contact_id, contact_functional_expertise.functional_expertise_id
45. 0.145 0.145 ↑ 1.0 2,200 1

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

46. 0.003 0.023 ↑ 1.0 15 1

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

  • Buckets: 1024 Batches: 1 Memory Usage: 9kB
47. 0.020 0.020 ↑ 1.0 15 1

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

48. 0.075 0.075 ↓ 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.003..0.003 rows=0 loops=25)

  • Index Cond: ((record_id = c.id) AND (record_type = 'contact'::text))
  • Heap Fetches: 0
49. 0.300 0.425 ↓ 0.0 0 25

GroupAggregate (cost=37.78..5,853.14 rows=200 width=12) (actual time=0.017..0.017 rows=0 loops=25)

  • Group Key: candidate.contact_id
50. 0.025 0.125 ↓ 0.0 0 25

Bitmap Heap Scan on candidate (cost=37.78..5,842.67 rows=1,693 width=4) (actual time=0.005..0.005 rows=0 loops=25)

  • Recheck Cond: ((contact_id IS NOT NULL) AND (contact_id = c.id))
51. 0.100 0.100 ↓ 0.0 0 25

Bitmap Index Scan on candidate__contact_id__fkey (cost=0.00..37.35 rows=1,693 width=0) (actual time=0.004..0.004 rows=0 loops=25)

  • Index Cond: ((contact_id IS NOT NULL) AND (contact_id = c.id))
52. 0.025 0.350 ↓ 0.0 0 25

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

  • Group Key: cgc.contact_id
53. 0.169 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)
54. 0.025 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
55. 0.075 0.075 ↓ 0.0 0 25

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

  • Index Cond: (contact_id = c.id)
56. 0.031 0.056 ↑ 1.0 200 1

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

  • Buckets: 1024 Batches: 1 Memory Usage: 21kB
57. 0.025 0.025 ↑ 1.0 200 1

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

Planning time : 3.279 ms
Execution time : 588.344 ms