explain.depesz.com

PostgreSQL's explain analyze made readable

Result: e0BS

Settings
# exclusive inclusive rows x rows loops node
1. 0.643 9.534 ↑ 68.3 360 1

HashAggregate (cost=23,698.64..23,944.69 rows=24,605 width=4) (actual time=9.467..9.534 rows=360 loops=1)

  • Group Key: distinctclosedateowner.ownerid
2.          

CTE owners

3. 0.778 2.204 ↑ 2.0 935 1

Nested Loop Semi Join (cost=60.06..17,848.01 rows=1,861 width=4) (actual time=0.154..2.204 rows=935 loops=1)

4. 0.365 0.491 ↑ 2.0 935 1

Bitmap Heap Scan on t166_userrolehierarchy (cost=59.64..6,841.91 rows=1,861 width=8) (actual time=0.141..0.491 rows=935 loops=1)

  • Recheck Cond: ((c663_ancestor_role_id = 237423) AND (end_stamp = '32503680000000'::bigint))
  • Heap Blocks: exact=131
5. 0.126 0.126 ↑ 2.0 945 1

Bitmap Index Scan on ix_t166_userrolehierarchy_ancestor_end_start_timestamp (cost=0.00..59.17 rows=1,861 width=0) (actual time=0.126..0.126 rows=945 loops=1)

  • Index Cond: ((c663_ancestor_role_id = 237423) AND (end_stamp = '32503680000000'::bigint))
6. 0.935 0.935 ↑ 1.0 1 935

Index Scan using ix_t67_userroledim_sid_timestamp_partial on t67_userroledim (cost=0.42..5.90 rows=1 width=4) (actual time=0.001..0.001 rows=1 loops=935)

  • Index Cond: ((sid = t166_userrolehierarchy.c662_user_role_id) AND (end_stamp = '32503680000000'::bigint))
  • Filter: ((NOT deleted) AND (sid <> '-2'::integer))
7. 0.173 8.891 ↑ 19.6 4,093 1

Nested Loop (cost=42.30..5,650.54 rows=80,033 width=4) (actual time=2.740..8.891 rows=4,093 loops=1)

8. 0.360 2.814 ↓ 3.7 738 1

HashAggregate (cost=41.87..43.87 rows=200 width=4) (actual time=2.712..2.814 rows=738 loops=1)

  • Group Key: owners.c1
9. 2.454 2.454 ↑ 2.0 935 1

CTE Scan on owners (cost=0.00..37.22 rows=1,861 width=4) (actual time=0.156..2.454 rows=935 loops=1)

10. 5.904 5.904 ↑ 1.0 6 738

Index Only Scan using ix_distinctclosedateowner_owner_closedate on distinctclosedateowner (cost=0.43..27.97 rows=6 width=4) (actual time=0.003..0.008 rows=6 loops=738)

  • Index Cond: ((ownerid = owners.c1) AND (closedate <= 20190131) AND (closedate >= 20181101))
  • Heap Fetches: 4093