explain.depesz.com

PostgreSQL's explain analyze made readable

Result: rxUT

Settings
# exclusive inclusive rows x rows loops node
1. 15.395 1,072.767 ↑ 3.0 8,167 1

HashAggregate (cost=271,220.18..271,466.23 rows=24,605 width=4) (actual time=1,071.930..1,072.767 rows=8,167 loops=1)

  • Group Key: distinctclosedateowner.ownerid
2.          

CTE owners

3. 196.344 648.661 ↑ 9.9 129,794 1

Hash Join (cost=59,712.37..236,520.46 rows=1,284,043 width=4) (actual time=148.680..648.661 rows=129,794 loops=1)

  • Hash Cond: (t166_userrolehierarchy.c662_user_role_id = t67_userroledim.sid)
4. 311.582 440.003 ↓ 1.0 1,322,322 1

Bitmap Heap Scan on t166_userrolehierarchy (cost=37,314.55..194,763.20 rows=1,284,043 width=8) (actual time=135.669..440.003 rows=1,322,322 loops=1)

  • Recheck Cond: ((c663_ancestor_role_id = ANY ('{436,247157}'::integer[])) AND (end_stamp = '32503680000000'::bigint))
  • Heap Blocks: exact=39413
5. 128.421 128.421 ↓ 1.0 1,325,655 1

Bitmap Index Scan on ix_t166_userrolehierarchy_ancestor_end_start_timestamp (cost=0.00..36,993.54 rows=1,284,043 width=0) (actual time=128.421..128.421 rows=1,325,655 loops=1)

  • Index Cond: ((c663_ancestor_role_id = ANY ('{436,247157}'::integer[])) AND (end_stamp = '32503680000000'::bigint))
6. 1.372 12.314 ↑ 9.3 8,370 1

Hash (cost=21,117.18..21,117.18 rows=78,051 width=4) (actual time=12.314..12.314 rows=8,370 loops=1)

  • Buckets: 131072 Batches: 2 Memory Usage: 1177kB
7. 6.047 10.942 ↑ 9.3 8,370 1

Bitmap Heap Scan on t67_userroledim (cost=4,261.99..21,117.18 rows=78,051 width=4) (actual time=5.328..10.942 rows=8,370 loops=1)

  • Recheck Cond: ((end_stamp = '32503680000000'::bigint) AND ((c377_portaltype IS NULL) OR (c377_portaltype = 'None'::text)))
  • Filter: ((NOT deleted) AND (sid <> '-2'::integer))
  • Rows Removed by Filter: 5639
  • Heap Blocks: exact=2150
8. 4.895 4.895 ↑ 5.6 14,039 1

Bitmap Index Scan on ix_t67_userroledim_sid_end_start_timestamp_partial (cost=0.00..4,242.48 rows=78,925 width=0) (actual time=4.895..4.895 rows=14,039 loops=1)

  • Index Cond: (end_stamp = '32503680000000'::bigint)
9. 73.150 1,057.372 ↓ 1.2 93,387 1

Nested Loop (cost=28,891.40..34,499.64 rows=80,033 width=4) (actual time=715.242..1,057.372 rows=93,387 loops=1)

10. 64.333 742.540 ↓ 604.2 120,841 1

HashAggregate (cost=28,890.97..28,892.97 rows=200 width=4) (actual time=715.214..742.540 rows=120,841 loops=1)

  • Group Key: owners.c1
11. 678.207 678.207 ↑ 9.9 129,794 1

CTE Scan on owners (cost=0.00..25,680.86 rows=1,284,043 width=4) (actual time=148.683..678.207 rows=129,794 loops=1)

12. 241.682 241.682 ↑ 6.0 1 120,841

Index Only Scan using ix_distinctclosedateowner_owner_closedate on distinctclosedateowner (cost=0.43..27.97 rows=6 width=4) (actual time=0.002..0.002 rows=1 loops=120,841)

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