explain.depesz.com

PostgreSQL's explain analyze made readable

Result: rI75

Settings
# exclusive inclusive rows x rows loops node
1. 0.862 102.473 ↑ 33.6 360 1

HashAggregate (cost=51,428.01..51,548.91 rows=12,090 width=4) (actual time=102.424..102.473 rows=360 loops=1)

  • Group Key: distinctclosedateowner.ownerid
2. 17.078 101.611 ↑ 3.0 4,093 1

Hash Join (cost=21,291.44..51,397.78 rows=12,090 width=4) (actual time=24.520..101.611 rows=4,093 loops=1)

  • Hash Cond: (distinctclosedateowner.ownerid = t166_userrolehierarchy.sid)
3. 63.125 82.120 ↓ 1.2 196,760 1

Bitmap Heap Scan on distinctclosedateowner (cost=3,397.11..32,888.10 rows=160,066 width=4) (actual time=22.034..82.120 rows=196,760 loops=1)

  • Recheck Cond: ((closedate <= 20190131) AND (closedate >= 20181101))
  • Heap Blocks: exact=17913
4. 18.995 18.995 ↓ 1.2 196,760 1

Bitmap Index Scan on ix_closedate_distinctclosedateowner (cost=0.00..3,357.09 rows=160,066 width=0) (actual time=18.995..18.995 rows=196,760 loops=1)

  • Index Cond: ((closedate <= 20190131) AND (closedate >= 20181101))
5. 0.091 2.413 ↑ 2.5 738 1

Hash (cost=17,871.19..17,871.19 rows=1,852 width=4) (actual time=2.413..2.413 rows=738 loops=1)

  • Buckets: 2048 Batches: 1 Memory Usage: 42kB
6. 0.314 2.322 ↑ 2.5 738 1

HashAggregate (cost=17,852.67..17,871.19 rows=1,852 width=4) (actual time=2.233..2.322 rows=738 loops=1)

  • Group Key: t166_userrolehierarchy.sid
7. 0.679 2.008 ↑ 2.0 935 1

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

8. 0.303 0.394 ↑ 2.0 935 1

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

  • Recheck Cond: ((c663_ancestor_role_id = 237423) AND (end_stamp = '32503680000000'::bigint))
  • Heap Blocks: exact=131
9. 0.091 0.091 ↑ 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.091..0.091 rows=945 loops=1)

  • Index Cond: ((c663_ancestor_role_id = 237423) AND (end_stamp = '32503680000000'::bigint))
10. 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))