explain.depesz.com

PostgreSQL's explain analyze made readable

Result: 1i24

Settings
# exclusive inclusive rows x rows loops node
1. 7.910 659.780 ↑ 19.2 8,420 1

Hash Join (cost=93,554.35..260,133.24 rows=161,644 width=4) (actual time=349.620..659.780 rows=8,420 loops=1)

  • Hash Cond: (t166_userrolehierarchy.c662_user_role_id = t67_userroledim.sid)
  • Planning time: 1.152 ms
  • Execution time: 661.103 ms
2. 151.263 639.401 ↑ 4.7 34,130 1

Hash Join (cost=71,156.53..235,030.61 rows=161,644 width=8) (actual time=336.709..639.401 rows=34,130 loops=1)

  • Hash Cond: (t166_userrolehierarchy.sid = distinctclosedateowner.ownerid)
3. 221.054 352.158 ↓ 1.0 1,322,324 1

Bitmap Heap Scan on t166_userrolehierarchy (cost=37,314.65..194,764.43 rows=1,284,052 width=8) (actual time=138.509..352.158 rows=1,322,324 loops=1)

  • Recheck Cond: ((c663_ancestor_role_id = ANY ('{436,247157}'::integer[])) AND (end_stamp = '32503680000000'::bigint))
  • Heap Blocks: exact=39414
4. 131.104 131.104 ↓ 1.0 1,325,659 1

Bitmap Index Scan on ix_t166_userrolehierarchy_ancestor_end_start_timestamp (cost=0.00..36,993.64 rows=1,284,052 width=0) (actual time=131.104..131.104 rows=1,325,659 loops=1)

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

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

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

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

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

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

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

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

  • Buckets: 131072 Batches: 2 Memory Usage: 1177kB
10. 6.320 11.108 ↑ 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.241..11.108 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.788 4.788 ↑ 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.788..4.788 rows=14,039 loops=1)

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