explain.depesz.com

PostgreSQL's explain analyze made readable

Result: rc16

Settings
# exclusive inclusive rows x rows loops node
1. 1.689 11,172.429 ↓ 249.0 249 1

Unique (cost=51,356.53..51,356.54 rows=1 width=33) (actual time=11,170.088..11,172.429 rows=249 loops=1)

  • Buffers: shared hit=2217985
2.          

CTE pwg

3. 1.132 53.629 ↑ 2.0 1,465 1

Subquery Scan on tmp (cost=50,169.39..50,317.29 rows=2,963 width=56) (actual time=50.617..53.629 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. 4.569 52.481 ↑ 1.8 3,363 1

HashAggregate (cost=50,161.09..50,220.25 rows=5,916 width=56) (actual time=50.595..52.481 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.548 47.912 ↑ 1.8 3,363 1

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

  • Buffers: shared hit=8192
6. 0.246 1.114 ↑ 1.0 1,157 1

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

  • Buffers: shared hit=13
7. 0.620 0.868 ↑ 1.0 1,157 1

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

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

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

  • Buffers: shared hit=12
9. 0.025 0.049 ↑ 1.0 57 1

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

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

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

  • Buffers: shared hit=1
11. 8.339 46.250 ↑ 2.2 2,206 1

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

  • Group Key: i.inventory_pool_id, i.model_id
  • Buffers: shared hit=8179
12. 20.263 20.263 ↓ 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..20.263 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.016 0.016 ↓ 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.016..0.016 rows=0 loops=1)

  • Index Cond: (user_id = 'c0777d74-668b-5e01-abb5-f8277baa0ea8'::uuid)
  • Heap Fetches: 0
  • Buffers: shared hit=2
20. 8.486 11,170.740 ↓ 9,904.0 9,904 1

Sort (cost=1,039.25..1,039.25 rows=1 width=33) (actual time=11,170.087..11,170.740 rows=9,904 loops=1)

  • Sort Key: model_groups.id, model_groups.name
  • Sort Method: quicksort Memory: 1236kB
  • Buffers: shared hit=2217985
21. 8.793 11,162.254 ↓ 9,904.0 9,904 1

Nested Loop (cost=5.92..1,039.24 rows=1 width=33) (actual time=50.898..11,162.254 rows=9,904 loops=1)

  • Buffers: shared hit=2217979
22. 7.628 11,131.197 ↓ 11,132.0 11,132 1

Nested Loop (cost=5.64..1,038.94 rows=1 width=16) (actual time=50.887..11,131.197 rows=11,132 loops=1)

  • Buffers: shared hit=2184583
23. 5.104 11,104.444 ↓ 6,375.0 6,375 1

Nested Loop (cost=5.36..1,038.56 rows=1 width=48) (actual time=50.863..11,104.444 rows=6,375 loops=1)

  • Buffers: shared hit=2162628
24. 3.888 87.017 ↓ 1,772.8 7,091 1

Nested Loop (cost=5.07..995.54 rows=4 width=96) (actual time=50.751..87.017 rows=7,091 loops=1)

  • Join Filter: (inventory_pools.id = items.inventory_pool_id)
  • Rows Removed by Join Filter: 2392
  • Buffers: shared hit=24462
25. 1.737 64.084 ↓ 3.2 1,465 1

Nested Loop (cost=4.65..373.39 rows=459 width=64) (actual time=50.728..64.084 rows=1,465 loops=1)

  • Buffers: shared hit=12598
26. 1.345 56.487 ↓ 3.2 1,465 1

Hash Join (cost=4.37..71.57 rows=459 width=48) (actual time=50.691..56.487 rows=1,465 loops=1)

  • Hash Cond: (pwg.inventory_pool_id = inventory_pools.id)
  • Buffers: shared hit=8197
27. 55.086 55.086 ↑ 2.0 1,465 1

CTE Scan on pwg (cost=0.00..59.26 rows=2,963 width=32) (actual time=50.620..55.086 rows=1,465 loops=1)

  • Buffers: shared hit=8194
28. 0.014 0.056 ↑ 1.0 31 1

Hash (cost=3.98..3.98 rows=31 width=16) (actual time=0.056..0.056 rows=31 loops=1)

  • Buckets: 1024 Batches: 1 Memory Usage: 10kB
  • Buffers: shared hit=3
29. 0.042 0.042 ↑ 1.0 31 1

Seq Scan on inventory_pools (cost=0.00..3.98 rows=31 width=16) (actual time=0.009..0.042 rows=31 loops=1)

  • Filter: is_active
  • Rows Removed by Filter: 67
  • Buffers: shared hit=3
30. 5.860 5.860 ↑ 1.0 1 1,465

Index Only Scan using models_pkey on models (cost=0.29..0.66 rows=1 width=16) (actual time=0.004..0.004 rows=1 loops=1,465)

  • Index Cond: (id = pwg.model_id)
  • Heap Fetches: 1465
  • Buffers: shared hit=4401
31. 19.045 19.045 ↓ 6.0 6 1,465

Index Scan using index_items_on_model_id_and_retired_and_inventory_pool_id on items (cost=0.41..1.34 rows=1 width=32) (actual time=0.007..0.013 rows=6 loops=1,465)

  • 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=11864
32. 11,012.323 11,012.323 ↑ 1.0 1 7,091

Index Scan using index_access_rights_on_inventory_pool_id on access_rights (cost=0.29..10.74 rows=1 width=16) (actual time=0.641..1.553 rows=1 loops=7,091)

  • Index Cond: (inventory_pool_id = items.inventory_pool_id)
  • Filter: (user_id = 'c0777d74-668b-5e01-abb5-f8277baa0ea8'::uuid)
  • Rows Removed by Filter: 7000
  • Buffers: shared hit=2138166
33. 19.125 19.125 ↓ 2.0 2 6,375

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

  • Index Cond: (model_id = models.id)
  • Heap Fetches: 11132
  • Buffers: shared hit=21955
34. 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=33) (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 : 13.774 ms
Execution time : 11,173.077 ms