explain.depesz.com

PostgreSQL's explain analyze made readable

Result: NWtR

Settings
# exclusive inclusive rows x rows loops node
1. 1.637 20,919.652 ↓ 249.0 249 1

Unique (cost=50,800.50..50,800.51 rows=1 width=33) (actual time=20,917.378..20,919.652 rows=249 loops=1)

  • Buffers: shared hit=3972575
2. 9.569 20,918.015 ↓ 9,904.0 9,904 1

Sort (cost=50,800.50..50,800.51 rows=1 width=33) (actual time=20,917.375..20,918.015 rows=9,904 loops=1)

  • Sort Key: model_groups.id, model_groups.name
  • Sort Method: quicksort Memory: 1236kB
  • Buffers: shared hit=3972575
3. 4.438 20,908.446 ↓ 9,904.0 9,904 1

Nested Loop (cost=50,372.90..50,800.49 rows=1 width=33) (actual time=61.814..20,908.446 rows=9,904 loops=1)

  • Buffers: shared hit=3972572
4. 5.863 20,874.296 ↓ 9,904.0 9,904 1

Nested Loop (cost=50,372.61..50,800.08 rows=1 width=81) (actual time=61.791..20,874.296 rows=9,904 loops=1)

  • Buffers: shared hit=3942845
5. 13.378 20,835.037 ↓ 11,132.0 11,132 1

Nested Loop (cost=50,372.34..50,799.78 rows=1 width=64) (actual time=61.780..20,835.037 rows=11,132 loops=1)

  • Buffers: shared hit=3909449
6. 7.052 99.314 ↓ 6,041.5 12,083 1

Nested Loop (cost=50,372.05..50,778.27 rows=2 width=112) (actual time=54.670..99.314 rows=12,083 loops=1)

  • Join Filter: (inventory_pools.id = items.inventory_pool_id)
  • Rows Removed by Join Filter: 4777
  • Buffers: shared hit=30248
7. 2.215 64.122 ↓ 10.0 1,876 1

Nested Loop (cost=50,371.64..50,521.23 rows=188 width=80) (actual time=54.648..64.122 rows=1,876 loops=1)

  • Buffers: shared hit=12775
8. 1.054 56.047 ↓ 9.5 1,465 1

Merge Join (cost=50,371.35..50,378.00 rows=154 width=48) (actual time=54.599..56.047 rows=1,465 loops=1)

  • Merge Cond: (inventory_pools.id = pwg.inventory_pool_id)
  • Buffers: shared hit=8197
9. 0.038 0.092 ↑ 1.0 31 1

Sort (cost=4.75..4.83 rows=31 width=16) (actual time=0.090..0.092 rows=31 loops=1)

  • Sort Key: inventory_pools.id
  • Sort Method: quicksort Memory: 26kB
  • Buffers: shared hit=3
10. 0.054 0.054 ↑ 1.0 31 1

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

  • Filter: is_active
  • Rows Removed by Filter: 67
  • Buffers: shared hit=3
11. 0.973 54.901 ↓ 1.5 1,465 1

Sort (cost=50,366.60..50,369.08 rows=991 width=32) (actual time=54.505..54.901 rows=1,465 loops=1)

  • Sort Key: pwg.inventory_pool_id
  • Sort Method: quicksort Memory: 163kB
  • Buffers: shared hit=8194
12. 0.657 53.928 ↓ 1.5 1,465 1

Subquery Scan on pwg (cost=50,169.39..50,317.29 rows=991 width=32) (actual time=52.249..53.928 rows=1,465 loops=1)

  • Filter: ((pwg.quantity > 0) AND ((hashed SubPlan 1) OR (pwg.entitlement_group_id IS NULL)))
  • Rows Removed by Filter: 1898
  • Buffers: shared hit=8194
13. 3.810 53.254 ↑ 1.8 3,363 1

HashAggregate (cost=50,161.09..50,220.25 rows=5,916 width=56) (actual time=52.226..53.254 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
14. 0.599 49.444 ↑ 1.8 3,363 1

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

  • Buffers: shared hit=8192
15. 0.278 1.131 ↑ 1.0 1,157 1

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

  • Buffers: shared hit=13
16. 0.596 0.853 ↑ 1.0 1,157 1

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

  • Hash Cond: (entitlements.entitlement_group_id = entitlement_groups.id)
  • Buffers: shared hit=13
17. 0.214 0.214 ↑ 1.0 1,157 1

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

  • Buffers: shared hit=12
18. 0.023 0.043 ↑ 1.0 57 1

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

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

Seq Scan on entitlement_groups (cost=0.00..1.57 rows=57 width=32) (actual time=0.007..0.020 rows=57 loops=1)

  • Buffers: shared hit=1
20. 8.768 47.714 ↑ 2.2 2,206 1

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

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

Seq Scan on items i (cost=0.00..2,065.12 rows=7,761 width=48) (actual time=0.016..21.298 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
22.          

SubPlan (for HashAggregate)

23. 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_1.inventory_pool_id, es.model_id
  • Buffers: shared hit=6480
24. 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_1.id)
  • Rows Removed by Join Filter: 1
  • Buffers: shared hit=6480
25. 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
26. 9.342 9.342 ↓ 1.5 3 1,038

Seq Scan on entitlement_groups entitlement_groups_1 (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
27.          

SubPlan (for Subquery Scan)

28. 0.017 0.017 ↓ 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.017..0.017 rows=0 loops=1)

  • Index Cond: (user_id = 'c0777d74-668b-5e01-abb5-f8277baa0ea8'::uuid)
  • Heap Fetches: 0
  • Buffers: shared hit=2
29. 5.860 5.860 ↑ 1.0 1 1,465

Index Only Scan using index_model_links_on_model_id_and_model_group_id on model_links (cost=0.29..0.92 rows=1 width=32) (actual time=0.004..0.004 rows=1 loops=1,465)

  • Index Cond: (model_id = pwg.model_id)
  • Heap Fetches: 1876
  • Buffers: shared hit=4578
30. 28.140 28.140 ↓ 9.0 9 1,876

Index Scan using index_items_on_model_id_and_retired_and_inventory_pool_id on items (cost=0.41..1.35 rows=1 width=32) (actual time=0.007..0.015 rows=9 loops=1,876)

  • Index Cond: ((model_id = model_links.model_id) AND (retired IS NULL))
  • Filter: (is_borrowable AND (parent_id IS NULL))
  • Rows Removed by Filter: 3
  • Buffers: shared hit=17473
31. 20,722.345 20,722.345 ↑ 1.0 1 12,083

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.704..1.715 rows=1 loops=12,083)

  • Index Cond: (inventory_pool_id = items.inventory_pool_id)
  • Filter: (user_id = 'c0777d74-668b-5e01-abb5-f8277baa0ea8'::uuid)
  • Rows Removed by Filter: 7487
  • Buffers: shared hit=3879201
32. 33.396 33.396 ↑ 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.003..0.003 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
33. 29.712 29.712 ↑ 1.0 1 9,904

Index Only Scan using models_pkey on models (cost=0.29..0.42 rows=1 width=16) (actual time=0.003..0.003 rows=1 loops=9,904)

  • Index Cond: (id = model_links.model_id)
  • Heap Fetches: 9904
  • Buffers: shared hit=29727
Planning time : 13.843 ms
Execution time : 20,920.299 ms