explain.depesz.com

PostgreSQL's explain analyze made readable

Result: lZjB : Query1

Settings
# exclusive inclusive rows x rows loops node
1. 1,357.036 17,126.647 ↑ 64.0 13 1

HashAggregate (cost=8,146.46..8,173.50 rows=832 width=116) (actual time=17,126.635..17,126.647 rows=13 loops=1)

  • Group Key: ((((date_part('year'::text, (dm_useravailability_facts1.date)::timestamp without time zone))::integer * 100) + (date_part('month'::text, (dm_useravailability_facts1.date)::timestamp without time zone))::integer) + 300000000)
2.          

CTE userinfo0cte

3. 1.096 1.096 ↓ 1.0 5,176 1

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

4. 611.199 15,769.611 ↓ 41.2 1,464,242 1

Nested Loop (cost=249.74..5,935.44 rows=35,570 width=116) (actual time=31.363..15,769.611 rows=1,464,242 loops=1)

5. 6.717 34.810 ↓ 41.1 5,174 1

HashAggregate (cost=249.31..250.57 rows=126 width=12) (actual time=30.235..34.810 rows=5,174 loops=1)

  • Group Key: userinfo2.id
6. 1.698 28.093 ↓ 41.1 5,174 1

Hash Join (cost=162.83..249.00 rows=126 width=12) (actual time=11.310..28.093 rows=5,174 loops=1)

  • Hash Cond: (userinfo2.id = userinfo5.id0)
7. 3.540 19.555 ↓ 20.5 5,174 1

Nested Loop (cost=41.90..126.00 rows=253 width=8) (actual time=4.462..19.555 rows=5,174 loops=1)

8. 3.166 5.667 ↓ 20.5 5,174 1

HashAggregate (cost=41.61..44.14 rows=253 width=4) (actual time=4.425..5.667 rows=5,174 loops=1)

  • Group Key: userpermissionset3.userid
9. 0.741 2.501 ↓ 20.5 5,174 1

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

10. 0.036 0.036 ↓ 2.0 2 1

Seq Scan on permissionsetrole permissionsetrole4 (cost=0.00..1.31 rows=1 width=16) (actual time=0.025..0.036 rows=2 loops=1)

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

Index Scan using ixupspermissionsetid on userpermissionset userpermissionset3 (cost=0.28..36.34 rows=333 width=20) (actual time=0.030..0.862 rows=2,587 loops=2)

  • Index Cond: (permissionsetid = permissionsetrole4.permissionsetid)
12. 10.348 10.348 ↑ 1.0 1 5,174

Index Only Scan using userinfo_pkey on userinfo userinfo2 (cost=0.28..0.33 rows=1 width=4) (actual time=0.002..0.002 rows=1 loops=5,174)

  • Index Cond: (id = userpermissionset3.userid)
  • Heap Fetches: 5,174
13. 1.110 6.840 ↓ 25.9 5,176 1

Hash (cost=118.44..118.44 rows=200 width=4) (actual time=6.840..6.840 rows=5,176 loops=1)

  • Buckets: 8,192 (originally 1024) Batches: 1 (originally 1) Memory Usage: 246kB
14. 3.020 5.730 ↓ 25.9 5,176 1

HashAggregate (cost=116.44..118.44 rows=200 width=4) (actual time=4.708..5.730 rows=5,176 loops=1)

  • Group Key: userinfo5.id0
15. 2.710 2.710 ↓ 1.0 5,176 1

CTE Scan on userinfo0cte userinfo5 (cost=0.00..103.50 rows=5,175 width=4) (actual time=0.007..2.710 rows=5,176 loops=1)

16. 15,123.602 15,123.602 ↓ 1.0 283 5,174

Index Scan using dm_useravailability_facts_pkey on dm_useravailability_facts dm_useravailability_facts1 (cost=0.43..35.95 rows=282 width=120) (actual time=1.041..2.923 rows=283 loops=5,174)

  • Index Cond: ((userid = userinfo2.id) AND (date >= '2020-06-01'::date) AND (date <= '2021-06-30'::date))
Planning time : 8.664 ms
Execution time : 17,127.246 ms