explain.depesz.com

PostgreSQL's explain analyze made readable

Result: GsDN

Settings
# exclusive inclusive rows x rows loops node
1. 0.002 24,612.864 ↓ 0.0 0 1

Limit (cost=8,974,657.98..8,974,658.05 rows=25 width=613) (actual time=24,612.864..24,612.864 rows=0 loops=1)

2. 0.004 24,612.862 ↓ 0.0 0 1

Sort (cost=8,974,657.98..8,974,724.99 rows=26,802 width=613) (actual time=24,612.862..24,612.862 rows=0 loops=1)

  • Sort Key: ce.position_count DESC
  • Sort Method: quicksort Memory: 25kB
3. 22.301 24,612.858 ↓ 0.0 0 1

Nested Loop (cost=2,453.83..8,973,901.65 rows=26,802 width=613) (actual time=24,612.858..24,612.858 rows=0 loops=1)

4. 57.333 2,825.857 ↓ 1.0 26,870 1

Nested Loop Left Join (cost=2,310.32..5,126,193.13 rows=26,802 width=581) (actual time=43.977..2,825.857 rows=26,870 loops=1)

5. 49.445 2,768.524 ↓ 1.0 26,870 1

Nested Loop Left Join (cost=2,310.03..5,116,376.60 rows=26,802 width=556) (actual time=43.973..2,768.524 rows=26,870 loops=1)

6. 47.726 2,638.469 ↓ 1.0 26,870 1

Merge Left Join (cost=2,309.76..5,108,130.40 rows=26,802 width=539) (actual time=43.960..2,638.469 rows=26,870 loops=1)

  • Merge Cond: (c.id = ci.company_id)
7. 49.015 2,458.282 ↓ 1.0 26,870 1

Nested Loop Left Join (cost=2.00..5,104,678.53 rows=26,802 width=507) (actual time=0.333..2,458.282 rows=26,870 loops=1)

8. 55.945 447.757 ↓ 1.0 26,870 1

Nested Loop Left Join (cost=1.29..778,711.53 rows=26,802 width=499) (actual time=0.085..447.757 rows=26,870 loops=1)

9. 62.198 149.982 ↓ 1.0 26,870 1

Merge Left Join (cost=0.57..3,332.88 rows=26,802 width=491) (actual time=0.018..149.982 rows=26,870 loops=1)

  • Merge Cond: (c.id = ce.company_id)
10. 45.377 45.377 ↓ 1.0 26,870 1

Index Scan using company__pkey on company c (cost=0.29..1,721.63 rows=26,802 width=346) (actual time=0.007..45.377 rows=26,870 loops=1)

  • Filter: (deleted_timestamp IS NULL)
  • Rows Removed by Filter: 229
11. 42.407 42.407 ↓ 1.0 27,099 1

Index Scan using company_extension__pkey on company_extension ce (cost=0.29..1,213.02 rows=27,094 width=153) (actual time=0.006..42.407 rows=27,099 loops=1)

12. 26.870 241.830 ↓ 0.0 0 26,870

GroupAggregate (cost=0.71..28.91 rows=1 width=8) (actual time=0.009..0.009 rows=0 loops=26,870)

  • Group Key: pd.company_id
13. 26.870 214.960 ↓ 0.0 0 26,870

Nested Loop (cost=0.71..28.83 rows=14 width=8) (actual time=0.008..0.008 rows=0 loops=26,870)

14. 188.090 188.090 ↓ 0.0 0 26,870

Index Scan using position_description__company_id__fkey on position_description pd (cost=0.29..9.88 rows=1 width=12) (actual time=0.007..0.007 rows=0 loops=26,870)

  • Index Cond: (c.id = company_id)
  • Filter: ((floated_job = 1) AND (position_category = 1))
  • Rows Removed by Filter: 2
15. 0.000 0.000 ↓ 0.0 0

Index Scan using index_pc_pd on position_candidate pc_1 (cost=0.42..18.72 rows=23 width=8) (never executed)

  • Index Cond: (position_description_id = pd.id)
16. 268.700 1,961.510 ↓ 0.0 0 26,870

GroupAggregate (cost=0.71..161.38 rows=1 width=8) (actual time=0.073..0.073 rows=0 loops=26,870)

  • Group Key: pd_1.company_id
17. 485.480 1,692.810 ↑ 5.8 20 26,870

Nested Loop (cost=0.71..160.80 rows=115 width=8) (actual time=0.005..0.063 rows=20 loops=26,870)

18. 107.480 107.480 ↑ 4.0 2 26,870

Index Scan using position_description__company_id__fkey on position_description pd_1 (cost=0.29..9.88 rows=8 width=12) (actual time=0.002..0.004 rows=2 loops=26,870)

  • Index Cond: (c.id = company_id)
  • Filter: ((floated_job = 0) AND (position_category = 1))
  • Rows Removed by Filter: 0
19. 1,099.850 1,099.850 ↑ 1.9 12 43,994

Index Scan using index_pc_pd on position_candidate pc_1_1 (cost=0.42..18.64 rows=23 width=8) (actual time=0.008..0.025 rows=12 loops=43,994)

  • Index Cond: (position_description_id = pd_1.id)
20. 21.937 132.461 ↑ 1.0 22,424 1

Materialize (cost=2,307.75..3,102.64 rows=22,711 width=36) (actual time=43.622..132.461 rows=22,424 loops=1)

21. 51.670 110.524 ↑ 1.0 22,424 1

GroupAggregate (cost=2,307.75..2,818.75 rows=22,711 width=23) (actual time=43.618..110.524 rows=22,424 loops=1)

  • Group Key: ci.company_id
22. 28.963 58.854 ↑ 1.0 22,478 1

Sort (cost=2,307.75..2,364.53 rows=22,711 width=23) (actual time=43.603..58.854 rows=22,478 loops=1)

  • Sort Key: ci.company_id
  • Sort Method: quicksort Memory: 2378kB
23. 20.064 29.891 ↑ 1.0 22,478 1

Hash Join (cost=2.10..664.49 rows=22,711 width=23) (actual time=0.067..29.891 rows=22,478 loops=1)

  • Hash Cond: (ci.industry_id = v.id)
24. 9.777 9.777 ↑ 1.0 22,478 1

Seq Scan on company_industry ci (cost=0.00..350.11 rows=22,711 width=8) (actual time=0.011..9.777 rows=22,478 loops=1)

25. 0.026 0.050 ↑ 1.0 49 1

Hash (cost=1.49..1.49 rows=49 width=23) (actual time=0.050..0.050 rows=49 loops=1)

  • Buckets: 1024 Batches: 1 Memory Usage: 11kB
26. 0.024 0.024 ↑ 1.0 49 1

Seq Scan on vertical v (cost=0.00..1.49 rows=49 width=23) (actual time=0.003..0.024 rows=49 loops=1)

27. 80.610 80.610 ↑ 1.0 1 26,870

Index Scan using client_account_pkey on user_account ua (cost=0.28..0.30 rows=1 width=25) (actual time=0.002..0.003 rows=1 loops=26,870)

  • Index Cond: (c.user_account_id = id)
28. 0.000 0.000 ↓ 0.0 0 26,870

Index Scan using company__pkey on company parent (cost=0.29..0.36 rows=1 width=33) (actual time=0.000..0.000 rows=0 loops=26,870)

  • Index Cond: (c.parent_id = id)
29. 134.350 21,764.700 ↓ 0.0 0 26,870

Aggregate (cost=143.51..143.53 rows=1 width=21) (actual time=0.810..0.810 rows=0 loops=26,870)

  • Filter: (array_to_string(array_agg(user_account.name), ', '::text) ~~* 'test'::text)
  • Rows Removed by Filter: 1
30. 72.959 21,630.350 ↑ 488.0 1 26,870

Result (cost=2.76..141.06 rows=488 width=21) (actual time=0.359..0.805 rows=1 loops=26,870)

  • One-Time Filter: (c.company_owners IS NOT NULL)
31. 11,093.794 21,557.391 ↑ 488.0 1 26,713

Hash Semi Join (cost=2.76..141.06 rows=488 width=21) (actual time=0.359..0.807 rows=1 loops=26,713)

  • Hash Cond: (((user_account.id)::character varying)::text = ((unnest((string_to_array(c.company_owners, ','::text))::character varying[])))::text)
32. 10,330.032 10,330.032 ↑ 1.0 976 22,263

Seq Scan on user_account (cost=0.00..128.97 rows=976 width=25) (actual time=0.004..0.464 rows=976 loops=22,263)

  • Filter: (deleted_timestamp IS NULL)
  • Rows Removed by Filter: 21
33. 53.426 133.565 ↑ 100.0 1 26,713

Hash (cost=1.51..1.51 rows=100 width=32) (actual time=0.005..0.005 rows=1 loops=26,713)

  • Buckets: 1024 Batches: 1 Memory Usage: 9kB
34. 80.139 80.139 ↑ 100.0 1 26,713

Result (cost=0.00..0.51 rows=100 width=0) (actual time=0.002..0.003 rows=1 loops=26,713)