explain.depesz.com

PostgreSQL's explain analyze made readable

Result: 97jc

Settings
# exclusive inclusive rows x rows loops node
1. 0.002 21,529.033 ↓ 0.0 0 1

Limit (cost=3,894,656.29..3,894,656.36 rows=25 width=581) (actual time=21,529.033..21,529.033 rows=0 loops=1)

2. 0.006 21,529.031 ↓ 0.0 0 1

Sort (cost=3,894,656.29..3,894,656.40 rows=43 width=581) (actual time=21,529.031..21,529.031 rows=0 loops=1)

  • Sort Key: ce.position_count DESC
  • Sort Method: quicksort Memory: 25kB
3. 0.001 21,529.025 ↓ 0.0 0 1

Nested Loop Left Join (cost=2,309.47..3,894,655.13 rows=43 width=581) (actual time=21,529.025..21,529.025 rows=0 loops=1)

4. 0.001 21,529.024 ↓ 0.0 0 1

Nested Loop Left Join (cost=2,309.18..3,888,433.47 rows=43 width=556) (actual time=21,529.024..21,529.024 rows=0 loops=1)

  • Join Filter: (c.user_account_id = ua.id)
5. 0.001 21,529.023 ↓ 0.0 0 1

Nested Loop Left Join (cost=2,309.18..3,887,659.60 rows=43 width=539) (actual time=21,529.023..21,529.023 rows=0 loops=1)

  • Join Filter: (c.id = ic.company_id)
6. 0.001 21,529.022 ↓ 0.0 0 1

Nested Loop Left Join (cost=1.43..3,869,908.36 rows=43 width=507) (actual time=21,529.022..21,529.022 rows=0 loops=1)

7. 0.001 21,529.021 ↓ 0.0 0 1

Nested Loop Left Join (cost=0.71..3,862,967.96 rows=43 width=499) (actual time=21,529.021..21,529.021 rows=0 loops=1)

8. 0.002 21,529.020 ↓ 0.0 0 1

Nested Loop Left Join (cost=0.00..3,861,723.98 rows=43 width=491) (actual time=21,529.020..21,529.020 rows=0 loops=1)

  • Join Filter: (c.id = ce.company_id)
9. 87.556 21,529.018 ↓ 0.0 0 1

Seq Scan on company c (cost=0.00..3,843,333.68 rows=43 width=346) (actual time=21,529.018..21,529.018 rows=0 loops=1)

  • Filter: ((deleted_timestamp IS NULL) AND ((((SubPlan 2))::character varying(200))::text ~~* 'test'::text))
  • Rows Removed by Filter: 27098
10.          

SubPlan (forSeq Scan)

11. 134.345 21,441.462 ↑ 1.0 1 26,869

Aggregate (cost=142.27..142.29 rows=1 width=21) (actual time=0.797..0.798 rows=1 loops=26,869)

12. 10,937.320 21,307.117 ↑ 488.0 1 26,869

Hash Semi Join (cost=2.76..141.05 rows=488 width=21) (actual time=0.352..0.793 rows=1 loops=26,869)

  • Hash Cond: (((user_account_1.id)::character varying)::text = ((unnest((string_to_array(c.company_owners, ','::text))::character varying[])))::text)
13. 10,262.321 10,262.321 ↓ 1.0 976 22,261

Seq Scan on user_account user_account_1 (cost=0.00..128.96 rows=975 width=25) (actual time=0.004..0.461 rows=976 loops=22,261)

  • Filter: (deleted_timestamp IS NULL)
  • Rows Removed by Filter: 21
14. 26.869 107.476 ↑ 100.0 1 26,869

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

  • Buckets: 1024 Batches: 1 Memory Usage: 9kB
15. 80.607 80.607 ↑ 100.0 1 26,869

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

16. 0.000 0.000 ↓ 0.0 0

Materialize (cost=0.00..982.41 rows=27,094 width=153) (never executed)

17. 0.000 0.000 ↓ 0.0 0

Seq Scan on company_extension ce (cost=0.00..846.94 rows=27,094 width=153) (never executed)

18. 0.000 0.000 ↓ 0.0 0

GroupAggregate (cost=0.71..28.91 rows=1 width=8) (never executed)

  • Group Key: pd.company_id
19. 0.000 0.000 ↓ 0.0 0

Nested Loop (cost=0.71..28.83 rows=14 width=8) (never executed)

20. 0.000 0.000 ↓ 0.0 0

Index Scan using position_description__company_id__fkey on position_description pd (cost=0.29..9.88 rows=1 width=12) (never executed)

  • Index Cond: (c.id = company_id)
  • Filter: ((floated_job = 1) AND (position_category = 1))
21. 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)
22. 0.000 0.000 ↓ 0.0 0

GroupAggregate (cost=0.71..161.38 rows=1 width=8) (never executed)

  • Group Key: pd_1.company_id
23. 0.000 0.000 ↓ 0.0 0

Nested Loop (cost=0.71..160.80 rows=115 width=8) (never executed)

24. 0.000 0.000 ↓ 0.0 0

Index Scan using position_description__company_id__fkey on position_description pd_1 (cost=0.29..9.88 rows=8 width=12) (never executed)

  • Index Cond: (c.id = company_id)
  • Filter: ((floated_job = 0) AND (position_category = 1))
25. 0.000 0.000 ↓ 0.0 0

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

  • Index Cond: (position_description_id = pd_1.id)
26. 0.000 0.000 ↓ 0.0 0

Materialize (cost=2,307.75..3,159.42 rows=22,711 width=36) (never executed)

27. 0.000 0.000 ↓ 0.0 0

Subquery Scan on ic (cost=2,307.75..3,045.86 rows=22,711 width=36) (never executed)

28. 0.000 0.000 ↓ 0.0 0

GroupAggregate (cost=2,307.75..2,818.75 rows=22,711 width=23) (never executed)

  • Group Key: ci.company_id
29. 0.000 0.000 ↓ 0.0 0

Sort (cost=2,307.75..2,364.53 rows=22,711 width=23) (never executed)

  • Sort Key: ci.company_id
30. 0.000 0.000 ↓ 0.0 0

Hash Join (cost=2.10..664.49 rows=22,711 width=23) (never executed)

  • Hash Cond: (ci.industry_id = v.id)
31. 0.000 0.000 ↓ 0.0 0

Seq Scan on company_industry ci (cost=0.00..350.11 rows=22,711 width=8) (never executed)

32. 0.000 0.000 ↓ 0.0 0

Hash (cost=1.49..1.49 rows=49 width=23) (never executed)

33. 0.000 0.000 ↓ 0.0 0

Seq Scan on vertical v (cost=0.00..1.49 rows=49 width=23) (never executed)

34. 0.000 0.000 ↓ 0.0 0

Materialize (cost=0.00..133.94 rows=996 width=25) (never executed)

35. 0.000 0.000 ↓ 0.0 0

Seq Scan on user_account ua (cost=0.00..128.96 rows=996 width=25) (never executed)

36. 0.000 0.000 ↓ 0.0 0

Index Scan using company__pkey on company parent (cost=0.29..2.38 rows=1 width=33) (never executed)

  • Index Cond: (c.parent_id = id)
37.          

SubPlan (forNested Loop Left Join)

38. 0.000 0.000 ↓ 0.0 0

Aggregate (cost=142.27..142.29 rows=1 width=21) (never executed)

39. 0.000 0.000 ↓ 0.0 0

Hash Semi Join (cost=2.76..141.05 rows=488 width=21) (never executed)

  • Hash Cond: (((user_account.id)::character varying)::text = ((unnest((string_to_array(c.company_owners, ','::text))::character varying[])))::text)
40. 0.000 0.000 ↓ 0.0 0

Seq Scan on user_account (cost=0.00..128.96 rows=975 width=25) (never executed)

  • Filter: (deleted_timestamp IS NULL)
41. 0.000 0.000 ↓ 0.0 0

Hash (cost=1.51..1.51 rows=100 width=32) (never executed)

42. 0.000 0.000 ↓ 0.0 0

Result (cost=0.00..0.51 rows=100 width=0) (never executed)