explain.depesz.com

PostgreSQL's explain analyze made readable

Result: BhGJ : test1

Settings
# exclusive inclusive rows x rows loops node
1. 0.005 10.677 ↑ 1.0 1 1

Aggregate (cost=7,351.19..7,351.20 rows=1 width=8) (actual time=10.677..10.677 rows=1 loops=1)

  • Output: count(1)
2. 0.024 10.672 ↑ 50.2 4 1

HashAggregate (cost=7,346.66..7,348.67 rows=201 width=3,107) (actual time=10.669..10.672 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
  • Group Key: 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
3.          

CTE tmp

4. 0.003 5.234 ↑ 50.0 4 1

Unique (cost=6,379.16..6,390.78 rows=200 width=558) (actual time=5.231..5.234 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.007 5.208 ↑ 464.6 5 1

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

7. 0.018 5.141 ↑ 5.8 4 1

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

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

Seq Scan on iot.subscription s_2 (cost=0.00..412.51 rows=23 width=38) (actual time=2.868..5.067 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.060 ↓ 0.0 0 2

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

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

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

  • Output: ch.resource_id, ch.sub_resource_id, ch.parent_id, ch.lvl
12. 0.050 0.050 ↓ 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.010..0.010 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.017 5.231 ↑ 464.6 5 1

Sort (cost=176.34..182.14 rows=2,323 width=558) (actual time=5.230..5.231 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. 5.214 5.214 ↑ 464.6 5 1

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

  • Output: ch_1.resource_id, ch_1.sub_resource_id, ch_1.parent_id, ch_1.lvl
15. 0.001 10.648 ↑ 50.2 4 1

Append (cost=59.09..942.32 rows=201 width=3,107) (actual time=5.732..10.648 rows=4 loops=1)

16. 0.007 5.764 ↑ 50.0 4 1

Nested Loop (cost=59.09..484.82 rows=200 width=1,336) (actual time=5.731..5.764 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
17. 0.029 5.717 ↑ 50.0 4 1

Hash Join (cost=58.68..63.22 rows=200 width=233) (actual time=5.709..5.717 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)
18. 5.238 5.238 ↑ 50.0 4 1

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

  • Output: tmp.resource_id, tmp.sub_resource_id, tmp.parent_id
19. 0.167 0.450 ↓ 1.0 303 1

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

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

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

  • Output: ae.resource_name, ae.resource_id
21. 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)
22. 0.025 4.883 ↓ 0.0 0 1

Nested Loop (cost=0.27..455.49 rows=1 width=1,336) (actual time=4.883..4.883 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
23. 4.822 4.822 ↑ 5.8 4 1

Seq Scan on iot.subscription s_1 (cost=0.00..412.51 rows=23 width=1,321) (actual time=3.057..4.822 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
24. 0.036 0.036 ↓ 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.009..0.009 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)
Planning time : 1.280 ms
Execution time : 10.944 ms