explain.depesz.com

PostgreSQL's explain analyze made readable

Result: GrYv

Settings
# exclusive inclusive rows x rows loops node
1. 0.018 28,250.718 ↓ 32.0 32 1

GroupAggregate (cost=905.58..905.61 rows=1 width=556) (actual time=28,250.706..28,250.718 rows=32 loops=1)

  • Group Key: (COALESCE(l2.bind_location_id, l2.id)), s2.pos_type
2. 0.100 28,250.700 ↓ 32.0 32 1

Sort (cost=905.58..905.59 rows=1 width=565) (actual time=28,250.698..28,250.700 rows=32 loops=1)

  • Sort Key: (COALESCE(l2.bind_location_id, l2.id)), s2.pos_type
  • Sort Method: quicksort Memory: 29kB
3. 0.119 28,250.600 ↓ 32.0 32 1

Nested Loop Left Join (cost=347.76..905.57 rows=1 width=565) (actual time=1,356.998..28,250.600 rows=32 loops=1)

4. 0.133 28,250.161 ↓ 32.0 32 1

Nested Loop Semi Join (cost=347.34..905.12 rows=1 width=127) (actual time=1,356.964..28,250.161 rows=32 loops=1)

5. 0.326 28,249.401 ↓ 33.0 33 1

Nested Loop Semi Join (cost=346.78..878.04 rows=1 width=127) (actual time=1,356.933..28,249.401 rows=33 loops=1)

  • Join Filter: ((s2.location_id)::text = (lon.location_id)::text)
6. 56.097 28,246.870 ↓ 105.0 105 1

Nested Loop (cost=346.22..876.22 rows=1 width=168) (actual time=237.063..28,246.870 rows=105 loops=1)

7. 33.781 570.418 ↓ 28,592.5 57,185 1

Nested Loop (cost=0.84..33.36 rows=2 width=78) (actual time=0.587..570.418 rows=57,185 loops=1)

8. 151.616 151.616 ↓ 29,617.0 29,617 1

Index Scan using ix_address_point on address a2 (cost=0.41..8.81 rows=1 width=15) (actual time=0.575..151.616 rows=29,617 loops=1)

  • Index Cond: (point && '0101000020E6100000000000E0A35057C000000000297C4640'::geography)
  • Filter: (('0101000020E6100000000000E0A35057C000000000297C4640'::geography && _st_expand(point, '160934.399999999994'::double precision)) AND _st_dwithin(point, '0101000020E6100000000000E0A35057C000000000297C4640'::geography, '160934.399999999994'::double precision, true))
  • Rows Removed by Filter: 4333
9. 385.021 385.021 ↑ 2.5 2 29,617

Index Scan using ix_location_new_20190619_1__address_id on location l2 (cost=0.43..24.51 rows=5 width=93) (actual time=0.010..0.013 rows=2 loops=29,617)

  • Index Cond: ((address_id)::text = (a2.id)::text)
10. 48.945 27,620.355 ↓ 0.0 0 57,185

Bitmap Heap Scan on sku s2 (cost=345.38..421.42 rows=1 width=90) (actual time=0.483..0.483 rows=0 loops=57,185)

  • Recheck Cond: (((location_id)::text = (l2.id)::text) AND ((treatment_type_code)::text = 'M001400'::text))
  • Filter: ((effective_date <= ('2020-07-31'::cstring)::date) AND (('2020-05-07'::cstring)::date <= term_date) AND ((SubPlan 1) OR (alternatives: SubPlan 2 or hashed SubPlan 3)))
  • Rows Removed by Filter: 0
  • Heap Blocks: exact=583
11. 1,077.690 27,563.170 ↓ 0.0 0 57,185

BitmapAnd (cost=345.38..345.38 rows=1 width=0) (actual time=0.482..0.482 rows=0 loops=57,185)

12. 514.665 514.665 ↑ 26.0 1 57,185

Bitmap Index Scan on ix_sku_new_20190619_1__location_id (cost=0.00..0.79 rows=26 width=0) (actual time=0.009..0.009 rows=1 loops=57,185)

  • Index Cond: ((location_id)::text = (l2.id)::text)
13. 25,970.815 25,970.815 ↑ 1.1 14,822 16,081

Bitmap Index Scan on ix_sku_new_20190619_1__treatment_type_code (cost=0.00..342.07 rows=16,219 width=0) (actual time=1.615..1.615 rows=14,822 loops=16,081)

  • Index Cond: ((treatment_type_code)::text = 'M001400'::text)
14.          

SubPlan (for Bitmap Heap Scan)

15. 0.772 4.632 ↑ 1.0 1 193

Nested Loop (cost=0.70..9.88 rows=1 width=0) (actual time=0.024..0.024 rows=1 loops=193)

16. 0.579 0.579 ↑ 1.0 1 193

Seq Scan on location_price_factor_pos_type_mapping lpfm (cost=0.00..1.15 rows=1 width=32) (actual time=0.003..0.003 rows=1 loops=193)

  • Filter: (pos_type_source = (s2.pos_type)::text)
  • Rows Removed by Filter: 6
17. 3.281 3.281 ↑ 1.0 1 193

Index Only Scan using lpf_new_20190909_pkey on location_price_factor lpf (cost=0.70..8.72 rows=1 width=19) (actual time=0.017..0.017 rows=1 loops=193)

  • Index Cond: ((location_id = (s2.location_id)::text) AND (pos_type = lpfm.pos_type_destination) AND (treatment_type_code = (s2.treatment_type_code)::text))
  • Heap Fetches: 105
18. 2.728 3.608 ↓ 0.0 0 88

Bitmap Heap Scan on pos_price_factor ppf (cost=4.60..62.13 rows=1 width=0) (actual time=0.041..0.041 rows=0 loops=88)

  • Recheck Cond: ((treatment_type_code)::text = (s2.treatment_type_code)::text)
  • Filter: (((pos_type)::text = (s2.pos_type)::text) AND ((''::text = ANY ('{000000001,Pharmacy_NVBND,000000003}'::text[])) OR ((network_id)::text = ANY ('{000000001,Pharmacy_NVBND,000000003}'::text[]))))
  • Rows Removed by Filter: 44
  • Heap Blocks: exact=1408
19. 0.880 0.880 ↓ 1.0 44 88

Bitmap Index Scan on ix_pos_price_factor_new_20190619_1__treatment_type_code (cost=0.00..4.60 rows=42 width=0) (actual time=0.010..0.010 rows=44 loops=88)

  • Index Cond: ((treatment_type_code)::text = (s2.treatment_type_code)::text)
20. 0.000 0.000 ↓ 0.0 0

Seq Scan on pos_price_factor ppf_1 (cost=0.00..104.06 rows=216 width=64) (never executed)

  • Filter: ((''::text = ANY ('{000000001,Pharmacy_NVBND,000000003}'::text[])) OR ((network_id)::text = ANY ('{000000001,Pharmacy_NVBND,000000003}'::text[])))
21. 2.205 2.205 ↓ 0.0 0 105

Index Scan using ix_location_organization_network_new_20190619_1__location_id on location_organization_network lon (cost=0.56..1.81 rows=1 width=41) (actual time=0.021..0.021 rows=0 loops=105)

  • Index Cond: ((location_id)::text = (l2.id)::text)
  • Filter: ((effective_date <= ('2020-07-31'::cstring)::date) AND (('2020-05-07'::cstring)::date <= term_date) AND ((''::text = ANY ('{000000001,Pharmacy_NVBND,000000003}'::text[])) OR ((network_id)::text = ANY ('{000000001,Pharmacy_NVBND,000000003}'::text[]))))
  • Rows Removed by Filter: 3
22. 0.627 0.627 ↑ 1.0 1 33

Index Scan using ix_network_provider_new_20190619_1__sku_id on network_provider np (cost=0.56..27.07 rows=1 width=41) (actual time=0.019..0.019 rows=1 loops=33)

  • Index Cond: ((sku_id)::text = (s2.id)::text)
  • Filter: ((effective_date <= ('2020-07-31'::cstring)::date) AND (('2020-05-07'::cstring)::date <= term_date) AND ((''::text = ANY ('{000000001,Pharmacy_NVBND,000000003}'::text[])) OR ((network_id)::text = ANY ('{000000001,Pharmacy_NVBND,000000003}'::text[]))))
  • Rows Removed by Filter: 2
23. 0.320 0.320 ↑ 1.0 1 32

Index Only Scan using ix_bind_location__id on bind_location bl (cost=0.42..0.44 rows=1 width=37) (actual time=0.010..0.010 rows=1 loops=32)

  • Index Cond: (id = (l2.bind_location_id)::text)
  • Heap Fetches: 0
Planning time : 14.283 ms
Execution time : 28,250.927 ms