explain.depesz.com

PostgreSQL's explain analyze made readable

Result: F7QSh

Settings
# exclusive inclusive rows x rows loops node
1. 0.822 9,312.315 ↑ 1.0 1 1

Aggregate (cost=58.70..58.71 rows=1 width=8) (actual time=9,312.314..9,312.315 rows=1 loops=1)

  • Output: count(sku0_.id)
  • Buffers: shared hit=5582904
2. 56.005 9,311.493 ↓ 3,285.0 3,285 1

Nested Loop (cost=3.62..58.70 rows=1 width=41) (actual time=28.628..9,311.493 rows=3,285 loops=1)

  • Output: sku0_.id
  • Buffers: shared hit=5582904
3. 4.438 9,236.990 ↓ 18,498.0 18,498 1

Nested Loop (cost=3.49..58.10 rows=1 width=53) (actual time=28.574..9,236.990 rows=18,498 loops=1)

  • Output: sku0_.id, networkpro7_.network_id
  • Buffers: shared hit=5576331
4. 2.857 9,164.716 ↓ 5,653.0 5,653 1

Nested Loop (cost=2.93..56.51 rows=1 width=41) (actual time=28.545..9,164.716 rows=5,653 loops=1)

  • Output: sku0_.id
  • Join Filter: ((sku0_.practitioner_id)::text = (practition1_.id)::text)
  • Buffers: shared hit=5535119
5. 6.692 9,110.982 ↓ 5,653.0 5,653 1

Nested Loop (cost=2.38..55.92 rows=1 width=164) (actual time=28.508..9,110.982 rows=5,653 loops=1)

  • Output: sku0_.id, sku0_.practitioner_id, specialtie2_.practitioner_id, practition4_.practitioner_id
  • Join Filter: ((specialtie2_.practitioner_id)::text = (sku0_.practitioner_id)::text)
  • Buffers: shared hit=5506764
6. 156.619 9,057.626 ↓ 5,833.0 5,833 1

Nested Loop (cost=1.95..55.44 rows=1 width=82) (actual time=28.465..9,057.626 rows=5,833 loops=1)

  • Output: specialtie2_.practitioner_id, practition4_.practitioner_id
  • Join Filter: ((specialtie2_.specialty_id)::text = (specialty3_.id)::text)
  • Rows Removed by Join Filter: 1164580
  • Buffers: shared hit=5483528
7. 0.067 0.067 ↑ 1.0 1 1

Seq Scan on public.specialty specialty3_ (cost=0.00..5.40 rows=1 width=41) (actual time=0.025..0.067 rows=1 loops=1)

  • Output: specialty3_.id, specialty3_.type, specialty3_.code, specialty3_.description, specialty3_.version, specialty3_.created_at, specialty3_.created_by, specialty3_.updated_at, specialty3_.updated_by
  • Filter: ((specialty3_.code)::text = 'SP0032'::text)
  • Rows Removed by Filter: 191
  • Buffers: shared hit=3
8. 757.562 8,900.940 ↓ 45,015.9 1,170,413 1

Nested Loop (cost=1.95..49.72 rows=26 width=123) (actual time=0.748..8,900.940 rows=1,170,413 loops=1)

  • Output: specialtie2_.practitioner_id, specialtie2_.specialty_id, practition4_.practitioner_id
  • Buffers: shared hit=5483525
9. 198.856 1,752.202 ↓ 53,259.8 798,897 1

Nested Loop (cost=1.40..40.41 rows=15 width=41) (actual time=0.727..1,752.202 rows=798,897 loops=1)

  • Output: practition4_.practitioner_id
  • Buffers: shared hit=1101095
10. 7.779 380.090 ↓ 41,902.0 41,902 1

Nested Loop (cost=0.84..33.26 rows=1 width=41) (actual time=0.707..380.090 rows=41,902 loops=1)

  • Output: location5_.id
  • Buffers: shared hit=119676
11. 89.613 89.613 ↓ 21,746.0 21,746 1

Index Scan using ix_address_point_gix2 on public.address address6_ (cost=0.41..8.81 rows=1 width=15) (actual time=0.658..89.613 rows=21,746 loops=1)

  • Output: address6_.id, address6_.city, address6_.state_code, address6_.line_1, address6_.line_2, address6_.type, address6_.zip_code, address6_.county, address6_.zip_plus, address6_.country, address6_.geocode_status, address6_.latitude, address6_.longitude, address6_.original_latitude, address6_.original_longitude, address6_.point, address6_.admin_changed, address6_.version, address6_.created_at, address6_.created_by, address6_.updated_at, address6_.updated_by
  • Index Cond: (address6_.point && '0101000020E6100000000000E0A35057C000000000297C4640'::geography)
  • Filter: (((address6_.state_code)::text = 'MN'::text) AND ('0101000020E6100000000000E0A35057C000000000297C4640'::geography && _st_expand(address6_.point, '80467.1999999999971'::double precision)) AND _st_dwithin(address6_.point, '0101000020E6100000000000E0A35057C000000000297C4640'::geography, '80467.1999999999971'::double precision, true))
  • Rows Removed by Filter: 3291
  • Buffers: shared hit=11948
12. 282.698 282.698 ↑ 2.5 2 21,746

Index Scan using ix_location__address_id on public.location location5_ (cost=0.43..24.40 rows=5 width=55) (actual time=0.011..0.013 rows=2 loops=21,746)

  • Output: location5_.id, location5_.address_id, location5_.name, location5_.display_name, location5_.website, location5_.location_grouped_id, location5_.admin_changed, location5_.version, location5_.created_at, location5_.created_by, location5_.updated_at, location5_.updated_by
  • Index Cond: ((location5_.address_id)::text = (address6_.id)::text)
  • Buffers: shared hit=107728
13. 1,173.256 1,173.256 ↑ 9.4 19 41,902

Index Scan using ix_practitioner_location_network__location_id on public.practitioner_location_network practition4_ (cost=0.56..5.36 rows=179 width=82) (actual time=0.011..0.028 rows=19 loops=41,902)

  • Output: practition4_.id, practition4_.practitioner_id, practition4_.location_id, practition4_.network_id, practition4_.effective_date, practition4_.term_date, practition4_.display, practition4_.display_reason_code, practition4_.version, practition4_.created_at, practition4_.created_by, practition4_.updated_at, practition4_.updated_by
  • Index Cond: ((practition4_.location_id)::text = (location5_.id)::text)
  • Buffers: shared hit=981419
14. 6,391.176 6,391.176 ↑ 2.0 1 798,897

Index Scan using ix_practitioner_specialty__practitioner_id on public.practitioner_specialty specialtie2_ (cost=0.55..0.60 rows=2 width=82) (actual time=0.008..0.008 rows=1 loops=798,897)

  • Output: specialtie2_.id, specialtie2_.practitioner_id, specialtie2_.specialty_id, specialtie2_."primary", specialtie2_.version, specialtie2_.created_at, specialtie2_.created_by, specialtie2_.updated_at, specialtie2_.updated_by
  • Index Cond: ((specialtie2_.practitioner_id)::text = (practition4_.practitioner_id)::text)
  • Buffers: shared hit=4382430
15. 46.664 46.664 ↑ 1.0 1 5,833

Index Scan using ix_sku__practitioner_id on public.sku sku0_ (cost=0.43..0.46 rows=1 width=82) (actual time=0.008..0.008 rows=1 loops=5,833)

  • Output: sku0_.id, sku0_.location_id, sku0_.pos_type, sku0_.treatment_type_code, sku0_.episode_count, sku0_.effective_date, sku0_.term_date, sku0_.practitioner_id, sku0_.version, sku0_.created_at, sku0_.created_by, sku0_.updated_at, sku0_.updated_by, sku0_.admin_changed
  • Index Cond: ((sku0_.practitioner_id)::text = (practition4_.practitioner_id)::text)
  • Filter: ((sku0_.effective_date <= '2019-09-09'::date) AND (sku0_.term_date >= '2019-09-09'::date) AND ((sku0_.treatment_type_code)::text = 'Z005100'::text))
  • Buffers: shared hit=23236
16. 50.877 50.877 ↑ 1.0 1 5,653

Index Only Scan using ix_practitioner__id on public.practitioner practition1_ (cost=0.55..0.58 rows=1 width=41) (actual time=0.008..0.009 rows=1 loops=5,653)

  • Output: practition1_.id
  • Index Cond: (practition1_.id = (practition4_.practitioner_id)::text)
  • Heap Fetches: 5653
  • Buffers: shared hit=28355
17. 67.836 67.836 ↑ 1.3 3 5,653

Index Scan using ix_network_provider__sku_id on public.network_provider networkpro7_ (cost=0.56..1.55 rows=4 width=53) (actual time=0.010..0.012 rows=3 loops=5,653)

  • Output: networkpro7_.id, networkpro7_.sku_id, networkpro7_.network_id, networkpro7_.effective_date, networkpro7_.term_date, networkpro7_.version, networkpro7_.created_at, networkpro7_.created_by, networkpro7_.updated_at, networkpro7_.updated_by, networkpro7_.admin_changed
  • Index Cond: ((networkpro7_.sku_id)::text = (sku0_.id)::text)
  • Filter: ((networkpro7_.effective_date <= '2019-09-09'::date) AND (networkpro7_.term_date >= '2019-09-09'::date))
  • Buffers: shared hit=41212
18. 18.498 18.498 ↓ 0.0 0 18,498

Index Only Scan using ix_network__id on public.network network8_ (cost=0.14..0.59 rows=1 width=15) (actual time=0.001..0.001 rows=0 loops=18,498)

  • Output: network8_.id
  • Index Cond: ((network8_.id = (networkpro7_.network_id)::text) AND (network8_.id = ANY ('{000000000,000000003,000000004,Pharmacy_NVBND}'::text[])))
  • Heap Fetches: 3285
  • Buffers: shared hit=6570
Planning time : 24.415 ms
Execution time : 9,312.668 ms