explain.depesz.com

PostgreSQL's explain analyze made readable

Result: sX88 : Optimization for: plan #Jh5p

Settings

Optimization path:

Optimization(s) for this plan:

# exclusive inclusive rows x rows loops node
1. 0.070 178.227 ↓ 2.0 2 1

HashAggregate (cost=31,889.82..31,889.83 rows=1 width=17) (actual time=178.226..178.227 rows=2 loops=1)

  • Group Key: aps.id, perms.is_perm
2.          

CTE aps

3. 0.004 0.017 ↓ 2.0 2 1

Nested Loop (cost=0.56..16.61 rows=1 width=16) (actual time=0.012..0.017 rows=2 loops=1)

4. 0.007 0.007 ↓ 2.0 2 1

Index Scan using attendances_patient_id_idx on attendances a (cost=0.28..8.30 rows=1 width=16) (actual time=0.006..0.007 rows=2 loops=1)

  • Index Cond: (patient_id = '54d9df16-5371-11e8-80ea-034fb4ed6904'::uuid)
5. 0.006 0.006 ↑ 1.0 1 2

Index Scan using attendance_periods_attendance_id_idx on attendance_periods ap (cost=0.28..8.30 rows=1 width=32) (actual time=0.003..0.003 rows=1 loops=2)

  • Index Cond: (attendance_id = a.id)
6.          

CTE perms

7. 35.859 177.943 ↓ 231.0 231 1

Hash Semi Join (cost=1,008.34..31,873.16 rows=1 width=17) (actual time=44.403..177.943 rows=231 loops=1)

  • Hash Cond: (apa.attendance_period_id = aps_1.id)
8. 8.062 142.076 ↓ 76.7 256,097 1

Gather (cost=1,008.30..31,864.34 rows=3,341 width=16) (actual time=0.288..142.076 rows=256,097 loops=1)

  • Workers Planned: 2
  • Workers Launched: 2
9. 69.797 134.014 ↓ 61.3 85,366 3 / 3

Hash Join (cost=8.30..30,530.24 rows=1,392 width=16) (actual time=0.123..134.014 rows=85,366 loops=3)

  • Hash Cond: (apa.user_group_id = users_user_groups.user_group_id)
10. 64.188 64.188 ↑ 1.4 473,833 3 / 3

Parallel Seq Scan on attendance_period_acl apa (cost=0.00..28,712.00 rows=683,600 width=32) (actual time=0.020..64.188 rows=473,833 loops=3)

11. 0.003 0.029 ↑ 1.0 1 3 / 3

Hash (cost=8.29..8.29 rows=1 width=16) (actual time=0.029..0.029 rows=1 loops=3)

  • Buckets: 1024 Batches: 1 Memory Usage: 9kB
12. 0.026 0.026 ↑ 1.0 1 3 / 3

Index Only Scan using users_user_groups_pkey on users_user_groups (cost=0.27..8.29 rows=1 width=16) (actual time=0.025..0.026 rows=1 loops=3)

  • Index Cond: (user_id = '1304ff22-3368-11e8-ba9d-43a62f2ef9fd'::uuid)
  • Heap Fetches: 0
13. 0.002 0.008 ↓ 2.0 2 1

Hash (cost=0.02..0.02 rows=1 width=16) (actual time=0.008..0.008 rows=2 loops=1)

  • Buckets: 1024 Batches: 1 Memory Usage: 9kB
14. 0.006 0.006 ↓ 2.0 2 1

CTE Scan on aps aps_1 (cost=0.00..0.02 rows=1 width=16) (actual time=0.000..0.006 rows=2 loops=1)

15. 0.084 178.157 ↓ 231.0 231 1

Nested Loop Left Join (cost=0.00..0.05 rows=1 width=17) (actual time=110.540..178.157 rows=231 loops=1)

  • Join Filter: (perms.attendance_period_id = aps.id)
  • Rows Removed by Join Filter: 231
16. 0.015 0.015 ↓ 2.0 2 1

CTE Scan on aps (cost=0.00..0.02 rows=1 width=16) (actual time=0.013..0.015 rows=2 loops=1)

17. 178.058 178.058 ↓ 231.0 231 2

CTE Scan on perms (cost=0.00..0.02 rows=1 width=17) (actual time=22.203..89.029 rows=231 loops=2)

Planning time : 0.429 ms
Execution time : 180.509 ms