explain.depesz.com

PostgreSQL's explain analyze made readable

Result: AxGC

Settings
# exclusive inclusive rows x rows loops node
1. 0.000 20,831.452 ↓ 4.0 4 1

Sort (cost=51,578.40..51,578.40 rows=1 width=33) (actual time=20,831.452..20,831.452 rows=4 loops=1)

  • Sort Key: model_groups.name
  • Sort Method: quicksort Memory: 25kB
2.          

CTE all_borrowable_categories

3. 1.591 20,830.825 ↓ 249.0 249 1

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

4. 9.240 20,829.234 ↓ 9,904.0 9,904 1

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

  • Sort Key: model_groups_1.id, model_groups_1.name
  • Sort Method: quicksort Memory: 1236kB
5. 12.787 20,819.994 ↓ 9,904.0 9,904 1

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

6. 4.674 20,787.399 ↓ 9,904.0 9,904 1

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

7. 15.480 20,749.329 ↓ 11,132.0 11,132 1

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

8. 5.409 96.085 ↓ 6,041.5 12,083 1

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

  • Join Filter: (inventory_pools.id = items.inventory_pool_id)
  • Rows Removed by Join Filter: 4777
9. 2.183 62.536 ↓ 10.0 1,876 1

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

10. 0.962 54.493 ↓ 9.5 1,465 1

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

  • Merge Cond: (inventory_pools.id = pwg.inventory_pool_id)
11. 0.045 0.092 ↑ 1.0 31 1

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

  • Sort Key: inventory_pools.id
  • Sort Method: quicksort Memory: 26kB
12. 0.047 0.047 ↑ 1.0 31 1

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

  • Filter: is_active
  • Rows Removed by Filter: 67
13. 0.886 53.439 ↓ 1.5 1,465 1

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

  • Sort Key: pwg.inventory_pool_id
  • Sort Method: quicksort Memory: 163kB
14. 0.570 52.553 ↓ 1.5 1,465 1

Subquery Scan on pwg (cost=50,169.39..50,317.29 rows=991 width=32) (actual time=50.892..52.553 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
15. 3.597 51.967 ↑ 1.8 3,363 1

HashAggregate (cost=50,161.09..50,220.25 rows=5,916 width=56) (actual time=50.872..51.967 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)
16. 0.368 48.370 ↑ 1.8 3,363 1

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

17. 0.226 1.026 ↑ 1.0 1,157 1

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

18. 0.543 0.800 ↑ 1.0 1,157 1

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

  • Hash Cond: (entitlements.entitlement_group_id = entitlement_groups_1.id)
19. 0.217 0.217 ↑ 1.0 1,157 1

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

20. 0.018 0.040 ↑ 1.0 57 1

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

  • Buckets: 1024 Batches: 1 Memory Usage: 12kB
21. 0.022 0.022 ↑ 1.0 57 1

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

22. 7.714 46.976 ↑ 2.2 2,206 1

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

  • Group Key: i.inventory_pool_id, i.model_id
23. 21.614 21.614 ↓ 1.2 9,075 1

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

  • Filter: ((retired IS NULL) AND is_borrowable AND (parent_id IS NULL))
  • Rows Removed by Filter: 27537
24.          

SubPlan (for HashAggregate)

25. 2.206 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
26. 2.726 15.442 ↓ 0.0 0 2,206

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

  • Join Filter: (es.entitlement_group_id = entitlement_groups.id)
  • Rows Removed by Join Filter: 1
27. 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)
28. 8.304 8.304 ↓ 1.5 3 1,038

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

  • Filter: (inventory_pool_id = i.inventory_pool_id)
  • Rows Removed by Filter: 42
29.          

SubPlan (for Subquery Scan)

30. 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
31. 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
32. 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
33. 20,637.764 20,637.764 ↑ 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.700..1.708 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
34. 33.396 33.396 ↑ 1.0 1 11,132

Index Scan using model_groups_pkey on model_groups model_groups_1 (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
35. 19.808 19.808 ↑ 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.002..0.002 rows=1 loops=9,904)

  • Index Cond: (id = model_links.model_id)
  • Heap Fetches: 9904
36.          

Initplan (for Sort)

37. 20,830.912 20,830.912 ↓ 249.0 249 1

CTE Scan on all_borrowable_categories (cost=0.00..0.02 rows=1 width=16) (actual time=20,828.635..20,830.912 rows=249 loops=1)

38. 0.012 20,831.396 ↓ 4.0 4 1

Nested Loop (cost=4.56..777.86 rows=1 width=33) (actual time=20,831.090..20,831.396 rows=4 loops=1)

39. 0.018 0.040 ↓ 2.0 4 1

Bitmap Heap Scan on model_group_links (cost=4.29..8.85 rows=2 width=16) (actual time=0.031..0.040 rows=4 loops=1)

  • Recheck Cond: (parent_id = '39d77309-8540-5fa0-a0ac-9f7bd1ea4dce'::uuid)
  • Heap Blocks: exact=2
40. 0.022 0.022 ↓ 2.0 4 1

Bitmap Index Scan on index_model_group_links_on_parent_id (cost=0.00..4.29 rows=2 width=0) (actual time=0.022..0.022 rows=4 loops=1)

  • Index Cond: (parent_id = '39d77309-8540-5fa0-a0ac-9f7bd1ea4dce'::uuid)
41. 20,831.036 20,831.344 ↑ 1.0 1 4

Index Scan using model_groups_pkey on model_groups (cost=0.27..329.61 rows=1 width=33) (actual time=5,207.836..5,207.836 rows=1 loops=4)

  • Index Cond: (id = model_group_links.child_id)
  • Filter: ((SubPlan 5) && $12)
42.          

SubPlan (for Index Scan)

43. 0.024 0.308 ↑ 201.0 1 4

HashAggregate (cost=319.31..321.32 rows=201 width=16) (actual time=0.076..0.077 rows=1 loops=4)

  • Group Key: category_tree_1.child_id
44.          

CTE category_tree

45. 0.028 0.256 ↓ 0.0 0 4

Recursive Union (cost=4.31..261.58 rows=2,365 width=64) (actual time=0.021..0.064 rows=0 loops=4)

46. 0.012 0.032 ↓ 0.0 0 4

Bitmap Heap Scan on model_group_links model_group_links_1 (cost=4.31..10.58 rows=5 width=64) (actual time=0.008..0.008 rows=0 loops=4)

  • Recheck Cond: (parent_id = model_groups.id)
  • Heap Blocks: exact=1
47. 0.020 0.020 ↓ 0.0 0 4

Bitmap Index Scan on index_model_group_links_on_parent_id (cost=0.00..4.31 rows=5 width=0) (actual time=0.005..0.005 rows=0 loops=4)

  • Index Cond: (parent_id = model_groups.id)
48. 0.120 0.196 ↓ 0.0 0 4

Hash Join (cost=1.62..20.37 rows=236 width=64) (actual time=0.049..0.049 rows=0 loops=4)

  • Hash Cond: (mgl.parent_id = category_tree.child_id)
  • Join Filter: (mgl.child_id <> ALL (category_tree.path))
49. 0.068 0.068 ↑ 2.0 244 2

Seq Scan on model_group_links mgl (cost=0.00..10.88 rows=488 width=32) (actual time=0.006..0.034 rows=244 loops=2)

50. 0.004 0.008 ↓ 0.0 0 4

Hash (cost=1.00..1.00 rows=50 width=48) (actual time=0.002..0.002 rows=0 loops=4)

  • Buckets: 1024 Batches: 1 Memory Usage: 8kB
51. 0.004 0.004 ↓ 0.0 0 4

WorkTable Scan on category_tree (cost=0.00..1.00 rows=50 width=48) (actual time=0.001..0.001 rows=0 loops=4)

52. 0.012 0.284 ↑ 201.0 1 4

Append (cost=53.21..57.23 rows=201 width=16) (actual time=0.070..0.071 rows=1 loops=4)

53. 0.008 0.272 ↓ 0.0 0 4

HashAggregate (cost=53.21..55.21 rows=200 width=16) (actual time=0.068..0.068 rows=0 loops=4)

  • Group Key: category_tree_1.child_id
54. 0.264 0.264 ↓ 0.0 0 4

CTE Scan on category_tree category_tree_1 (cost=0.00..47.30 rows=2,365 width=16) (actual time=0.022..0.066 rows=0 loops=4)

55. 0.000 0.000 ↑ 1.0 1 4

Result (cost=0.00..0.01 rows=1 width=16) (actual time=0.000..0.000 rows=1 loops=4)

Planning time : 15.080 ms
Execution time : 20,832.275 ms