explain.depesz.com

PostgreSQL's explain analyze made readable

Result: KAHl

Settings

Optimization(s) for this plan:

# exclusive inclusive rows x rows loops node
1. 0.096 19.723 ↑ 1.9 18 1

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

  • Hash Cond: ((sub.package_identity)::text = (package.identity)::text)
  • Buffers: shared hit=4437 read=133 dirtied=16, local hit=110 read=3 dirtied=3
2. 0.027 19.604 ↑ 1.4 18 1

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

  • Hash Cond: (child.sid = org.sid)
  • Buffers: shared hit=4420 read=133 dirtied=16, local hit=110 read=3 dirtied=3
3. 17.470 17.470 ↑ 55.6 18 1

Function Scan on common_entities_fn_get_child_organizations child (cost=0.25..30.25 rows=1,000 width=8) (actual time=17.467..17.470 rows=18 loops=1)

  • Buffers: shared hit=4182 read=70 dirtied=16, local hit=110 read=3 dirtied=3
4. 0.014 2.107 ↓ 1.4 18 1

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

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

Nested Loop (cost=4.44..154.37 rows=13 width=82) (actual time=1.780..2.093 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.042 1.087 ↓ 1.5 204 1

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

  • Buffers: shared hit=223 read=61
7. 0.015 0.043 ↓ 1.5 3 1

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

  • Hash Cond: ((pta.application_identity)::text = (app.identity)::text)
  • Buffers: shared hit=2
8. 0.016 0.016 ↓ 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.008..0.016 rows=47 loops=1)

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

Hash (cost=1.72..1.72 rows=1 width=37) (actual time=0.012..0.012 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. 1.002 1.002 ↑ 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.050..0.334 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.350 0.408 ↑ 1.0 18 204

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

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

Bitmap Heap Scan on common_entities_organization org (cost=2.41..23.03 rows=18 width=65) (actual time=0.043..0.058 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.036 0.036 ↑ 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.036..0.036 rows=18 loops=1)

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

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

  • Buckets: 1024 Batches: 1 Memory Usage: 10kB
  • Buffers: shared hit=1
16. 0.009 0.009 ↓ 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.009 rows=16 loops=1)

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