explain.depesz.com

PostgreSQL's explain analyze made readable

Result: Pf5E

Settings
# exclusive inclusive rows x rows loops node
1. 0.117 12.184 ↑ 1.0 1,000 1

Limit (cost=42.24..1,251.06 rows=1,000 width=1,553) (actual time=0.651..12.184 rows=1,000 loops=1)

2. 3.295 12.067 ↑ 3,759.4 1,000 1

Nested Loop Left Join (cost=42.24..4,544,548.27 rows=3,759,442 width=1,553) (actual time=0.651..12.067 rows=1,000 loops=1)

  • Join Filter: (uf.scope_id = sd.id)
  • Rows Removed by Join Filter: 239
3. 0.673 8.772 ↑ 3,759.4 1,000 1

Nested Loop Left Join (cost=42.24..4,079,316.22 rows=3,759,442 width=1,285) (actual time=0.288..8.772 rows=1,000 loops=1)

4. 0.873 6.099 ↑ 3,759.4 1,000 1

Nested Loop Left Join (cost=41.82..2,403,117.93 rows=3,759,442 width=1,249) (actual time=0.278..6.099 rows=1,000 loops=1)

5. 0.607 4.226 ↑ 3,759.4 1,000 1

Nested Loop Left Join (cost=41.54..1,290,058.23 rows=3,759,442 width=1,073) (actual time=0.271..4.226 rows=1,000 loops=1)

6. 0.293 1.619 ↑ 3,759.4 1,000 1

Hash Left Join (cost=41.25..125,983.48 rows=3,759,442 width=1,049) (actual time=0.262..1.619 rows=1,000 loops=1)

  • Hash Cond: (uf.operation_id = opd.id)
7. 0.271 1.322 ↑ 3,759.4 1,000 1

Hash Left Join (cost=40.12..109,194.06 rows=3,759,442 width=645) (actual time=0.254..1.322 rows=1,000 loops=1)

  • Hash Cond: (uf.object_id = objd.object_id)
8. 0.246 1.044 ↑ 3,759.4 1,000 1

Hash Left Join (cost=38.91..94,943.20 rows=3,759,442 width=241) (actual time=0.243..1.044 rows=1,000 loops=1)

  • Hash Cond: (uf.app_id = ad.id)
9. 0.413 0.794 ↑ 3,759.4 1,000 1

Hash Left Join (cost=37.82..82,647.79 rows=3,759,442 width=159) (actual time=0.230..0.794 rows=1,000 loops=1)

  • Hash Cond: (uf.org_id = org.organisation_id)
10. 0.162 0.162 ↑ 3,759.4 1,000 1

Seq Scan on usage_fact uf (cost=0.00..72,694.42 rows=3,759,442 width=41) (actual time=0.004..0.162 rows=1,000 loops=1)

11. 0.103 0.219 ↑ 1.3 676 1

Hash (cost=26.81..26.81 rows=881 width=122) (actual time=0.219..0.219 rows=676 loops=1)

  • Buckets: 1024 Batches: 1 Memory Usage: 45kB
12. 0.116 0.116 ↑ 1.3 676 1

Seq Scan on organisation org (cost=0.00..26.81 rows=881 width=122) (actual time=0.003..0.116 rows=676 loops=1)

13. 0.002 0.004 ↑ 1.0 4 1

Hash (cost=1.04..1.04 rows=4 width=86) (actual time=0.004..0.004 rows=4 loops=1)

  • Buckets: 1024 Batches: 1 Memory Usage: 9kB
14. 0.002 0.002 ↑ 1.0 4 1

Seq Scan on app_dim ad (cost=0.00..1.04 rows=4 width=86) (actual time=0.001..0.002 rows=4 loops=1)

15. 0.003 0.007 ↑ 1.0 9 1

Hash (cost=1.09..1.09 rows=9 width=408) (actual time=0.006..0.007 rows=9 loops=1)

  • Buckets: 1024 Batches: 1 Memory Usage: 9kB
16. 0.004 0.004 ↑ 1.0 9 1

Seq Scan on object_dim objd (cost=0.00..1.09 rows=9 width=408) (actual time=0.001..0.004 rows=9 loops=1)

17. 0.002 0.004 ↑ 1.0 6 1

Hash (cost=1.06..1.06 rows=6 width=408) (actual time=0.004..0.004 rows=6 loops=1)

  • Buckets: 1024 Batches: 1 Memory Usage: 9kB
18. 0.002 0.002 ↑ 1.0 6 1

Seq Scan on operation_dim opd (cost=0.00..1.06 rows=6 width=408) (actual time=0.001..0.002 rows=6 loops=1)

19. 2.000 2.000 ↑ 1.0 1 1,000

Index Scan using users_pkey on users u (cost=0.29..0.31 rows=1 width=28) (actual time=0.002..0.002 rows=1 loops=1,000)

  • Index Cond: (uf.user_id = user_id)
20. 1.000 1.000 ↑ 1.0 1 1,000

Index Scan using la_pkey on la (cost=0.28..0.30 rows=1 width=180) (actual time=0.001..0.001 rows=1 loops=1,000)

  • Index Cond: (uf.local_authority_id = la_id)
21. 2.000 2.000 ↑ 1.0 1 1,000

Index Scan using date_dim_pkey on date_dim dd (cost=0.42..0.45 rows=1 width=44) (actual time=0.002..0.002 rows=1 loops=1,000)

  • Index Cond: (uf.date_id = date_id)
22. 0.000 0.000 ↑ 7.0 1 1,000

Materialize (cost=0.00..1.10 rows=7 width=264) (actual time=0.000..0.000 rows=1 loops=1,000)

23. 0.003 0.003 ↑ 1.2 6 1

Seq Scan on scope_dim sd (cost=0.00..1.07 rows=7 width=264) (actual time=0.002..0.003 rows=6 loops=1)

Planning time : 5.137 ms
Execution time : 12.361 ms