explain.depesz.com

PostgreSQL's explain analyze made readable

Result: WXyH

Settings
# exclusive inclusive rows x rows loops node
1. 110.325 405.597 ↑ 1.0 29,712 1

WindowAgg (cost=4,133.64..1,792,332.15 rows=29,779 width=71) (actual time=98.104..405.597 rows=29,712 loops=1)

2.          

CTE tmp

3. 1.368 2.652 ↓ 1.0 470 1

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

  • Group Key: position_description.company_id
4. 1.284 1.284 ↓ 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.024..1.284 rows=1,618 loops=1)

  • Heap Fetches: 240
5. 44.497 117.000 ↑ 1.0 29,712 1

Sort (cost=4,004.13..4,078.58 rows=29,779 width=71) (actual time=98.017..117.000 rows=29,712 loops=1)

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

Hash Right Join (cost=1,776.13..1,791.26 rows=29,779 width=71) (actual time=49.728..72.503 rows=29,712 loops=1)

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

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

8. 20.221 49.566 ↑ 1.0 29,712 1

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

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

Seq Scan on company c (cost=0.00..1,403.89 rows=29,779 width=63) (actual time=0.013..29.345 rows=29,712 loops=1)

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

SubPlan (forWindowAgg)

11. 89.136 178.272 ↑ 1.0 1 29,712

Aggregate (cost=60.00..60.02 rows=1 width=17) (actual time=0.006..0.006 rows=1 loops=29,712)

12. 89.136 89.136 ↑ 8.0 1 29,712

Index Scan using client_account_pkey on user_account (cost=0.28..59.98 rows=8 width=17) (actual time=0.001..0.003 rows=1 loops=29,712)

  • Index Cond: (id = ANY (c.company_owner_ids))
  • Filter: (deleted_timestamp IS NULL)
  • Rows Removed by Filter: 0