explain.depesz.com

PostgreSQL's explain analyze made readable

Result: ySsd

Settings
# exclusive inclusive rows x rows loops node
1. 0.002 11,747.552 ↑ 1.0 25 1

Limit (cost=22,521,064.04..22,521,064.10 rows=25 width=637) (actual time=11,747.549..11,747.552 rows=25 loops=1)

2. 284.161 11,747.550 ↑ 6,873.0 25 1

Sort (cost=22,521,064.04..22,521,493.60 rows=171,825 width=637) (actual time=11,747.549..11,747.550 rows=25 loops=1)

  • Sort Key: c.job_title
  • Sort Method: top-N heapsort Memory: 47kB
3. 228.655 11,463.389 ↓ 1.1 186,496 1

Nested Loop Left Join (cost=130.17..22,516,215.26 rows=171,825 width=637) (actual time=0.150..11,463.389 rows=186,496 loops=1)

4. 35.686 11,048.238 ↓ 1.1 186,496 1

Nested Loop Left Join (cost=122.00..21,099,518.13 rows=171,825 width=590) (actual time=0.140..11,048.238 rows=186,496 loops=1)

5. 38.592 10,080.072 ↓ 1.1 186,496 1

Nested Loop Left Join (cost=93.87..16,260,147.92 rows=171,825 width=558) (actual time=0.128..10,080.072 rows=186,496 loops=1)

6. 143.397 7,803.528 ↓ 1.1 186,496 1

Nested Loop Left Join (cost=79.92..13,858,162.42 rows=171,825 width=526) (actual time=0.099..7,803.528 rows=186,496 loops=1)

7. 95.705 7,473.635 ↓ 1.1 186,496 1

Nested Loop Left Join (cost=71.61..12,425,571.48 rows=171,825 width=518) (actual time=0.093..7,473.635 rows=186,496 loops=1)

8. 178.799 7,377.930 ↓ 1.1 186,496 1

Nested Loop Left Join (cost=71.19..12,301,363.89 rows=171,825 width=508) (actual time=0.092..7,377.930 rows=186,496 loops=1)

9. 88.536 5,334.171 ↓ 1.1 186,496 1

Nested Loop Left Join (cost=60.88..10,524,564.53 rows=171,825 width=476) (actual time=0.066..5,334.171 rows=186,496 loops=1)

10. 8.413 4,686.147 ↓ 1.1 186,496 1

Nested Loop Left Join (cost=34.26..5,945,423.87 rows=171,825 width=460) (actual time=0.059..4,686.147 rows=186,496 loops=1)

11. 94.201 1,880.294 ↓ 1.1 186,496 1

Nested Loop Left Join (cost=25.78..4,481,474.87 rows=171,825 width=428) (actual time=0.035..1,880.294 rows=186,496 loops=1)

12. 77.886 1,786.093 ↓ 1.1 186,496 1

Nested Loop Left Join (cost=25.63..4,453,466.71 rows=171,825 width=414) (actual time=0.032..1,786.093 rows=186,496 loops=1)

13. 174.818 1,521.711 ↓ 1.1 186,496 1

Nested Loop Left Join (cost=17.33..3,022,164.46 rows=171,825 width=406) (actual time=0.029..1,521.711 rows=186,496 loops=1)

14. 75.953 1,160.397 ↓ 1.1 186,496 1

Nested Loop Left Join (cost=0.71..160,764.80 rows=171,825 width=358) (actual time=0.022..1,160.397 rows=186,496 loops=1)

15. 133.866 711.452 ↓ 1.1 186,496 1

Nested Loop (cost=0.29..78,735.17 rows=171,825 width=295) (actual time=0.017..711.452 rows=186,496 loops=1)

16. 204.594 204.594 ↓ 1.0 186,496 1

Seq Scan on contact c (cost=0.00..15,257.53 rows=186,354 width=243) (actual time=0.010..204.594 rows=186,496 loops=1)

  • Filter: ((deleted_timestamp IS NULL) AND ((board >= 1) OR (board IS NULL)))
  • Rows Removed by Filter: 14906
17. 372.992 372.992 ↑ 1.0 1 186,496

Index Scan using company__pkey on company co (cost=0.29..0.34 rows=1 width=56) (actual time=0.002..0.002 rows=1 loops=186,496)

  • Index Cond: (id = c.company_id)
  • Filter: (deleted_timestamp IS NULL)
18. 372.992 372.992 ↑ 1.0 1 186,496

Index Scan using contact_extension_pkey on contact_extension ce (cost=0.42..0.48 rows=1 width=67) (actual time=0.002..0.002 rows=1 loops=186,496)

  • Index Cond: (c.id = contact_id)
19. 0.000 186.496 ↑ 1.0 1 186,496

Aggregate (cost=16.62..16.63 rows=1 width=48) (actual time=0.001..0.001 rows=1 loops=186,496)

20. 0.000 186.496 ↓ 0.0 0 186,496

Nested Loop (cost=0.57..16.61 rows=1 width=25) (actual time=0.001..0.001 rows=0 loops=186,496)

21. 186.496 186.496 ↓ 0.0 0 186,496

Index Only Scan using contact_location_pkey on contact_location col (cost=0.28..8.30 rows=1 width=4) (actual time=0.001..0.001 rows=0 loops=186,496)

  • Index Cond: (contact_id = c.id)
  • Heap Fetches: 3122
22. 6.244 6.244 ↑ 1.0 1 3,122

Index Scan using company_location__pkey on company_location cl (cost=0.29..8.31 rows=1 width=29) (actual time=0.002..0.002 rows=1 loops=3,122)

  • Index Cond: (id = col.company_location_id)
23. 0.000 186.496 ↑ 1.0 1 186,496

Aggregate (cost=8.30..8.31 rows=1 width=8) (actual time=0.001..0.001 rows=1 loops=186,496)

24. 186.496 186.496 ↓ 0.0 0 186,496

Index Scan using position_description__contact_id__fkey on position_description (cost=0.28..8.30 rows=1 width=0) (actual time=0.001..0.001 rows=0 loops=186,496)

  • Index Cond: (contact_id = c.id)
  • Filter: ((floated_job = 0) AND (position_category = 1))
  • Rows Removed by Filter: 0
25. 0.000 0.000 ↓ 0.0 0 186,496

Index Scan using client_account_pkey on user_account ua (cost=0.14..0.16 rows=1 width=18) (actual time=0.000..0.000 rows=0 loops=186,496)

  • Index Cond: (id = co.company_owner_id)
  • Filter: ((deleted_timestamp IS NULL) AND (system_admin = 0))
  • Rows Removed by Filter: 0
26. 1,118.976 2,797.440 ↑ 1.0 1 186,496

Aggregate (cost=8.49..8.50 rows=1 width=32) (actual time=0.015..0.015 rows=1 loops=186,496)

27. 1,678.464 1,678.464 ↑ 9.0 1 186,496

Seq Scan on user_account ua_1 (cost=0.00..8.46 rows=9 width=14) (actual time=0.005..0.009 rows=1 loops=186,496)

  • Filter: (id = ANY (c.contact_owner_ids))
  • Rows Removed by Filter: 64
28. 372.992 559.488 ↑ 1.0 1 186,496

Aggregate (cost=26.62..26.63 rows=1 width=16) (actual time=0.003..0.003 rows=1 loops=186,496)

29. 0.000 186.496 ↓ 0.0 0 186,496

Nested Loop (cost=4.59..26.60 rows=2 width=6) (actual time=0.001..0.001 rows=0 loops=186,496)

30. 186.496 186.496 ↓ 0.0 0 186,496

Index Scan using position_description__contact_id__fkey on position_description pd (cost=0.28..8.29 rows=1 width=10) (actual time=0.001..0.001 rows=0 loops=186,496)

  • Index Cond: (contact_id = c.id)
  • Filter: (position_category = 1)
  • Rows Removed by Filter: 0
31. 0.569 2.276 ↑ 2.5 2 569

Bitmap Heap Scan on position_candidate pc_1 (cost=4.32..18.26 rows=5 width=8) (actual time=0.004..0.004 rows=2 loops=569)

  • Recheck Cond: (position_description_id = pd.id)
  • Heap Blocks: exact=560
32. 1.707 1.707 ↑ 2.5 2 569

Bitmap Index Scan on index_pc_pd (cost=0.00..4.31 rows=5 width=0) (actual time=0.003..0.003 rows=2 loops=569)

  • Index Cond: (position_description_id = pd.id)
33. 932.480 1,864.960 ↑ 1.0 1 186,496

Aggregate (cost=10.31..10.32 rows=1 width=32) (actual time=0.010..0.010 rows=1 loops=186,496)

34. 374.148 932.480 ↓ 0.0 0 186,496

Hash Join (cost=8.32..10.30 rows=1 width=16) (actual time=0.005..0.005 rows=0 loops=186,496)

  • Hash Cond: (v.id = ci.industry_id)
35. 185.340 185.340 ↑ 1.0 77 46,335

Seq Scan on vertical v (cost=0.00..1.77 rows=77 width=20) (actual time=0.001..0.004 rows=77 loops=46,335)

36. 186.496 372.992 ↓ 0.0 0 186,496

Hash (cost=8.31..8.31 rows=1 width=4) (actual time=0.002..0.002 rows=0 loops=186,496)

  • Buckets: 1024 Batches: 1 Memory Usage: 8kB
37. 186.496 186.496 ↓ 0.0 0 186,496

Index Scan using contact_industry_contact_id_idx on contact_industry ci (cost=0.29..8.31 rows=1 width=4) (actual time=0.001..0.001 rows=0 loops=186,496)

  • Index Cond: (contact_id = c.id)
38. 0.000 0.000 ↓ 0.0 0 186,496

Index Scan using contact__pkey on contact c1 (cost=0.42..0.72 rows=1 width=18) (actual time=0.000..0.000 rows=0 loops=186,496)

  • Index Cond: (id = c.report_to)
  • Filter: (deleted_timestamp IS NULL)
39. 0.000 186.496 ↑ 1.0 1 186,496

Aggregate (cost=8.31..8.32 rows=1 width=8) (actual time=0.001..0.001 rows=1 loops=186,496)

40. 186.496 186.496 ↓ 0.0 0 186,496

Index Only Scan using candidate__contact_id__fkey on candidate (cost=0.29..8.30 rows=1 width=0) (actual time=0.001..0.001 rows=0 loops=186,496)

  • Index Cond: ((contact_id IS NOT NULL) AND (contact_id = c.id))
  • Heap Fetches: 7855
41. 932.480 2,237.952 ↑ 1.0 1 186,496

Aggregate (cost=13.95..13.96 rows=1 width=32) (actual time=0.012..0.012 rows=1 loops=186,496)

42. 559.330 1,305.472 ↓ 0.0 0 186,496

Hash Join (cost=10.38..13.94 rows=2 width=24) (actual time=0.005..0.007 rows=0 loops=186,496)

  • Hash Cond: (fex.id = cfex.functional_expertise_id)
43. 186.654 186.654 ↑ 1.0 112 31,109

Seq Scan on functional_expertise fex (cost=0.00..3.12 rows=112 width=28) (actual time=0.001..0.006 rows=112 loops=31,109)

44. 0.000 559.488 ↓ 0.0 0 186,496

Hash (cost=10.36..10.36 rows=2 width=4) (actual time=0.003..0.003 rows=0 loops=186,496)

  • Buckets: 1024 Batches: 1 Memory Usage: 8kB
45. 0.000 559.488 ↓ 0.0 0 186,496

Subquery Scan on cfex (cost=10.33..10.36 rows=2 width=4) (actual time=0.003..0.003 rows=0 loops=186,496)

46. 186.496 559.488 ↓ 0.0 0 186,496

Unique (cost=10.33..10.34 rows=2 width=8) (actual time=0.003..0.003 rows=0 loops=186,496)

47. 186.496 372.992 ↓ 0.0 0 186,496

Sort (cost=10.33..10.33 rows=2 width=8) (actual time=0.002..0.002 rows=0 loops=186,496)

  • Sort Key: contact_functional_expertise.functional_expertise_id
  • Sort Method: quicksort Memory: 25kB
48. 186.496 186.496 ↓ 0.0 0 186,496

Index Scan using contact_functional_expertise_contact_id__idx on contact_functional_expertise (cost=0.29..10.32 rows=2 width=8) (actual time=0.001..0.001 rows=0 loops=186,496)

  • Index Cond: (contact_id = c.id)
49. 186.496 932.480 ↑ 1.0 1 186,496

Aggregate (cost=28.13..28.14 rows=1 width=32) (actual time=0.005..0.005 rows=1 loops=186,496)

50. 209.148 745.984 ↑ 2.0 1 186,496

Nested Loop (cost=0.70..28.12 rows=2 width=37) (actual time=0.003..0.004 rows=1 loops=186,496)

51. 372.992 372.992 ↑ 2.0 1 186,496

Index Only Scan using contact_group_contact_idx on contact_group_contact cgc (cost=0.42..11.52 rows=2 width=4) (actual time=0.002..0.002 rows=1 loops=186,496)

  • Index Cond: (contact_id = c.id)
  • Heap Fetches: 163844
52. 163.844 163.844 ↑ 1.0 1 163,844

Index Scan using contact_group_pkey on contact_group cg (cost=0.28..8.30 rows=1 width=45) (actual time=0.001..0.001 rows=1 loops=163,844)

  • Index Cond: (id = cgc.contact_group_id)
53. 0.000 186.496 ↑ 1.0 1 186,496

Aggregate (cost=8.17..8.18 rows=1 width=32) (actual time=0.001..0.001 rows=1 loops=186,496)

54. 186.496 186.496 ↓ 0.0 0 186,496

Index Scan using branch_record_record_id_record_type__idx on branch_record (cost=0.15..8.17 rows=1 width=8) (actual time=0.001..0.001 rows=0 loops=186,496)

  • Index Cond: ((c.id = record_id) AND ((record_type)::text = 'contact'::text))
Planning time : 3.064 ms
Execution time : 11,748.057 ms