explain.depesz.com

PostgreSQL's explain analyze made readable

Result: 1bF8 : Optimization for: plan #KAHl

Settings

Optimization path:

# exclusive inclusive rows x rows loops node
1. 0.073 5.036 ↑ 1.9 18 1

Hash Join (cost=156.98..191.64 rows=35 width=8) (actual time=5.023..5.036 rows=18 loops=1)

  • Hash Cond: ((sub.package_identity)::text = (package.identity)::text)
  • Buffers: shared hit=565 read=114
2. 0.032 4.947 ↑ 1.4 18 1

Hash Join (cost=155.04..189.57 rows=26 width=82) (actual time=4.940..4.947 rows=18 loops=1)

  • Hash Cond: (child.sid = org.sid)
  • Buffers: shared hit=548 read=114
3. 2.924 2.924 ↑ 55.6 18 1

Function Scan on fn_org_get_organization_and_all_child_organizations_by_sid child (cost=0.25..30.25 rows=1,000 width=8) (actual time=2.922..2.924 rows=18 loops=1)

  • Buffers: shared hit=310 read=51
4. 0.007 1.991 ↓ 1.4 18 1

Hash (cost=154.37..154.37 rows=13 width=82) (actual time=1.991..1.991 rows=18 loops=1)

  • Buckets: 1024 Batches: 1 Memory Usage: 11kB
  • Buffers: shared hit=235 read=63
5. 0.664 1.984 ↓ 1.4 18 1

Nested Loop (cost=4.44..154.37 rows=13 width=82) (actual time=1.657..1.984 rows=18 loops=1)

  • Join Filter: (((org.identity)::text = (sub.organization_identity)::text) OR ((org.billing_organization_identity)::text = (sub.organization_identity)::text))
  • Rows Removed by Join Filter: 3654
  • Buffers: shared hit=235 read=63
6. 0.037 0.912 ↓ 1.5 204 1

Nested Loop (cost=2.02..40.84 rows=134 width=111) (actual time=0.065..0.912 rows=204 loops=1)

  • Buffers: shared hit=223 read=61
7. 0.016 0.038 ↓ 1.5 3 1

Hash Join (cost=1.75..4.31 rows=2 width=37) (actual time=0.020..0.038 rows=3 loops=1)

  • Hash Cond: ((pta.application_identity)::text = (app.identity)::text)
  • Buffers: shared hit=2
8. 0.013 0.013 ↓ 1.0 47 1

Seq Scan on meta_data_package_to_application pta (cost=0.00..2.38 rows=46 width=74) (actual time=0.006..0.013 rows=47 loops=1)

  • Filter: (NOT mark_for_delete)
  • Buffers: shared hit=1
9. 0.001 0.009 ↑ 1.0 1 1

Hash (cost=1.72..1.72 rows=1 width=37) (actual time=0.009..0.009 rows=1 loops=1)

  • Buckets: 1024 Batches: 1 Memory Usage: 9kB
  • Buffers: shared hit=1
10. 0.008 0.008 ↑ 1.0 1 1

Seq Scan on meta_data_application app (cost=0.00..1.72 rows=1 width=37) (actual time=0.004..0.008 rows=1 loops=1)

  • Filter: ((NOT mark_for_delete) AND ((name)::text ~~ '%HOS%'::text))
  • Rows Removed by Filter: 22
  • Buffers: shared hit=1
11. 0.837 0.837 ↑ 1.3 68 3

Index Scan using ix_subscription_subscription_01 on subscription_subscription sub (cost=0.28..15.53 rows=91 width=74) (actual time=0.048..0.279 rows=68 loops=3)

  • Index Cond: ((package_identity)::text = (pta.package_identity)::text)
  • Filter: (NOT mark_for_delete)
  • Buffers: shared hit=221 read=61
12. 0.294 0.408 ↑ 1.0 18 204

Materialize (cost=2.41..23.12 rows=18 width=65) (actual time=0.001..0.002 rows=18 loops=204)

  • Buffers: shared hit=12 read=2
13. 0.022 0.114 ↑ 1.0 18 1

Bitmap Heap Scan on common_entities_organization org (cost=2.41..23.03 rows=18 width=65) (actual time=0.100..0.114 rows=18 loops=1)

  • Recheck Cond: ((company_identity)::text = 'fefafda1-ccd7-4fa0-b3a2-283fbae275aa'::text)
  • Filter: (NOT mark_for_delete)
  • Heap Blocks: exact=12
  • Buffers: shared hit=12 read=2
14. 0.092 0.092 ↑ 1.0 18 1

Bitmap Index Scan on ix_common_entities_organization_01 (cost=0.00..2.41 rows=18 width=0) (actual time=0.092..0.092 rows=18 loops=1)

  • Index Cond: ((company_identity)::text = 'fefafda1-ccd7-4fa0-b3a2-283fbae275aa'::text)
  • Buffers: shared read=2
15. 0.005 0.016 ↓ 1.1 16 1

Hash (cost=1.45..1.45 rows=15 width=37) (actual time=0.016..0.016 rows=16 loops=1)

  • Buckets: 1024 Batches: 1 Memory Usage: 10kB
  • Buffers: shared hit=1
16. 0.011 0.011 ↓ 1.1 16 1

Seq Scan on meta_data_package package (cost=0.00..1.45 rows=15 width=37) (actual time=0.006..0.011 rows=16 loops=1)

  • Filter: (NOT mark_for_delete)
  • Buffers: shared hit=1
Planning time : 5.787 ms
Execution time : 5.300 ms