explain.depesz.com

PostgreSQL's explain analyze made readable

Result: cRIv : Optimization for: plan #QUM7

Settings

Optimization path:

# exclusive inclusive rows x rows loops node
1. 0.024 16,950.601 ↑ 1.0 25 1

Limit (cost=81,140.69..178,221.39 rows=25 width=1,708) (actual time=4,163.374..16,950.601 rows=25 loops=1)

2. 0.265 16,950.577 ↑ 1,042,920.3 25 1

Nested Loop Left Join (cost=81,140.69..101,247,516,097.93 rows=26,073,008 width=1,708) (actual time=4,163.373..16,950.577 rows=25 loops=1)

3. 0.093 16,949.712 ↑ 521,460.2 25 1

Nested Loop Left Join (cost=81,139.98..101,131,345,686.30 rows=13,036,504 width=1,669) (actual time=4,163.314..16,949.712 rows=25 loops=1)

4. 0.074 10,235.544 ↑ 260,730.1 25 1

Nested Loop Left Join (cost=66,354.63..4,747,941,429.16 rows=6,518,252 width=1,637) (actual time=3,839.360..10,235.544 rows=25 loops=1)

5. 0.091 10,235.245 ↑ 260,730.1 25 1

Nested Loop Left Join (cost=66,354.20..4,737,658,886.63 rows=6,518,252 width=1,629) (actual time=3,839.331..10,235.245 rows=25 loops=1)

6. 0.092 10,232.479 ↑ 130,365.0 25 1

Nested Loop Left Join (cost=66,350.42..4,711,430,708.96 rows=3,259,126 width=1,597) (actual time=3,839.215..10,232.479 rows=25 loops=1)

7. 0.096 10,232.387 ↑ 130,365.0 25 1

Nested Loop Left Join (cost=66,349.99..4,709,682,682.05 rows=3,259,126 width=1,579) (actual time=3,839.211..10,232.387 rows=25 loops=1)

8. 0.116 10,232.116 ↑ 130,365.0 25 1

Nested Loop Left Join (cost=66,349.44..4,682,364,228.96 rows=3,259,126 width=1,563) (actual time=3,839.194..10,232.116 rows=25 loops=1)

9. 0.061 10,231.525 ↑ 130,365.0 25 1

Nested Loop Left Join (cost=66,342.35..4,659,169,898.51 rows=3,259,126 width=1,531) (actual time=3,839.167..10,231.525 rows=25 loops=1)

10. 0.137 10,231.439 ↑ 130,365.0 25 1

Nested Loop Left Join (cost=66,342.21..4,658,631,865.43 rows=3,259,126 width=1,511) (actual time=3,839.163..10,231.439 rows=25 loops=1)

11. 0.976 10,230.552 ↑ 22,477.2 25 1

Nested Loop Left Join (cost=66,341.78..4,658,346,316.68 rows=561,929 width=1,442) (actual time=3,839.135..10,230.552 rows=25 loops=1)

  • Join Filter: (c.id = pc.contact_id)
  • Rows Removed by Join Filter: 7325
12. 3,000.282 10,227.876 ↑ 22,477.2 25 1

Nested Loop Left Join (cost=66,240.19..4,655,404,514.13 rows=561,929 width=1,434) (actual time=3,837.943..10,227.876 rows=25 loops=1)

  • Join Filter: (c.id = clc.contact_id)
  • Rows Removed by Join Filter: 8061647
13. 0.117 0.969 ↑ 22,477.2 25 1

Nested Loop (cost=0.84..325,720.01 rows=561,929 width=1,386) (actual time=0.308..0.969 rows=25 loops=1)

14. 0.477 0.477 ↑ 22,482.4 25 1

Index Scan using contact_insert_timestamp_desc__idx on contact c (cost=0.42..66,147.43 rows=562,059 width=1,347) (actual time=0.283..0.477 rows=25 loops=1)

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

Index Scan using company__pkey on company co (cost=0.42..0.46 rows=1 width=43) (actual time=0.015..0.015 rows=1 loops=25)

  • Index Cond: (id = c.company_id)
  • Filter: (deleted_timestamp IS NULL)
16. 3,826.199 7,226.625 ↑ 1.0 322,466 25

Materialize (cost=66,239.34..85,393.43 rows=334,465 width=52) (actual time=70.007..289.065 rows=322,466 loops=25)

17. 81.236 3,400.426 ↑ 1.0 334,449 1

Subquery Scan on clc (cost=66,239.34..80,454.11 rows=334,465 width=52) (actual time=1,749.919..3,400.426 rows=334,449 loops=1)

18. 1,392.252 3,319.190 ↑ 1.0 334,449 1

GroupAggregate (cost=66,239.34..77,109.46 rows=334,465 width=52) (actual time=1,749.918..3,319.190 rows=334,449 loops=1)

  • Group Key: col.contact_id
19. 892.950 1,926.938 ↑ 1.0 334,449 1

Sort (cost=66,239.34..67,075.51 rows=334,465 width=57) (actual time=1,749.902..1,926.938 rows=334,449 loops=1)

  • Sort Key: col.contact_id
  • Sort Method: external merge Disk: 24256kB
20. 236.028 1,033.988 ↑ 1.0 334,449 1

Merge Join (cost=10,339.61..28,184.03 rows=334,465 width=57) (actual time=141.085..1,033.988 rows=334,449 loops=1)

  • Merge Cond: (col.company_location_id = cl.id)
21. 565.020 565.020 ↑ 1.0 334,449 1

Index Scan using contact_location_company_location_id_idx on contact_location col (cost=0.42..8,659.29 rows=334,465 width=8) (actual time=0.015..565.020 rows=334,449 loops=1)

22. 232.940 232.940 ↑ 1.0 306,303 1

Index Scan using company_location__pkey on company_location cl (cost=0.42..14,578.85 rows=306,303 width=57) (actual time=0.023..232.940 rows=306,303 loops=1)

23. 0.609 1.700 ↑ 1.2 293 25

Materialize (cost=101.59..110.31 rows=349 width=12) (actual time=0.040..0.068 rows=293 loops=25)

24. 0.041 1.091 ↑ 1.2 293 1

Subquery Scan on pc (cost=101.59..108.57 rows=349 width=12) (actual time=0.987..1.091 rows=293 loops=1)

25. 0.220 1.050 ↑ 1.2 293 1

HashAggregate (cost=101.59..105.08 rows=349 width=12) (actual time=0.986..1.050 rows=293 loops=1)

  • Group Key: position_description.contact_id
26. 0.830 0.830 ↑ 1.0 420 1

Seq Scan on position_description (cost=0.00..99.49 rows=420 width=4) (actual time=0.027..0.830 rows=420 loops=1)

  • Filter: ((floated_job = 0) AND (position_category = 1))
  • Rows Removed by Filter: 146
27. 0.750 0.750 ↑ 1.0 1 25

Index Scan using contact_extension_pkey on contact_extension ce (cost=0.43..0.51 rows=1 width=73) (actual time=0.030..0.030 rows=1 loops=25)

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

Index Scan using client_account_pkey on user_account ua (cost=0.14..0.17 rows=1 width=24) (actual time=0.001..0.001 rows=0 loops=25)

  • Index Cond: (id = co.company_owner_id)
  • Filter: ((deleted_timestamp IS NULL) AND (system_admin = 0))
29. 0.275 0.475 ↑ 1.0 1 25

Aggregate (cost=7.08..7.10 rows=1 width=32) (actual time=0.019..0.019 rows=1 loops=25)

30. 0.200 0.200 ↑ 10.0 1 25

Index Scan using client_account_pkey on user_account ua_1 (cost=0.14..7.06 rows=10 width=20) (actual time=0.006..0.008 rows=1 loops=25)

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

GroupAggregate (cost=0.56..8.36 rows=1 width=20) (actual time=0.007..0.007 rows=0 loops=25)

  • Group Key: pd.contact_id
32. 0.025 0.150 ↓ 0.0 0 25

Nested Loop (cost=0.56..8.29 rows=5 width=10) (actual time=0.006..0.006 rows=0 loops=25)

33. 0.125 0.125 ↓ 0.0 0 25

Index Scan using position_description__contact_id__fkey on position_description pd (cost=0.28..2.50 rows=1 width=14) (actual time=0.005..0.005 rows=0 loops=25)

  • Index Cond: (contact_id = c.id)
  • Filter: (position_category = 1)
34. 0.000 0.000 ↓ 0.0 0

Index Scan using index_pc_pd on position_candidate pc_1 (cost=0.28..5.74 rows=5 width=8) (never executed)

  • Index Cond: (position_description_id = pd.id)
35. 0.000 0.000 ↓ 0.0 0 25

Index Scan using contact__pkey on contact (cost=0.42..0.54 rows=1 width=26) (actual time=0.000..0.000 rows=0 loops=25)

  • Index Cond: (id = c.report_to)
  • Filter: (deleted_timestamp IS NULL)
36. 0.325 2.675 ↑ 2.0 1 25

GroupAggregate (cost=3.78..8.01 rows=2 width=36) (actual time=0.107..0.107 rows=1 loops=25)

  • Group Key: ci.contact_id
37. 0.925 2.350 ↓ 1.5 3 25

Hash Join (cost=3.78..7.97 rows=2 width=19) (actual time=0.064..0.094 rows=3 loops=25)

  • Hash Cond: (v.id = ci.industry_id)
38. 0.625 0.625 ↑ 1.0 172 25

Seq Scan on vertical v (cost=0.00..3.72 rows=172 width=19) (actual time=0.007..0.025 rows=172 loops=25)

39. 0.100 0.800 ↓ 1.5 3 25

Hash (cost=3.76..3.76 rows=2 width=8) (actual time=0.032..0.032 rows=3 loops=25)

  • Buckets: 1024 Batches: 1 Memory Usage: 9kB
40. 0.700 0.700 ↓ 1.5 3 25

Index Scan using contact_industry_contact_id_idx on contact_industry ci (cost=0.42..3.76 rows=2 width=8) (actual time=0.019..0.028 rows=3 loops=25)

  • Index Cond: (contact_id = c.id)
41. 0.050 0.225 ↓ 0.0 0 25

GroupAggregate (cost=0.42..1.56 rows=1 width=12) (actual time=0.009..0.009 rows=0 loops=25)

  • Group Key: candidate.contact_id
42. 0.175 0.175 ↓ 0.0 0 25

Index Only Scan using candidate__contact_id__fkey on candidate (cost=0.42..1.54 rows=1 width=4) (actual time=0.007..0.007 rows=0 loops=25)

  • Index Cond: (contact_id = c.id)
  • Heap Fetches: 0
43. 0.250 6,714.075 ↑ 2.0 1 25

GroupAggregate (cost=14,785.35..14,786.65 rows=2 width=36) (actual time=268.563..268.563 rows=1 loops=25)

  • Group Key: contact_functional_expertise.contact_id
44. 0.250 6,713.825 ↑ 2.0 1 25

Hash Join (cost=14,785.35..14,786.62 rows=2 width=520) (actual time=268.552..268.553 rows=1 loops=25)

  • Hash Cond: (fex.id = contact_functional_expertise.functional_expertise_id)
45. 0.150 0.150 ↑ 1.8 10 25

Seq Scan on functional_expertise fex (cost=0.00..1.18 rows=18 width=520) (actual time=0.005..0.006 rows=10 loops=25)

46. 0.075 6,713.425 ↑ 2.0 1 25

Hash (cost=14,785.33..14,785.33 rows=2 width=8) (actual time=268.537..268.537 rows=1 loops=25)

  • Buckets: 1024 Batches: 1 Memory Usage: 9kB
47. 0.100 6,713.350 ↑ 2.0 1 25

Unique (cost=14,785.30..14,785.31 rows=2 width=8) (actual time=268.533..268.534 rows=1 loops=25)

48. 0.275 6,713.250 ↑ 2.0 1 25

Sort (cost=14,785.30..14,785.30 rows=2 width=8) (actual time=268.530..268.530 rows=1 loops=25)

  • Sort Key: contact_functional_expertise.functional_expertise_id
  • Sort Method: quicksort Memory: 25kB
49. 6,712.975 6,712.975 ↑ 2.0 1 25

Seq Scan on contact_functional_expertise (cost=0.00..14,785.29 rows=2 width=8) (actual time=265.961..268.519 rows=1 loops=25)

  • Filter: (contact_id = c.id)
  • Rows Removed by Filter: 783400
50. 0.025 0.600 ↓ 0.0 0 25

GroupAggregate (cost=0.71..8.79 rows=2 width=36) (actual time=0.024..0.024 rows=0 loops=25)

  • Group Key: cgc.contact_id
51. 0.050 0.575 ↓ 0.0 0 25

Nested Loop (cost=0.71..8.75 rows=2 width=25) (actual time=0.023..0.023 rows=0 loops=25)

52. 0.525 0.525 ↓ 0.0 0 25

Index Only Scan using contact_group_contact_idx on contact_group_contact cgc (cost=0.43..3.75 rows=2 width=8) (actual time=0.021..0.021 rows=0 loops=25)

  • Index Cond: (contact_id = c.id)
  • Heap Fetches: 0
53. 0.000 0.000 ↓ 0.0 0

Index Scan using contact_group_pkey on contact_group cg (cost=0.28..2.50 rows=1 width=29) (never executed)

  • Index Cond: (id = cgc.contact_group_id)