explain.depesz.com

PostgreSQL's explain analyze made readable

Result: FKms

Settings
# exclusive inclusive rows x rows loops node
1. 6.805 130.199 ↓ 9,904.0 9,904 1

Nested Loop (cost=50,580.33..50,679.11 rows=1 width=2,796) (actual time=52.538..130.199 rows=9,904 loops=1)

  • Buffers: shared hit=76213
2.          

CTE pwg

3. 0.705 54.157 ↑ 2.0 1,465 1

Subquery Scan on tmp (cost=50,169.39..50,317.29 rows=2,963 width=56) (actual time=52.128..54.157 rows=1,465 loops=1)

  • Filter: ((hashed SubPlan 1) OR ((tmp.entitlement_group_id IS NULL) AND (tmp.quantity > 0)))
  • Rows Removed by Filter: 1898
  • Buffers: shared hit=8194
4. 3.872 53.434 ↑ 1.8 3,363 1

HashAggregate (cost=50,161.09..50,220.25 rows=5,916 width=56) (actual time=52.105..53.434 rows=3,363 loops=1)

  • Group Key: "*SELECT* 1".model_id, "*SELECT* 1".inventory_pool_id, "*SELECT* 1".entitlement_group_id, (("*SELECT* 1".quantity)::bigint)
  • Buffers: shared hit=8192
5. 0.788 49.562 ↑ 1.8 3,363 1

Append (cost=2.28..50,101.93 rows=5,916 width=56) (actual time=0.051..49.562 rows=3,363 loops=1)

  • Buffers: shared hit=8192
6. 0.255 0.998 ↑ 1.0 1,157 1

Subquery Scan on *SELECT* 1 (cost=2.28..43.57 rows=1,157 width=56) (actual time=0.050..0.998 rows=1,157 loops=1)

  • Buffers: shared hit=13
7. 0.522 0.743 ↑ 1.0 1,157 1

Hash Join (cost=2.28..29.11 rows=1,157 width=52) (actual time=0.050..0.743 rows=1,157 loops=1)

  • Hash Cond: (entitlements.entitlement_group_id = entitlement_groups_1.id)
  • Buffers: shared hit=13
8. 0.191 0.191 ↑ 1.0 1,157 1

Seq Scan on entitlements (cost=0.00..23.57 rows=1,157 width=36) (actual time=0.007..0.191 rows=1,157 loops=1)

  • Buffers: shared hit=12
9. 0.016 0.030 ↑ 1.0 57 1

Hash (cost=1.57..1.57 rows=57 width=32) (actual time=0.030..0.030 rows=57 loops=1)

  • Buckets: 1024 Batches: 1 Memory Usage: 12kB
  • Buffers: shared hit=1
10. 0.014 0.014 ↑ 1.0 57 1

Seq Scan on entitlement_groups entitlement_groups_1 (cost=0.00..1.57 rows=57 width=32) (actual time=0.005..0.014 rows=57 loops=1)

  • Buffers: shared hit=1
11. 8.547 47.776 ↑ 2.2 2,206 1

HashAggregate (cost=2,123.33..50,010.77 rows=4,759 width=56) (actual time=27.128..47.776 rows=2,206 loops=1)

  • Group Key: i.inventory_pool_id, i.model_id
  • Buffers: shared hit=8179
12. 21.581 21.581 ↓ 1.2 9,075 1

Seq Scan on items i (cost=0.00..2,065.12 rows=7,761 width=48) (actual time=0.010..21.581 rows=9,075 loops=1)

  • Filter: ((retired IS NULL) AND is_borrowable AND (parent_id IS NULL))
  • Rows Removed by Filter: 27537
  • Buffers: shared hit=1699
13.          

SubPlan (for HashAggregate)

14. 0.000 17.648 ↓ 0.0 0 2,206

GroupAggregate (cost=0.28..10.05 rows=1 width=40) (actual time=0.008..0.008 rows=0 loops=2,206)

  • Group Key: entitlement_groups.inventory_pool_id, es.model_id
  • Buffers: shared hit=6480
15. 3.894 17.648 ↓ 0.0 0 2,206

Nested Loop (cost=0.28..10.03 rows=1 width=36) (actual time=0.007..0.008 rows=0 loops=2,206)

  • Join Filter: (es.entitlement_group_id = entitlement_groups.id)
  • Rows Removed by Join Filter: 1
  • Buffers: shared hit=6480
16. 4.412 4.412 ↓ 0.0 0 2,206

Index Scan using index_entitlements_on_model_id on entitlements es (cost=0.28..8.29 rows=1 width=36) (actual time=0.002..0.002 rows=0 loops=2,206)

  • Index Cond: (model_id = i.model_id)
  • Buffers: shared hit=5442
17. 9.342 9.342 ↓ 1.5 3 1,038

Seq Scan on entitlement_groups (cost=0.00..1.71 rows=2 width=32) (actual time=0.008..0.009 rows=3 loops=1,038)

  • Filter: (inventory_pool_id = i.inventory_pool_id)
  • Rows Removed by Filter: 42
  • Buffers: shared hit=1038
18.          

SubPlan (for Subquery Scan)

19. 0.018 0.018 ↓ 0.0 0 1

Index Only Scan using idx_user_egroup on entitlement_groups_users (cost=0.28..8.29 rows=1 width=16) (actual time=0.018..0.018 rows=0 loops=1)

  • Index Cond: (user_id = 'c0777d74-668b-5e01-abb5-f8277baa0ea8'::uuid)
  • Heap Fetches: 0
  • Buffers: shared hit=2
20. 10.860 101.130 ↓ 11,132.0 11,132 1

Nested Loop (cost=262.77..361.53 rows=1 width=2,738) (actual time=52.524..101.130 rows=11,132 loops=1)

  • Buffers: shared hit=42817
21. 5.730 77.520 ↓ 6,375.0 6,375 1

Nested Loop (cost=262.49..361.15 rows=1 width=2,686) (actual time=52.508..77.520 rows=6,375 loops=1)

  • Join Filter: (inventory_pools.id = items.inventory_pool_id)
  • Rows Removed by Join Filter: 677
  • Buffers: shared hit=20862
22. 1.110 61.251 ↓ 83.6 1,171 1

Nested Loop (cost=262.07..342.17 rows=14 width=2,192) (actual time=52.487..61.251 rows=1,171 loops=1)

  • Buffers: shared hit=11816
23. 1.237 56.628 ↓ 78.1 1,171 1

Hash Join (cost=261.79..332.31 rows=15 width=1,492) (actual time=52.466..56.628 rows=1,171 loops=1)

  • Hash Cond: (pwg.inventory_pool_id = inventory_pools.id)
  • Buffers: shared hit=8298
24. 55.073 55.073 ↑ 2.0 1,465 1

CTE Scan on pwg (cost=0.00..59.26 rows=2,963 width=56) (actual time=52.131..55.073 rows=1,465 loops=1)

  • Buffers: shared hit=8194
25. 0.041 0.318 ↓ 8.0 8 1

Hash (cost=261.77..261.77 rows=1 width=1,436) (actual time=0.318..0.318 rows=8 loops=1)

  • Buckets: 1024 Batches: 1 Memory Usage: 13kB
  • Buffers: shared hit=104
26. 0.017 0.277 ↓ 8.0 8 1

Nested Loop (cost=0.41..261.77 rows=1 width=1,436) (actual time=0.033..0.277 rows=8 loops=1)

  • Buffers: shared hit=104
27. 0.043 0.043 ↑ 1.0 31 1

Seq Scan on inventory_pools (cost=0.00..3.98 rows=31 width=1,363) (actual time=0.008..0.043 rows=31 loops=1)

  • Filter: is_active
  • Rows Removed by Filter: 67
  • Buffers: shared hit=3
28. 0.217 0.217 ↓ 0.0 0 31

Index Scan using index_access_rights_pool_id_user_id on access_rights (cost=0.41..8.31 rows=1 width=73) (actual time=0.007..0.007 rows=0 loops=31)

  • Index Cond: ((inventory_pool_id = inventory_pools.id) AND (user_id = 'c0777d74-668b-5e01-abb5-f8277baa0ea8'::uuid))
  • Buffers: shared hit=101
29. 3.513 3.513 ↑ 1.0 1 1,171

Index Scan using models_pkey on models (cost=0.29..0.66 rows=1 width=700) (actual time=0.003..0.003 rows=1 loops=1,171)

  • Index Cond: (id = pwg.model_id)
  • Buffers: shared hit=3518
30. 10.539 10.539 ↓ 6.0 6 1,171

Index Scan using index_items_on_model_id_and_retired_and_inventory_pool_id on items (cost=0.41..1.34 rows=1 width=494) (actual time=0.005..0.009 rows=6 loops=1,171)

  • Index Cond: ((model_id = models.id) AND (retired IS NULL))
  • Filter: (is_borrowable AND (parent_id IS NULL))
  • Rows Removed by Filter: 2
  • Buffers: shared hit=9046
31. 12.750 12.750 ↓ 2.0 2 6,375

Index Scan using index_model_links_on_model_id_and_model_group_id on model_links (cost=0.29..0.37 rows=1 width=52) (actual time=0.002..0.002 rows=2 loops=6,375)

  • Index Cond: (model_id = models.id)
  • Buffers: shared hit=21955
32. 22.264 22.264 ↑ 1.0 1 11,132

Index Scan using model_groups_pkey on model_groups (cost=0.27..0.30 rows=1 width=58) (actual time=0.002..0.002 rows=1 loops=11,132)

  • Index Cond: (id = model_links.model_group_id)
  • Filter: ((type)::text = 'Category'::text)
  • Rows Removed by Filter: 0
  • Buffers: shared hit=33396
Planning time : 15.371 ms
Execution time : 131.462 ms