explain.depesz.com

PostgreSQL's explain analyze made readable

Result: BUbk

Settings
# exclusive inclusive rows x rows loops node
1. 0.002 47,760.391 ↑ 1.0 1 1

Limit (cost=231,136.93..231,136.93 rows=1 width=108) (actual time=47,760.391..47,760.391 rows=1 loops=1)

2.          

CTE data

3. 29.298 32.092 ↓ 1.0 2,185 1

Hash Right Join (cost=198.69..846.17 rows=2,089 width=44) (actual time=2.506..32.092 rows=2,185 loops=1)

  • Hash Cond: (site_users.user_id = user_ids.user_id)
4. 0.454 0.454 ↑ 1.0 2,174 1

Seq Scan on site_users (cost=0.00..85.74 rows=2,174 width=8) (actual time=0.005..0.454 rows=2,174 loops=1)

5. 0.240 2.340 ↓ 1.0 1,103 1

Hash (cost=185.22..185.22 rows=1,078 width=44) (actual time=2.340..2.340 rows=1,103 loops=1)

  • Buckets: 2,048 Batches: 1 Memory Usage: 103kB
6. 0.254 2.100 ↓ 1.0 1,103 1

Hash Left Join (cost=100.97..185.22 rows=1,078 width=44) (actual time=0.923..2.100 rows=1,103 loops=1)

  • Hash Cond: (user_has_organisation.organisation_id = organisations.organisation_id)
7. 0.238 1.837 ↓ 1.0 1,103 1

Hash Left Join (cost=99.47..168.90 rows=1,078 width=28) (actual time=0.908..1.837 rows=1,103 loops=1)

  • Hash Cond: (user_ids.user_id = user_has_organisation.user_id)
8. 0.294 1.301 ↓ 1.0 1,103 1

Hash Join (cost=67.72..122.54 rows=1,078 width=20) (actual time=0.604..1.301 rows=1,103 loops=1)

  • Hash Cond: (users.user_id = user_ids.user_id)
9. 0.248 0.656 ↓ 1.0 1,103 1

Hash Right Join (cost=30.75..70.66 rows=1,100 width=16) (actual time=0.248..0.656 rows=1,103 loops=1)

  • Hash Cond: (codes.code_user_id = users.user_id)
10. 0.170 0.170 ↑ 1.0 1,091 1

Seq Scan on user_online_induction_codes codes (cost=0.00..24.91 rows=1,091 width=16) (actual time=0.002..0.170 rows=1,091 loops=1)

  • Filter: (NOT deleted)
11. 0.128 0.238 ↓ 1.0 1,103 1

Hash (cost=17.00..17.00 rows=1,100 width=8) (actual time=0.238..0.238 rows=1,103 loops=1)

  • Buckets: 2,048 Batches: 1 Memory Usage: 60kB
12. 0.110 0.110 ↓ 1.0 1,103 1

Seq Scan on users (cost=0.00..17.00 rows=1,100 width=8) (actual time=0.003..0.110 rows=1,103 loops=1)

13. 0.137 0.351 ↓ 1.0 1,103 1

Hash (cost=23.22..23.22 rows=1,100 width=12) (actual time=0.351..0.351 rows=1,103 loops=1)

  • Buckets: 2,048 Batches: 1 Memory Usage: 64kB
14. 0.214 0.214 ↓ 1.0 1,103 1

Seq Scan on user_ids (cost=0.00..23.22 rows=1,100 width=12) (actual time=0.007..0.214 rows=1,103 loops=1)

  • Filter: (NOT deleted)
  • Rows Removed by Filter: 22
15. 0.176 0.298 ↓ 1.0 1,103 1

Hash (cost=18.00..18.00 rows=1,100 width=16) (actual time=0.298..0.298 rows=1,103 loops=1)

  • Buckets: 2,048 Batches: 1 Memory Usage: 68kB
16. 0.122 0.122 ↓ 1.0 1,103 1

Seq Scan on user_has_organisation (cost=0.00..18.00 rows=1,100 width=16) (actual time=0.002..0.122 rows=1,103 loops=1)

17. 0.005 0.009 ↑ 1.0 22 1

Hash (cost=1.22..1.22 rows=22 width=24) (actual time=0.009..0.009 rows=22 loops=1)

  • Buckets: 1,024 Batches: 1 Memory Usage: 10kB
18. 0.004 0.004 ↑ 1.0 22 1

Seq Scan on organisations (cost=0.00..1.22 rows=22 width=24) (actual time=0.002..0.004 rows=22 loops=1)

19. 0.267 47,760.389 ↑ 209.0 1 1

Sort (cost=230,290.76..230,291.28 rows=209 width=108) (actual time=47,760.389..47,760.389 rows=1 loops=1)

  • Sort Key: data.sequential_id
  • Sort Method: top-N heapsort Memory: 25kB
20. 1.905 47,760.122 ↓ 5.3 1,103 1

HashAggregate (cost=230,287.62..230,289.71 rows=209 width=108) (actual time=47,759.817..47,760.122 rows=1,103 loops=1)

  • Group Key: data.sequential_id, data.user_id, data.subject_id, data.organisation_id, data.organisation_name, data.induction_code
21. 91.841 47,758.217 ↑ 260.9 2,370 1

Hash Left Join (cost=207,195.17..219,467.18 rows=618,311 width=108) (actual time=47,639.053..47,758.217 rows=2,370 loops=1)

  • Hash Cond: (data.subject_id = event_log.subject_id)
22. 33.427 33.427 ↓ 1.0 2,185 1

CTE Scan on data (cost=0.00..41.78 rows=2,089 width=100) (actual time=2.508..33.427 rows=2,185 loops=1)

23. 360.836 47,632.949 ↑ 1.0 651,745 1

Hash (cost=195,229.21..195,229.21 rows=651,757 width=24) (actual time=47,632.949..47,632.949 rows=651,745 loops=1)

  • Buckets: 65,536 Batches: 16 Memory Usage: 2,639kB
24. 47,272.113 47,272.113 ↑ 1.0 651,745 1

Seq Scan on event_log (cost=0.00..195,229.21 rows=651,757 width=24) (actual time=0.269..47,272.113 rows=651,745 loops=1)

  • Filter: (origin_id = this_origin_id())
Planning time : 1.066 ms
Execution time : 47,760.648 ms