explain.depesz.com

PostgreSQL's explain analyze made readable

Result: tpbh

Settings
# exclusive inclusive rows x rows loops node
1. 0.003 9,680.812 ↑ 1.0 20 1

Limit (cost=1,022,924.17..1,022,924.22 rows=20 width=376) (actual time=9,680.809..9,680.812 rows=20 loops=1)

2.          

CTE userinfo0cte

3. 0.665 0.665 ↓ 1.0 5,176 1

Seq Scan on userinfo userinfo0 (cost=0.00..165.75 rows=5,175 width=4) (actual time=0.003..0.665 rows=5,176 loops=1)

4. 0.113 9,680.809 ↑ 682.6 20 1

Sort (cost=1,022,758.42..1,022,792.55 rows=13,653 width=376) (actual time=9,680.809..9,680.809 rows=20 loops=1)

  • Sort Key: eravailability_factsandgroups1.primaryrolename3 COLLATE "en_US
  • Sort Method: top-N heapsort Memory: 30kB
5. 0.021 9,680.696 ↑ 121.9 112 1

Subquery Scan on eravailability_factsandgroups1 (cost=1,021,780.73..1,022,395.12 rows=13,653 width=376) (actual time=9,680.621..9,680.696 rows=112 loops=1)

6. 0.051 9,680.675 ↑ 121.9 112 1

Unique (cost=1,021,780.73..1,022,258.59 rows=13,653 width=408) (actual time=9,680.619..9,680.675 rows=112 loops=1)

7. 0.228 9,680.624 ↑ 121.9 112 1

Sort (cost=1,021,780.73..1,021,814.87 rows=13,653 width=408) (actual time=9,680.618..9,680.624 rows=112 loops=1)

  • Sort Key: upr.placeholderprojectroleid, (min((phpr.name)::text)) COLLATE "en_US", (count(DISTINCT f.userid)), ((sum(0.00000000) + CASE WHEN (sum((date_part('epoch'::text, f.scheduleduration))::numeric) > 0.00000000) THEN CASE WHEN (((1.00000000 - (sum((((date_part('epoch'::text, f.bookedtimeoffduration))::numeric + (date_part('epoch'::text, f.holidayduration))::numeric) + (date_part('epoch'::text, f.totalallocatedduration))::numeric)) / sum((date_part('epoch'::text, f.scheduleduration))::numeric))) * 100.00000000) IS NULL) THEN NULL::numeric ELSE CASE WHEN (((1.00000000 - (sum((((date_part('epoch'::text, f.bookedtimeoffduration))::numeric + (date_part('epoch'::text, f.holidayduration))::numeric) + (date_part('epoch'::text, f.totalallocatedduration))::numeric)) / sum((date_part('epoch'::text, f.scheduleduration))::numeric))) * 100.00000000) > 0.00000000) THEN ((1.00000000 - (sum((((date_part('epoch'::text, f.bookedtimeoffduration))::numeric + (date_part('epoch'::text, f.holidayduration))::numeric) + (date_part('epoch'::text, f.totalallocatedduration))::numeric)) / sum((date_part('epoch'::text, f.scheduleduration))::numeric))) * 100.00000000) ELSE 0.00000000 END END ELSE 0.00000000 END)), ((sum(0.00000000) + CASE WHEN (sum(CASE WHEN ((f.date >= '2020-08-01'::date) AND (f.date <= '2020-08-31'::date)) THEN (date_part('epoch'::text, f.scheduleduration))::numeric ELSE NULL::numeric END) > 0.00000000) THEN CASE WHEN (((1.00000000 - (sum(CASE WHEN ((f.date >= '2020-08-01'::date) AND (f.date <= '2020-08-31'::date)) THEN (((date_part('epoch'::text, f.bookedtimeoffduration))::numeric + (date_part('epoch'::text, f.holidayduration))::numeric) + (date_part('epoch'::text, f.totalallocatedduration))::numeric) ELSE NULL::numeric END) / sum(CASE WHEN ((f.date >= '2020-08-01'::date) AND (f.date <= '2020-08-31'::date)) THEN (date_part('epoch'::text, f.scheduleduration))::numeric ELSE NULL::numeric END))) * 100.00000000) IS NULL) THEN NULL::numeric ELSE CASE WHEN (((1.00000000 - (sum(CASE WHEN ((f.date >= '2020-08-01'::date) AND (f.date <= '2020-08-31'::date)) THEN (((date_part('epoch'::text, f.bookedtimeoffduration))::numeric + (date_part('epoch'::text, f.holidayduration))::numeric) + (date_part('epoch'::text, f.totalallocatedduration))::numeric) ELSE NULL::numeric END) / sum(CASE WHEN ((f.date >= '2020-08-01'::date) AND (f.date <= '2020-08-31'::date)) THEN (date_part('epoch'::text, f.scheduleduration))::numeric ELSE NULL::numeric END))) * 100.00000000) > 0.00000000) THEN ((1.00000000 - (sum(CASE WHEN ((f.date >= '2020-08-01'::date) AND (f.date <= '2020-08-31'::date)) THEN (((date_part('epoch'::text, f.bookedtimeoffduration))::numeric + (date_part('epoch'::text, f.holidayduration))::numeric) + (date_part('epoch'::text, f.totalallocatedduration))::numeric) ELSE NULL::numeric END) / sum(CASE WHEN ((f.date >= '2020-08-01'::date) AND (f.date <= '2020-08-31'::date)) THEN (date_part('epoch'::text, f.scheduleduration))::numeric ELSE NULL::numeric END))) * 100.00000000) ELSE 0.00000000 END END ELSE 0.00000000 END)), ((sum(0.00000000) + CASE WHEN (sum(CASE WHEN ((f.date >= '2020-09-01'::date) AND (f.date <= '2020-09-30'::date)) THEN (date_part('epoch'::text, f.scheduleduration))::numeric ELSE NULL::numeric END) > 0.00000000) THEN CASE WHEN (((1.00000000 - (sum(CASE WHEN ((f.date >= '2020-09-01'::date) AND (f.date <= '2020-09-30'::date)) THEN (((date_part('epoch'::text, f.bookedtimeoffduration))::numeric + (date_part('epoch'::text, f.holidayduration))::numeric) + (date_part('epoch'::text, f.totalallocatedduration))::numeric) ELSE NULL::numeric END) / sum(CASE WHEN ((f.date >= '2020-09-01'::date) AND (f.date <= '2020-09-30'::date)) THEN (date_part('epoch'::text, f.scheduleduration))::numeric ELSE NULL::numeric END))) * 100.00000000) IS NULL) THEN NULL::numeric ELSE CASE WHEN (((1.00000000 - (sum(CASE WHEN ((f.date >= '2020-09-01'::date) AND (f.date <= '2020-09-30'::date)) THEN (((date_part('epoch'::text, f.bookedtimeoffduration))::numeric + (date_part('epoch'::text, f.holidayduration))::numeric) + (date_part('epoch'::text, f.totalallocatedduration))::numeric) ELSE NULL::numeric END) / sum(CASE WHEN ((f.date >= '2020-09-01'::date) AND (f.date <= '2020-09-30'::date)) THEN (date_part('epoch'::text, f.scheduleduration))::numeric ELSE NULL::numeric END))) * 100.00000000) > 0.00000000) THEN ((1.00000000 - (sum(CASE WHEN ((f.date >= '2020-09-01'::date) AND (f.date <= '2020-09-30'::date)) THEN (((date_part('epoch'::text, f.bookedtimeoffduration))::numeric + (date_part('epoch'::text, f.holidayduration))::numeric) + (date_part('epoch'::text, f.totalallocatedduration))::numeric) ELSE NULL::numeric END) / sum(CASE WHEN ((f.date >= '2020-09-01'::date) AND (f.date <= '2020-09-30'::date)) THEN (date_part('epoch'::text, f.scheduleduration))::numeric ELSE NULL::numeric END))) * 100.00000000) ELSE 0.00000000 END END ELSE 0.00000000 END)), ((sum(0.00000000) + CASE WHEN (sum(CASE WHEN ((f.date >= '2020-10-01'::date) AND (f.date <= '2020-10-31'::date)) THEN (date_part('epoch'::text, f.scheduleduration))::numeric ELSE NULL::numeric END) > 0.00000000) THEN CASE WHEN (((1.00000000 - (sum(CASE WHEN ((f.date >= '2020-10-01'::date) AND (f.date <= '2020-10-31'::date)) THEN (((date_part('epoch'::text, f.bookedtimeoffduration))::numeric + (date_part('epoch'::text, f.holidayduration))::numeric) + (date_part('epoch'::text, f.totalallocatedduration))::numeric) ELSE NULL::numeric END) / sum(CASE WHEN ((f.date >= '2020-10-01'::date) AND (f.date <= '2020-10-31'::date)) THEN (date_part('epoch'::text, f.scheduleduration))::numeric ELSE NULL::numeric END))) * 100.00000000) IS NULL) THEN NULL::numeric ELSE CASE WHEN (((1.00000000 - (sum(CASE WHEN ((f.date >= '2020-10-01'::date) AND (f.date <= '2020-10-31'::date)) THEN (((date_part('epoch'::text, f.bookedtimeoffduration))::numeric + (date_part('epoch'::text, f.holidayduration))::numeric) + (date_part('epoch'::text, f.totalallocatedduration))::numeric) ELSE NULL::numeric END) / sum(CASE WHEN ((f.date >= '2020-10-01'::date) AND (f.date <= '2020-10-31'::date)) THEN (date_part('epoch'::text, f.scheduleduration))::numeric ELSE NULL::numeric END))) * 100.00000000) > 0.00000000) THEN ((1.00000000 - (sum(CASE WHEN ((f.date >= '2020-10-01'::date) AND (f.date <= '2020-10-31'::date)) THEN (((date_part('epoch'::text, f.bookedtimeoffduration))::numeric + (date_part('epoch'::text, f.holidayduration))::numeric) + (date_part('epoch'::text, f.totalallocatedduration))::numeric) ELSE NULL::numeric END) / sum(CASE WHEN ((f.date >= '2020-10-01'::date) AND (f.date <= '2020-10-31'::date)) THEN (date_part('epoch'::text, f.scheduleduration))::numeric ELSE NULL::numeric END))) * 100.00000000) ELSE 0.00000000 END END ELSE 0.00000000 END)), ((sum(0.00000000) + CASE WHEN (sum(CASE WHEN ((f.date >= '2020-11-01'::date) AND (f.date <= '2020-11-30'::date)) THEN (date_part('epoch'::text, f.scheduleduration))::numeric ELSE NULL::numeric END) > 0.00000000) THEN CASE WHEN (((1.00000000 - (sum(CASE WHEN ((f.date >= '2020-11-01'::date) AND (f.date <= '2020-11-30'::date)) THEN (((date_part('epoch'::text, f.bookedtimeoffduration))::numeric + (date_part('epoch'::text, f.holidayduration))::numeric) + (date_part('epoch'::text, f.totalallocatedduration))::numeric) ELSE NULL::numeric END) / sum(CASE WHEN ((f.date >= '2020-11-01'::date) AND (f.date <= '2020-11-30'::date)) THEN (date_part('epoch'::text, f.scheduleduration))::numeric ELSE NULL::numeric END))) * 100.00000000) IS NULL) THEN NULL::numeric ELSE CASE WHEN (((1.00000000 - (sum(CASE WHEN ((f.date >= '2020-11-01'::date) AND (f.date <= '2020-11-30'::date)) THEN (((date_part('epoch'::text, f.bookedtimeoffduration))::numeric + (date_part('epoch'::text, f.holidayduration))::numeric) + (date_part('epoch'::text, f.totalallocatedduration))::numeric) ELSE NULL::numeric END) / sum(CASE WHEN ((f.date >= '2020-11-01'::date) AND (f.date <= '2020-11-30'::date)) THEN (date_part('epoch'::text, f.scheduleduration))::numeric ELSE NULL::numeric END))) * 100.00000000) > 0.00000000) THEN ((1.00000000 - (sum(CASE WHEN ((f.date >= '2020-11-01'::date) AND (f.date <= '2020-11-30'::date)) THEN (((date_part('epoch'::text, f.bookedtimeoffduration))::numeric + (date_part('epoch'::text, f.holidayduration))::numeric) + (date_part('epoch'::text, f.totalallocatedduration))::numeric) ELSE NULL::numeric END) / sum(CASE WHEN ((f.date >= '2020-11-01'::date) AND (f.date <= '2020-11-30'::date)) THEN (date_part('epoch'::text, f.scheduleduration))::numeric ELSE NULL::numeric END))) * 100.00000000) ELSE 0.00000000 END END ELSE 0.00000000 END)), ((sum(0.00000000) + CASE WHEN (sum(CASE WHEN ((f.date >= '2020-12-01'::date) AND (f.date <= '2020-12-31'::date)) THEN (date_part('epoch'::text, f.scheduleduration))::numeric ELSE NULL::numeric END) > 0.00000000) THEN CASE WHEN (((1.00000000 - (sum(CASE WHEN ((f.date >= '2020-12-01'::date) AND (f.date <= '2020-12-31'::date)) THEN (((date_part('epoch'::text, f.bookedtimeoffduration))::numeric + (date_part('epoch'::text, f.holidayduration))::numeric) + (date_part('epoch'::text, f.totalallocatedduration))::numeric) ELSE NULL::numeric END) / sum(CASE WHEN ((f.date >= '2020-12-01'::date) AND (f.date <= '2020-12-31'::date)) THEN (date_part('epoch'::text, f.scheduleduration))::numeric ELSE NULL::numeric END))) * 100.00000000) IS NULL) THEN NULL::numeric ELSE CASE WHEN (((1.00000000 - (sum(CASE WHEN ((f.date >= '2020-12-01'::date) AND (f.date <= '2020-12-31'::date)) THEN (((date_part('epoch'::text, f.bookedtimeoffduration))::numeric + (date_part('epoch'::text, f.holidayduration))::numeric) + (date_part('epoch'::text, f.totalallocatedduration))::numeric) ELSE NULL::numeric END) / sum(CASE WHEN ((f.date >= '2020-12-01'::date) AND (f.date <= '2020-12-31'::date)) THEN (date_part('epoch'::text, f.scheduleduration))::numeric ELSE NULL::numeric END))) * 100.00000000) > 0.00000000) THEN ((1.00000000 - (sum(CASE WHEN ((f.date >= '2020-12-01'::date) AND (f.date <= '2020-12-31'::date)) THEN (((date_part('epoch'::text, f.bookedtimeoffduration))::numeric + (date_part('epoch'::text, f.holidayduration))::numeric) + (date_part('epoch'::text, f.totalallocatedduration))::numeric) ELSE NULL::numeric END) / sum(CASE WHEN ((f.date >= '2020-12-01'::date) AND (f.date <= '2020-12-31'::date)) THEN (date_part('epoch'::text, f.scheduleduration))::numeric ELSE NULL::numeric END))) * 100.00000000) ELSE 0.00000000 END END ELSE 0.00000000 END)), ((sum(0.00000000) + CASE WHEN (sum(CASE WHEN ((f.date >= '2021-01-01'::date) AND (f.date <= '2021-01-31'::date)) THEN (date_part('epoch'::text, f.scheduleduration))::numeric ELSE NULL::numeric END) > 0.00000000) THEN CASE WHEN (((1.00000000 - (sum(CASE WHEN ((f.date >= '2021-01-01'::date) AND (f.date <= '2021-01-31'::date)) THEN (((date_part('epoch'::text, f.bookedtimeoffduration))::numeric + (date_part('epoch'::text, f.holidayduration))::numeric) + (date_part('epoch'::text, f.totalallocatedduration))::numeric) ELSE NULL::numeric END) / sum(CASE WHEN ((f.date >= '2021-01-01'::date) AND (f.date <= '2021-01-31'::date)) THEN (date_part('epoch'::text, f.scheduleduration))::numeric ELSE NULL::numeric END))) * 100.00000000) IS NULL) THEN NULL::numeric ELSE CASE WHEN (((1.00000000 - (sum(CASE WHEN ((f.date >= '2021-01-01'::date) AND (f.date <= '2021-01-31'::date)) THEN (((date_part('epoch'::text, f.bookedtimeoffduration))::numeric + (date_part('epoch'::text, f.holidayduration))::numeric) + (date_part('epoch'::text, f.totalallocatedduration))::numeric) ELSE NULL::numeric END) / sum(CASE WHEN ((f.date >= '2021-01-01'::date) AND (f.date <= '2021-01-31'::date)) THEN (date_part('epoch'::text, f.scheduleduration))::numeric ELSE NULL::numeric END))) * 100.00000000) > 0.00000000) THEN ((1.00000000 - (sum(CASE WHEN ((f.date >= '2021-01-01'::date) AND (f.date <= '2021-01-31'::date)) THEN (((date_part('epoch'::text, f.bookedtimeoffduration))::numeric + (date_part('epoch'::text, f.holidayduration))::numeric) + (date_part('epoch'::text, f.totalallocatedduration))::numeric) ELSE NULL::numeric END) / sum(CASE WHEN ((f.date >= '2021-01-01'::date) AND (f.date <= '2021-01-31'::date)) THEN (date_part('epoch'::text, f.scheduleduration))::numeric ELSE NULL::numeric END))) * 100.00000000) ELSE 0.00000000 END END ELSE 0.00000000 END)), ((sum(0.00000000) + CASE WHEN (sum(CASE WHEN ((f.date >= '2021-02-01'::date) AND (f.date <= '2021-02-28'::date)) THEN (date_part('epoch'::text, f.scheduleduration))::numeric ELSE NULL::numeric END) > 0.00000000) THEN CASE WHEN (((1.00000000 - (sum(CASE WHEN ((f.date >= '2021-02-01'::date) AND (f.date <= '2021-02-28'::date)) THEN (((date_part('epoch'::text, f.bookedtimeoffduration))::numeric + (date_part('epoch'::text, f.holidayduration))::numeric) + (date_part('epoch'::text, f.totalallocatedduration))::numeric) ELSE NULL::numeric END) / sum(CASE WHEN ((f.date >= '2021-02-01'::date) AND (f.date <= '2021-02-28'::date)) THEN (date_part('epoch'::text, f.scheduleduration))::numeric ELSE NULL::numeric END))) * 100.00000000) IS NULL) THEN NULL::numeric ELSE CASE WHEN (((1.00000000 - (sum(CASE WHEN ((f.date >= '2021-02-01'::date) AND (f.date <= '2021-02-28'::date)) THEN (((date_part('epoch'::text, f.bookedtimeoffduration))::numeric + (date_part('epoch'::text, f.holidayduration))::numeric) + (date_part('epoch'::text, f.totalallocatedduration))::numeric) ELSE NULL::numeric END) / sum(CASE WHEN ((f.date >= '2021-02-01'::date) AND (f.date <= '2021-02-28'::date)) THEN (date_part('epoch'::text, f.scheduleduration))::numeric ELSE NULL::numeric END))) * 100.00000000) > 0.00000000) THEN ((1.00000000 - (sum(CASE WHEN ((f.date >= '2021-02-01'::date) AND (f.date <= '2021-02-28'::date)) THEN (((date_part('epoch'::text, f.bookedtimeoffduration))::numeric + (date_part('epoch'::text, f.holidayduration))::numeric) + (date_part('epoch'::text, f.totalallocatedduration))::numeric) ELSE NULL::numeric END) / sum(CASE WHEN ((f.date >= '2021-02-01'::date) AND (f.date <= '2021-02-28'::date)) THEN (date_part('epoch'::text, f.scheduleduration))::numeric ELSE NULL::numeric END))) * 100.00000000) ELSE 0.00000000 END END ELSE 0.00000000 END)), ((sum(0.00000000) + CASE WHEN (sum(CASE WHEN ((f.date >= '2021-03-01'::date) AND (f.date <= '2021-03-31'::date)) THEN (date_part('epoch'::text, f.scheduleduration))::numeric ELSE NULL::numeric END) > 0.00000000) THEN CASE WHEN (((1.00000000 - (sum(CASE WHEN ((f.date >= '2021-03-01'::date) AND (f.date <= '2021-03-31'::date)) THEN (((date_part('epoch'::text, f.bookedtimeoffduration))::numeric + (date_part('epoch'::text, f.holidayduration))::numeric) + (date_part('epoch'::text, f.totalallocatedduration))::numeric) ELSE NULL::numeric END) / sum(CASE WHEN ((f.date >= '2021-03-01'::date) AND (f.date <= '2021-03-31'::date)) THEN (date_part('epoch'::text, f.scheduleduration))::numeric ELSE NULL::numeric END))) * 100.00000000) IS NULL) THEN NULL::numeric ELSE CASE WHEN (((1.00000000 - (sum(CASE WHEN ((f.date >= '2021-03-01'::date) AND (f.date <= '2021-03-31'::date)) THEN (((date_part('epoch'::text, f.bookedtimeoffduration))::numeric + (date_part('epoch'::text, f.holidayduration))::numeric) + (date_part('epoch'::text, f.totalallocatedduration))::numeric) ELSE NULL::numeric END) / sum(CASE WHEN ((f.date >= '2021-03-01'::date) AND (f.date <= '2021-03-31'::date)) THEN (date_part('epoch'::text, f.scheduleduration))::numeric ELSE NULL::numeric END))) * 100.00000000) > 0.00000000) THEN ((1.00000000 - (sum(CASE WHEN ((f.date >= '2021-03-01'::date) AND (f.date <= '2021-03-31'::date)) THEN (((date_part('epoch'::text, f.bookedtimeoffduration))::numeric + (date_part('epoch'::text, f.holidayduration))::numeric) + (date_part('epoch'::text, f.totalallocatedduration))::numeric) ELSE NULL::numeric END) / sum(CASE WHEN ((f.date >= '2021-03-01'::date) AND (f.date <= '2021-03-31'::date)) THEN (date_part('epoch'::text, f.scheduleduration))::numeric ELSE NULL::numeric END))) * 100.00000000) ELSE 0.00000000 END END ELSE 0.00000000 END)), ((sum(0.00000000) + CASE WHEN (sum(CASE WHEN ((f.date >= '2021-04-01'::date) AND (f.date <= '2021-04-30'::date)) THEN (date_part('epoch'::text, f.scheduleduration))::numeric ELSE NULL::numeric END) > 0.00000000) THEN CASE WHEN (((1.00000000 - (sum(CASE WHEN ((f.date >= '2021-04-01'::date) AND (f.date <= '2021-04-30'::date)) THEN (((date_part('epoch'::text, f.bookedtimeoffduration))::numeric + (date_part('epoch'::text, f.holidayduration))::numeric) + (date_part('epoch'::text, f.totalallocatedduration))::numeric) ELSE NULL::numeric END) / sum(CASE WHEN ((f.date >= '2021-04-01'::date) AND (f.date <= '2021-04-30'::date)) THEN (date_part('epoch'::text, f.scheduleduration))::numeric ELSE NULL::numeric END))) * 100.00000000) IS NULL) THEN NULL::numeric ELSE CASE WHEN (((1.00000000 - (sum(CASE WHEN ((f.date >= '2021-04-01'::date) AND (f.date <= '2021-04-30'::date)) THEN (((date_part('epoch'::text, f.bookedtimeoffduration))::numeric + (date_part('epoch'::text, f.holidayduration))::numeric) + (date_part('epoch'::text, f.totalallocatedduration))::numeric) ELSE NULL::numeric END) / sum(CASE WHEN ((f.date >= '2021-04-01'::date) AND (f.date <= '2021-04-30'::date)) THEN (date_part('epoch'::text, f.scheduleduration))::numeric ELSE NULL::numeric END))) * 100.00000000) > 0.00000000) THEN ((1.00000000 - (sum(CASE WHEN ((f.date >= '2021-04-01'::date) AND (f.date <= '2021-04-30'::date)) THEN (((date_part('epoch'::text, f.bookedtimeoffduration))::numeric + (date_part('epoch'::text, f.holidayduration))::numeric) + (date_part('epoch'::text, f.totalallocatedduration))::numeric) ELSE NULL::numeric END) / sum(CASE WHEN ((f.date >= '2021-04-01'::date) AND (f.date <= '2021-04-30'::date)) THEN (date_part('epoch'::text, f.scheduleduration))::numeric ELSE NULL::numeric END))) * 100.00000000) ELSE 0.00000000 END END ELSE 0.00000000 END))
  • Sort Method: quicksort Memory: 54kB
8. 5,259.227 9,680.396 ↑ 121.9 112 1

GroupAggregate (cost=345,191.50..1,020,842.98 rows=13,653 width=408) (actual time=4,321.825..9,680.396 rows=112 loops=1)

  • Group Key: upr.placeholderprojectroleid, (upper((phpr.name)::text))
  • Filter: ((count(DISTINCT f.userid) IS NOT NULL) OR ((sum(0.00000000) + CASE WHEN (sum((date_part('epoch'::text, f.scheduleduration))::numeric) > 0.00000000) THEN CASE WHEN (((1.00000000 - (sum((((date_part('epoch'::text, f.bookedtimeoffduration))::numeric + (date_part('epoch'::text, f.holidayduration))::numeric) + (date_part('epoch'::text, f.totalallocatedduration))::numeric)) / sum((date_part('epoch'::text, f.scheduleduration))::numeric))) * 100.00000000) IS NULL) THEN NULL::numeric ELSE CASE WHEN (((1.00000000 - (sum((((date_part('epoch'::text, f.bookedtimeoffduration))::numeric + (date_part('epoch'::text, f.holidayduration))::numeric) + (date_part('epoch'::text, f.totalallocatedduration))::numeric)) / sum((date_part('epoch'::text, f.scheduleduration))::numeric))) * 100.00000000) > 0.00000000) THEN ((1.00000000 - (sum((((date_part('epoch'::text, f.bookedtimeoffduration))::numeric + (date_part('epoch'::text, f.holidayduration))::numeric) + (date_part('epoch'::text, f.totalallocatedduration))::numeric)) / sum((date_part('epoch'::text, f.scheduleduration))::numeric))) * 100.00000000) ELSE 0.00000000 END END ELSE 0.00000000 END) IS NOT NULL) OR ((sum(0.00000000) + CASE WHEN (sum(CASE WHEN ((f.date >= '2020-08-01'::date) AND (f.date <= '2020-08-31'::date)) THEN (date_part('epoch'::text, f.scheduleduration))::numeric ELSE NULL::numeric END) > 0.00000000) THEN CASE WHEN (((1.00000000 - (sum(CASE WHEN ((f.date >= '2020-08-01'::date) AND (f.date <= '2020-08-31'::date)) THEN (((date_part('epoch'::text, f.bookedtimeoffduration))::numeric + (date_part('epoch'::text, f.holidayduration))::numeric) + (date_part('epoch'::text, f.totalallocatedduration))::numeric) ELSE NULL::numeric END) / sum(CASE WHEN ((f.date >= '2020-08-01'::date) AND (f.date <= '2020-08-31'::date)) THEN (date_part('epoch'::text, f.scheduleduration))::numeric ELSE NULL::numeric END))) * 100.00000000) IS NULL) THEN NULL::numeric ELSE CASE WHEN (((1.00000000 - (sum(CASE WHEN ((f.date >= '2020-08-01'::date) AND (f.date <= '2020-08-31'::date)) THEN (((date_part('epoch'::text, f.bookedtimeoffduration))::numeric + (date_part('epoch'::text, f.holidayduration))::numeric) + (date_part('epoch'::text, f.totalallocatedduration))::numeric) ELSE NULL::numeric END) / sum(CASE WHEN ((f.date >= '2020-08-01'::date) AND (f.date <= '2020-08-31'::date)) THEN (date_part('epoch'::text, f.scheduleduration))::numeric ELSE NULL::numeric END))) * 100.00000000) > 0.00000000) THEN ((1.00000000 - (sum(CASE WHEN ((f.date >= '2020-08-01'::date) AND (f.date <= '2020-08-31'::date)) THEN (((date_part('epoch'::text, f.bookedtimeoffduration))::numeric + (date_part('epoch'::text, f.holidayduration))::numeric) + (date_part('epoch'::text, f.totalallocatedduration))::numeric) ELSE NULL::numeric END) / sum(CASE WHEN ((f.date >= '2020-08-01'::date) AND (f.date <= '2020-08-31'::date)) THEN (date_part('epoch'::text, f.scheduleduration))::numeric ELSE NULL::numeric END))) * 100.00000000) ELSE 0.00000000 END END ELSE 0.00000000 END) IS NOT NULL) OR ((sum(0.00000000) + CASE WHEN (sum(CASE WHEN ((f.date >= '2020-09-01'::date) AND (f.date <= '2020-09-30'::date)) THEN (date_part('epoch'::text, f.scheduleduration))::numeric ELSE NULL::numeric END) > 0.00000000) THEN CASE WHEN (((1.00000000 - (sum(CASE WHEN ((f.date >= '2020-09-01'::date) AND (f.date <= '2020-09-30'::date)) THEN (((date_part('epoch'::text, f.bookedtimeoffduration))::numeric + (date_part('epoch'::text, f.holidayduration))::numeric) + (date_part('epoch'::text, f.totalallocatedduration))::numeric) ELSE NULL::numeric END) / sum(CASE WHEN ((f.date >= '2020-09-01'::date) AND (f.date <= '2020-09-30'::date)) THEN (date_part('epoch'::text, f.scheduleduration))::numeric ELSE NULL::numeric END))) * 100.00000000) IS NULL) THEN NULL::numeric ELSE CASE WHEN (((1.00000000 - (sum(CASE WHEN ((f.date >= '2020-09-01'::date) AND (f.date <= '2020-09-30'::date)) THEN (((date_part('epoch'::text, f.bookedtimeoffduration))::numeric + (date_part('epoch'::text, f.holidayduration))::numeric) + (date_part('epoch'::text, f.totalallocatedduration))::numeric) ELSE NULL::numeric END) / sum(CASE WHEN ((f.date >= '2020-09-01'::date) AND (f.date <= '2020-09-30'::date)) THEN (date_part('epoch'::text, f.scheduleduration))::numeric ELSE NULL::numeric END))) * 100.00000000) > 0.00000000) THEN ((1.00000000 - (sum(CASE WHEN ((f.date >= '2020-09-01'::date) AND (f.date <= '2020-09-30'::date)) THEN (((date_part('epoch'::text, f.bookedtimeoffduration))::numeric + (date_part('epoch'::text, f.holidayduration))::numeric) + (date_part('epoch'::text, f.totalallocatedduration))::numeric) ELSE NULL::numeric END) / sum(CASE WHEN ((f.date >= '2020-09-01'::date) AND (f.date <= '2020-09-30'::date)) THEN (date_part('epoch'::text, f.scheduleduration))::numeric ELSE NULL::numeric END))) * 100.00000000) ELSE 0.00000000 END END ELSE 0.00000000 END) IS NOT NULL) OR ((sum(0.00000000) + CASE WHEN (sum(CASE WHEN ((f.date >= '2020-10-01'::date) AND (f.date <= '2020-10-31'::date)) THEN (date_part('epoch'::text, f.scheduleduration))::numeric ELSE NULL::numeric END) > 0.00000000) THEN CASE WHEN (((1.00000000 - (sum(CASE WHEN ((f.date >= '2020-10-01'::date) AND (f.date <= '2020-10-31'::date)) THEN (((date_part('epoch'::text, f.bookedtimeoffduration))::numeric + (date_part('epoch'::text, f.holidayduration))::numeric) + (date_part('epoch'::text, f.totalallocatedduration))::numeric) ELSE NULL::numeric END) / sum(CASE WHEN ((f.date >= '2020-10-01'::date) AND (f.date <= '2020-10-31'::date)) THEN (date_part('epoch'::text, f.scheduleduration))::numeric ELSE NULL::numeric END))) * 100.00000000) IS NULL) THEN NULL::numeric ELSE CASE WHEN (((1.00000000 - (sum(CASE WHEN ((f.date >= '2020-10-01'::date) AND (f.date <= '2020-10-31'::date)) THEN (((date_part('epoch'::text, f.bookedtimeoffduration))::numeric + (date_part('epoch'::text, f.holidayduration))::numeric) + (date_part('epoch'::text, f.totalallocatedduration))::numeric) ELSE NULL::numeric END) / sum(CASE WHEN ((f.date >= '2020-10-01'::date) AND (f.date <= '2020-10-31'::date)) THEN (date_part('epoch'::text, f.scheduleduration))::numeric ELSE NULL::numeric END))) * 100.00000000) > 0.00000000) THEN ((1.00000000 - (sum(CASE WHEN ((f.date >= '2020-10-01'::date) AND (f.date <= '2020-10-31'::date)) THEN (((date_part('epoch'::text, f.bookedtimeoffduration))::numeric + (date_part('epoch'::text, f.holidayduration))::numeric) + (date_part('epoch'::text, f.totalallocatedduration))::numeric) ELSE NULL::numeric END) / sum(CASE WHEN ((f.date >= '2020-10-01'::date) AND (f.date <= '2020-10-31'::date)) THEN (date_part('epoch'::text, f.scheduleduration))::numeric ELSE NULL::numeric END))) * 100.00000000) ELSE 0.00000000 END END ELSE 0.00000000 END) IS NOT NULL) OR ((sum(0.00000000) + CASE WHEN (sum(CASE WHEN ((f.date >= '2020-11-01'::date) AND (f.date <= '2020-11-30'::date)) THEN (date_part('epoch'::text, f.scheduleduration))::numeric ELSE NULL::numeric END) > 0.00000000) THEN CASE WHEN (((1.00000000 - (sum(CASE WHEN ((f.date >= '2020-11-01'::date) AND (f.date <= '2020-11-30'::date)) THEN (((date_part('epoch'::text, f.bookedtimeoffduration))::numeric + (date_part('epoch'::text, f.holidayduration))::numeric) + (date_part('epoch'::text, f.totalallocatedduration))::numeric) ELSE NULL::numeric END) / sum(CASE WHEN ((f.date >= '2020-11-01'::date) AND (f.date <= '2020-11-30'::date)) THEN (date_part('epoch'::text, f.scheduleduration))::numeric ELSE NULL::numeric END))) * 100.00000000) IS NULL) THEN NULL::numeric ELSE CASE WHEN (((1.00000000 - (sum(CASE WHEN ((f.date >= '2020-11-01'::date) AND (f.date <= '2020-11-30'::date)) THEN (((date_part('epoch'::text, f.bookedtimeoffduration))::numeric + (date_part('epoch'::text, f.holidayduration))::numeric) + (date_part('epoch'::text, f.totalallocatedduration))::numeric) ELSE NULL::numeric END) / sum(CASE WHEN ((f.date >= '2020-11-01'::date) AND (f.date <= '2020-11-30'::date)) THEN (date_part('epoch'::text, f.scheduleduration))::numeric ELSE NULL::numeric END))) * 100.00000000) > 0.00000000) THEN ((1.00000000 - (sum(CASE WHEN ((f.date >= '2020-11-01'::date) AND (f.date <= '2020-11-30'::date)) THEN (((date_part('epoch'::text, f.bookedtimeoffduration))::numeric + (date_part('epoch'::text, f.holidayduration))::numeric) + (date_part('epoch'::text, f.totalallocatedduration))::numeric) ELSE NULL::numeric END) / sum(CASE WHEN ((f.date >= '2020-11-01'::date) AND (f.date <= '2020-11-30'::date)) THEN (date_part('epoch'::text, f.scheduleduration))::numeric ELSE NULL::numeric END))) * 100.00000000) ELSE 0.00000000 END END ELSE 0.00000000 END) IS NOT NULL) OR ((sum(0.00000000) + CASE WHEN (sum(CASE WHEN ((f.date >= '2020-12-01'::date) AND (f.date <= '2020-12-31'::date)) THEN (date_part('epoch'::text, f.scheduleduration))::numeric ELSE NULL::numeric END) > 0.00000000) THEN CASE WHEN (((1.00000000 - (sum(CASE WHEN ((f.date >= '2020-12-01'::date) AND (f.date <= '2020-12-31'::date)) THEN (((date_part('epoch'::text, f.bookedtimeoffduration))::numeric + (date_part('epoch'::text, f.holidayduration))::numeric) + (date_part('epoch'::text, f.totalallocatedduration))::numeric) ELSE NULL::numeric END) / sum(CASE WHEN ((f.date >= '2020-12-01'::date) AND (f.date <= '2020-12-31'::date)) THEN (date_part('epoch'::text, f.scheduleduration))::numeric ELSE NULL::numeric END))) * 100.00000000) IS NULL) THEN NULL::numeric ELSE CASE WHEN (((1.00000000 - (sum(CASE WHEN ((f.date >= '2020-12-01'::date) AND (f.date <= '2020-12-31'::date)) THEN (((date_part('epoch'::text, f.bookedtimeoffduration))::numeric + (date_part('epoch'::text, f.holidayduration))::numeric) + (date_part('epoch'::text, f.totalallocatedduration))::numeric) ELSE NULL::numeric END) / sum(CASE WHEN ((f.date >= '2020-12-01'::date) AND (f.date <= '2020-12-31'::date)) THEN (date_part('epoch'::text, f.scheduleduration))::numeric ELSE NULL::numeric END))) * 100.00000000) > 0.00000000) THEN ((1.00000000 - (sum(CASE WHEN ((f.date >= '2020-12-01'::date) AND (f.date <= '2020-12-31'::date)) THEN (((date_part('epoch'::text, f.bookedtimeoffduration))::numeric + (date_part('epoch'::text, f.holidayduration))::numeric) + (date_part('epoch'::text, f.totalallocatedduration))::numeric) ELSE NULL::numeric END) / sum(CASE WHEN ((f.date >= '2020-12-01'::date) AND (f.date <= '2020-12-31'::date)) THEN (date_part('epoch'::text, f.scheduleduration))::numeric ELSE NULL::numeric END))) * 100.00000000) ELSE 0.00000000 END END ELSE 0.00000000 END) IS NOT NULL) OR ((sum(0.00000000) + CASE WHEN (sum(CASE WHEN ((f.date >= '2021-01-01'::date) AND (f.date <= '2021-01-31'::date)) THEN (date_part('epoch'::text, f.scheduleduration))::numeric ELSE NULL::numeric END) > 0.00000000) THEN CASE WHEN (((1.00000000 - (sum(CASE WHEN ((f.date >= '2021-01-01'::date) AND (f.date <= '2021-01-31'::date)) THEN (((date_part('epoch'::text, f.bookedtimeoffduration))::numeric + (date_part('epoch'::text, f.holidayduration))::numeric) + (date_part('epoch'::text, f.totalallocatedduration))::numeric) ELSE NULL::numeric END) / sum(CASE WHEN ((f.date >= '2021-01-01'::date) AND (f.date <= '2021-01-31'::date)) THEN (date_part('epoch'::text, f.scheduleduration))::numeric ELSE NULL::numeric END))) * 100.00000000) IS NULL) THEN NULL::numeric ELSE CASE WHEN (((1.00000000 - (sum(CASE WHEN ((f.date >= '2021-01-01'::date) AND (f.date <= '2021-01-31'::date)) THEN (((date_part('epoch'::text, f.bookedtimeoffduration))::numeric + (date_part('epoch'::text, f.holidayduration))::numeric) + (date_part('epoch'::text, f.totalallocatedduration))::numeric) ELSE NULL::numeric END) / sum(CASE WHEN ((f.date >= '2021-01-01'::date) AND (f.date <= '2021-01-31'::date)) THEN (date_part('epoch'::text, f.scheduleduration))::numeric ELSE NULL::numeric END))) * 100.00000000) > 0.00000000) THEN ((1.00000000 - (sum(CASE WHEN ((f.date >= '2021-01-01'::date) AND (f.date <= '2021-01-31'::date)) THEN (((date_part('epoch'::text, f.bookedtimeoffduration))::numeric + (date_part('epoch'::text, f.holidayduration))::numeric) + (date_part('epoch'::text, f.totalallocatedduration))::numeric) ELSE NULL::numeric END) / sum(CASE WHEN ((f.date >= '2021-01-01'::date) AND (f.date <= '2021-01-31'::date)) THEN (date_part('epoch'::text, f.scheduleduration))::numeric ELSE NULL::numeric END))) * 100.00000000) ELSE 0.00000000 END END ELSE 0.00000000 END) IS NOT NULL) OR ((sum(0.00000000) + CASE WHEN (sum(CASE WHEN ((f.date >= '2021-02-01'::date) AND (f.date <= '2021-02-28'::date)) THEN (date_part('epoch'::text, f.scheduleduration))::numeric ELSE NULL::numeric END) > 0.00000000) THEN CASE WHEN (((1.00000000 - (sum(CASE WHEN ((f.date >= '2021-02-01'::date) AND (f.date <= '2021-02-28'::date)) THEN (((date_part('epoch'::text, f.bookedtimeoffduration))::numeric + (date_part('epoch'::text, f.holidayduration))::numeric) + (date_part('epoch'::text, f.totalallocatedduration))::numeric) ELSE NULL::numeric END) / sum(CASE WHEN ((f.date >= '2021-02-01'::date) AND (f.date <= '2021-02-28'::date)) THEN (date_part('epoch'::text, f.scheduleduration))::numeric ELSE NULL::numeric END))) * 100.00000000) IS NULL) THEN NULL::numeric ELSE CASE WHEN (((1.00000000 - (sum(CASE WHEN ((f.date >= '2021-02-01'::date) AND (f.date <= '2021-02-28'::date)) THEN (((date_part('epoch'::text, f.bookedtimeoffduration))::numeric + (date_part('epoch'::text, f.holidayduration))::numeric) + (date_part('epoch'::text, f.totalallocatedduration))::numeric) ELSE NULL::numeric END) / sum(CASE WHEN ((f.date >= '2021-02-01'::date) AND (f.date <= '2021-02-28'::date)) THEN (date_part('epoch'::text, f.scheduleduration))::numeric ELSE NULL::numeric END))) * 100.00000000) > 0.00000000) THEN ((1.00000000 - (sum(CASE WHEN ((f.date >= '2021-02-01'::date) AND (f.date <= '2021-02-28'::date)) THEN (((date_part('epoch'::text, f.bookedtimeoffduration))::numeric + (date_part('epoch'::text, f.holidayduration))::numeric) + (date_part('epoch'::text, f.totalallocatedduration))::numeric) ELSE NULL::numeric END) / sum(CASE WHEN ((f.date >= '2021-02-01'::date) AND (f.date <= '2021-02-28'::date)) THEN (date_part('epoch'::text, f.scheduleduration))::numeric ELSE NULL::numeric END))) * 100.00000000) ELSE 0.00000000 END END ELSE 0.00000000 END) IS NOT NULL) OR ((sum(0.00000000) + CASE WHEN (sum(CASE WHEN ((f.date >= '2021-03-01'::date) AND (f.date <= '2021-03-31'::date)) THEN (date_part('epoch'::text, f.scheduleduration))::numeric ELSE NULL::numeric END) > 0.00000000) THEN CASE WHEN (((1.00000000 - (sum(CASE WHEN ((f.date >= '2021-03-01'::date) AND (f.date <= '2021-03-31'::date)) THEN (((date_part('epoch'::text, f.bookedtimeoffduration))::numeric + (date_part('epoch'::text, f.holidayduration))::numeric) + (date_part('epoch'::text, f.totalallocatedduration))::numeric) ELSE NULL::numeric END) / sum(CASE WHEN ((f.date >= '2021-03-01'::date) AND (f.date <= '2021-03-31'::date)) THEN (date_part('epoch'::text, f.scheduleduration))::numeric ELSE NULL::numeric END))) * 100.00000000) IS NULL) THEN NULL::numeric ELSE CASE WHEN (((1.00000000 - (sum(CASE WHEN ((f.date >= '2021-03-01'::date) AND (f.date <= '2021-03-31'::date)) THEN (((date_part('epoch'::text, f.bookedtimeoffduration))::numeric + (date_part('epoch'::text, f.holidayduration))::numeric) + (date_part('epoch'::text, f.totalallocatedduration))::numeric) ELSE NULL::numeric END) / sum(CASE WHEN ((f.date >= '2021-03-01'::date) AND (f.date <= '2021-03-31'::date)) THEN (date_part('epoch'::text, f.scheduleduration))::numeric ELSE NULL::numeric END))) * 100.00000000) > 0.00000000) THEN ((1.00000000 - (sum(CASE WHEN ((f.date >= '2021-03-01'::date) AND (f.date <= '2021-03-31'::date)) THEN (((date_part('epoch'::text, f.bookedtimeoffduration))::numeric + (date_part('epoch'::text, f.holidayduration))::numeric) + (date_part('epoch'::text, f.totalallocatedduration))::numeric) ELSE NULL::numeric END) / sum(CASE WHEN ((f.date >= '2021-03-01'::date) AND (f.date <= '2021-03-31'::date)) THEN (date_part('epoch'::text, f.scheduleduration))::numeric ELSE NULL::numeric END))) * 100.00000000) ELSE 0.00000000 END END ELSE 0.00000000 END) IS NOT NULL) OR ((sum(0.00000000) + CASE WHEN (sum(CASE WHEN ((f.date >= '2021-04-01'::date) AND (f.date <= '2021-04-30'::date)) THEN (date_part('epoch'::text, f.scheduleduration))::numeric ELSE NULL::numeric END) > 0.00000000) THEN CASE WHEN (((1.00000000 - (sum(CASE WHEN ((f.date >= '2021-04-01'::date) AND (f.date <= '2021-04-30'::date)) THEN (((date_part('epoch'::text, f.bookedtimeoffduration))::numeric + (date_part('epoch'::text, f.holidayduration))::numeric) + (date_part('epoch'::text, f.totalallocatedduration))::numeric) ELSE NULL::numeric END) / sum(CASE WHEN ((f.date >= '2021-04-01'::date) AND (f.date <= '2021-04-30'::date)) THEN (date_part('epoch'::text, f.scheduleduration))::numeric ELSE NULL::numeric END))) * 100.00000000) IS NULL) THEN NULL::numeric ELSE CASE WHEN (((1.00000000 - (sum(CASE WHEN ((f.date >= '2021-04-01'::date) AND (f.date <= '2021-04-30'::date)) THEN (((date_part('epoch'::text, f.bookedtimeoffduration))::numeric + (date_part('epoch'::text, f.holidayduration))::numeric) + (date_part('epoch'::text, f.totalallocatedduration))::numeric) ELSE NULL::numeric END) / sum(CASE WHEN ((f.date >= '2021-04-01'::date) AND (f.date <= '2021-04-30'::date)) THEN (date_part('epoch'::text, f.scheduleduration))::numeric ELSE NULL::numeric END))) * 100.00000000) > 0.00000000) THEN ((1.00000000 - (sum(CASE WHEN ((f.date >= '2021-04-01'::date) AND (f.date <= '2021-04-30'::date)) THEN (((date_part('epoch'::text, f.bookedtimeoffduration))::numeric + (date_part('epoch'::text, f.holidayduration))::numeric) + (date_part('epoch'::text, f.totalallocatedduration))::numeric) ELSE NULL::numeric END) / sum(CASE WHEN ((f.date >= '2021-04-01'::date) AND (f.date <= '2021-04-30'::date)) THEN (date_part('epoch'::text, f.scheduleduration))::numeric ELSE NULL::numeric END))) * 100.00000000) ELSE 0.00000000 END END ELSE 0.00000000 END) IS NOT NULL))
9. 1,026.600 4,421.169 ↓ 4.0 1,008,930 1

Sort (cost=345,191.50..345,820.92 rows=251,767 width=147) (actual time=4,258.817..4,421.169 rows=1,008,930 loops=1)

  • Sort Key: upr.placeholderprojectroleid, (upper((phpr.name)::text)) COLLATE "en_US
  • Sort Method: external merge Disk: 155,912kB
10. 712.239 3,394.569 ↓ 4.0 1,008,930 1

Hash Join (cost=711.17..322,605.83 rows=251,767 width=147) (actual time=10.785..3,394.569 rows=1,008,930 loops=1)

  • Hash Cond: (f.userid = userinfo4.id)
11. 246.752 2,674.458 ↓ 1.0 1,009,125 1

Hash Left Join (cost=332.38..318,952.41 rows=1,006,874 width=115) (actual time=2.900..2,674.458 rows=1,009,125 loops=1)

  • Hash Cond: (f.userid = ueg.userid)
  • Join Filter: ((ueg.enddate >= f.date) AND (ueg.startdate <= f.date))
12. 254.709 2,426.339 ↓ 1.0 1,009,125 1

Hash Left Join (cost=166.99..299,911.05 rows=1,006,874 width=115) (actual time=1.501..2,426.339 rows=1,009,125 loops=1)

  • Hash Cond: (f.userid = udg.userid)
  • Join Filter: ((udg.enddate >= f.date) AND (udg.startdate <= f.date))
13. 132.409 2,170.252 ↓ 1.0 1,009,125 1

Merge Left Join (cost=1.60..280,869.70 rows=1,006,874 width=115) (actual time=0.088..2,170.252 rows=1,009,125 loops=1)

  • Merge Cond: (f.userid = us.userid)
  • Join Filter: ((us.enddate >= f.date) AND (us.startdate <= f.date))
14. 115.429 2,037.837 ↓ 1.0 1,009,125 1

Merge Left Join (cost=1.44..273,926.39 rows=1,006,874 width=115) (actual time=0.081..2,037.837 rows=1,009,125 loops=1)

  • Merge Cond: (f.userid = uc.userid)
  • Join Filter: ((uc.enddate >= f.date) AND (uc.startdate <= f.date))
15. 118.429 1,922.403 ↓ 1.0 1,009,125 1

Merge Left Join (cost=1.29..266,983.08 rows=1,006,874 width=115) (actual time=0.076..1,922.403 rows=1,009,125 loops=1)

  • Merge Cond: (f.userid = ud.userid)
  • Join Filter: ((ud.enddate >= f.date) AND (ud.startdate <= f.date))
16. 291.036 1,803.969 ↓ 1.0 1,009,125 1

Merge Left Join (cost=1.14..260,039.77 rows=1,006,874 width=115) (actual time=0.070..1,803.969 rows=1,009,125 loops=1)

  • Merge Cond: (f.userid = ul.userid)
  • Join Filter: ((ul.enddate >= f.date) AND (ul.startdate <= f.date))
17. 361.970 1,450.616 ↓ 1.0 1,009,125 1

Merge Left Join (cost=0.85..237,228.13 rows=1,006,874 width=115) (actual time=0.046..1,450.616 rows=1,009,125 loops=1)

  • Merge Cond: (f.userid = upr.userid)
  • Join Filter: ((upr.enddate >= f.date) AND (upr.startdate <= f.date))
18. 1,016.374 1,016.374 ↓ 1.0 1,009,125 1

Index Scan using dm_useravailability_facts_pkey on dm_useravailability_facts f (cost=0.43..213,472.62 rows=1,006,874 width=72) (actual time=0.021..1,016.374 rows=1,009,125 loops=1)

  • Index Cond: ((date >= '2020-08-01'::date) AND (date <= '2021-04-30'::date))
19. 59.205 72.272 ↓ 195.0 1,008,346 1

Materialize (cost=0.42..1,121.56 rows=5,172 width=55) (actual time=0.024..72.272 rows=1,008,346 loops=1)

20. 5.870 13.067 ↑ 1.0 5,172 1

Nested Loop Left Join (cost=0.42..1,108.63 rows=5,172 width=55) (actual time=0.020..13.067 rows=5,172 loops=1)

21. 2.025 2.025 ↑ 1.0 5,172 1

Index Scan using ixuprauserid on userprojectroleassignment upr (cost=0.28..276.86 rows=5,172 width=28) (actual time=0.012..2.025 rows=5,172 loops=1)

  • Filter: isprimary
22. 5.172 5.172 ↑ 1.0 1 5,172

Index Scan using placeholderprojectrole_pkey on placeholderprojectrole phpr (cost=0.14..0.16 rows=1 width=43) (actual time=0.001..0.001 rows=1 loops=5,172)

  • Index Cond: (id = upr.placeholderprojectroleid)
23. 59.731 62.317 ↓ 195.0 1,008,541 1

Materialize (cost=0.28..173.81 rows=5,173 width=28) (actual time=0.023..62.317 rows=1,008,541 loops=1)

24. 2.586 2.586 ↑ 1.0 5,173 1

Index Only Scan using ix4ul_userlocationstartend on userlocation ul (cost=0.28..160.88 rows=5,173 width=28) (actual time=0.022..2.586 rows=5,173 loops=1)

  • Heap Fetches: 5,173
25. 0.000 0.005 ↓ 0.0 0 1

Materialize (cost=0.15..30.93 rows=1,130 width=28) (actual time=0.005..0.005 rows=0 loops=1)

26. 0.005 0.005 ↓ 0.0 0 1

Index Only Scan using ix4ud_userdivisionstartend on userdivision ud (cost=0.15..28.10 rows=1,130 width=28) (actual time=0.005..0.005 rows=0 loops=1)

  • Heap Fetches: 0
27. 0.002 0.005 ↓ 0.0 0 1

Materialize (cost=0.15..30.93 rows=1,130 width=28) (actual time=0.005..0.005 rows=0 loops=1)

28. 0.003 0.003 ↓ 0.0 0 1

Index Only Scan using ix4ucc_usercostcenterstartend on usercostcenter uc (cost=0.15..28.10 rows=1,130 width=28) (actual time=0.003..0.003 rows=0 loops=1)

  • Heap Fetches: 0
29. 0.001 0.006 ↓ 0.0 0 1

Materialize (cost=0.15..30.93 rows=1,130 width=28) (actual time=0.006..0.006 rows=0 loops=1)

30. 0.005 0.005 ↓ 0.0 0 1

Index Only Scan using ix4usc_userservicecenterstartend on userservicecenter us (cost=0.15..28.10 rows=1,130 width=28) (actual time=0.004..0.005 rows=0 loops=1)

  • Heap Fetches: 0
31. 0.783 1.378 ↓ 1.0 5,174 1

Hash (cost=100.73..100.73 rows=5,173 width=28) (actual time=1.378..1.378 rows=5,174 loops=1)

  • Buckets: 8,192 Batches: 1 Memory Usage: 368kB
32. 0.595 0.595 ↓ 1.0 5,174 1

Seq Scan on userdepartmentgroup udg (cost=0.00..100.73 rows=5,173 width=28) (actual time=0.004..0.595 rows=5,174 loops=1)

33. 0.770 1.367 ↑ 1.0 5,173 1

Hash (cost=100.73..100.73 rows=5,173 width=28) (actual time=1.367..1.367 rows=5,173 loops=1)

  • Buckets: 8,192 Batches: 1 Memory Usage: 368kB
34. 0.597 0.597 ↑ 1.0 5,173 1

Seq Scan on useremployeetypegroup ueg (cost=0.00..100.73 rows=5,173 width=28) (actual time=0.005..0.597 rows=5,173 loops=1)

35. 0.676 7.872 ↓ 4.0 5,174 1

Hash (cost=362.61..362.61 rows=1,294 width=4) (actual time=7.872..7.872 rows=5,174 loops=1)

  • Buckets: 8,192 (originally 2048) Batches: 1 (originally 1) Memory Usage: 246kB
36. 4.047 7.196 ↓ 4.0 5,174 1

Seq Scan on userinfo userinfo4 (cost=158.05..362.61 rows=1,294 width=4) (actual time=2.618..7.196 rows=5,174 loops=1)

  • Filter: ((id = 2) OR ((hashed SubPlan 2) AND (hashed SubPlan 3)))
  • Rows Removed by Filter: 2
37.          

SubPlan (for Seq Scan)

38. 0.511 1.547 ↓ 20.5 5,174 1

Nested Loop (cost=0.28..40.98 rows=253 width=4) (actual time=0.046..1.547 rows=5,174 loops=1)

39. 0.026 0.026 ↓ 2.0 2 1

Seq Scan on permissionsetrole permissionsetrole6 (cost=0.00..1.31 rows=1 width=16) (actual time=0.019..0.026 rows=2 loops=1)

  • Filter: (upper((roleuri)::text) = 'URN:REPLICON:USER-ACCESS-ROLE:PROJECT-RESOURCE'::text)
  • Rows Removed by Filter: 19
40. 1.010 1.010 ↓ 7.8 2,587 2

Index Scan using ixupspermissionsetid on userpermissionset userpermissionset5 (cost=0.28..36.34 rows=333 width=20) (actual time=0.018..0.505 rows=2,587 loops=2)

  • Index Cond: (permissionsetid = permissionsetrole6.permissionsetid)
41. 1.602 1.602 ↓ 1.0 5,176 1

CTE Scan on userinfo0cte userinfo7 (cost=0.00..103.50 rows=5,175 width=4) (actual time=0.004..1.602 rows=5,176 loops=1)

Planning time : 6.656 ms
Execution time : 9,704.155 ms