explain.depesz.com

PostgreSQL's explain analyze made readable

Result: crYy

Settings
# exclusive inclusive rows x rows loops node
1. 1.991 28.491 ↑ 25.8 955 1

HashAggregate (cost=23,698.64..23,944.69 rows=24,605 width=4) (actual time=28.378..28.491 rows=955 loops=1)

  • Group Key: distinctclosedateowner.ownerid
2.          

CTE owners

3. 0.000 6.674 ↓ 1.5 2,832 1

Nested Loop Semi Join (cost=60.06..17,848.01 rows=1,861 width=4) (actual time=0.279..6.674 rows=2,832 loops=1)

4. 1.102 1.335 ↓ 1.5 2,832 1

Bitmap Heap Scan on t166_userrolehierarchy (cost=59.64..6,841.91 rows=1,861 width=8) (actual time=0.265..1.335 rows=2,832 loops=1)

  • Recheck Cond: ((c663_ancestor_role_id = 237422) AND (end_stamp = '32503680000000'::bigint))
  • Heap Blocks: exact=312
5. 0.233 0.233 ↓ 1.6 2,894 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.233..0.233 rows=2,894 loops=1)

  • Index Cond: ((c663_ancestor_role_id = 237422) AND (end_stamp = '32503680000000'::bigint))
6. 5.664 5.664 ↑ 1.0 1 2,832

Index Scan using ix_t67_userroledim_sid_timestamp_partial on t67_userroledim (cost=0.42..5.90 rows=1 width=4) (actual time=0.002..0.002 rows=1 loops=2,832)

  • Index Cond: ((sid = t166_userrolehierarchy.c662_user_role_id) AND (end_stamp = '32503680000000'::bigint))
  • Filter: ((NOT deleted) AND (sid <> '-2'::integer))
7. 1.577 26.500 ↑ 6.3 12,654 1

Nested Loop (cost=42.30..5,650.54 rows=80,033 width=4) (actual time=8.146..26.500 rows=12,654 loops=1)

8. 1.057 8.431 ↓ 11.8 2,356 1

HashAggregate (cost=41.87..43.87 rows=200 width=4) (actual time=8.126..8.431 rows=2,356 loops=1)

  • Group Key: owners.c1
9. 7.374 7.374 ↓ 1.5 2,832 1

CTE Scan on owners (cost=0.00..37.22 rows=1,861 width=4) (actual time=0.281..7.374 rows=2,832 loops=1)

10. 16.492 16.492 ↑ 1.2 5 2,356

Index Only Scan using ix_distinctclosedateowner_owner_closedate on distinctclosedateowner (cost=0.43..27.97 rows=6 width=4) (actual time=0.003..0.007 rows=5 loops=2,356)

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