explain.depesz.com

PostgreSQL's explain analyze made readable

Result: LFCV

Settings
# exclusive inclusive rows x rows loops node
1. 2.320 110.821 ↑ 12.7 955 1

HashAggregate (cost=51,428.01..51,548.91 rows=12,090 width=4) (actual time=110.714..110.821 rows=955 loops=1)

  • Group Key: distinctclosedateowner.ownerid
  • Planning time: 1.036 ms
  • Execution time: 111.079 ms
2. 19.659 108.501 ↓ 1.0 12,654 1

Hash Join (cost=21,291.44..51,397.78 rows=12,090 width=4) (actual time=29.573..108.501 rows=12,654 loops=1)

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

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

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

  • Index Cond: ((closedate <= 20190131) AND (closedate >= 20181101))
5. 0.343 7.574 ↓ 1.3 2,356 1

Hash (cost=17,871.19..17,871.19 rows=1,852 width=4) (actual time=7.574..7.574 rows=2,356 loops=1)

  • Buckets: 4096 (originally 2048) Batches: 1 (originally 1) Memory Usage: 115kB
6. 0.887 7.231 ↓ 1.3 2,356 1

HashAggregate (cost=17,852.67..17,871.19 rows=1,852 width=4) (actual time=6.976..7.231 rows=2,356 loops=1)

  • Group Key: t166_userrolehierarchy.sid
7. 0.000 6.344 ↓ 1.5 2,832 1

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

8. 0.818 1.057 ↓ 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.270..1.057 rows=2,832 loops=1)

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

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