explain.depesz.com

PostgreSQL's explain analyze made readable

Result: RG2v

Settings
# exclusive inclusive rows x rows loops node
1. 7.813 663.647 ↑ 19.2 8,420 1

Hash Join (cost=93,554.25..260,131.95 rows=161,643 width=4) (actual time=352.372..663.647 rows=8,420 loops=1)

  • Hash Cond: (t166_userrolehierarchy.c662_user_role_id = t67_userroledim.sid)
2. 152.403 643.009 ↑ 4.7 34,130 1

Hash Join (cost=71,156.43..235,029.33 rows=161,643 width=8) (actual time=339.152..643.009 rows=34,130 loops=1)

  • Hash Cond: (t166_userrolehierarchy.sid = distinctclosedateowner.ownerid)
3. 221.776 353.909 ↓ 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=139.779..353.909 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
4. 132.133 132.133 ↓ 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=132.133..132.133 rows=1,325,655 loops=1)

  • Index Cond: ((c663_ancestor_role_id = ANY ('{436,247157}'::integer[])) AND (end_stamp = '32503680000000'::bigint))
5. 5.198 136.697 ↓ 1.4 33,916 1

Hash (cost=33,534.31..33,534.31 rows=24,605 width=4) (actual time=136.697..136.697 rows=33,916 loops=1)

  • Buckets: 65536 (originally 32768) Batches: 1 (originally 1) Memory Usage: 1705kB
6. 41.699 131.499 ↓ 1.4 33,916 1

HashAggregate (cost=33,288.26..33,534.31 rows=24,605 width=4) (actual time=126.934..131.499 rows=33,916 loops=1)

  • Group Key: distinctclosedateowner.ownerid
7. 70.305 89.800 ↓ 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.568..89.800 rows=196,760 loops=1)

  • Recheck Cond: ((closedate <= 20190131) AND (closedate >= 20181101))
  • Heap Blocks: exact=17913
8. 19.495 19.495 ↓ 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=19.495..19.495 rows=196,760 loops=1)

  • Index Cond: ((closedate <= 20190131) AND (closedate >= 20181101))
9. 1.426 12.825 ↑ 9.3 8,370 1

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

  • Buckets: 131072 Batches: 2 Memory Usage: 1177kB
10. 6.411 11.399 ↑ 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.435..11.399 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
11. 4.988 4.988 ↑ 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.988..4.988 rows=14,039 loops=1)

  • Index Cond: (end_stamp = '32503680000000'::bigint)