explain.depesz.com

PostgreSQL's explain analyze made readable

Result: u5bH

Settings
# exclusive inclusive rows x rows loops node
1. 16.658 938.447 ↑ 3.0 8,167 1

HashAggregate (cost=431,484.25..431,730.30 rows=24,605 width=4) (actual time=937.464..938.447 rows=8,167 loops=1)

  • Group Key: distinctclosedateowner.ownerid
  • Planning time: 1.080 ms
  • Execution time: 939.954 ms
2. 59.056 921.789 ↑ 1.7 93,387 1

Hash Join (cost=397,382.74..431,084.08 rows=160,066 width=4) (actual time=794.159..921.789 rows=93,387 loops=1)

  • Hash Cond: (distinctclosedateowner.ownerid = t166_userrolehierarchy.sid)
3. 72.156 91.665 ↓ 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.579..91.665 rows=196,760 loops=1)

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

  • Index Cond: ((closedate <= 20190131) AND (closedate >= 20181101))
5. 16.864 771.068 ↑ 1.6 120,840 1

Hash (cost=390,777.37..390,777.37 rows=195,541 width=4) (actual time=771.068..771.068 rows=120,840 loops=1)

  • Buckets: 131072 Batches: 4 Memory Usage: 2090kB
6. 15.985 754.204 ↑ 1.6 120,840 1

Unique (cost=384,357.15..390,777.37 rows=195,541 width=4) (actual time=722.618..754.204 rows=120,840 loops=1)

7. 69.902 738.219 ↑ 9.9 129,793 1

Sort (cost=384,357.15..387,567.26 rows=1,284,043 width=4) (actual time=722.617..738.219 rows=129,793 loops=1)

  • Sort Key: t166_userrolehierarchy.sid
  • Sort Method: external merge Disk: 1768kB
8. 199.797 668.317 ↑ 9.9 129,793 1

Hash Join (cost=59,712.37..236,520.46 rows=1,284,043 width=4) (actual time=153.241..668.317 rows=129,793 loops=1)

  • Hash Cond: (t166_userrolehierarchy.c662_user_role_id = t67_userroledim.sid)
9. 323.439 456.373 ↓ 1.0 1,322,321 1

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

  • Recheck Cond: ((c663_ancestor_role_id = ANY ('{436,247157}'::integer[])) AND (end_stamp = '32503680000000'::bigint))
  • Heap Blocks: exact=39413
10. 132.934 132.934 ↓ 1.0 1,325,653 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.934..132.934 rows=1,325,653 loops=1)

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

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

  • Buckets: 131072 Batches: 2 Memory Usage: 1177kB
12. 6.316 10.811 ↑ 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=4.933..10.811 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: 5638
  • Heap Blocks: exact=2150
13. 4.495 4.495 ↑ 5.6 14,038 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.495..4.495 rows=14,038 loops=1)

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