explain.depesz.com

PostgreSQL's explain analyze made readable

Result: WUXe

Settings
# exclusive inclusive rows x rows loops node
1. 219.767 26,990.989 ↑ 1.0 29,712 1

WindowAgg (cost=4,133.64..4,543,968.25 rows=29,779 width=54) (actual time=90.104..26,990.989 rows=29,712 loops=1)

2.          

CTE tmp

3. 1.185 2.204 ↓ 1.0 470 1

GroupAggregate (cost=0.28..129.51 rows=469 width=4) (actual time=0.026..2.204 rows=470 loops=1)

  • Group Key: position_description.company_id
4. 1.019 1.019 ↓ 1.0 1,618 1

Index Only Scan using position_description__company_id__fkey on position_description (cost=0.28..116.75 rows=1,614 width=4) (actual time=0.020..1.019 rows=1,618 loops=1)

  • Heap Fetches: 240
5. 55.014 119.558 ↑ 1.0 29,712 1

Sort (cost=4,004.13..4,078.58 rows=29,779 width=54) (actual time=88.566..119.558 rows=29,712 loops=1)

  • Sort Key: (COALESCE(tmp.cnt, '0'::bigint)) DESC
  • Sort Method: external sort Disk: 2032kB
6. 17.842 64.544 ↑ 1.0 29,712 1

Hash Right Join (cost=1,776.13..1,791.26 rows=29,779 width=54) (actual time=44.033..64.544 rows=29,712 loops=1)

  • Hash Cond: (tmp.company_id = c.id)
7. 2.740 2.740 ↓ 1.0 470 1

CTE Scan on tmp (cost=0.00..9.38 rows=469 width=12) (actual time=0.028..2.740 rows=470 loops=1)

8. 19.738 43.962 ↑ 1.0 29,712 1

Hash (cost=1,403.89..1,403.89 rows=29,779 width=46) (actual time=43.962..43.962 rows=29,712 loops=1)

  • Buckets: 32768 Batches: 1 Memory Usage: 2686kB
9. 24.224 24.224 ↑ 1.0 29,712 1

Seq Scan on company c (cost=0.00..1,403.89 rows=29,779 width=46) (actual time=0.009..24.224 rows=29,712 loops=1)

  • Filter: (deleted_timestamp IS NULL)
  • Rows Removed by Filter: 5208
10.          

SubPlan (forWindowAgg)

11. 207.984 26,651.664 ↑ 1.0 1 29,712

Aggregate (cost=152.41..152.42 rows=1 width=17) (actual time=0.896..0.897 rows=1 loops=29,712)

12. 12,629.783 26,443.680 ↑ 439.0 1 29,712

Hash Semi Join (cost=2.76..151.31 rows=439 width=17) (actual time=0.327..0.890 rows=1 loops=29,712)

  • Hash Cond: (((user_account.id)::character varying)::text = ((unnest((string_to_array(c.company_owners, ','::text))::character varying[])))::text)
13. 13,635.625 13,635.625 ↓ 1.0 879 21,817

Seq Scan on user_account (cost=0.00..140.15 rows=878 width=21) (actual time=0.002..0.625 rows=879 loops=21,817)

  • Filter: (deleted_timestamp IS NULL)
  • Rows Removed by Filter: 237
14. 59.424 178.272 ↑ 100.0 1 29,712

Hash (cost=1.51..1.51 rows=100 width=32) (actual time=0.006..0.006 rows=1 loops=29,712)

  • Buckets: 1024 Batches: 1 Memory Usage: 9kB
15. 118.848 118.848 ↑ 100.0 1 29,712

Result (cost=0.00..0.51 rows=100 width=0) (actual time=0.003..0.004 rows=1 loops=29,712)

Planning time : 0.594 ms