explain.depesz.com

PostgreSQL's explain analyze made readable

Result: IbnE

Settings
# exclusive inclusive rows x rows loops node
1. 0.000 22.166 ↑ 1.0 1 1

Aggregate (cost=7,335.61..7,335.62 rows=1 width=8) (actual time=22.166..22.166 rows=1 loops=1)

  • Output: count(1)
2. 0.002 22.159 ↑ 50.2 4 1

Append (cost=6,449.87..7,333.10 rows=201 width=1,336) (actual time=5.275..22.159 rows=4 loops=1)

3.          

CTE tmp

4. 0.003 5.005 ↑ 50.0 4 1

Unique (cost=6,379.16..6,390.78 rows=200 width=558) (actual time=5.002..5.005 rows=4 loops=1)

  • Output: ch_1.resource_id, ch_1.sub_resource_id, ch_1.parent_id, ch_1.lvl
5.          

CTE ch

6. 0.008 4.984 ↑ 464.6 5 1

Recursive Union (cost=0.42..6,202.83 rows=2,323 width=363) (actual time=3.028..4.984 rows=5 loops=1)

7. 0.010 4.924 ↑ 5.8 4 1

Nested Loop (cost=0.42..473.12 rows=23 width=71) (actual time=3.026..4.924 rows=4 loops=1)

  • Output: c.resource_id, s_2.resource_id, c.parent_id, 0
  • Inner Unique: true
8. 4.858 4.858 ↑ 5.8 4 1

Seq Scan on iot.subscription s_2 (cost=0.00..412.51 rows=23 width=38) (actual time=2.998..4.858 rows=4 loops=1)

  • Output: s_2.resource_name, s_2.resource_id, s_2.resource_type, s_2.parent_id, s_2.access_control_policy_ids, s_2.creation_time, s_2.expiration_time, s_2.last_modified_time, s_2.labels, s_2.expiration_counter, s_2.notification_uri, s_2.group_id, s_2.notification_forwarding_uri, s_2.batch_notify, s_2.rate_limit, s_2.pre_subscription_notify, s_2.pending_notification, s_2.notification_storage_priority, s_2.latest_notify, s_2.notification_content_type, s_2.notification_event_cat, s_2.creator, s_2.subscriber_uri, s_2.schedule_id, s_2.group_subscription_resource_id, s_2.parent_type
  • Filter: ((s_2.group_subscription_resource_id IS NULL) AND (lower((s_2.creator)::text) = 'c264516aa-5e6f2974'::text))
  • Rows Removed by Filter: 4673
9. 0.056 0.056 ↑ 1.0 1 4

Index Scan using container_pkey on iot.container c (cost=0.42..2.64 rows=1 width=48) (actual time=0.014..0.014 rows=1 loops=4)

  • Output: c.resource_id, c.resource_type, c.resource_name, c.parent_id, c.expiration_time, c.labels, c.creation_time, c.last_modified_time, c.state_tag, c.announce_to, c.announced_attribute, c.creator, c.max_nr_of_instances, c.max_byte_size, c.max_instance_age, c.current_nr_of_instances, c.current_bytesize, c.location_id, c.ontology_ref, c.latest, c.oldest, c.id, c.inactive_since
  • Index Cond: ((c.resource_id)::text = (s_2.parent_id)::text)
10. 0.008 0.052 ↓ 0.0 0 2

Nested Loop (cost=0.42..568.33 rows=230 width=363) (actual time=0.011..0.026 rows=0 loops=2)

  • Output: ch.resource_id, ch.sub_resource_id, c_1.parent_id, (ch.lvl + 1)
  • Inner Unique: true
11. 0.004 0.004 ↑ 115.0 2 2

WorkTable Scan on ch (cost=0.00..4.60 rows=230 width=558) (actual time=0.001..0.002 rows=2 loops=2)

  • Output: ch.resource_id, ch.sub_resource_id, ch.parent_id, ch.lvl
12. 0.040 0.040 ↓ 0.0 0 5

Index Scan using container_pkey on iot.container c_1 (cost=0.42..2.45 rows=1 width=48) (actual time=0.008..0.008 rows=0 loops=5)

  • Output: c_1.resource_id, c_1.resource_type, c_1.resource_name, c_1.parent_id, c_1.expiration_time, c_1.labels, c_1.creation_time, c_1.last_modified_time, c_1.state_tag, c_1.announce_to, c_1.announced_attribute, c_1.creator, c_1.max_nr_of_instances, c_1.max_byte_size, c_1.max_instance_age, c_1.current_nr_of_instances, c_1.current_bytesize, c_1.location_id, c_1.ontology_ref, c_1.latest, c_1.oldest, c_1.id, c_1.inactive_since
  • Index Cond: ((c_1.resource_id)::text = (ch.parent_id)::text)
13. 0.014 5.002 ↑ 464.6 5 1

Sort (cost=176.34..182.14 rows=2,323 width=558) (actual time=5.001..5.002 rows=5 loops=1)

  • Output: ch_1.resource_id, ch_1.sub_resource_id, ch_1.parent_id, ch_1.lvl
  • Sort Key: ch_1.resource_id, ch_1.lvl DESC
  • Sort Method: quicksort Memory: 25kB
14. 4.988 4.988 ↑ 464.6 5 1

CTE Scan on ch ch_1 (cost=0.00..46.46 rows=2,323 width=558) (actual time=3.029..4.988 rows=5 loops=1)

  • Output: ch_1.resource_id, ch_1.sub_resource_id, ch_1.parent_id, ch_1.lvl
15. 0.006 5.306 ↑ 50.0 4 1

Nested Loop (cost=59.09..484.82 rows=200 width=1,336) (actual time=5.274..5.306 rows=4 loops=1)

  • Output: ae.resource_name, s.resource_name, s.resource_id, s.resource_type, s.parent_id, s.access_control_policy_ids, s.creation_time, s.expiration_time, s.last_modified_time, s.labels, s.expiration_counter, s.notification_uri, s.group_id, s.notification_forwarding_uri, s.batch_notify, s.rate_limit, s.pre_subscription_notify, s.pending_notification, s.notification_storage_priority, s.latest_notify, s.notification_content_type, s.notification_event_cat, s.creator, s.subscriber_uri, s.schedule_id, s.group_subscription_resource_id, s.parent_type
  • Inner Unique: true
16. 0.015 5.260 ↑ 50.0 4 1

Hash Join (cost=58.68..63.22 rows=200 width=233) (actual time=5.253..5.260 rows=4 loops=1)

  • Output: tmp.sub_resource_id, ae.resource_name
  • Inner Unique: true
  • Hash Cond: ((tmp.parent_id)::text = (ae.resource_id)::text)
17. 5.009 5.009 ↑ 50.0 4 1

CTE Scan on tmp (cost=0.00..4.00 rows=200 width=436) (actual time=5.004..5.009 rows=4 loops=1)

  • Output: tmp.resource_id, tmp.sub_resource_id, tmp.parent_id
18. 0.091 0.236 ↓ 1.0 303 1

Hash (cost=54.97..54.97 rows=297 width=34) (actual time=0.235..0.236 rows=303 loops=1)

  • Output: ae.resource_name, ae.resource_id
  • Buckets: 1024 Batches: 1 Memory Usage: 28kB
19. 0.145 0.145 ↓ 1.0 303 1

Seq Scan on iot.application_entity ae (cost=0.00..54.97 rows=297 width=34) (actual time=0.009..0.145 rows=303 loops=1)

  • Output: ae.resource_name, ae.resource_id
20. 0.040 0.040 ↑ 1.0 1 4

Index Scan using subscription_pkey on iot.subscription s (cost=0.41..2.11 rows=1 width=1,321) (actual time=0.010..0.010 rows=1 loops=4)

  • Output: s.resource_name, s.resource_id, s.resource_type, s.parent_id, s.access_control_policy_ids, s.creation_time, s.expiration_time, s.last_modified_time, s.labels, s.expiration_counter, s.notification_uri, s.group_id, s.notification_forwarding_uri, s.batch_notify, s.rate_limit, s.pre_subscription_notify, s.pending_notification, s.notification_storage_priority, s.latest_notify, s.notification_content_type, s.notification_event_cat, s.creator, s.subscriber_uri, s.schedule_id, s.group_subscription_resource_id, s.parent_type
  • Index Cond: ((s.resource_id)::text = (tmp.sub_resource_id)::text)
21. 0.012 16.851 ↓ 0.0 0 1

Nested Loop (cost=0.27..455.49 rows=1 width=1,336) (actual time=16.851..16.851 rows=0 loops=1)

  • Output: ae_1.resource_name, s_1.resource_name, s_1.resource_id, s_1.resource_type, s_1.parent_id, s_1.access_control_policy_ids, s_1.creation_time, s_1.expiration_time, s_1.last_modified_time, s_1.labels, s_1.expiration_counter, s_1.notification_uri, s_1.group_id, s_1.notification_forwarding_uri, s_1.batch_notify, s_1.rate_limit, s_1.pre_subscription_notify, s_1.pending_notification, s_1.notification_storage_priority, s_1.latest_notify, s_1.notification_content_type, s_1.notification_event_cat, s_1.creator, s_1.subscriber_uri, s_1.schedule_id, s_1.group_subscription_resource_id, s_1.parent_type
  • Inner Unique: true
22. 16.795 16.795 ↑ 5.8 4 1

Seq Scan on iot.subscription s_1 (cost=0.00..412.51 rows=23 width=1,321) (actual time=2.614..16.795 rows=4 loops=1)

  • Output: s_1.resource_name, s_1.resource_id, s_1.resource_type, s_1.parent_id, s_1.access_control_policy_ids, s_1.creation_time, s_1.expiration_time, s_1.last_modified_time, s_1.labels, s_1.expiration_counter, s_1.notification_uri, s_1.group_id, s_1.notification_forwarding_uri, s_1.batch_notify, s_1.rate_limit, s_1.pre_subscription_notify, s_1.pending_notification, s_1.notification_storage_priority, s_1.latest_notify, s_1.notification_content_type, s_1.notification_event_cat, s_1.creator, s_1.subscriber_uri, s_1.schedule_id, s_1.group_subscription_resource_id, s_1.parent_type
  • Filter: ((s_1.group_subscription_resource_id IS NULL) AND (lower((s_1.creator)::text) = 'c264516aa-5e6f2974'::text))
  • Rows Removed by Filter: 4673
23. 0.044 0.044 ↓ 0.0 0 4

Index Scan using applicationentity_pkey on iot.application_entity ae_1 (cost=0.27..1.87 rows=1 width=34) (actual time=0.011..0.011 rows=0 loops=4)

  • Output: ae_1.resource_id, ae_1.resource_type, ae_1.resource_name, ae_1.parent_id, ae_1.expiration_time, ae_1.creation_time, ae_1.last_modified_time, ae_1.labels, ae_1.announce_to, ae_1.announced_attribute, ae_1.app_name, ae_1.app_id, ae_1.ae_id, ae_1.point_of_access, ae_1.ontology_ref, ae_1.node_link, ae_1.active, ae_1.protocol, ae_1.tenant_id, ae_1.application_entity_id, ae_1.onem2m_schema, ae_1.request_reachability, ae_1.uiot_api_ver, ae_1.id, ae_1.application_hash
  • Index Cond: ((ae_1.resource_id)::text = (s_1.parent_id)::text)
  • QUERY PLAN
24. 0.004 0.048 ↑ 1.0 1 1

Aggregate (cost=7.88..7.89 rows=1 width=8) (actual time=0.047..0.048 rows=1 loops=1)

  • Output: count(*)
25. 0.004 0.044 ↑ 4.0 4 1

Nested Loop (cost=0.68..7.84 rows=16 width=0) (actual time=0.040..0.044 rows=4 loops=1)

26. 0.024 0.024 ↑ 1.0 1 1

Index Scan using applicationentity_pkey on iot.application_entity ae (cost=0.27..2.49 rows=1 width=23) (actual time=0.023..0.024 rows=1 loops=1)

  • Output: ae.resource_id, ae.resource_type, ae.resource_name, ae.parent_id, ae.expiration_time, ae.creation_time, ae.last_modified_time, ae.labels, ae.announce_to, ae.announced_attribute, ae.app_name, ae.app_id, ae.ae_id, ae.point_of_access, ae.ontology_ref, ae.node_link, ae.active, ae.protocol, ae.tenant_id, ae.application_entity_id, ae.onem2m_schema, ae.request_reachability, ae.uiot_api_ver, ae.id, ae.application_hash
  • Index Cond: ((ae.resource_id)::text = '7072521332670219964'::text)
27. 0.016 0.016 ↑ 5.0 4 1

Index Only Scan using subscription_creator_idx on iot.subscription s (cost=0.41..5.15 rows=20 width=26) (actual time=0.014..0.016 rows=4 loops=1)

  • Output: s.creator
  • Index Cond: (s.creator = (ae.ae_id)::text)
  • Heap Fetches: 3
Planning time : 0.470 ms
Execution time : 0.085 ms