explain.depesz.com

PostgreSQL's explain analyze made readable

Result: ogr

Settings
# exclusive inclusive rows x rows loops node
1. 0.002 3.892 ↑ 100.0 1 1

Limit (cost=1,541.22..1,541.47 rows=100 width=1,176) (actual time=3.891..3.892 rows=1 loops=1)

2.          

CTE userinfo0cte

3. 0.000 0.000 ↓ 0.0 0

Seq Scan on userinfo userinfo0 (cost=0.00..1.02 rows=2 width=4) (never executed)

4. 0.009 3.890 ↑ 440.0 1 1

Sort (cost=1,540.20..1,541.30 rows=440 width=1,176) (actual time=3.889..3.890 rows=1 loops=1)

  • Sort Key: eravailability_factsandgroups1.primaryrolename3 COLLATE "en_US
  • Sort Method: quicksort Memory: 27kB
5. 0.004 3.881 ↑ 440.0 1 1

Subquery Scan on eravailability_factsandgroups1 (cost=1,437.58..1,523.38 rows=440 width=1,176) (actual time=3.878..3.881 rows=1 loops=1)

6. 0.002 3.877 ↑ 440.0 1 1

Unique (cost=1,437.58..1,518.98 rows=440 width=1,208) (actual time=3.876..3.877 rows=1 loops=1)

7. 0.063 3.875 ↑ 440.0 1 1

Sort (cost=1,437.58..1,438.68 rows=440 width=1,208) (actual time=3.875..3.875 rows=1 loops=1)

  • Sort Key: upr.placeholderprojectroleid, (min((phpr.name)::text)) COLLATE "en_US", (sum(f.totalallocatedduration)), (sum(CASE WHEN ((f.date >= '2020-07-01'::date) AND (f.date <= '2020-07-31'::date)) THEN f.totalallocatedduration ELSE NULL::interval END)), (sum(CASE WHEN ((f.date >= '2020-08-01'::date) AND (f.date <= '2020-08-31'::date)) THEN f.totalallocatedduration ELSE NULL::interval END)), (sum(CASE WHEN ((f.date >= '2020-09-01'::date) AND (f.date <= '2020-09-30'::date)) THEN f.totalallocatedduration ELSE NULL::interval END)), (sum(CASE WHEN ((f.date >= '2020-10-01'::date) AND (f.date <= '2020-10-31'::date)) THEN f.totalallocatedduration ELSE NULL::interval END)), (sum(CASE WHEN ((f.date >= '2020-11-01'::date) AND (f.date <= '2020-11-30'::date)) THEN f.totalallocatedduration ELSE NULL::interval END)), (sum(CASE WHEN ((f.date >= '2020-12-01'::date) AND (f.date <= '2020-12-31'::date)) THEN f.totalallocatedduration ELSE NULL::interval END)), (sum(CASE WHEN ((f.date >= '2021-01-01'::date) AND (f.date <= '2021-01-31'::date)) THEN f.totalallocatedduration ELSE NULL::interval END)), (sum(CASE WHEN ((f.date >= '2021-02-01'::date) AND (f.date <= '2021-02-28'::date)) THEN f.totalallocatedduration ELSE NULL::interval END)), (sum(CASE WHEN ((f.date >= '2021-03-01'::date) AND (f.date <= '2021-03-31'::date)) THEN f.totalallocatedduration ELSE NULL::interval END)), (sum(CASE WHEN ((f.date >= '2021-04-01'::date) AND (f.date <= '2021-04-30'::date)) THEN f.totalallocatedduration ELSE NULL::interval END)), (sum(CASE WHEN ((f.date >= '2021-05-01'::date) AND (f.date <= '2021-05-31'::date)) THEN f.totalallocatedduration ELSE NULL::interval END)), (sum(CASE WHEN ((f.date >= '2021-06-01'::date) AND (f.date <= '2021-06-30'::date)) THEN f.totalallocatedduration ELSE NULL::interval END)), (sum(CASE WHEN ((f.date >= '2021-07-01'::date) AND (f.date <= '2021-07-31'::date)) THEN f.totalallocatedduration ELSE NULL::interval END)), (sum(f.holidayduration)), (sum(CASE WHEN ((f.date >= '2020-07-01'::date) AND (f.date <= '2020-07-31'::date)) THEN f.holidayduration ELSE NULL::interval END)), (sum(CASE WHEN ((f.date >= '2020-08-01'::date) AND (f.date <= '2020-08-31'::date)) THEN f.holidayduration ELSE NULL::interval END)), (sum(CASE WHEN ((f.date >= '2020-09-01'::date) AND (f.date <= '2020-09-30'::date)) THEN f.holidayduration ELSE NULL::interval END)), (sum(CASE WHEN ((f.date >= '2020-10-01'::date) AND (f.date <= '2020-10-31'::date)) THEN f.holidayduration ELSE NULL::interval END)), (sum(CASE WHEN ((f.date >= '2020-11-01'::date) AND (f.date <= '2020-11-30'::date)) THEN f.holidayduration ELSE NULL::interval END)), (sum(CASE WHEN ((f.date >= '2020-12-01'::date) AND (f.date <= '2020-12-31'::date)) THEN f.holidayduration ELSE NULL::interval END)), (sum(CASE WHEN ((f.date >= '2021-01-01'::date) AND (f.date <= '2021-01-31'::date)) THEN f.holidayduration ELSE NULL::interval END)), (sum(CASE WHEN ((f.date >= '2021-02-01'::date) AND (f.date <= '2021-02-28'::date)) THEN f.holidayduration ELSE NULL::interval END)), (sum(CASE WHEN ((f.date >= '2021-03-01'::date) AND (f.date <= '2021-03-31'::date)) THEN f.holidayduration ELSE NULL::interval END)), (sum(CASE WHEN ((f.date >= '2021-04-01'::date) AND (f.date <= '2021-04-30'::date)) THEN f.holidayduration ELSE NULL::interval END)), (sum(CASE WHEN ((f.date >= '2021-05-01'::date) AND (f.date <= '2021-05-31'::date)) THEN f.holidayduration ELSE NULL::interval END)), (sum(CASE WHEN ((f.date >= '2021-06-01'::date) AND (f.date <= '2021-06-30'::date)) THEN f.holidayduration ELSE NULL::interval END)), (sum(CASE WHEN ((f.date >= '2021-07-01'::date) AND (f.date <= '2021-07-31'::date)) THEN f.holidayduration ELSE NULL::interval END)), (sum(f.overallocatedduration)), (sum(CASE WHEN ((f.date >= '2020-07-01'::date) AND (f.date <= '2020-07-31'::date)) THEN f.overallocatedduration ELSE NULL::interval END)), (sum(CASE WHEN ((f.date >= '2020-08-01'::date) AND (f.date <= '2020-08-31'::date)) THEN f.overallocatedduration ELSE NULL::interval END)), (sum(CASE WHEN ((f.date >= '2020-09-01'::date) AND (f.date <= '2020-09-30'::date)) THEN f.overallocatedduration ELSE NULL::interval END)), (sum(CASE WHEN ((f.date >= '2020-10-01'::date) AND (f.date <= '2020-10-31'::date)) THEN f.overallocatedduration ELSE NULL::interval END)), (sum(CASE WHEN ((f.date >= '2020-11-01'::date) AND (f.date <= '2020-11-30'::date)) THEN f.overallocatedduration ELSE NULL::interval END)), (sum(CASE WHEN ((f.date >= '2020-12-01'::date) AND (f.date <= '2020-12-31'::date)) THEN f.overallocatedduration ELSE NULL::interval END)), (sum(CASE WHEN ((f.date >= '2021-01-01'::date) AND (f.date <= '2021-01-31'::date)) THEN f.overallocatedduration ELSE NULL::interval END)), (sum(CASE WHEN ((f.date >= '2021-02-01'::date) AND (f.date <= '2021-02-28'::date)) THEN f.overallocatedduration ELSE NULL::interval END)), (sum(CASE WHEN ((f.date >= '2021-03-01'::date) AND (f.date <= '2021-03-31'::date)) THEN f.overallocatedduration ELSE NULL::interval END)), (sum(CASE WHEN ((f.date >= '2021-04-01'::date) AND (f.date <= '2021-04-30'::date)) THEN f.overallocatedduration ELSE NULL::interval END)), (sum(CASE WHEN ((f.date >= '2021-05-01'::date) AND (f.date <= '2021-05-31'::date)) THEN f.overallocatedduration ELSE NULL::interval END)), (sum(CASE WHEN ((f.date >= '2021-06-01'::date) AND (f.date <= '2021-06-30'::date)) THEN f.overallocatedduration ELSE NULL::interval END)), (sum(CASE WHEN ((f.date >= '2021-07-01'::date) AND (f.date <= '2021-07-31'::date)) THEN f.overallocatedduration ELSE NULL::interval END)), (count(DISTINCT f.userid)), (sum(f.scheduleduration)), (sum(CASE WHEN ((f.date >= '2020-07-01'::date) AND (f.date <= '2020-07-31'::date)) THEN f.scheduleduration ELSE NULL::interval END)), (sum(CASE WHEN ((f.date >= '2020-08-01'::date) AND (f.date <= '2020-08-31'::date)) THEN f.scheduleduration ELSE NULL::interval END)), (sum(CASE WHEN ((f.date >= '2020-09-01'::date) AND (f.date <= '2020-09-30'::date)) THEN f.scheduleduration ELSE NULL::interval END)), (sum(CASE WHEN ((f.date >= '2020-10-01'::date) AND (f.date <= '2020-10-31'::date)) THEN f.scheduleduration ELSE NULL::interval END)), (sum(CASE WHEN ((f.date >= '2020-11-01'::date) AND (f.date <= '2020-11-30'::date)) THEN f.scheduleduration ELSE NULL::interval END)), (sum(CASE WHEN ((f.date >= '2020-12-01'::date) AND (f.date <= '2020-12-31'::date)) THEN f.scheduleduration ELSE NULL::interval END)), (sum(CASE WHEN ((f.date >= '2021-01-01'::date) AND (f.date <= '2021-01-31'::date)) THEN f.scheduleduration ELSE NULL::interval END)), (sum(CASE WHEN ((f.date >= '2021-02-01'::date) AND (f.date <= '2021-02-28'::date)) THEN f.scheduleduration ELSE NULL::interval END)), (sum(CASE WHEN ((f.date >= '2021-03-01'::date) AND (f.date <= '2021-03-31'::date)) THEN f.scheduleduration ELSE NULL::interval END)), (sum(CASE WHEN ((f.date >= '2021-04-01'::date) AND (f.date <= '2021-04-30'::date)) THEN f.scheduleduration ELSE NULL::interval END)), (sum(CASE WHEN ((f.date >= '2021-05-01'::date) AND (f.date <= '2021-05-31'::date)) THEN f.scheduleduration ELSE NULL::interval END)), (sum(CASE WHEN ((f.date >= '2021-06-01'::date) AND (f.date <= '2021-06-30'::date)) THEN f.scheduleduration ELSE NULL::interval END)), (sum(CASE WHEN ((f.date >= '2021-07-01'::date) AND (f.date <= '2021-07-31'::date)) THEN f.scheduleduration ELSE NULL::interval END)), (sum(f.bookedtimeoffduration)), (sum(CASE WHEN ((f.date >= '2020-07-01'::date) AND (f.date <= '2020-07-31'::date)) THEN f.bookedtimeoffduration ELSE NULL::interval END)), (sum(CASE WHEN ((f.date >= '2020-08-01'::date) AND (f.date <= '2020-08-31'::date)) THEN f.bookedtimeoffduration ELSE NULL::interval END)), (sum(CASE WHEN ((f.date >= '2020-09-01'::date) AND (f.date <= '2020-09-30'::date)) THEN f.bookedtimeoffduration ELSE NULL::interval END)), (sum(CASE WHEN ((f.date >= '2020-10-01'::date) AND (f.date <= '2020-10-31'::date)) THEN f.bookedtimeoffduration ELSE NULL::interval END)), (sum(CASE WHEN ((f.date >= '2020-11-01'::date) AND (f.date <= '2020-11-30'::date)) THEN f.bookedtimeoffduration ELSE NULL::interval END)), (sum(CASE WHEN ((f.date >= '2020-12-01'::date) AND (f.date <= '2020-12-31'::date)) THEN f.bookedtimeoffduration ELSE NULL::interval END)), (sum(CASE WHEN ((f.date >= '2021-01-01'::date) AND (f.date <= '2021-01-31'::date)) THEN f.bookedtimeoffduration ELSE NULL::interval END)), (sum(CASE WHEN ((f.date >= '2021-02-01'::date) AND (f.date <= '2021-02-28'::date)) THEN f.bookedtimeoffduration ELSE NULL::interval END)), (sum(CASE WHEN ((f.date >= '2021-03-01'::date) AND (f.date <= '2021-03-31'::date)) THEN f.bookedtimeoffduration ELSE NULL::interval END)), (sum(CASE WHEN ((f.date >= '2021-04-01'::date) AND (f.date <= '2021-04-30'::date)) THEN f.bookedtimeoffduration ELSE NULL::interval END)), (sum(CASE WHEN ((f.date >= '2021-05-01'::date) AND (f.date <= '2021-05-31'::date)) THEN f.bookedtimeoffduration ELSE NULL::interval END)), (sum(CASE WHEN ((f.date >= '2021-06-01'::date) AND (f.date <= '2021-06-30'::date)) THEN f.bookedtimeoffduration ELSE NULL::interval END)), (sum(CASE WHEN ((f.date >= '2021-07-01'::date) AND (f.date <= '2021-07-31'::date)) THEN f.bookedtimeoffduration ELSE NULL::interval END))
  • Sort Method: quicksort Memory: 27kB
8. 1.207 3.812 ↑ 440.0 1 1

GroupAggregate (cost=966.16..1,418.26 rows=440 width=1,208) (actual time=3.811..3.812 rows=1 loops=1)

  • Group Key: upr.placeholderprojectroleid, (upper((phpr.name)::text))
  • Filter: ((sum(f.totalallocatedduration) IS NOT NULL) OR (sum(CASE WHEN ((f.date >= '2020-07-01'::date) AND (f.date <= '2020-07-31'::date)) THEN f.totalallocatedduration ELSE NULL::interval END) IS NOT NULL) OR (sum(CASE WHEN ((f.date >= '2020-08-01'::date) AND (f.date <= '2020-08-31'::date)) THEN f.totalallocatedduration ELSE NULL::interval END) IS NOT NULL) OR (sum(CASE WHEN ((f.date >= '2020-09-01'::date) AND (f.date <= '2020-09-30'::date)) THEN f.totalallocatedduration ELSE NULL::interval END) IS NOT NULL) OR (sum(CASE WHEN ((f.date >= '2020-10-01'::date) AND (f.date <= '2020-10-31'::date)) THEN f.totalallocatedduration ELSE NULL::interval END) IS NOT NULL) OR (sum(CASE WHEN ((f.date >= '2020-11-01'::date) AND (f.date <= '2020-11-30'::date)) THEN f.totalallocatedduration ELSE NULL::interval END) IS NOT NULL) OR (sum(CASE WHEN ((f.date >= '2020-12-01'::date) AND (f.date <= '2020-12-31'::date)) THEN f.totalallocatedduration ELSE NULL::interval END) IS NOT NULL) OR (sum(CASE WHEN ((f.date >= '2021-01-01'::date) AND (f.date <= '2021-01-31'::date)) THEN f.totalallocatedduration ELSE NULL::interval END) IS NOT NULL) OR (sum(CASE WHEN ((f.date >= '2021-02-01'::date) AND (f.date <= '2021-02-28'::date)) THEN f.totalallocatedduration ELSE NULL::interval END) IS NOT NULL) OR (sum(CASE WHEN ((f.date >= '2021-03-01'::date) AND (f.date <= '2021-03-31'::date)) THEN f.totalallocatedduration ELSE NULL::interval END) IS NOT NULL) OR (sum(CASE WHEN ((f.date >= '2021-04-01'::date) AND (f.date <= '2021-04-30'::date)) THEN f.totalallocatedduration ELSE NULL::interval END) IS NOT NULL) OR (sum(CASE WHEN ((f.date >= '2021-05-01'::date) AND (f.date <= '2021-05-31'::date)) THEN f.totalallocatedduration ELSE NULL::interval END) IS NOT NULL) OR (sum(CASE WHEN ((f.date >= '2021-06-01'::date) AND (f.date <= '2021-06-30'::date)) THEN f.totalallocatedduration ELSE NULL::interval END) IS NOT NULL) OR (sum(CASE WHEN ((f.date >= '2021-07-01'::date) AND (f.date <= '2021-07-31'::date)) THEN f.totalallocatedduration ELSE NULL::interval END) IS NOT NULL) OR (sum(f.holidayduration) IS NOT NULL) OR (sum(CASE WHEN ((f.date >= '2020-07-01'::date) AND (f.date <= '2020-07-31'::date)) THEN f.holidayduration ELSE NULL::interval END) IS NOT NULL) OR (sum(CASE WHEN ((f.date >= '2020-08-01'::date) AND (f.date <= '2020-08-31'::date)) THEN f.holidayduration ELSE NULL::interval END) IS NOT NULL) OR (sum(CASE WHEN ((f.date >= '2020-09-01'::date) AND (f.date <= '2020-09-30'::date)) THEN f.holidayduration ELSE NULL::interval END) IS NOT NULL) OR (sum(CASE WHEN ((f.date >= '2020-10-01'::date) AND (f.date <= '2020-10-31'::date)) THEN f.holidayduration ELSE NULL::interval END) IS NOT NULL) OR (sum(CASE WHEN ((f.date >= '2020-11-01'::date) AND (f.date <= '2020-11-30'::date)) THEN f.holidayduration ELSE NULL::interval END) IS NOT NULL) OR (sum(CASE WHEN ((f.date >= '2020-12-01'::date) AND (f.date <= '2020-12-31'::date)) THEN f.holidayduration ELSE NULL::interval END) IS NOT NULL) OR (sum(CASE WHEN ((f.date >= '2021-01-01'::date) AND (f.date <= '2021-01-31'::date)) THEN f.holidayduration ELSE NULL::interval END) IS NOT NULL) OR (sum(CASE WHEN ((f.date >= '2021-02-01'::date) AND (f.date <= '2021-02-28'::date)) THEN f.holidayduration ELSE NULL::interval END) IS NOT NULL) OR (sum(CASE WHEN ((f.date >= '2021-03-01'::date) AND (f.date <= '2021-03-31'::date)) THEN f.holidayduration ELSE NULL::interval END) IS NOT NULL) OR (sum(CASE WHEN ((f.date >= '2021-04-01'::date) AND (f.date <= '2021-04-30'::date)) THEN f.holidayduration ELSE NULL::interval END) IS NOT NULL) OR (sum(CASE WHEN ((f.date >= '2021-05-01'::date) AND (f.date <= '2021-05-31'::date)) THEN f.holidayduration ELSE NULL::interval END) IS NOT NULL) OR (sum(CASE WHEN ((f.date >= '2021-06-01'::date) AND (f.date <= '2021-06-30'::date)) THEN f.holidayduration ELSE NULL::interval END) IS NOT NULL) OR (sum(CASE WHEN ((f.date >= '2021-07-01'::date) AND (f.date <= '2021-07-31'::date)) THEN f.holidayduration ELSE NULL::interval END) IS NOT NULL) OR (sum(f.overallocatedduration) IS NOT NULL) OR (sum(CASE WHEN ((f.date >= '2020-07-01'::date) AND (f.date <= '2020-07-31'::date)) THEN f.overallocatedduration ELSE NULL::interval END) IS NOT NULL) OR (sum(CASE WHEN ((f.date >= '2020-08-01'::date) AND (f.date <= '2020-08-31'::date)) THEN f.overallocatedduration ELSE NULL::interval END) IS NOT NULL) OR (sum(CASE WHEN ((f.date >= '2020-09-01'::date) AND (f.date <= '2020-09-30'::date)) THEN f.overallocatedduration ELSE NULL::interval END) IS NOT NULL) OR (sum(CASE WHEN ((f.date >= '2020-10-01'::date) AND (f.date <= '2020-10-31'::date)) THEN f.overallocatedduration ELSE NULL::interval END) IS NOT NULL) OR (sum(CASE WHEN ((f.date >= '2020-11-01'::date) AND (f.date <= '2020-11-30'::date)) THEN f.overallocatedduration ELSE NULL::interval END) IS NOT NULL) OR (sum(CASE WHEN ((f.date >= '2020-12-01'::date) AND (f.date <= '2020-12-31'::date)) THEN f.overallocatedduration ELSE NULL::interval END) IS NOT NULL) OR (sum(CASE WHEN ((f.date >= '2021-01-01'::date) AND (f.date <= '2021-01-31'::date)) THEN f.overallocatedduration ELSE NULL::interval END) IS NOT NULL) OR (sum(CASE WHEN ((f.date >= '2021-02-01'::date) AND (f.date <= '2021-02-28'::date)) THEN f.overallocatedduration ELSE NULL::interval END) IS NOT NULL) OR (sum(CASE WHEN ((f.date >= '2021-03-01'::date) AND (f.date <= '2021-03-31'::date)) THEN f.overallocatedduration ELSE NULL::interval END) IS NOT NULL) OR (sum(CASE WHEN ((f.date >= '2021-04-01'::date) AND (f.date <= '2021-04-30'::date)) THEN f.overallocatedduration ELSE NULL::interval END) IS NOT NULL) OR (sum(CASE WHEN ((f.date >= '2021-05-01'::date) AND (f.date <= '2021-05-31'::date)) THEN f.overallocatedduration ELSE NULL::interval END) IS NOT NULL) OR (sum(CASE WHEN ((f.date >= '2021-06-01'::date) AND (f.date <= '2021-06-30'::date)) THEN f.overallocatedduration ELSE NULL::interval END) IS NOT NULL) OR (sum(CASE WHEN ((f.date >= '2021-07-01'::date) AND (f.date <= '2021-07-31'::date)) THEN f.overallocatedduration ELSE NULL::interval END) IS NOT NULL) OR (count(DISTINCT f.userid) IS NOT NULL) OR (sum(f.scheduleduration) IS NOT NULL) OR (sum(CASE WHEN ((f.date >= '2020-07-01'::date) AND (f.date <= '2020-07-31'::date)) THEN f.scheduleduration ELSE NULL::interval END) IS NOT NULL) OR (sum(CASE WHEN ((f.date >= '2020-08-01'::date) AND (f.date <= '2020-08-31'::date)) THEN f.scheduleduration ELSE NULL::interval END) IS NOT NULL) OR (sum(CASE WHEN ((f.date >= '2020-09-01'::date) AND (f.date <= '2020-09-30'::date)) THEN f.scheduleduration ELSE NULL::interval END) IS NOT NULL) OR (sum(CASE WHEN ((f.date >= '2020-10-01'::date) AND (f.date <= '2020-10-31'::date)) THEN f.scheduleduration ELSE NULL::interval END) IS NOT NULL) OR (sum(CASE WHEN ((f.date >= '2020-11-01'::date) AND (f.date <= '2020-11-30'::date)) THEN f.scheduleduration ELSE NULL::interval END) IS NOT NULL) OR (sum(CASE WHEN ((f.date >= '2020-12-01'::date) AND (f.date <= '2020-12-31'::date)) THEN f.scheduleduration ELSE NULL::interval END) IS NOT NULL) OR (sum(CASE WHEN ((f.date >= '2021-01-01'::date) AND (f.date <= '2021-01-31'::date)) THEN f.scheduleduration ELSE NULL::interval END) IS NOT NULL) OR (sum(CASE WHEN ((f.date >= '2021-02-01'::date) AND (f.date <= '2021-02-28'::date)) THEN f.scheduleduration ELSE NULL::interval END) IS NOT NULL) OR (sum(CASE WHEN ((f.date >= '2021-03-01'::date) AND (f.date <= '2021-03-31'::date)) THEN f.scheduleduration ELSE NULL::interval END) IS NOT NULL) OR (sum(CASE WHEN ((f.date >= '2021-04-01'::date) AND (f.date <= '2021-04-30'::date)) THEN f.scheduleduration ELSE NULL::interval END) IS NOT NULL) OR (sum(CASE WHEN ((f.date >= '2021-05-01'::date) AND (f.date <= '2021-05-31'::date)) THEN f.scheduleduration ELSE NULL::interval END) IS NOT NULL) OR (sum(CASE WHEN ((f.date >= '2021-06-01'::date) AND (f.date <= '2021-06-30'::date)) THEN f.scheduleduration ELSE NULL::interval END) IS NOT NULL) OR (sum(CASE WHEN ((f.date >= '2021-07-01'::date) AND (f.date <= '2021-07-31'::date)) THEN f.scheduleduration ELSE NULL::interval END) IS NOT NULL) OR (sum(f.bookedtimeoffduration) IS NOT NULL) OR (sum(CASE WHEN ((f.date >= '2020-07-01'::date) AND (f.date <= '2020-07-31'::date)) THEN f.bookedtimeoffduration ELSE NULL::interval END) IS NOT NULL) OR (sum(CASE WHEN ((f.date >= '2020-08-01'::date) AND (f.date <= '2020-08-31'::date)) THEN f.bookedtimeoffduration ELSE NULL::interval END) IS NOT NULL) OR (sum(CASE WHEN ((f.date >= '2020-09-01'::date) AND (f.date <= '2020-09-30'::date)) THEN f.bookedtimeoffduration ELSE NULL::interval END) IS NOT NULL) OR (sum(CASE WHEN ((f.date >= '2020-10-01'::date) AND (f.date <= '2020-10-31'::date)) THEN f.bookedtimeoffduration ELSE NULL::interval END) IS NOT NULL) OR (sum(CASE WHEN ((f.date >= '2020-11-01'::date) AND (f.date <= '2020-11-30'::date)) THEN f.bookedtimeoffduration ELSE NULL::interval END) IS NOT NULL) OR (sum(CASE WHEN ((f.date >= '2020-12-01'::date) AND (f.date <= '2020-12-31'::date)) THEN f.bookedtimeoffduration ELSE NULL::interval END) IS NOT NULL) OR (sum(CASE WHEN ((f.date >= '2021-01-01'::date) AND (f.date <= '2021-01-31'::date)) THEN f.bookedtimeoffduration ELSE NULL::interval END) IS NOT NULL) OR (sum(CASE WHEN ((f.date >= '2021-02-01'::date) AND (f.date <= '2021-02-28'::date)) THEN f.bookedtimeoffduration ELSE NULL::interval END) IS NOT NULL) OR (sum(CASE WHEN ((f.date >= '2021-03-01'::date) AND (f.date <= '2021-03-31'::date)) THEN f.bookedtimeoffduration ELSE NULL::interval END) IS NOT NULL) OR (sum(CASE WHEN ((f.date >= '2021-04-01'::date) AND (f.date <= '2021-04-30'::date)) THEN f.bookedtimeoffduration ELSE NULL::interval END) IS NOT NULL) OR (sum(CASE WHEN ((f.date >= '2021-05-01'::date) AND (f.date <= '2021-05-31'::date)) THEN f.bookedtimeoffduration ELSE NULL::interval END) IS NOT NULL) OR (sum(CASE WHEN ((f.date >= '2021-06-01'::date) AND (f.date <= '2021-06-30'::date)) THEN f.bookedtimeoffduration ELSE NULL::interval END) IS NOT NULL) OR (sum(CASE WHEN ((f.date >= '2021-07-01'::date) AND (f.date <= '2021-07-31'::date)) THEN f.bookedtimeoffduration ELSE NULL::interval END) IS NOT NULL))
9. 0.272 2.605 ↑ 1.6 281 1

Sort (cost=966.16..967.26 rows=440 width=254) (actual time=2.497..2.605 rows=281 loops=1)

  • Sort Key: upr.placeholderprojectroleid, (upper((phpr.name)::text)) COLLATE "en_US
  • Sort Method: quicksort Memory: 64kB
10. 0.242 2.333 ↑ 1.6 281 1

Merge Join (cost=216.78..946.85 rows=440 width=254) (actual time=0.488..2.333 rows=281 loops=1)

  • Merge Cond: (f.userid = userinfo4.id)
11. 0.211 2.027 ↑ 3.1 281 1

Merge Left Join (cost=213.09..935.45 rows=881 width=222) (actual time=0.421..2.027 rows=281 loops=1)

  • Merge Cond: (f.userid = ueg.userid)
  • Join Filter: ((ueg.enddate >= f.date) AND (ueg.startdate <= f.date))
12. 0.210 1.807 ↑ 3.1 281 1

Merge Left Join (cost=212.94..772.59 rows=881 width=222) (actual time=0.410..1.807 rows=281 loops=1)

  • Merge Cond: (f.userid = udg.userid)
  • Join Filter: ((udg.enddate >= f.date) AND (udg.startdate <= f.date))
13. 0.214 1.593 ↑ 3.1 281 1

Merge Left Join (cost=212.79..609.72 rows=881 width=222) (actual time=0.406..1.593 rows=281 loops=1)

  • Merge Cond: (f.userid = us.userid)
  • Join Filter: ((us.enddate >= f.date) AND (us.startdate <= f.date))
14. 0.208 1.375 ↑ 3.1 281 1

Merge Left Join (cost=212.64..446.86 rows=881 width=222) (actual time=0.401..1.375 rows=281 loops=1)

  • Merge Cond: (f.userid = upr.userid)
15. 0.218 1.161 ↑ 1.0 281 1

Merge Left Join (cost=146.74..367.04 rows=282 width=88) (actual time=0.394..1.161 rows=281 loops=1)

  • Merge Cond: (f.userid = uc.userid)
  • Join Filter: ((uc.enddate >= f.date) AND (uc.startdate <= f.date))
16. 0.219 0.939 ↑ 1.0 281 1

Merge Left Join (cost=146.58..273.37 rows=282 width=88) (actual time=0.389..0.939 rows=281 loops=1)

  • Merge Cond: (f.userid = ud.userid)
  • Join Filter: ((ud.enddate >= f.date) AND (ud.startdate <= f.date))
17. 0.227 0.713 ↑ 1.0 281 1

Merge Left Join (cost=146.43..179.70 rows=282 width=88) (actual time=0.381..0.713 rows=281 loops=1)

  • Merge Cond: (f.userid = ul.userid)
  • Join Filter: ((ul.enddate >= f.date) AND (ul.startdate <= f.date))
18. 0.257 0.481 ↑ 1.0 281 1

Sort (cost=67.83..68.53 rows=282 width=88) (actual time=0.374..0.481 rows=281 loops=1)

  • Sort Key: f.userid
  • Sort Method: quicksort Memory: 64kB
19. 0.224 0.224 ↑ 1.0 281 1

Seq Scan on dm_useravailability_facts f (cost=0.00..56.35 rows=282 width=88) (actual time=0.026..0.224 rows=281 loops=1)

  • Filter: ((date >= '2020-07-01'::date) AND (date <= '2021-07-31'::date) AND (userid = ANY ('{20,26,27,11,17,33,31,34,12,10,18,2,15,21,5,19,37,24,8,28,30,29,16,36,23,22,14,35,9,7}'::integer[])))
  • Rows Removed by Filter: 240
20. 0.003 0.005 ↓ 0.0 0 1

Sort (cost=78.60..81.43 rows=1,130 width=28) (actual time=0.005..0.005 rows=0 loops=1)

  • Sort Key: ul.userid
  • Sort Method: quicksort Memory: 25kB
21. 0.002 0.002 ↓ 0.0 0 1

Seq Scan on userlocation ul (cost=0.00..21.30 rows=1,130 width=28) (actual time=0.002..0.002 rows=0 loops=1)

22. 0.002 0.007 ↓ 0.0 0 1

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

23. 0.005 0.005 ↓ 0.0 0 1

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

  • Heap Fetches: 0
24. 0.001 0.004 ↓ 0.0 0 1

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

25. 0.003 0.003 ↓ 0.0 0 1

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

  • Heap Fetches: 0
26. 0.003 0.006 ↓ 0.0 0 1

Sort (cost=65.90..67.46 rows=625 width=138) (actual time=0.005..0.006 rows=0 loops=1)

  • Sort Key: upr.userid
  • Sort Method: quicksort Memory: 25kB
27. 0.001 0.003 ↓ 0.0 0 1

Hash Left Join (cost=12.70..36.87 rows=625 width=138) (actual time=0.002..0.003 rows=0 loops=1)

  • Hash Cond: (upr.placeholderprojectroleid = phpr.id)
28. 0.002 0.002 ↓ 0.0 0 1

Seq Scan on userprojectroleassignment upr (cost=0.00..22.50 rows=625 width=20) (actual time=0.001..0.002 rows=0 loops=1)

  • Filter: isprimary
29. 0.000 0.000 ↓ 0.0 0

Hash (cost=11.20..11.20 rows=120 width=134) (never executed)

30. 0.000 0.000 ↓ 0.0 0

Seq Scan on placeholderprojectrole phpr (cost=0.00..11.20 rows=120 width=134) (never executed)

31. 0.001 0.004 ↓ 0.0 0 1

Materialize (cost=0.15..63.93 rows=1,130 width=28) (actual time=0.003..0.004 rows=0 loops=1)

32. 0.003 0.003 ↓ 0.0 0 1

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

  • Heap Fetches: 0
33. 0.002 0.004 ↓ 0.0 0 1

Materialize (cost=0.15..63.93 rows=1,130 width=28) (actual time=0.003..0.004 rows=0 loops=1)

34. 0.002 0.002 ↓ 0.0 0 1

Index Only Scan using ix4ucc_userdepartmentgroupstartend on userdepartmentgroup udg (cost=0.15..61.10 rows=1,130 width=28) (actual time=0.002..0.002 rows=0 loops=1)

  • Heap Fetches: 0
35. 0.005 0.009 ↓ 0.0 0 1

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

36. 0.004 0.004 ↓ 0.0 0 1

Index Only Scan using ix4ucc_useremployeetypegroupstartend on useremployeetypegroup ueg (cost=0.15..61.10 rows=1,130 width=28) (actual time=0.004..0.004 rows=0 loops=1)

  • Heap Fetches: 0
37. 0.003 0.064 ↑ 1.0 1 1

Sort (cost=3.69..3.69 rows=1 width=4) (actual time=0.064..0.064 rows=1 loops=1)

  • Sort Key: userinfo4.id
  • Sort Method: quicksort Memory: 25kB
38. 0.009 0.061 ↑ 1.0 1 1

Seq Scan on userinfo userinfo4 (cost=2.64..3.68 rows=1 width=4) (actual time=0.060..0.061 rows=1 loops=1)

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

SubPlan (for Seq Scan)

40. 0.013 0.052 ↑ 1.0 1 1

Nested Loop (cost=0.00..2.59 rows=1 width=4) (actual time=0.041..0.052 rows=1 loops=1)

  • Join Filter: (permissionsetrole6.permissionsetid = userpermissionset5.permissionsetid)
  • Rows Removed by Join Filter: 21
41. 0.027 0.027 ↓ 2.0 2 1

Seq Scan on permissionsetrole permissionsetrole6 (cost=0.00..1.34 rows=1 width=16) (actual time=0.020..0.027 rows=2 loops=1)

  • Filter: (upper((roleuri)::text) = 'URN:REPLICON:USER-ACCESS-ROLE:PROJECT-RESOURCE'::text)
  • Rows Removed by Filter: 21
42. 0.012 0.012 ↑ 1.0 11 2

Seq Scan on userpermissionset userpermissionset5 (cost=0.00..1.11 rows=11 width=20) (actual time=0.002..0.006 rows=11 loops=2)

43. 0.000 0.000 ↓ 0.0 0

CTE Scan on userinfo0cte userinfo7 (cost=0.00..0.04 rows=2 width=4) (never executed)

Planning time : 5.922 ms
Execution time : 5.145 ms