explain.depesz.com

PostgreSQL's explain analyze made readable

Result: 1kP3

Settings
# exclusive inclusive rows x rows loops node
1. 0.001 790,409.364 ↑ 1.0 25 1

Limit (cost=200,357,060.09..200,357,060.16 rows=25 width=637) (actual time=790,409.362..790,409.364 rows=25 loops=1)

2. 812.392 790,409.363 ↑ 6,873.0 25 1

Sort (cost=200,357,060.09..200,357,489.66 rows=171,825 width=637) (actual time=790,409.362..790,409.363 rows=25 loops=1)

  • Sort Key: c.job_title
  • Sort Method: top-N heapsort Memory: 47kB
3. 658.565 789,596.971 ↓ 1.1 186,496 1

Nested Loop Left Join (cost=1,165.16..200,352,211.32 rows=171,825 width=637) (actual time=3.744..789,596.971 rows=186,496 loops=1)

4. 346.596 788,565.414 ↓ 1.1 186,496 1

Nested Loop Left Join (cost=1,156.98..198,935,514.19 rows=171,825 width=590) (actual time=3.733..788,565.414 rows=186,496 loops=1)

5. 221.680 786,167.362 ↓ 1.1 186,496 1

Nested Loop Left Join (cost=1,128.85..194,096,143.98 rows=171,825 width=558) (actual time=3.717..786,167.362 rows=186,496 loops=1)

6. 94.360 13,292.754 ↓ 1.1 186,496 1

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

7. 209.600 12,638.906 ↓ 1.1 186,496 1

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

8. 256.671 12,429.306 ↓ 1.1 186,496 1

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

9. 132.344 9,375.195 ↓ 1.1 186,496 1

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

10. 275.323 8,123.875 ↓ 1.1 186,496 1

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

11. 234.432 3,932.136 ↓ 1.1 186,496 1

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

12. 198.210 3,697.704 ↓ 1.1 186,496 1

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

13. 220.678 3,126.502 ↓ 1.1 186,496 1

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

14. 152.545 2,346.336 ↓ 1.1 186,496 1

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

15. 430.105 1,447.807 ↓ 1.1 186,496 1

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

16. 458.214 458.214 ↓ 1.0 186,496 1

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

  • Filter: ((deleted_timestamp IS NULL) AND ((board >= 1) OR (board IS NULL)))
  • Rows Removed by Filter: 14906
17. 559.488 559.488 ↑ 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.003..0.003 rows=1 loops=186,496)

  • Index Cond: (id = c.company_id)
  • Filter: (deleted_timestamp IS NULL)
18. 745.984 745.984 ↑ 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.004..0.004 rows=1 loops=186,496)

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

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

20. 0.000 372.992 ↓ 0.0 0 186,496

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

21. 372.992 372.992 ↓ 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.002..0.002 rows=0 loops=186,496)

  • Index Cond: (contact_id = c.id)
  • Heap Fetches: 3122
22. 9.366 9.366 ↑ 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.003..0.003 rows=1 loops=3,122)

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

Aggregate (cost=8.30..8.31 rows=1 width=8) (actual time=0.002..0.002 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,864.960 3,916.416 ↑ 1.0 1 186,496

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

27. 2,051.456 2,051.456 ↑ 9.0 1 186,496

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

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

Aggregate (cost=26.62..26.63 rows=1 width=16) (actual time=0.006..0.006 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. 1.138 2.845 ↑ 2.5 2 569

Bitmap Heap Scan on position_candidate pc_1 (cost=4.32..18.26 rows=5 width=8) (actual time=0.005..0.005 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. 1,305.472 2,797.440 ↑ 1.0 1 186,496

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

34. 700.805 1,491.968 ↓ 0.0 0 186,496

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

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

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

36. 186.496 559.488 ↓ 0.0 0 186,496

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

  • Buckets: 1024 Batches: 1 Memory Usage: 8kB
37. 372.992 372.992 ↓ 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.002..0.002 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. 186.496 559.488 ↑ 1.0 1 186,496

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

40. 372.992 372.992 ↓ 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.002..0.002 rows=0 loops=186,496)

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

Aggregate (cost=1,048.93..1,048.94 rows=1 width=32) (actual time=4.143..4.143 rows=1 loops=186,496)

42. 497.112 771,347.456 ↓ 0.0 0 186,496

Hash Join (cost=1,045.37..1,048.93 rows=2 width=24) (actual time=4.135..4.136 rows=0 loops=186,496)

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

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

44. 186.496 770,601.472 ↓ 0.0 0 186,496

Hash (cost=1,045.34..1,045.34 rows=2 width=4) (actual time=4.132..4.132 rows=0 loops=186,496)

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

Subquery Scan on cfex (cost=1,045.31..1,045.34 rows=2 width=4) (actual time=4.131..4.131 rows=0 loops=186,496)

46. 186.496 770,414.976 ↓ 0.0 0 186,496

Unique (cost=1,045.31..1,045.32 rows=2 width=8) (actual time=4.131..4.131 rows=0 loops=186,496)

47. 1,118.976 770,228.480 ↓ 0.0 0 186,496

Sort (cost=1,045.31..1,045.32 rows=2 width=8) (actual time=4.130..4.130 rows=0 loops=186,496)

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

Seq Scan on contact_functional_expertise (cost=0.00..1,045.30 rows=2 width=8) (actual time=3.819..4.124 rows=0 loops=186,496)

  • Filter: (contact_id = c.id)
  • Rows Removed by Filter: 55384
49. 372.992 2,051.456 ↑ 1.0 1 186,496

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

50. 440.948 1,678.464 ↑ 2.0 1 186,496

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

51. 745.984 745.984 ↑ 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.004..0.004 rows=1 loops=186,496)

  • Index Cond: (contact_id = c.id)
  • Heap Fetches: 163844
52. 491.532 491.532 ↑ 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.003..0.003 rows=1 loops=163,844)

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

Aggregate (cost=8.17..8.18 rows=1 width=32) (actual time=0.002..0.002 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 : 4.406 ms
Execution time : 790,409.916 ms