explain.depesz.com

PostgreSQL's explain analyze made readable

Result: LxA

Settings
# exclusive inclusive rows x rows loops node
1. 0.000 0.000 ↓ 0.0

Aggregate (cost=591.76..591.77 rows=1 width=32) (actual rows= loops=)

2. 0.000 0.000 ↓ 0.0

Nested Loop Semi Join (cost=42.43..591.74 rows=1 width=44) (actual rows= loops=)

3. 0.000 0.000 ↓ 0.0

Index Scan using asset_type_idx on asset (cost=0.29..482.87 rows=2 width=60) (actual rows= loops=)

  • Index Cond: (type = ANY ('{device,device_group}'::e_asset_type[]))
  • Filter: (((application_id = (((current_setting('hasura.user'::text))::json ->> 'x-hasura-application-id'::text))::uuid) OR ((application_id IS NULL) AND ((((current_setting('hasura.user'::text))::json ->> 'x-hasura-application-id'::text))::uuid IS NULL))) AND (((type = 'inventory'::e_asset_type) AND (alternatives: SubPlan 2 or hashed SubPlan 3)) OR ((type = ANY ('{device_group,device}'::e_asset_type[])) AND (alternatives: SubPlan 4 or hashed SubPlan 5))))
4.          

SubPlan (forIndex Scan)

5. 0.000 0.000 ↓ 0.0

Nested Loop (cost=50.58..58.75 rows=1 width=0) (actual rows= loops=)

  • Join Filter: (asset_2.path ~ (concat('*.', replace((inventory_2.id)::text, '-'::text, ''::text), '.*'))::lquery)
6. 0.000 0.000 ↓ 0.0

Index Scan using asset_pkey on asset asset_2 (cost=0.29..8.31 rows=1 width=242) (actual rows= loops=)

  • Index Cond: (id = asset.id)
  • Filter: ((type = 'inventory'::e_asset_type) OR (type = 'device'::e_asset_type) OR (type = 'device_group'::e_asset_type))
7. 0.000 0.000 ↓ 0.0

HashAggregate (cost=50.29..50.32 rows=3 width=61) (actual rows= loops=)

  • Group Key: user_2.ref_id, user_2.application_id, inventory_2.id, permissions_2.create_application, permissions_2.read_application, permissions_2.update_application, permissions_2.create_inventory, permissions_2.read_inventory, permissions_2.update_inventory, permissions_2.create_user, permissions_2.read_user, permissions_2.update_user, permissions_2.control_device, permissions_2.create_device, permissions_2.read_device, permissions_2.update_device
8. 0.000 0.000 ↓ 0.0

Append (cost=0.86..50.17 rows=3 width=61) (actual rows= loops=)

9. 0.000 0.000 ↓ 0.0

Nested Loop (cost=0.86..25.12 rows=1 width=61) (actual rows= loops=)

10. 0.000 0.000 ↓ 0.0

Nested Loop (cost=0.86..25.09 rows=1 width=53) (actual rows= loops=)

11. 0.000 0.000 ↓ 0.0

Nested Loop (cost=0.57..16.78 rows=1 width=53) (actual rows= loops=)

  • Join Filter: (user_2.parent_id = user_group_1.id)
12. 0.000 0.000 ↓ 0.0

Index Scan using asset_type_idx on asset user_2 (cost=0.29..8.36 rows=2 width=48) (actual rows= loops=)

  • Index Cond: (type = 'user'::e_asset_type)
  • Filter: (deleted_at IS NULL)
13. 0.000 0.000 ↓ 0.0

Materialize (cost=0.29..8.37 rows=2 width=37) (actual rows= loops=)

14. 0.000 0.000 ↓ 0.0

Index Scan using asset_type_idx on asset user_group_1 (cost=0.29..8.36 rows=2 width=37) (actual rows= loops=)

  • Index Cond: (type = 'user_group'::e_asset_type)
  • Filter: (deleted_at IS NULL)
15. 0.000 0.000 ↓ 0.0

Index Scan using asset_pkey on asset inventory_2 (cost=0.29..8.30 rows=1 width=16) (actual rows= loops=)

  • Index Cond: (id = user_group_1.parent_id)
  • Filter: ((deleted_at IS NULL) AND (type = 'inventory'::e_asset_type))
16. 0.000 0.000 ↓ 0.0

Function Scan on jsonb_populate_record permissions_2 (cost=0.00..0.01 rows=1 width=13) (actual rows= loops=)

  • Filter: read_inventory
17. 0.000 0.000 ↓ 0.0

Nested Loop (cost=0.57..25.03 rows=2 width=61) (actual rows= loops=)

18. 0.000 0.000 ↓ 0.0

Nested Loop (cost=0.57..24.99 rows=2 width=53) (actual rows= loops=)

19. 0.000 0.000 ↓ 0.0

Index Scan using asset_type_idx on asset user_3 (cost=0.29..8.36 rows=2 width=53) (actual rows= loops=)

  • Index Cond: (type = 'user'::e_asset_type)
  • Filter: (deleted_at IS NULL)
20. 0.000 0.000 ↓ 0.0

Index Scan using asset_pkey on asset inventory_3 (cost=0.29..8.30 rows=1 width=16) (actual rows= loops=)

  • Index Cond: (id = user_3.parent_id)
  • Filter: ((deleted_at IS NULL) AND (type = 'inventory'::e_asset_type))
21. 0.000 0.000 ↓ 0.0

Function Scan on jsonb_populate_record permissions_3 (cost=0.00..0.01 rows=1 width=13) (actual rows= loops=)

  • Filter: read_inventory
22. 0.000 0.000 ↓ 0.0

Nested Loop (cost=58.65..173.98 rows=28 width=16) (actual rows= loops=)

23. 0.000 0.000 ↓ 0.0

HashAggregate (cost=50.29..50.32 rows=3 width=61) (actual rows= loops=)

  • Group Key: user_4.ref_id, user_4.application_id, inventory_4.id, permissions_4.create_application, permissions_4.read_application, permissions_4.update_application, permissions_4.create_inventory, permissions_4.read_inventory, permissions_4.update_inventory, permissions_4.create_user, permissions_4.read_user, permissions_4.update_user, permissions_4.control_device, permissions_4.create_device, permissions_4.read_device, permissions_4.update_device
24. 0.000 0.000 ↓ 0.0

Append (cost=0.86..50.17 rows=3 width=61) (actual rows= loops=)

25. 0.000 0.000 ↓ 0.0

Nested Loop (cost=0.86..25.12 rows=1 width=61) (actual rows= loops=)

26. 0.000 0.000 ↓ 0.0

Nested Loop (cost=0.86..25.09 rows=1 width=53) (actual rows= loops=)

27. 0.000 0.000 ↓ 0.0

Nested Loop (cost=0.57..16.78 rows=1 width=53) (actual rows= loops=)

  • Join Filter: (user_4.parent_id = user_group_2.id)
28. 0.000 0.000 ↓ 0.0

Index Scan using asset_type_idx on asset user_4 (cost=0.29..8.36 rows=2 width=48) (actual rows= loops=)

  • Index Cond: (type = 'user'::e_asset_type)
  • Filter: (deleted_at IS NULL)
29. 0.000 0.000 ↓ 0.0

Materialize (cost=0.29..8.37 rows=2 width=37) (actual rows= loops=)

30. 0.000 0.000 ↓ 0.0

Index Scan using asset_type_idx on asset user_group_2 (cost=0.29..8.36 rows=2 width=37) (actual rows= loops=)

  • Index Cond: (type = 'user_group'::e_asset_type)
  • Filter: (deleted_at IS NULL)
31. 0.000 0.000 ↓ 0.0

Index Scan using asset_pkey on asset inventory_4 (cost=0.29..8.30 rows=1 width=16) (actual rows= loops=)

  • Index Cond: (id = user_group_2.parent_id)
  • Filter: ((deleted_at IS NULL) AND (type = 'inventory'::e_asset_type))
32. 0.000 0.000 ↓ 0.0

Function Scan on jsonb_populate_record permissions_4 (cost=0.00..0.01 rows=1 width=13) (actual rows= loops=)

  • Filter: read_inventory
33. 0.000 0.000 ↓ 0.0

Nested Loop (cost=0.57..25.03 rows=2 width=61) (actual rows= loops=)

34. 0.000 0.000 ↓ 0.0

Nested Loop (cost=0.57..24.99 rows=2 width=53) (actual rows= loops=)

35. 0.000 0.000 ↓ 0.0

Index Scan using asset_type_idx on asset user_5 (cost=0.29..8.36 rows=2 width=53) (actual rows= loops=)

  • Index Cond: (type = 'user'::e_asset_type)
  • Filter: (deleted_at IS NULL)
36. 0.000 0.000 ↓ 0.0

Index Scan using asset_pkey on asset inventory_5 (cost=0.29..8.30 rows=1 width=16) (actual rows= loops=)

  • Index Cond: (id = user_5.parent_id)
  • Filter: ((deleted_at IS NULL) AND (type = 'inventory'::e_asset_type))
37. 0.000 0.000 ↓ 0.0

Function Scan on jsonb_populate_record permissions_5 (cost=0.00..0.01 rows=1 width=13) (actual rows= loops=)

  • Filter: read_inventory
38. 0.000 0.000 ↓ 0.0

Bitmap Heap Scan on asset asset_3 (cost=8.36..41.12 rows=9 width=258) (actual rows= loops=)

  • Recheck Cond: (path ~ (concat('*.', replace((inventory_4.id)::text, '-'::text, ''::text), '.*'))::lquery)
  • Filter: ((type = 'inventory'::e_asset_type) OR (type = 'device'::e_asset_type) OR (type = 'device_group'::e_asset_type))
39. 0.000 0.000 ↓ 0.0

Bitmap Index Scan on asset_path_gist (cost=0.00..8.36 rows=9 width=0) (actual rows= loops=)

  • Index Cond: (path ~ (concat('*.', replace((inventory_4.id)::text, '-'::text, ''::text), '.*'))::lquery)
40. 0.000 0.000 ↓ 0.0

Nested Loop (cost=50.58..58.75 rows=1 width=0) (actual rows= loops=)

  • Join Filter: (asset_4.path ~ (concat('*.', replace((inventory_6.id)::text, '-'::text, ''::text), '.*'))::lquery)
41. 0.000 0.000 ↓ 0.0

Index Scan using asset_pkey on asset asset_4 (cost=0.29..8.31 rows=1 width=242) (actual rows= loops=)

  • Index Cond: (id = asset.id)
  • Filter: ((type = 'inventory'::e_asset_type) OR (type = 'device'::e_asset_type) OR (type = 'device_group'::e_asset_type))
42. 0.000 0.000 ↓ 0.0

HashAggregate (cost=50.29..50.32 rows=3 width=61) (actual rows= loops=)

  • Group Key: user_6.ref_id, user_6.application_id, inventory_6.id, permissions_6.create_application, permissions_6.read_application, permissions_6.update_application, permissions_6.create_inventory, permissions_6.read_inventory, permissions_6.update_inventory, permissions_6.create_user, permissions_6.read_user, permissions_6.update_user, permissions_6.control_device, permissions_6.create_device, permissions_6.read_device, permissions_6.update_device
43. 0.000 0.000 ↓ 0.0

Append (cost=0.86..50.17 rows=3 width=61) (actual rows= loops=)

44. 0.000 0.000 ↓ 0.0

Nested Loop (cost=0.86..25.12 rows=1 width=61) (actual rows= loops=)

45. 0.000 0.000 ↓ 0.0

Nested Loop (cost=0.86..25.09 rows=1 width=53) (actual rows= loops=)

46. 0.000 0.000 ↓ 0.0

Nested Loop (cost=0.57..16.78 rows=1 width=53) (actual rows= loops=)

  • Join Filter: (user_6.parent_id = user_group_3.id)
47. 0.000 0.000 ↓ 0.0

Index Scan using asset_type_idx on asset user_6 (cost=0.29..8.36 rows=2 width=48) (actual rows= loops=)

  • Index Cond: (type = 'user'::e_asset_type)
  • Filter: (deleted_at IS NULL)
48. 0.000 0.000 ↓ 0.0

Materialize (cost=0.29..8.37 rows=2 width=37) (actual rows= loops=)

49. 0.000 0.000 ↓ 0.0

Index Scan using asset_type_idx on asset user_group_3 (cost=0.29..8.36 rows=2 width=37) (actual rows= loops=)

  • Index Cond: (type = 'user_group'::e_asset_type)
  • Filter: (deleted_at IS NULL)
50. 0.000 0.000 ↓ 0.0

Index Scan using asset_pkey on asset inventory_6 (cost=0.29..8.30 rows=1 width=16) (actual rows= loops=)

  • Index Cond: (id = user_group_3.parent_id)
  • Filter: ((deleted_at IS NULL) AND (type = 'inventory'::e_asset_type))
51. 0.000 0.000 ↓ 0.0

Function Scan on jsonb_populate_record permissions_6 (cost=0.00..0.01 rows=1 width=13) (actual rows= loops=)

  • Filter: read_device
52. 0.000 0.000 ↓ 0.0

Nested Loop (cost=0.57..25.03 rows=2 width=61) (actual rows= loops=)

53. 0.000 0.000 ↓ 0.0

Nested Loop (cost=0.57..24.99 rows=2 width=53) (actual rows= loops=)

54. 0.000 0.000 ↓ 0.0

Index Scan using asset_type_idx on asset user_7 (cost=0.29..8.36 rows=2 width=53) (actual rows= loops=)

  • Index Cond: (type = 'user'::e_asset_type)
  • Filter: (deleted_at IS NULL)
55. 0.000 0.000 ↓ 0.0

Index Scan using asset_pkey on asset inventory_7 (cost=0.29..8.30 rows=1 width=16) (actual rows= loops=)

  • Index Cond: (id = user_7.parent_id)
  • Filter: ((deleted_at IS NULL) AND (type = 'inventory'::e_asset_type))
56. 0.000 0.000 ↓ 0.0

Function Scan on jsonb_populate_record permissions_7 (cost=0.00..0.01 rows=1 width=13) (actual rows= loops=)

  • Filter: read_device
57. 0.000 0.000 ↓ 0.0

Nested Loop (cost=58.65..173.98 rows=28 width=16) (actual rows= loops=)

58. 0.000 0.000 ↓ 0.0

HashAggregate (cost=50.29..50.32 rows=3 width=61) (actual rows= loops=)

  • Group Key: user_8.ref_id, user_8.application_id, inventory_8.id, permissions_8.create_application, permissions_8.read_application, permissions_8.update_application, permissions_8.create_inventory, permissions_8.read_inventory, permissions_8.update_inventory, permissions_8.create_user, permissions_8.read_user, permissions_8.update_user, permissions_8.control_device, permissions_8.create_device, permissions_8.read_device, permissions_8.update_device
59. 0.000 0.000 ↓ 0.0

Append (cost=0.86..50.17 rows=3 width=61) (actual rows= loops=)

60. 0.000 0.000 ↓ 0.0

Nested Loop (cost=0.86..25.12 rows=1 width=61) (actual rows= loops=)

61. 0.000 0.000 ↓ 0.0

Nested Loop (cost=0.86..25.09 rows=1 width=53) (actual rows= loops=)

62. 0.000 0.000 ↓ 0.0

Nested Loop (cost=0.57..16.78 rows=1 width=53) (actual rows= loops=)

  • Join Filter: (user_8.parent_id = user_group_4.id)
63. 0.000 0.000 ↓ 0.0

Index Scan using asset_type_idx on asset user_8 (cost=0.29..8.36 rows=2 width=48) (actual rows= loops=)

  • Index Cond: (type = 'user'::e_asset_type)
  • Filter: (deleted_at IS NULL)
64. 0.000 0.000 ↓ 0.0

Materialize (cost=0.29..8.37 rows=2 width=37) (actual rows= loops=)

65. 0.000 0.000 ↓ 0.0

Index Scan using asset_type_idx on asset user_group_4 (cost=0.29..8.36 rows=2 width=37) (actual rows= loops=)

  • Index Cond: (type = 'user_group'::e_asset_type)
  • Filter: (deleted_at IS NULL)
66. 0.000 0.000 ↓ 0.0

Index Scan using asset_pkey on asset inventory_8 (cost=0.29..8.30 rows=1 width=16) (actual rows= loops=)

  • Index Cond: (id = user_group_4.parent_id)
  • Filter: ((deleted_at IS NULL) AND (type = 'inventory'::e_asset_type))
67. 0.000 0.000 ↓ 0.0

Function Scan on jsonb_populate_record permissions_8 (cost=0.00..0.01 rows=1 width=13) (actual rows= loops=)

  • Filter: read_device
68. 0.000 0.000 ↓ 0.0

Nested Loop (cost=0.57..25.03 rows=2 width=61) (actual rows= loops=)

69. 0.000 0.000 ↓ 0.0

Nested Loop (cost=0.57..24.99 rows=2 width=53) (actual rows= loops=)

70. 0.000 0.000 ↓ 0.0

Index Scan using asset_type_idx on asset user_9 (cost=0.29..8.36 rows=2 width=53) (actual rows= loops=)

  • Index Cond: (type = 'user'::e_asset_type)
  • Filter: (deleted_at IS NULL)
71. 0.000 0.000 ↓ 0.0

Index Scan using asset_pkey on asset inventory_9 (cost=0.29..8.30 rows=1 width=16) (actual rows= loops=)

  • Index Cond: (id = user_9.parent_id)
  • Filter: ((deleted_at IS NULL) AND (type = 'inventory'::e_asset_type))
72. 0.000 0.000 ↓ 0.0

Function Scan on jsonb_populate_record permissions_9 (cost=0.00..0.01 rows=1 width=13) (actual rows= loops=)

  • Filter: read_device
73. 0.000 0.000 ↓ 0.0

Bitmap Heap Scan on asset asset_5 (cost=8.36..41.12 rows=9 width=258) (actual rows= loops=)

  • Recheck Cond: (path ~ (concat('*.', replace((inventory_8.id)::text, '-'::text, ''::text), '.*'))::lquery)
  • Filter: ((type = 'inventory'::e_asset_type) OR (type = 'device'::e_asset_type) OR (type = 'device_group'::e_asset_type))
74. 0.000 0.000 ↓ 0.0

Bitmap Index Scan on asset_path_gist (cost=0.00..8.36 rows=9 width=0) (actual rows= loops=)

  • Index Cond: (path ~ (concat('*.', replace((inventory_8.id)::text, '-'::text, ''::text), '.*'))::lquery)
75. 0.000 0.000 ↓ 0.0

Nested Loop (cost=42.15..50.33 rows=1 width=16) (actual rows= loops=)

  • Join Filter: (asset_1.path ~ (concat('*.', replace((inventory.id)::text, '-'::text, ''::text), '.*'))::lquery)
76. 0.000 0.000 ↓ 0.0

Index Scan using asset_pkey on asset asset_1 (cost=0.29..8.31 rows=1 width=258) (actual rows= loops=)

  • Index Cond: (id = asset.id)
  • Filter: ((type = 'inventory'::e_asset_type) OR (type = 'device'::e_asset_type) OR (type = 'device_group'::e_asset_type))
77. 0.000 0.000 ↓ 0.0

Unique (cost=41.86..41.95 rows=2 width=61) (actual rows= loops=)

78. 0.000 0.000 ↓ 0.0

Sort (cost=41.86..41.87 rows=2 width=61) (actual rows= loops=)

  • Sort Key: "user".ref_id, "user".application_id, inventory.id, permissions.create_application, permissions.read_application, permissions.update_application, permissions.create_inventory, permissions.read_inventory, permissions.update_inventory, permissions.create_user, permissions.read_user, permissions.update_user, permissions.control_device, permissions.create_device, permissions.read_device, permissions.update_device
79. 0.000 0.000 ↓ 0.0

Append (cost=0.86..41.85 rows=2 width=61) (actual rows= loops=)

80. 0.000 0.000 ↓ 0.0

Nested Loop (cost=0.86..25.07 rows=1 width=61) (actual rows= loops=)

81. 0.000 0.000 ↓ 0.0

Nested Loop (cost=0.86..25.05 rows=1 width=53) (actual rows= loops=)

82. 0.000 0.000 ↓ 0.0

Nested Loop (cost=0.57..16.74 rows=1 width=53) (actual rows= loops=)

83. 0.000 0.000 ↓ 0.0

Index Scan using asset_type_idx on asset "user" (cost=0.29..8.42 rows=1 width=48) (actual rows= loops=)

  • Index Cond: (type = 'user'::e_asset_type)
  • Filter: ((deleted_at IS NULL) AND ((ref_id = (((current_setting('hasura.user'::text))::json ->> 'x-hasura-user-id'::text))::uuid) OR ((ref_id IS NULL) AND ((((current_setting('hasura.user'::text))::json ->> 'x-hasura-user-id'::text))::uuid IS NULL))))
84. 0.000 0.000 ↓ 0.0

Index Scan using asset_pkey on asset user_group (cost=0.29..8.30 rows=1 width=37) (actual rows= loops=)

  • Index Cond: (id = "user".parent_id)
  • Filter: ((deleted_at IS NULL) AND (type = 'user_group'::e_asset_type))
85. 0.000 0.000 ↓ 0.0

Index Scan using asset_pkey on asset inventory (cost=0.29..8.30 rows=1 width=16) (actual rows= loops=)

  • Index Cond: (id = user_group.parent_id)
  • Filter: ((deleted_at IS NULL) AND (type = 'inventory'::e_asset_type))
86. 0.000 0.000 ↓ 0.0

Function Scan on jsonb_populate_record permissions (cost=0.00..0.01 rows=1 width=13) (actual rows= loops=)

87. 0.000 0.000 ↓ 0.0

Nested Loop (cost=0.57..16.76 rows=1 width=61) (actual rows= loops=)

88. 0.000 0.000 ↓ 0.0

Nested Loop (cost=0.57..16.74 rows=1 width=53) (actual rows= loops=)

89. 0.000 0.000 ↓ 0.0

Index Scan using asset_type_idx on asset user_1 (cost=0.29..8.42 rows=1 width=53) (actual rows= loops=)

  • Index Cond: (type = 'user'::e_asset_type)
  • Filter: ((deleted_at IS NULL) AND ((ref_id = (((current_setting('hasura.user'::text))::json ->> 'x-hasura-user-id'::text))::uuid) OR ((ref_id IS NULL) AND ((((current_setting('hasura.user'::text))::json ->> 'x-hasura-user-id'::text))::uuid IS NULL))))
90. 0.000 0.000 ↓ 0.0

Index Scan using asset_pkey on asset inventory_1 (cost=0.29..8.30 rows=1 width=16) (actual rows= loops=)

  • Index Cond: (id = user_1.parent_id)
  • Filter: ((deleted_at IS NULL) AND (type = 'inventory'::e_asset_type))
91. 0.000 0.000 ↓ 0.0

Function Scan on jsonb_populate_record permissions_1 (cost=0.00..0.01 rows=1 width=13) (actual rows= loops=)

92.          

SubPlan (forAggregate)

93. 0.000 0.000 ↓ 0.0

Result (cost=0.00..0.01 rows=1 width=32) (actual rows= loops=)