explain.depesz.com

PostgreSQL's explain analyze made readable

Result: saUj

Settings
# exclusive inclusive rows x rows loops node
1. 0.100 30,446.893 ↓ 3.0 3 1

Nested Loop (cost=310,148.20..310,175.07 rows=1 width=312) (actual time=11,945.479..30,446.893 rows=3 loops=1)

  • Output: (count(1)), (sum(CASE WHEN (((sum((((((vc_1.actual_amount - vc_1.package_amount) + vc_1.account_amount) - vc_1.credits_amount) - vc_1.gift_amount) - vc_1.coupon_amount))) >= '50'::numeric) AND ((sum((((((vc_1.actual_amount - vc_1.package_amount) + vc_1.account_amount) - vc_1.credits_amount) - vc_1.gift_amount) - vc_1.coupon_amount))) IS NOT NULL)) THEN 1 ELSE 0 END)), (sum(CASE WHEN (((sum((((((vc_1.actual_amount - vc_1.package_amount) + vc_1.account_amount) - vc_1.credits_amount) - vc_1.gift_amount) - vc_1.coupon_amount))) < '50'::numeric) OR ((sum((((((vc_1.actual_amount - vc_1.package_amount) + vc_1.account_amount) - vc_1.credits_amount) - vc_1.gift_amount) - vc_1.coupon_amount))) IS NULL)) THEN 1 ELSE 0 END)), CASE WHEN ((count(1)) = 0) THEN '0.00%'::text ELSE ((round(((((sum(CASE WHEN (((sum((((((vc_1.actual_amount - vc_1.package_amount) + vc_1.account_amount) - vc_1.credits_amount) - vc_1.gift_amount) - vc_1.coupon_amount))) >= '50'::numeric) AND ((sum((((((vc_1.actual_amount - vc_1.package_amount) + vc_1.account_amount) - vc_1.credits_amount) - vc_1.gift_amount) - vc_1.coupon_amount))) IS NOT NULL)) THEN 1 ELSE 0 END)))::numeric / ((count(1)))::numeric) * '100'::numeric), 2))::text || '%'::text) END, (to_char(v_2.visit_datetime, 'yyyy-MM'::text)), (sum(CASE WHEN (((CASE WHEN (hashed SubPlan 3) THEN '北京'::text WHEN (hashed SubPlan 4) THEN '上海'::text WHEN (pc_1.ptf_city_id = 440,100) THEN '广州'::text WHEN (pc_1.ptf_city_id = 440,300) THEN '深圳'::text ELSE '其他'::text END) = '北京'::text) AND ((sum((((((vc_3.actual_amount - vc_3.package_amount) + vc_3.account_amount) - vc_3.credits_amount) - vc_3.gift_amount) - vc_3.coupon_amount))) >= '50'::numeric) AND ((sum((((((vc_3.actual_amount - vc_3.package_amount) + vc_3.account_amount) - vc_3.credits_amount) - vc_3.gift_amount) - vc_3.coupon_amount))) IS NOT NULL)) THEN 1 ELSE 0 END)), (sum(CASE WHEN (((CASE WHEN (hashed SubPlan 3) THEN '北京'::text WHEN (hashed SubPlan 4) THEN '上海'::text WHEN (pc_1.ptf_city_id = 440,100) THEN '广州'::text WHEN (pc_1.ptf_city_id = 440,300) THEN '深圳'::text ELSE '其他'::text END) = '北京'::text) AND (((sum((((((vc_3.actual_amount - vc_3.package_amount) + vc_3.account_amount) - vc_3.credits_amount) - vc_3.gift_amount) - vc_3.coupon_amount))) < '50'::numeric) OR ((sum((((((vc_3.actual_amount - vc_3.package_amount) + vc_3.account_amount) - vc_3.credits_amount) - vc_3.gift_amount) - vc_3.coupon_amount))) IS NULL))) THEN 1 ELSE 0 END)), CASE WHEN ((sum(CASE WHEN ((CASE WHEN (hashed SubPlan 3) THEN '北京'::text WHEN (hashed SubPlan 4) THEN '上海'::text WHEN (pc_1.ptf_city_id = 440,100) THEN '广州'::text WHEN (pc_1.ptf_city_id = 440,300) THEN '深圳'::text ELSE '其他'::text END) = '北京'::text) THEN 1 ELSE 0 END)) = 0) THEN '0.00%'::text ELSE ((round(((((sum(CASE WHEN (((CASE WHEN (hashed SubPlan 3) THEN '北京'::text WHEN (hashed SubPlan 4) THEN '上海'::text WHEN (pc_1.ptf_city_id = 440,100) THEN '广州'::text WHEN (pc_1.ptf_city_id = 440,300) THEN '深圳'::text ELSE '其他'::text END) = '北京'::text) AND ((sum((((((vc_3.actual_amount - vc_3.package_amount) + vc_3.account_amount) - vc_3.credits_amount) - vc_3.gift_amount) - vc_3.coupon_amount))) >= '50'::numeric) AND ((sum((((((vc_3.actual_amount - vc_3.package_amount) + vc_3.account_amount) - vc_3.credits_amount) - vc_3.gift_amount) - vc_3.coupon_amount))) IS NOT NULL)) THEN 1 ELSE 0 END)))::numeric / ((sum(CASE WHEN ((CASE WHEN (hashed SubPlan 3) THEN '北京'::text WHEN (hashed SubPlan 4) THEN '上海'::text WHEN (pc_1.ptf_city_id = 440,100) THEN '广州'::text WHEN (pc_1.ptf_city_id = 440,300) THEN '深圳'::text ELSE '其他'::text END) = '北京'::text) THEN 1 ELSE 0 END)))::numeric) * '100'::numeric), 2))::text || '%'::text) END, (sum(CASE WHEN (((CASE WHEN (hashed SubPlan 3) THEN '北京'::text WHEN (hashed SubPlan 4) THEN '上海'::text WHEN (pc_1.ptf_city_id = 440,100) THEN '广州'::text WHEN (pc_1.ptf_city_id = 440,300) THEN '深圳'::text ELSE '其他'::text END) = '上海'::text) AND ((sum((((((vc_3.actual_amount - vc_3.package_amount) + vc_3.account_amount) - vc_3.credits_amount) - vc_3.gift_amount) - vc_3.coupon_amount))) >= '50'::numeric) AND ((sum((((((vc_3.actual_amount - vc_3.package_amount) + vc_3.account_amount) - vc_3.credits_amount) - vc_3.gift_amount) - vc_3.coupon_amount))) IS NOT NULL)) THEN 1 ELSE 0 END)), (sum(CASE WHEN (((CASE WHEN (hashed SubPlan 3) THEN '北京'::text WHEN (hashed SubPlan 4) THEN '上海'::text WHEN (pc_1.ptf_city_id = 440,100) THEN '广州'::text WHEN (pc_1.ptf_city_id = 440,300) THEN '深圳'::text ELSE '其他'::text END) = '上海'::text) AND (((sum((((((vc_3.actual_amount - vc_3.package_amount) + vc_3.account_amount) - vc_3.credits_amount) - vc_3.gift_amount) - vc_3.coupon_amount))) < '50'::numeric) OR ((sum((((((vc_3.actual_amount - vc_3.package_amount) + vc_3.account_amount) - vc_3.credits_amount) - vc_3.gift_amount) - vc_3.coupon_amount))) IS NULL))) THEN 1 ELSE 0 END)), CASE WHEN ((sum(CASE WHEN ((CASE WHEN (hashed SubPlan 3) THEN '北京'::text WHEN (hashed SubPlan 4) THEN '上海'::text WHEN (pc_1.ptf_city_id = 440,100) THEN '广州'::text WHEN (pc_1.ptf_city_id = 440,300) THEN '深圳'::text ELSE '其他'::text END) = '上海'::text) THEN 1 ELSE 0 END)) = 0) THEN '0.00%'::text ELSE ((round(((((sum(CASE WHEN (((CASE WHEN (hashed SubPlan 3) THEN '北京'::text WHEN (hashed SubPlan 4) THEN '上海'::text WHEN (pc_1.ptf_city_id = 440,100) THEN '广州'::text WHEN (pc_1.ptf_city_id = 440,300) THEN '深圳'::text ELSE '其他'::text END) = '上海'::text) AND ((sum((((((vc_3.actual_amount - vc_3.package_amount) + vc_3.account_amount) - vc_3.credits_amount) - vc_3.gift_amount) - vc_3.coupon_amount))) >= '50'::numeric) AND ((sum((((((vc_3.actual_amount - vc_3.package_amount) + vc_3.account_amount) - vc_3.credits_amount) - vc_3.gift_amount) - vc_3.coupon_amount))) IS NOT NULL)) THEN 1 ELSE 0 END)))::numeric / ((sum(CASE WHEN ((CASE WHEN (hashed SubPlan 3) THEN '北京'::text WHEN (hashed SubPlan 4) THEN '上海'::text WHEN (pc_1.ptf_city_id = 440,100) THEN '广州'::text WHEN (pc_1.ptf_city_id = 440,300) THEN '深圳'::text ELSE '其他'::text END) = '上海'::text) THEN 1 ELSE 0 END)))::numeric) * '100'::numeric), 2))::text || '%'::text) END, (sum(CASE WHEN (((CASE WHEN (hashed SubPlan 3) THEN '北京'::text WHEN (hashed SubPlan 4) THEN '上海'::text WHEN (pc_1.ptf_city_id = 440,100) THEN '广州'::text WHEN (pc_1.ptf_city_id = 440,300) THEN '深圳'::text ELSE '其他'::text END) = '广州'::text) AND ((sum((((((vc_3.actual_amount - vc_3.package_amount) + vc_3.account_amount) - vc_3.credits_amount) - vc_3.gift_amount) - vc_3.coupon_amount))) >= '50'::numeric) AND ((sum((((((vc_3.actual_amount - vc_3.package_amount) + vc_3.account_amount) - vc_3.credits_amount) - vc_3.gift_amount) - vc_3.coupon_amount))) IS NOT NULL)) THEN 1 ELSE 0 END)), (sum(CASE WHEN (((CASE WHEN (hashed SubPlan 3) THEN '北京'::text WHEN (hashed SubPlan 4) THEN '上海'::text WHEN (pc_1.ptf_city_id = 440,100) THEN '广州'::text WHEN (pc_1.ptf_city_id = 440,300) THEN '深圳'::text ELSE '其他'::text END) = '广州'::text) AND (((sum((((((vc_3.actual_amount - vc_3.package_amount) + vc_3.account_amount) - vc_3.credits_amount) - vc_3.gift_amount) - vc_3.coupon_amount))) < '50'::numeric) OR ((sum((((((vc_3.actual_amount - vc_3.package_amount) + vc_3.account_amount) - vc_3.credits_amount) - vc_3.gift_amount) - vc_3.coupon_amount))) IS NULL))) THEN 1 ELSE 0 END)), CASE WHEN ((sum(CASE WHEN ((CASE WHEN (hashed SubPlan 3) THEN '北京'::text WHEN (hashed SubPlan 4) THEN '上海'::text WHEN (pc_1.ptf_city_id = 440,100) THEN '广州'::text WHEN (pc_1.ptf_city_id = 440,300) THEN '深圳'::text ELSE '其他'::text END) = '广州'::text) THEN 1 ELSE 0 END)) = 0) THEN '0.00%'::text ELSE ((round(((((sum(CASE WHEN (((CASE WHEN (hashed SubPlan 3) THEN '北京'::text WHEN (hashed SubPlan 4) THEN '上海'::text WHEN (pc_1.ptf_city_id = 440,100) THEN '广州'::text WHEN (pc_1.ptf_city_id = 440,300) THEN '深圳'::text ELSE '其他'::text END) = '广州'::text) AND ((sum((((((vc_3.actual_amount - vc_3.package_amount) + vc_3.account_amount) - vc_3.credits_amount) - vc_3.gift_amount) - vc_3.coupon_amount))) >= '50'::numeric) AND ((sum((((((vc_3.actual_amount - vc_3.package_amount) + vc_3.account_amount) - vc_3.credits_amount) - vc_3.gift_amount) - vc_3.coupon_amount))) IS NOT NULL)) THEN 1 ELSE 0 END)))::numeric / ((sum(CASE WHEN ((CASE WHEN (hashed SubPlan 3) THEN '北京'::text WHEN (hashed SubPlan 4) THEN '上海'::text WHEN (pc_1.ptf_city_id = 440,100) THEN '广州'::text WHEN (pc_1.ptf_city_id = 440,300) THEN '深圳'::text ELSE '其他'::text END) = '广州'::text) THEN 1 ELSE 0 END)))::numeric) * '100'::numeric), 2))::text || '%'::text) END, (sum(CASE WHEN (((CASE WHEN (hashed SubPlan 3) THEN '北京'::text WHEN (hashed SubPlan 4) THEN '上海'::text WHEN (pc_1.ptf_city_id = 440,100) THEN '广州'::text WHEN (pc_1.ptf_city_id = 440,300) THEN '深圳'::text ELSE '其他'::text END) = '深圳'::text) AND ((sum((((((vc_3.actual_amount - vc_3.package_amount) + vc_3.account_amount) - vc_3.credits_amount) - vc_3.gift_amount) - vc_3.coupon_amount))) >= '50'::numeric) AND ((sum((((((vc_3.actual_amount - vc_3.package_amount) + vc_3.account_amount) - vc_3.credits_amount) - vc_3.gift_amount) - vc_3.coupon_amount))) IS NOT NULL)) THEN 1 ELSE 0 END)), (sum(CASE WHEN (((CASE WHEN (hashed SubPlan 3) THEN '北京'::text WHEN (hashed SubPlan 4) THEN '上海'::text WHEN (pc_1.ptf_city_id = 440,100) THEN '广州'::text WHEN (pc_1.ptf_city_id = 440,300) THEN '深圳'::text ELSE '其他'::text END) = '深圳'::text) AND (((sum((((((vc_3.actual_amount - vc_3.package_amount) + vc_3.account_amount) - vc_3.credits_amount) - vc_3.gift_amount) - vc_3.coupon_amount))) < '50'::numeric) OR ((sum((((((vc_3.actual_amount - vc_3.package_amount) + vc_3.account_amount) - vc_3.credits_amount) - vc_3.gift_amount) - vc_3.coupon_amount))) IS NULL))) THEN 1 ELSE 0 END)), CASE WHEN ((sum(CASE WHEN ((CASE WHEN (hashed SubPlan 3) THEN '北京'::text WHEN (hashed SubPlan 4) THEN '上海'::text WHEN (pc_1.ptf_city_id = 440,100) THEN '广州'::text WHEN (pc_1.ptf_city_id = 440,300) THEN '深圳'::text ELSE '其他'::text END) = '深圳'::text) THEN 1 ELSE 0 END)) = 0) THEN '0.00%'::text ELSE ((round(((((sum(CASE WHEN (((CASE WHEN (hashed SubPlan 3) THEN '北京'::text WHEN (hashed SubPlan 4) THEN '上海'::text WHEN (pc_1.ptf_city_id = 440,100) THEN '广州'::text WHEN (pc_1.ptf_city_id = 440,300) THEN '深圳'::text ELSE '其他'::text END) = '深圳'::text) AND ((sum((((((vc_3.actual_amount - vc_3.package_amount) + vc_3.account_amount) - vc_3.credits_amount) - vc_3.gift_amount) - vc_3.coupon_amount))) >= '50'::numeric) AND ((sum((((((vc_3.actual_amount - vc_3.package_amount) + vc_3.account_amount) - vc_3.credits_amount) - vc_3.gift_amount) - vc_3.coupon_amount))) IS NOT NULL)) THEN 1 ELSE 0 END)))::numeric / ((sum(CASE WHEN ((CASE WHEN (hashed SubPlan 3) THEN '北京'::text WHEN (hashed SubPlan 4) THEN '上海'::text WHEN (pc_1.ptf_city_id = 440,100) THEN '广州'::text WHEN (pc_1.ptf_city_id = 440,300) THEN '深圳'::text ELSE '其他'::text END) = '深圳'::text) THEN 1 ELSE 0 END)))::numeric) * '100'::numeric), 2))::text || '%'::text) END, (to_char(v.visit_datetime, 'yyyy-MM'::text))
  • Inner Unique: true
  • Join Filter: ((to_char(v.visit_datetime, 'yyyy-MM'::text)) = (to_char(v_2.visit_datetime, 'yyyy-MM'::text)))
  • Rows Removed by Join Filter: 3
2. 4.463 15,998.331 ↓ 3.0 3 1

GroupAggregate (cost=155,067.43..155,081.05 rows=1 width=56) (actual time=8,402.103..15,998.331 rows=3 loops=1)

  • Output: (to_char(v.visit_datetime, 'yyyy-MM'::text)), count(1), sum(CASE WHEN (((sum((((((vc_1.actual_amount - vc_1.package_amount) + vc_1.account_amount) - vc_1.credits_amount) - vc_1.gift_amount) - vc_1.coupon_amount))) >= '50'::numeric) AND ((sum((((((vc_1.actual_amount - vc_1.package_amount) + vc_1.account_amount) - vc_1.credits_amount) - vc_1.gift_amount) - vc_1.coupon_amount))) IS NOT NULL)) THEN 1 ELSE 0 END), sum(CASE WHEN (((sum((((((vc_1.actual_amount - vc_1.package_amount) + vc_1.account_amount) - vc_1.credits_amount) - vc_1.gift_amount) - vc_1.coupon_amount))) < '50'::numeric) OR ((sum((((((vc_1.actual_amount - vc_1.package_amount) + vc_1.account_amount) - vc_1.credits_amount) - vc_1.gift_amount) - vc_1.coupon_amount))) IS NULL)) THEN 1 ELSE 0 END)
  • Group Key: (to_char(v.visit_datetime, 'yyyy-MM'::text))
3. 1,547.950 15,993.868 ↓ 1,280.0 1,280 1

Nested Loop Left Join (cost=155,067.43..155,081.02 rows=1 width=64) (actual time=2,751.576..15,993.868 rows=1,280 loops=1)

  • Output: (to_char(v.visit_datetime, 'yyyy-MM'::text)), (sum((((((vc_1.actual_amount - vc_1.package_amount) + vc_1.account_amount) - vc_1.credits_amount) - vc_1.gift_amount) - vc_1.coupon_amount)))
  • Join Filter: (v.cln_visit_id = vc_1.cln_visit_id)
  • Rows Removed by Join Filter: 7,187,236
4. 2.388 1,546.078 ↓ 1,280.0 1,280 1

Group (cost=32,646.45..32,646.46 rows=1 width=48) (actual time=1,542.592..1,546.078 rows=1,280 loops=1)

  • Output: (to_char(v.visit_datetime, 'yyyy-MM'::text)), v.cln_visit_id, v.ptf_clinic_id
  • Group Key: (to_char(v.visit_datetime, 'yyyy-MM'::text)), v.cln_visit_id
5. 3.206 1,543.690 ↓ 1,349.0 1,349 1

Sort (cost=32,646.45..32,646.45 rows=1 width=48) (actual time=1,542.587..1,543.690 rows=1,349 loops=1)

  • Output: (to_char(v.visit_datetime, 'yyyy-MM'::text)), v.cln_visit_id, v.ptf_clinic_id
  • Sort Key: (to_char(v.visit_datetime, 'yyyy-MM'::text)), v.cln_visit_id
  • Sort Method: quicksort Memory: 154kB
6. 6.913 1,540.484 ↓ 1,349.0 1,349 1

Nested Loop (cost=6.76..32,646.44 rows=1 width=48) (actual time=229.818..1,540.484 rows=1,349 loops=1)

  • Output: to_char(v.visit_datetime, 'yyyy-MM'::text), v.cln_visit_id, v.ptf_clinic_id
  • Inner Unique: true
7. 3.798 1,447.364 ↓ 1,119.6 7,837 1

Nested Loop (cost=1.99..32,612.66 rows=7 width=8) (actual time=0.223..1,447.364 rows=7,837 loops=1)

  • Output: vc.cln_visit_id
  • Inner Unique: true
8. 11.108 1,427.890 ↓ 279.9 7,838 1

Nested Loop (cost=1.70..32,603.99 rows=28 width=16) (actual time=0.202..1,427.890 rows=7,838 loops=1)

  • Output: vc.cln_visit_id, isb.cln_itemcat_id
  • Inner Unique: true
9. 193.770 1,408.944 ↓ 279.9 7,838 1

Nested Loop (cost=1.41..32,555.81 rows=28 width=16) (actual time=0.179..1,408.944 rows=7,838 loops=1)

  • Output: vc.cln_visit_id, itm.cln_itemsubcat_id
  • Inner Unique: true
10. 106.383 692.730 ↓ 4.5 261,222 1

Nested Loop (cost=0.99..5,810.43 rows=58,576 width=16) (actual time=0.095..692.730 rows=261,222 loops=1)

  • Output: vc.cln_visit_id, vc.cln_item_id
11. 0.017 0.114 ↓ 3.0 9 1

Nested Loop (cost=0.55..9.19 rows=3 width=8) (actual time=0.052..0.114 rows=9 loops=1)

  • Output: pc.ptf_clinic_id
12. 0.053 0.053 ↑ 1.0 2 1

Index Scan using ndx_unique_cliniccode on public.ptf_topclinic (cost=0.28..4.58 rows=2 width=8) (actual time=0.039..0.053 rows=2 loops=1)

  • Output: ptf_topclinic.ptf_topclinic_id, ptf_topclinic.clinic_code, ptf_topclinic.clinic_desc, ptf_topclinic.clinic_desc_lang1, ptf_topclinic.short_code, ptf_topclinic.ptf_city_id, ptf_topclinic.picture_file_id, ptf_topclinic.logo_file_id, ptf_topclinic.largelogo_file_id, ptf_topclinic.address_detail, ptf_topclinic.intro_main, ptf_topclinic.intro_main_lang1, ptf_topclinic.online_ind, ptf_topclinic.sysmodel_code, ptf_topclinic.remarks, ptf_topclinic.defunct_ind, ptf_topclinic.created_by, ptf_topclinic.created_datetime, ptf_topclinic.last_updated_by, ptf_topclinic.last_updated_datetime, ptf_topclinic.version, ptf_topclinic.offline_datetime, ptf_topclinic.institution_code, ptf_topclinic.source_type, ptf_topclinic.longitude, ptf_topclinic.latitude, ptf_topclinic.phone_no, ptf_topclinic.cln_filegroup_id, ptf_topclinic.byname, ptf_topclinic.last_session_token, ptf_topclinic.last_request_no, ptf_topclinic.clinic_type, ptf_topclinic.psm_id, ptf_topclinic.clinic_state, ptf_topclinic.abnormal_ind, ptf_topclinic.ptf_kbjperson_id, ptf_topclinic.start_datetime, ptf_topclinic.train_datetime, ptf_topclinic.open_datetime, ptf_topclinic.sign_datetime, ptf_topclinic.reset_datetime, ptf_topclinic.close_datetime, ptf_topclinic.abnormal_datetime, ptf_topclinic.old_clinic_status
  • Index Cond: ((ptf_topclinic.clinic_code)::text = ANY ('{xpg001,shzr001}'::text[]))
13. 0.044 0.044 ↓ 4.0 4 2

Index Scan using ndx_clnc_topid on public.ptf_clinic pc (cost=0.28..2.29 rows=1 width=16) (actual time=0.009..0.022 rows=4 loops=2)

  • Output: pc.ptf_clinic_id, pc.ptf_topclinic_id, pc.parent_id, pc.clinic_code, pc.clinic_desc, pc.short_code, pc.clinic_desc_lang1, pc.seq_no, pc.entity_ind, pc.ptf_city_id, pc.address_detail, pc.intro_main, pc.intro_main_lang1, pc.picture_file_id, pc.logo_file_id, pc.largelogo_file_id, pc.online_ind, pc.remarks, pc.defunct_ind, pc.created_by, pc.created_datetime, pc.last_updated_by, pc.last_updated_datetime, pc.version, pc.valid_date, pc.institution_code, pc.source_type, pc.longitude, pc.latitude, pc.phone_no, pc.business_hours, pc.cln_filegroup_id, pc.byname, pc.last_session_token, pc.last_request_no, pc.clinic_type, pc.psm_id, pc.clinic_state, pc.abnormal_ind, pc.ptf_kbjperson_id, pc.start_datetime, pc.train_datetime, pc.open_datetime, pc.sign_datetime, pc.reset_datetime, pc.close_datetime, pc.abnormal_datetime, pc.offline_datetime, pc.old_clinic_status
  • Index Cond: (pc.ptf_topclinic_id = ptf_topclinic.ptf_topclinic_id)
14. 586.233 586.233 ↑ 1.2 29,025 9

Index Scan using ndx_vstcharge_clinicid on public.cln_visitcharge vc (cost=0.43..1,591.95 rows=34,180 width=24) (actual time=0.022..65.137 rows=29,025 loops=9)

  • Output: vc.cln_visitcharge_id, vc.ptf_clinic_id, vc.cln_visit_id, vc.charge_no, vc.charge_status, vc.cln_item_id, vc.order_entered_doc_id, vc.qty, vc.unit, vc.unit_type, vc.price, vc.base_sale_price, vc.charge_amount, vc.discount_amount, vc.discounted_amount, vc.reduce_amount, vc.actual_amount, vc.total_favorable_amount, vc.cln_visitorderentryitem_id, vc.charged_by, vc.charged_datetime, vc.payment_ind, vc.cln_receipt_id, vc.parent_visitcharge_id, vc.executed_ind, vc.data_top_id, vc.created_by, vc.created_datetime, vc.last_updated_by, vc.last_updated_datetime, vc.version, vc.business_type, vc.package_amount, vc.account_amount, vc.credits_amount, vc.cln_visitopscharge_id, vc.source_type, vc.cost_amount, vc.gift_amount, vc.coupon_amount, vc.hear_about_path, vc.cln_hearaboutpath_id, vc.platform, vc.platform_order, vc.last_session_token, vc.last_request_no, vc.discount_rate, vc.coupon_charge_amount, vc.cln_visitchargeprescription_id, vc.seq_no_in_form, vc.cln_patientpost_id
  • Index Cond: (vc.ptf_clinic_id = pc.ptf_clinic_id)
  • Filter: (vc.payment_ind = 'Y'::bpchar)
  • Rows Removed by Filter: 1,358
15. 522.444 522.444 ↓ 0.0 0 261,222

Index Scan using pk_cln_item on public.cln_item itm (cost=0.42..0.46 rows=1 width=16) (actual time=0.002..0.002 rows=0 loops=261,222)

  • Output: itm.cln_item_id, itm.cln_itemsubcat_id, itm.item_code, itm.item_desc, itm.item_desc_lang1, itm.short_code, itm.other_item_desc, itm.other_item_desc_lang1, itm.order_item_ind, itm.charge_item_ind, itm.material_item_ind, itm.specs, itm.specs_lang1, itm.sale_price, itm.adjust_price_ind, itm.min_sale_price, itm.max_sale_price, itm.price_unit, itm.base_dose, itm.dose_unit, itm.openpack_unit, itm.conversion_rate, itm.dosage_form, itm.poisonous_item_ind, itm.skin_test_ind, itm.infusion_ind, itm.notice_lang1, itm.notice, itm.antibiotic_ind, itm.antibiotic_grade, itm.ptf_frequency_id, itm.usage, itm.ptf_item_id, itm.ptf_topclinic_id, itm.custom_ind, itm.remarks, itm.data_top_id, itm.defunct_ind, itm.created_by, itm.created_datetime, itm.last_updated_by, itm.last_updated_datetime, itm.version, itm.factory, itm.approval_no, itm.top_ind, itm.producing_area, itm.exec_mode, itm.exec_records_required, itm.components, itm.efficacy, itm.picture_file_id, itm.entered_ind, itm.review_days, itm.release_hours, itm.warning_cycle, itm.self_ind, itm.self_clinic_id, itm.group_lab_ind, itm.source_type, itm.days, itm.cln_organization_id, itm.cost_price, itm.per_dose, itm.psychotropic_type, itm.bar_code, itm.credits_ind, itm.stop_ind, itm.last_session_token, itm.last_request_no, itm.dose_ceil_type, itm.common_unit_type, itm.herbxitem_desc, itm.herbxitem_doseunit, itm.granule_dose, itm.herbx_shortcode, itm.gifts_ind, itm.common_herb_remarks, itm.exec_notice, itm.tax_rate, itm.special_description, itm.backup_description
  • Index Cond: (itm.cln_item_id = vc.cln_item_id)
  • Filter: (((itm.item_desc)::text ~~ '%抑那通%'::text) OR ((itm.item_desc)::text ~~ '%达菲林%'::text) OR ((itm.item_desc)::text ~~ '%贝恩%'::text) OR ((itm.item_desc)::text ~~ '%伯恩诺康%'::text) OR ((itm.item_desc)::text ~~ '%达必佳%'::text))
  • Rows Removed by Filter: 1
16. 7.838 7.838 ↑ 1.0 1 7,838

Index Scan using pk_cln_itemsubcat on public.cln_itemsubcat isb (cost=0.29..1.72 rows=1 width=16) (actual time=0.001..0.001 rows=1 loops=7,838)

  • Output: isb.cln_itemsubcat_id, isb.cln_itemcat_id, isb.itemsubcat_code, isb.itemsubcat_desc, isb.itemsubcat_desc_lang1, isb.short_code, isb.ptf_itemsubcat_id, isb.custom_ind, isb.seq_no, isb.remarks, isb.data_top_id, isb.defunct_ind, isb.created_by, isb.created_datetime, isb.last_updated_by, isb.last_updated_datetime, isb.version, isb.source_type, isb.code_rule, isb.last_session_token, isb.last_request_no
  • Index Cond: (isb.cln_itemsubcat_id = itm.cln_itemsubcat_id)
17. 15.676 15.676 ↑ 1.0 1 7,838

Index Scan using pk_cln_itemcat on public.cln_itemcat imc (cost=0.29..0.31 rows=1 width=8) (actual time=0.002..0.002 rows=1 loops=7,838)

  • Output: imc.cln_itemcat_id
  • Index Cond: (imc.cln_itemcat_id = isb.cln_itemcat_id)
  • Filter: (imc.medicine_ind = 'Y'::bpchar)
  • Rows Removed by Filter: 0
18. 86.164 86.207 ↓ 0.0 0 7,837

Index Scan using pk_cln_visit on public.cln_visit v (cost=4.77..4.83 rows=1 width=24) (actual time=0.011..0.011 rows=0 loops=7,837)

  • Output: v.visit_datetime, v.cln_visit_id, v.ptf_clinic_id
  • Index Cond: (v.cln_visit_id = vc.cln_visit_id)
  • Filter: (((v.visit_status)::text >= 'VST3'::text) AND (NOT (hashed SubPlan 1)) AND (v.visit_datetime >= to_timestamp(concat(to_date('2020-07-01'::text, 'yyyy-MM-dd'::text), ' 00:00:00'), 'yyyy-MM-dd HH24:MI:SS'::text)) AND (v.visit_datetime <= (to_timestamp(concat(to_date('2020-09-10'::text, 'yyyy-MM-dd'::text), ' 00:00:00'), 'yyyy-MM-dd HH24:MI:SS'::text) + '1 day'::interval)))
  • Rows Removed by Filter: 1
19.          

SubPlan (for Index Scan)

20. 0.043 0.043 ↓ 1.7 5 1

Index Scan using ndx_pcode_cat on public.ptf_codemstr (cost=0.28..4.33 rows=3 width=32) (actual time=0.028..0.043 rows=5 loops=1)

  • Output: ((ptf_codemstr.parent_code_cat)::text || (ptf_codemstr.code_abbr)::text)
  • Index Cond: ((ptf_codemstr.code_cat)::text = 'VTN'::text)
21. 10,010.880 12,899.840 ↓ 23.8 5,616 1,280

GroupAggregate (cost=122,420.99..122,429.25 rows=236 width=48) (actual time=0.947..10.078 rows=5,616 loops=1,280)

  • Output: vc_1.cln_visit_id, vc_1.ptf_clinic_id, sum((((((vc_1.actual_amount - vc_1.package_amount) + vc_1.account_amount) - vc_1.credits_amount) - vc_1.gift_amount) - vc_1.coupon_amount))
  • Group Key: vc_1.cln_visit_id, vc_1.ptf_clinic_id
22. 1,684.116 2,888.960 ↓ 24.6 5,794 1,280

Sort (cost=122,420.99..122,421.58 rows=236 width=36) (actual time=0.945..2.257 rows=5,794 loops=1,280)

  • Output: vc_1.cln_visit_id, vc_1.ptf_clinic_id, vc_1.actual_amount, vc_1.package_amount, vc_1.account_amount, vc_1.credits_amount, vc_1.gift_amount, vc_1.coupon_amount
  • Sort Key: vc_1.cln_visit_id, vc_1.ptf_clinic_id
  • Sort Method: quicksort Memory: 645kB
23. 68.796 1,204.844 ↓ 24.6 5,794 1

Nested Loop (cost=19,900.17..122,411.68 rows=236 width=36) (actual time=207.727..1,204.844 rows=5,794 loops=1)

  • Output: vc_1.cln_visit_id, vc_1.ptf_clinic_id, vc_1.actual_amount, vc_1.package_amount, vc_1.account_amount, vc_1.credits_amount, vc_1.gift_amount, vc_1.coupon_amount
  • Inner Unique: true
24. 94.742 698.216 ↓ 21.0 72,972 1

Hash Join (cost=19,895.40..105,667.60 rows=3,470 width=36) (actual time=82.330..698.216 rows=72,972 loops=1)

  • Output: vc_1.cln_visit_id, vc_1.ptf_clinic_id, vc_1.actual_amount, vc_1.package_amount, vc_1.account_amount, vc_1.credits_amount, vc_1.gift_amount, vc_1.coupon_amount
  • Hash Cond: (vc_1.cln_item_id = itm_1.cln_item_id)
25. 150.803 521.551 ↓ 4.5 261,222 1

Nested Loop (cost=0.84..85,518.68 rows=58,576 width=44) (actual time=0.067..521.551 rows=261,222 loops=1)

  • Output: vc_1.cln_visit_id, vc_1.ptf_clinic_id, vc_1.actual_amount, vc_1.package_amount, vc_1.account_amount, vc_1.credits_amount, vc_1.gift_amount, vc_1.coupon_amount, vc_1.cln_item_id
26. 0.066 0.066 ↑ 1.0 2 1

Index Scan using ndx_unique_cliniccode on public.ptf_topclinic ptf_topclinic_1 (cost=0.28..4.58 rows=2 width=8) (actual time=0.030..0.066 rows=2 loops=1)

  • Output: ptf_topclinic_1.ptf_topclinic_id, ptf_topclinic_1.clinic_code, ptf_topclinic_1.clinic_desc, ptf_topclinic_1.clinic_desc_lang1, ptf_topclinic_1.short_code, ptf_topclinic_1.ptf_city_id, ptf_topclinic_1.picture_file_id, ptf_topclinic_1.logo_file_id, ptf_topclinic_1.largelogo_file_id, ptf_topclinic_1.address_detail, ptf_topclinic_1.intro_main, ptf_topclinic_1.intro_main_lang1, ptf_topclinic_1.online_ind, ptf_topclinic_1.sysmodel_code, ptf_topclinic_1.remarks, ptf_topclinic_1.defunct_ind, ptf_topclinic_1.created_by, ptf_topclinic_1.created_datetime, ptf_topclinic_1.last_updated_by, ptf_topclinic_1.last_updated_datetime, ptf_topclinic_1.version, ptf_topclinic_1.offline_datetime, ptf_topclinic_1.institution_code, ptf_topclinic_1.source_type, ptf_topclinic_1.longitude, ptf_topclinic_1.latitude, ptf_topclinic_1.phone_no, ptf_topclinic_1.cln_filegroup_id, ptf_topclinic_1.byname, ptf_topclinic_1.last_session_token, ptf_topclinic_1.last_request_no, ptf_topclinic_1.clinic_type, ptf_topclinic_1.psm_id, ptf_topclinic_1.clinic_state, ptf_topclinic_1.abnormal_ind, ptf_topclinic_1.ptf_kbjperson_id, ptf_topclinic_1.start_datetime, ptf_topclinic_1.train_datetime, ptf_topclinic_1.open_datetime, ptf_topclinic_1.sign_datetime, ptf_topclinic_1.reset_datetime, ptf_topclinic_1.close_datetime, ptf_topclinic_1.abnormal_datetime, ptf_topclinic_1.old_clinic_status
  • Index Cond: ((ptf_topclinic_1.clinic_code)::text = ANY ('{xpg001,shzr001}'::text[]))
27. 370.682 370.682 ↓ 2.5 130,611 2

Index Scan using ndx_vstcharge_datatopid_chargedt on public.cln_visitcharge vc_1 (cost=0.56..42,235.05 rows=52,200 width=52) (actual time=0.025..185.341 rows=130,611 loops=2)

  • Output: vc_1.cln_visitcharge_id, vc_1.ptf_clinic_id, vc_1.cln_visit_id, vc_1.charge_no, vc_1.charge_status, vc_1.cln_item_id, vc_1.order_entered_doc_id, vc_1.qty, vc_1.unit, vc_1.unit_type, vc_1.price, vc_1.base_sale_price, vc_1.charge_amount, vc_1.discount_amount, vc_1.discounted_amount, vc_1.reduce_amount, vc_1.actual_amount, vc_1.total_favorable_amount, vc_1.cln_visitorderentryitem_id, vc_1.charged_by, vc_1.charged_datetime, vc_1.payment_ind, vc_1.cln_receipt_id, vc_1.parent_visitcharge_id, vc_1.executed_ind, vc_1.data_top_id, vc_1.created_by, vc_1.created_datetime, vc_1.last_updated_by, vc_1.last_updated_datetime, vc_1.version, vc_1.business_type, vc_1.package_amount, vc_1.account_amount, vc_1.credits_amount, vc_1.cln_visitopscharge_id, vc_1.source_type, vc_1.cost_amount, vc_1.gift_amount, vc_1.coupon_amount, vc_1.hear_about_path, vc_1.cln_hearaboutpath_id, vc_1.platform, vc_1.platform_order, vc_1.last_session_token, vc_1.last_request_no, vc_1.discount_rate, vc_1.coupon_charge_amount, vc_1.cln_visitchargeprescription_id, vc_1.seq_no_in_form, vc_1.cln_patientpost_id
  • Index Cond: (vc_1.data_top_id = ptf_topclinic_1.ptf_topclinic_id)
  • Filter: (vc_1.payment_ind = 'Y'::bpchar)
  • Rows Removed by Filter: 6,110
28. 2.862 81.923 ↑ 11.4 3,212 1

Hash (cost=19,435.18..19,435.18 rows=36,751 width=8) (actual time=81.923..81.923 rows=3,212 loops=1)

  • Output: itm_1.cln_item_id
  • Buckets: 65,536 Batches: 1 Memory Usage: 638kB
29. 5.257 79.061 ↑ 11.4 3,212 1

Nested Loop (cost=1.00..19,435.18 rows=36,751 width=8) (actual time=1.067..79.061 rows=3,212 loops=1)

  • Output: itm_1.cln_item_id
30. 2.553 55.058 ↑ 1.2 2,678 1

Nested Loop (cost=0.57..1,228.56 rows=3,340 width=8) (actual time=1.032..55.058 rows=2,678 loops=1)

  • Output: isb_1.cln_itemsubcat_id
31. 0.728 0.728 ↑ 1.1 523 1

Index Scan using ndx_clnic_icid on public.cln_itemcat imc_1 (cost=0.29..231.57 rows=552 width=8) (actual time=0.050..0.728 rows=523 loops=1)

  • Output: imc_1.cln_itemcat_id
  • Index Cond: (imc_1.ptf_itemcat_id = 4)
32. 51.777 51.777 ↑ 1.6 5 523

Index Scan using ndx_itmsubcat_catid on public.cln_itemsubcat isb_1 (cost=0.29..1.73 rows=8 width=16) (actual time=0.093..0.099 rows=5 loops=523)

  • Output: isb_1.cln_itemsubcat_id, isb_1.cln_itemcat_id, isb_1.itemsubcat_code, isb_1.itemsubcat_desc, isb_1.itemsubcat_desc_lang1, isb_1.short_code, isb_1.ptf_itemsubcat_id, isb_1.custom_ind, isb_1.seq_no, isb_1.remarks, isb_1.data_top_id, isb_1.defunct_ind, isb_1.created_by, isb_1.created_datetime, isb_1.last_updated_by, isb_1.last_updated_datetime, isb_1.version, isb_1.source_type, isb_1.code_rule, isb_1.last_session_token, isb_1.last_request_no
  • Index Cond: (isb_1.cln_itemcat_id = imc_1.cln_itemcat_id)
33. 18.746 18.746 ↑ 167.0 1 2,678

Index Scan using ndx_clnitm_isbid on public.cln_item itm_1 (cost=0.42..3.78 rows=167 width=16) (actual time=0.004..0.007 rows=1 loops=2,678)

  • Output: itm_1.cln_item_id, itm_1.cln_itemsubcat_id, itm_1.item_code, itm_1.item_desc, itm_1.item_desc_lang1, itm_1.short_code, itm_1.other_item_desc, itm_1.other_item_desc_lang1, itm_1.order_item_ind, itm_1.charge_item_ind, itm_1.material_item_ind, itm_1.specs, itm_1.specs_lang1, itm_1.sale_price, itm_1.adjust_price_ind, itm_1.min_sale_price, itm_1.max_sale_price, itm_1.price_unit, itm_1.base_dose, itm_1.dose_unit, itm_1.openpack_unit, itm_1.conversion_rate, itm_1.dosage_form, itm_1.poisonous_item_ind, itm_1.skin_test_ind, itm_1.infusion_ind, itm_1.notice_lang1, itm_1.notice, itm_1.antibiotic_ind, itm_1.antibiotic_grade, itm_1.ptf_frequency_id, itm_1.usage, itm_1.ptf_item_id, itm_1.ptf_topclinic_id, itm_1.custom_ind, itm_1.remarks, itm_1.data_top_id, itm_1.defunct_ind, itm_1.created_by, itm_1.created_datetime, itm_1.last_updated_by, itm_1.last_updated_datetime, itm_1.version, itm_1.factory, itm_1.approval_no, itm_1.top_ind, itm_1.producing_area, itm_1.exec_mode, itm_1.exec_records_required, itm_1.components, itm_1.efficacy, itm_1.picture_file_id, itm_1.entered_ind, itm_1.review_days, itm_1.release_hours, itm_1.warning_cycle, itm_1.self_ind, itm_1.self_clinic_id, itm_1.group_lab_ind, itm_1.source_type, itm_1.days, itm_1.cln_organization_id, itm_1.cost_price, itm_1.per_dose, itm_1.psychotropic_type, itm_1.bar_code, itm_1.credits_ind, itm_1.stop_ind, itm_1.last_session_token, itm_1.last_request_no, itm_1.dose_ceil_type, itm_1.common_unit_type, itm_1.herbxitem_desc, itm_1.herbxitem_doseunit, itm_1.granule_dose, itm_1.herbx_shortcode, itm_1.gifts_ind, itm_1.common_herb_remarks, itm_1.exec_notice, itm_1.tax_rate, itm_1.special_description, itm_1.backup_description
  • Index Cond: (itm_1.cln_itemsubcat_id = isb_1.cln_itemsubcat_id)
34. 437.804 437.832 ↓ 0.0 0 72,972

Index Scan using pk_cln_visit on public.cln_visit v_1 (cost=4.77..4.83 rows=1 width=8) (actual time=0.006..0.006 rows=0 loops=72,972)

  • Output: v_1.cln_visit_id
  • Index Cond: (v_1.cln_visit_id = vc_1.cln_visit_id)
  • Filter: (((v_1.visit_status)::text >= 'VST3'::text) AND (NOT (hashed SubPlan 2)) AND (v_1.visit_datetime >= to_timestamp(concat(to_date('2020-07-01'::text, 'yyyy-MM-dd'::text), ' 00:00:00'), 'yyyy-MM-dd HH24:MI:SS'::text)) AND (v_1.visit_datetime <= (to_timestamp(concat(to_date('2020-09-10'::text, 'yyyy-MM-dd'::text), ' 00:00:00'), 'yyyy-MM-dd HH24:MI:SS'::text) + '1 day'::interval)))
  • Rows Removed by Filter: 1
35.          

SubPlan (for Index Scan)

36. 0.028 0.028 ↓ 1.7 5 1

Index Scan using ndx_pcode_cat on public.ptf_codemstr ptf_codemstr_1 (cost=0.28..4.33 rows=3 width=32) (actual time=0.020..0.028 rows=5 loops=1)

  • Output: ((ptf_codemstr_1.parent_code_cat)::text || (ptf_codemstr_1.code_abbr)::text)
  • Index Cond: ((ptf_codemstr_1.code_cat)::text = 'VTN'::text)
37. 11.751 14,448.462 ↓ 2.0 2 3

GroupAggregate (cost=155,080.77..155,093.88 rows=1 width=312) (actual time=1,871.437..4,816.154 rows=2 loops=3)

  • Output: (to_char(v_2.visit_datetime, 'yyyy-MM'::text)), NULL::text, sum(CASE WHEN ((CASE WHEN (hashed SubPlan 3) THEN '北京'::text WHEN (hashed SubPlan 4) THEN '上海'::text WHEN (pc_1.ptf_city_id = 440,100) THEN '广州'::text WHEN (pc_1.ptf_city_id = 440,300) THEN '深圳'::text ELSE '其他'::text END) = '北京'::text) THEN 1 ELSE 0 END), sum(CASE WHEN (((CASE WHEN (hashed SubPlan 3) THEN '北京'::text WHEN (hashed SubPlan 4) THEN '上海'::text WHEN (pc_1.ptf_city_id = 440,100) THEN '广州'::text WHEN (pc_1.ptf_city_id = 440,300) THEN '深圳'::text ELSE '其他'::text END) = '北京'::text) AND ((sum((((((vc_3.actual_amount - vc_3.package_amount) + vc_3.account_amount) - vc_3.credits_amount) - vc_3.gift_amount) - vc_3.coupon_amount))) >= '50'::numeric) AND ((sum((((((vc_3.actual_amount - vc_3.package_amount) + vc_3.account_amount) - vc_3.credits_amount) - vc_3.gift_amount) - vc_3.coupon_amount))) IS NOT NULL)) THEN 1 ELSE 0 END), sum(CASE WHEN (((CASE WHEN (hashed SubPlan 3) THEN '北京'::text WHEN (hashed SubPlan 4) THEN '上海'::text WHEN (pc_1.ptf_city_id = 440,100) THEN '广州'::text WHEN (pc_1.ptf_city_id = 440,300) THEN '深圳'::text ELSE '其他'::text END) = '北京'::text) AND (((sum((((((vc_3.actual_amount - vc_3.package_amount) + vc_3.account_amount) - vc_3.credits_amount) - vc_3.gift_amount) - vc_3.coupon_amount))) < '50'::numeric) OR ((sum((((((vc_3.actual_amount - vc_3.package_amount) + vc_3.account_amount) - vc_3.credits_amount) - vc_3.gift_amount) - vc_3.coupon_amount))) IS NULL))) THEN 1 ELSE 0 END), NULL::text, sum(CASE WHEN ((CASE WHEN (hashed SubPlan 3) THEN '北京'::text WHEN (hashed SubPlan 4) THEN '上海'::text WHEN (pc_1.ptf_city_id = 440,100) THEN '广州'::text WHEN (pc_1.ptf_city_id = 440,300) THEN '深圳'::text ELSE '其他'::text END) = '上海'::text) THEN 1 ELSE 0 END), sum(CASE WHEN (((CASE WHEN (hashed SubPlan 3) THEN '北京'::text WHEN (hashed SubPlan 4) THEN '上海'::text WHEN (pc_1.ptf_city_id = 440,100) THEN '广州'::text WHEN (pc_1.ptf_city_id = 440,300) THEN '深圳'::text ELSE '其他'::text END) = '上海'::text) AND ((sum((((((vc_3.actual_amount - vc_3.package_amount) + vc_3.account_amount) - vc_3.credits_amount) - vc_3.gift_amount) - vc_3.coupon_amount))) >= '50'::numeric) AND ((sum((((((vc_3.actual_amount - vc_3.package_amount) + vc_3.account_amount) - vc_3.credits_amount) - vc_3.gift_amount) - vc_3.coupon_amount))) IS NOT NULL)) THEN 1 ELSE 0 END), sum(CASE WHEN (((CASE WHEN (hashed SubPlan 3) THEN '北京'::text WHEN (hashed SubPlan 4) THEN '上海'::text WHEN (pc_1.ptf_city_id = 440,100) THEN '广州'::text WHEN (pc_1.ptf_city_id = 440,300) THEN '深圳'::text ELSE '其他'::text END) = '上海'::text) AND (((sum((((((vc_3.actual_amount - vc_3.package_amount) + vc_3.account_amount) - vc_3.credits_amount) - vc_3.gift_amount) - vc_3.coupon_amount))) < '50'::numeric) OR ((sum((((((vc_3.actual_amount - vc_3.package_amount) + vc_3.account_amount) - vc_3.credits_amount) - vc_3.gift_amount) - vc_3.coupon_amount))) IS NULL))) THEN 1 ELSE 0 END), NULL::text, sum(CASE WHEN ((CASE WHEN (hashed SubPlan 3) THEN '北京'::text WHEN (hashed SubPlan 4) THEN '上海'::text WHEN (pc_1.ptf_city_id = 440,100) THEN '广州'::text WHEN (pc_1.ptf_city_id = 440,300) THEN '深圳'::text ELSE '其他'::text END) = '广州'::text) THEN 1 ELSE 0 END), sum(CASE WHEN (((CASE WHEN (hashed SubPlan 3) THEN '北京'::text WHEN (hashed SubPlan 4) THEN '上海'::text WHEN (pc_1.ptf_city_id = 440,100) THEN '广州'::text WHEN (pc_1.ptf_city_id = 440,300) THEN '深圳'::text ELSE '其他'::text END) = '广州'::text) AND ((sum((((((vc_3.actual_amount - vc_3.package_amount) + vc_3.account_amount) - vc_3.credits_amount) - vc_3.gift_amount) - vc_3.coupon_amount))) >= '50'::numeric) AND ((sum((((((vc_3.actual_amount - vc_3.package_amount) + vc_3.account_amount) - vc_3.credits_amount) - vc_3.gift_amount) - vc_3.coupon_amount))) IS NOT NULL)) THEN 1 ELSE 0 END), sum(CASE WHEN (((CASE WHEN (hashed SubPlan 3) THEN '北京'::text WHEN (hashed SubPlan 4) THEN '上海'::text WHEN (pc_1.ptf_city_id = 440,100) THEN '广州'::text WHEN (pc_1.ptf_city_id = 440,300) THEN '深圳'::text ELSE '其他'::text END) = '广州'::text) AND (((sum((((((vc_3.actual_amount - vc_3.package_amount) + vc_3.account_amount) - vc_3.credits_amount) - vc_3.gift_amount) - vc_3.coupon_amount))) < '50'::numeric) OR ((sum((((((vc_3.actual_amount - vc_3.package_amount) + vc_3.account_amount) - vc_3.credits_amount) - vc_3.gift_amount) - vc_3.coupon_amount))) IS NULL))) THEN 1 ELSE 0 END), NULL::text, sum(CASE WHEN ((CASE WHEN (hashed SubPlan 3) THEN '北京'::text WHEN (hashed SubPlan 4) THEN '上海'::text WHEN (pc_1.ptf_city_id = 440,100) THEN '广州'::text WHEN (pc_1.ptf_city_id = 440,300) THEN '深圳'::text ELSE '其他'::text END) = '深圳'::text) THEN 1 ELSE 0 END), sum(CASE WHEN (((CASE WHEN (hashed SubPlan 3) THEN '北京'::text WHEN (hashed SubPlan 4) THEN '上海'::text WHEN (pc_1.ptf_city_id = 440,100) THEN '广州'::text WHEN (pc_1.ptf_city_id = 440,300) THEN '深圳'::text ELSE '其他'::text END) = '深圳'::text) AND ((sum((((((vc_3.actual_amount - vc_3.package_amount) + vc_3.account_amount) - vc_3.credits_amount) - vc_3.gift_amount) - vc_3.coupon_amount))) >= '50'::numeric) AND ((sum((((((vc_3.actual_amount - vc_3.package_amount) + vc_3.account_amount) - vc_3.credits_amount) - vc_3.gift_amount) - vc_3.coupon_amount))) IS NOT NULL)) THEN 1 ELSE 0 END), sum(CASE WHEN (((CASE WHEN (hashed SubPlan 3) THEN '北京'::text WHEN (hashed SubPlan 4) THEN '上海'::text WHEN (pc_1.ptf_city_id = 440,100) THEN '广州'::text WHEN (pc_1.ptf_city_id = 440,300) THEN '深圳'::text ELSE '其他'::text END) = '深圳'::text) AND (((sum((((((vc_3.actual_amount - vc_3.package_amount) + vc_3.account_amount) - vc_3.credits_amount) - vc_3.gift_amount) - vc_3.coupon_amount))) < '50'::numeric) OR ((sum((((((vc_3.actual_amount - vc_3.package_amount) + vc_3.account_amount) - vc_3.credits_amount) - vc_3.gift_amount) - vc_3.coupon_amount))) IS NULL))) THEN 1 ELSE 0 END), NULL::text, NULL::bigint, NULL::bigint, NULL::bigint
  • Group Key: (to_char(v_2.visit_datetime, 'yyyy-MM'::text))
38. 1,433.097 14,436.711 ↓ 974.0 974 3

Nested Loop Left Join (cost=155,080.77..155,093.78 rows=1 width=96) (actual time=819.095..4,812.237 rows=974 loops=3)

  • Output: (to_char(v_2.visit_datetime, 'yyyy-MM'::text)), (CASE WHEN (hashed SubPlan 3) THEN '北京'::text WHEN (hashed SubPlan 4) THEN '上海'::text WHEN (pc_1.ptf_city_id = 440,100) THEN '广州'::text WHEN (pc_1.ptf_city_id = 440,300) THEN '深圳'::text ELSE '其他'::text END), (sum((((((vc_3.actual_amount - vc_3.package_amount) + vc_3.account_amount) - vc_3.credits_amount) - vc_3.gift_amount) - vc_3.coupon_amount)))
  • Inner Unique: true
  • Join Filter: (v_2.cln_visit_id = vc_3.cln_visit_id)
  • Rows Removed by Join Filter: 2,290,179
39. 3.369 1,385.742 ↓ 974.0 974 3

Group (cost=32,659.78..32,659.81 rows=1 width=72) (actual time=460.233..461.914 rows=974 loops=3)

  • Output: (to_char(v_2.visit_datetime, 'yyyy-MM'::text)), (CASE WHEN (hashed SubPlan 3) THEN '北京'::text WHEN (hashed SubPlan 4) THEN '上海'::text WHEN (pc_1.ptf_city_id = 440,100) THEN '广州'::text WHEN (pc_1.ptf_city_id = 440,300) THEN '深圳'::text ELSE '其他'::text END), v_2.cln_visit_id
  • Group Key: (to_char(v_2.visit_datetime, 'yyyy-MM'::text)), (CASE WHEN (hashed SubPlan 3) THEN '北京'::text WHEN (hashed SubPlan 4) THEN '上海'::text WHEN (pc_1.ptf_city_id = 440,100) THEN '广州'::text WHEN (pc_1.ptf_city_id = 440,300) THEN '深圳'::text ELSE '其他'::text END), v_2.cln_visit_id
40. 5.275 1,382.373 ↓ 1,013.0 1,013 3

Sort (cost=32,653.12..32,653.13 rows=1 width=72) (actual time=460.228..460.791 rows=1,013 loops=3)

  • Output: (to_char(v_2.visit_datetime, 'yyyy-MM'::text)), (CASE WHEN (hashed SubPlan 3) THEN '北京'::text WHEN (hashed SubPlan 4) THEN '上海'::text WHEN (pc_1.ptf_city_id = 440,100) THEN '广州'::text WHEN (pc_1.ptf_city_id = 440,300) THEN '深圳'::text ELSE '其他'::text END), v_2.cln_visit_id
  • Sort Key: (to_char(v_2.visit_datetime, 'yyyy-MM'::text)), (CASE WHEN (hashed SubPlan 3) THEN '北京'::text WHEN (hashed SubPlan 4) THEN '上海'::text WHEN (pc_1.ptf_city_id = 440,100) THEN '广州'::text WHEN (pc_1.ptf_city_id = 440,300) THEN '深圳'::text ELSE '其他'::text END), v_2.cln_visit_id
  • Sort Method: quicksort Memory: 154kB
41. 6.111 1,377.098 ↓ 1,349.0 1,349 1

Nested Loop (cost=13.42..32,653.11 rows=1 width=72) (actual time=188.744..1,377.098 rows=1,349 loops=1)

  • Output: to_char(v_2.visit_datetime, 'yyyy-MM'::text), CASE WHEN (hashed SubPlan 3) THEN '北京'::text WHEN (hashed SubPlan 4) THEN '上海'::text WHEN (pc_1.ptf_city_id = 440,100) THEN '广州'::text WHEN (pc_1.ptf_city_id = 440,300) THEN '深圳'::text ELSE '其他'::text END, v_2.cln_visit_id
  • Inner Unique: true
42. 6.335 1,308.229 ↓ 1,119.6 7,837 1

Nested Loop (cost=1.99..32,612.66 rows=7 width=16) (actual time=0.199..1,308.229 rows=7,837 loops=1)

  • Output: vc_2.cln_visit_id, pc_1.ptf_city_id
  • Inner Unique: true
43. 5.410 1,286.218 ↓ 279.9 7,838 1

Nested Loop (cost=1.70..32,603.99 rows=28 width=24) (actual time=0.147..1,286.218 rows=7,838 loops=1)

  • Output: vc_2.cln_visit_id, pc_1.ptf_city_id, isb_2.cln_itemcat_id
  • Inner Unique: true
44. 267.138 1,265.132 ↓ 279.9 7,838 1

Nested Loop (cost=1.41..32,555.81 rows=28 width=24) (actual time=0.129..1,265.132 rows=7,838 loops=1)

  • Output: vc_2.cln_visit_id, itm_2.cln_itemsubcat_id, pc_1.ptf_city_id
  • Inner Unique: true
45. 111.977 475.550 ↓ 4.5 261,222 1

Nested Loop (cost=0.99..5,810.43 rows=58,576 width=24) (actual time=0.081..475.550 rows=261,222 loops=1)

  • Output: vc_2.cln_visit_id, vc_2.cln_item_id, pc_1.ptf_city_id
46. 0.022 0.108 ↓ 3.0 9 1

Nested Loop (cost=0.55..9.19 rows=3 width=16) (actual time=0.051..0.108 rows=9 loops=1)

  • Output: pc_1.ptf_city_id, pc_1.ptf_clinic_id
47. 0.048 0.048 ↑ 1.0 2 1

Index Scan using ndx_unique_cliniccode on public.ptf_topclinic ptf_topclinic_2 (cost=0.28..4.58 rows=2 width=8) (actual time=0.036..0.048 rows=2 loops=1)

  • Output: ptf_topclinic_2.ptf_topclinic_id, ptf_topclinic_2.clinic_code, ptf_topclinic_2.clinic_desc, ptf_topclinic_2.clinic_desc_lang1, ptf_topclinic_2.short_code, ptf_topclinic_2.ptf_city_id, ptf_topclinic_2.picture_file_id, ptf_topclinic_2.logo_file_id, ptf_topclinic_2.largelogo_file_id, ptf_topclinic_2.address_detail, ptf_topclinic_2.intro_main, ptf_topclinic_2.intro_main_lang1, ptf_topclinic_2.online_ind, ptf_topclinic_2.sysmodel_code, ptf_topclinic_2.remarks, ptf_topclinic_2.defunct_ind, ptf_topclinic_2.created_by, ptf_topclinic_2.created_datetime, ptf_topclinic_2.last_updated_by, ptf_topclinic_2.last_updated_datetime, ptf_topclinic_2.version, ptf_topclinic_2.offline_datetime, ptf_topclinic_2.institution_code, ptf_topclinic_2.source_type, ptf_topclinic_2.longitude, ptf_topclinic_2.latitude, ptf_topclinic_2.phone_no, ptf_topclinic_2.cln_filegroup_id, ptf_topclinic_2.byname, ptf_topclinic_2.last_session_token, ptf_topclinic_2.last_request_no, ptf_topclinic_2.clinic_type, ptf_topclinic_2.psm_id, ptf_topclinic_2.clinic_state, ptf_topclinic_2.abnormal_ind, ptf_topclinic_2.ptf_kbjperson_id, ptf_topclinic_2.start_datetime, ptf_topclinic_2.train_datetime, ptf_topclinic_2.open_datetime, ptf_topclinic_2.sign_datetime, ptf_topclinic_2.reset_datetime, ptf_topclinic_2.close_datetime, ptf_topclinic_2.abnormal_datetime, ptf_topclinic_2.old_clinic_status
  • Index Cond: ((ptf_topclinic_2.clinic_code)::text = ANY ('{xpg001,shzr001}'::text[]))
48. 0.038 0.038 ↓ 4.0 4 2

Index Scan using ndx_clnc_topid on public.ptf_clinic pc_1 (cost=0.28..2.29 rows=1 width=24) (actual time=0.008..0.019 rows=4 loops=2)

  • Output: pc_1.ptf_clinic_id, pc_1.ptf_topclinic_id, pc_1.parent_id, pc_1.clinic_code, pc_1.clinic_desc, pc_1.short_code, pc_1.clinic_desc_lang1, pc_1.seq_no, pc_1.entity_ind, pc_1.ptf_city_id, pc_1.address_detail, pc_1.intro_main, pc_1.intro_main_lang1, pc_1.picture_file_id, pc_1.logo_file_id, pc_1.largelogo_file_id, pc_1.online_ind, pc_1.remarks, pc_1.defunct_ind, pc_1.created_by, pc_1.created_datetime, pc_1.last_updated_by, pc_1.last_updated_datetime, pc_1.version, pc_1.valid_date, pc_1.institution_code, pc_1.source_type, pc_1.longitude, pc_1.latitude, pc_1.phone_no, pc_1.business_hours, pc_1.cln_filegroup_id, pc_1.byname, pc_1.last_session_token, pc_1.last_request_no, pc_1.clinic_type, pc_1.psm_id, pc_1.clinic_state, pc_1.abnormal_ind, pc_1.ptf_kbjperson_id, pc_1.start_datetime, pc_1.train_datetime, pc_1.open_datetime, pc_1.sign_datetime, pc_1.reset_datetime, pc_1.close_datetime, pc_1.abnormal_datetime, pc_1.offline_datetime, pc_1.old_clinic_status
  • Index Cond: (pc_1.ptf_topclinic_id = ptf_topclinic_2.ptf_topclinic_id)
49. 363.465 363.465 ↑ 1.2 29,025 9

Index Scan using ndx_vstcharge_clinicid on public.cln_visitcharge vc_2 (cost=0.43..1,591.95 rows=34,180 width=24) (actual time=0.015..40.385 rows=29,025 loops=9)

  • Output: vc_2.cln_visitcharge_id, vc_2.ptf_clinic_id, vc_2.cln_visit_id, vc_2.charge_no, vc_2.charge_status, vc_2.cln_item_id, vc_2.order_entered_doc_id, vc_2.qty, vc_2.unit, vc_2.unit_type, vc_2.price, vc_2.base_sale_price, vc_2.charge_amount, vc_2.discount_amount, vc_2.discounted_amount, vc_2.reduce_amount, vc_2.actual_amount, vc_2.total_favorable_amount, vc_2.cln_visitorderentryitem_id, vc_2.charged_by, vc_2.charged_datetime, vc_2.payment_ind, vc_2.cln_receipt_id, vc_2.parent_visitcharge_id, vc_2.executed_ind, vc_2.data_top_id, vc_2.created_by, vc_2.created_datetime, vc_2.last_updated_by, vc_2.last_updated_datetime, vc_2.version, vc_2.business_type, vc_2.package_amount, vc_2.account_amount, vc_2.credits_amount, vc_2.cln_visitopscharge_id, vc_2.source_type, vc_2.cost_amount, vc_2.gift_amount, vc_2.coupon_amount, vc_2.hear_about_path, vc_2.cln_hearaboutpath_id, vc_2.platform, vc_2.platform_order, vc_2.last_session_token, vc_2.last_request_no, vc_2.discount_rate, vc_2.coupon_charge_amount, vc_2.cln_visitchargeprescription_id, vc_2.seq_no_in_form, vc_2.cln_patientpost_id
  • Index Cond: (vc_2.ptf_clinic_id = pc_1.ptf_clinic_id)
  • Filter: (vc_2.payment_ind = 'Y'::bpchar)
  • Rows Removed by Filter: 1,358
50. 522.444 522.444 ↓ 0.0 0 261,222

Index Scan using pk_cln_item on public.cln_item itm_2 (cost=0.42..0.46 rows=1 width=16) (actual time=0.002..0.002 rows=0 loops=261,222)

  • Output: itm_2.cln_item_id, itm_2.cln_itemsubcat_id, itm_2.item_code, itm_2.item_desc, itm_2.item_desc_lang1, itm_2.short_code, itm_2.other_item_desc, itm_2.other_item_desc_lang1, itm_2.order_item_ind, itm_2.charge_item_ind, itm_2.material_item_ind, itm_2.specs, itm_2.specs_lang1, itm_2.sale_price, itm_2.adjust_price_ind, itm_2.min_sale_price, itm_2.max_sale_price, itm_2.price_unit, itm_2.base_dose, itm_2.dose_unit, itm_2.openpack_unit, itm_2.conversion_rate, itm_2.dosage_form, itm_2.poisonous_item_ind, itm_2.skin_test_ind, itm_2.infusion_ind, itm_2.notice_lang1, itm_2.notice, itm_2.antibiotic_ind, itm_2.antibiotic_grade, itm_2.ptf_frequency_id, itm_2.usage, itm_2.ptf_item_id, itm_2.ptf_topclinic_id, itm_2.custom_ind, itm_2.remarks, itm_2.data_top_id, itm_2.defunct_ind, itm_2.created_by, itm_2.created_datetime, itm_2.last_updated_by, itm_2.last_updated_datetime, itm_2.version, itm_2.factory, itm_2.approval_no, itm_2.top_ind, itm_2.producing_area, itm_2.exec_mode, itm_2.exec_records_required, itm_2.components, itm_2.efficacy, itm_2.picture_file_id, itm_2.entered_ind, itm_2.review_days, itm_2.release_hours, itm_2.warning_cycle, itm_2.self_ind, itm_2.self_clinic_id, itm_2.group_lab_ind, itm_2.source_type, itm_2.days, itm_2.cln_organization_id, itm_2.cost_price, itm_2.per_dose, itm_2.psychotropic_type, itm_2.bar_code, itm_2.credits_ind, itm_2.stop_ind, itm_2.last_session_token, itm_2.last_request_no, itm_2.dose_ceil_type, itm_2.common_unit_type, itm_2.herbxitem_desc, itm_2.herbxitem_doseunit, itm_2.granule_dose, itm_2.herbx_shortcode, itm_2.gifts_ind, itm_2.common_herb_remarks, itm_2.exec_notice, itm_2.tax_rate, itm_2.special_description, itm_2.backup_description
  • Index Cond: (itm_2.cln_item_id = vc_2.cln_item_id)
  • Filter: (((itm_2.item_desc)::text ~~ '%抑那通%'::text) OR ((itm_2.item_desc)::text ~~ '%达菲林%'::text) OR ((itm_2.item_desc)::text ~~ '%贝恩%'::text) OR ((itm_2.item_desc)::text ~~ '%伯恩诺康%'::text) OR ((itm_2.item_desc)::text ~~ '%达必佳%'::text))
  • Rows Removed by Filter: 1
51. 15.676 15.676 ↑ 1.0 1 7,838

Index Scan using pk_cln_itemsubcat on public.cln_itemsubcat isb_2 (cost=0.29..1.72 rows=1 width=16) (actual time=0.002..0.002 rows=1 loops=7,838)

  • Output: isb_2.cln_itemsubcat_id, isb_2.cln_itemcat_id, isb_2.itemsubcat_code, isb_2.itemsubcat_desc, isb_2.itemsubcat_desc_lang1, isb_2.short_code, isb_2.ptf_itemsubcat_id, isb_2.custom_ind, isb_2.seq_no, isb_2.remarks, isb_2.data_top_id, isb_2.defunct_ind, isb_2.created_by, isb_2.created_datetime, isb_2.last_updated_by, isb_2.last_updated_datetime, isb_2.version, isb_2.source_type, isb_2.code_rule, isb_2.last_session_token, isb_2.last_request_no
  • Index Cond: (isb_2.cln_itemsubcat_id = itm_2.cln_itemsubcat_id)
52. 15.676 15.676 ↑ 1.0 1 7,838

Index Scan using pk_cln_itemcat on public.cln_itemcat imc_2 (cost=0.29..0.31 rows=1 width=8) (actual time=0.002..0.002 rows=1 loops=7,838)

  • Output: imc_2.cln_itemcat_id
  • Index Cond: (imc_2.cln_itemcat_id = isb_2.cln_itemcat_id)
  • Filter: (imc_2.medicine_ind = 'Y'::bpchar)
  • Rows Removed by Filter: 0
53. 62.674 62.696 ↓ 0.0 0 7,837

Index Scan using pk_cln_visit on public.cln_visit v_2 (cost=4.77..4.83 rows=1 width=16) (actual time=0.008..0.008 rows=0 loops=7,837)

  • Output: v_2.visit_datetime, v_2.cln_visit_id
  • Index Cond: (v_2.cln_visit_id = vc_2.cln_visit_id)
  • Filter: (((v_2.visit_status)::text >= 'VST3'::text) AND (NOT (hashed SubPlan 5)) AND (v_2.visit_datetime >= to_timestamp(concat(to_date('2020-07-01'::text, 'yyyy-MM-dd'::text), ' 00:00:00'), 'yyyy-MM-dd HH24:MI:SS'::text)) AND (v_2.visit_datetime <= (to_timestamp(concat(to_date('2020-09-10'::text, 'yyyy-MM-dd'::text), ' 00:00:00'), 'yyyy-MM-dd HH24:MI:SS'::text) + '1 day'::interval)))
  • Rows Removed by Filter: 1
54.          

SubPlan (for Index Scan)

55. 0.022 0.022 ↓ 1.7 5 1

Index Scan using ndx_pcode_cat on public.ptf_codemstr ptf_codemstr_2 (cost=0.28..4.33 rows=3 width=32) (actual time=0.015..0.022 rows=5 loops=1)

  • Output: ((ptf_codemstr_2.parent_code_cat)::text || (ptf_codemstr_2.code_abbr)::text)
  • Index Cond: ((ptf_codemstr_2.code_cat)::text = 'VTN'::text)
56.          

SubPlan (for Nested Loop)

57. 0.037 0.037 ↑ 1.0 18 1

Index Scan using ndx_city_stateid on public.ptf_city (cost=0.27..3.28 rows=18 width=8) (actual time=0.026..0.037 rows=18 loops=1)

  • Output: ptf_city.ptf_city_id
  • Index Cond: (ptf_city.ptf_state_id = 110,000)
58. 0.025 0.025 ↑ 1.0 19 1

Index Scan using ndx_city_stateid on public.ptf_city ptf_city_1 (cost=0.27..3.29 rows=19 width=8) (actual time=0.016..0.025 rows=19 loops=1)

  • Output: ptf_city_1.ptf_city_id
  • Index Cond: (ptf_city_1.ptf_state_id = 310,000)
59. 9,090.342 11,617.872 ↓ 10.0 2,352 2,922

GroupAggregate (cost=122,420.99..122,428.66 rows=236 width=40) (actual time=0.371..3.976 rows=2,352 loops=2,922)

  • Output: vc_3.cln_visit_id, sum((((((vc_3.actual_amount - vc_3.package_amount) + vc_3.account_amount) - vc_3.credits_amount) - vc_3.gift_amount) - vc_3.coupon_amount))
  • Group Key: vc_3.cln_visit_id
60. 1,455.153 2,527.530 ↓ 10.2 2,417 2,922

Sort (cost=122,420.99..122,421.58 rows=236 width=28) (actual time=0.369..0.865 rows=2,417 loops=2,922)

  • Output: vc_3.cln_visit_id, vc_3.actual_amount, vc_3.package_amount, vc_3.account_amount, vc_3.credits_amount, vc_3.gift_amount, vc_3.coupon_amount
  • Sort Key: vc_3.cln_visit_id
  • Sort Method: quicksort Memory: 645kB
61. 72.217 1,072.377 ↓ 24.6 5,794 1

Nested Loop (cost=19,900.17..122,411.68 rows=236 width=28) (actual time=133.238..1,072.377 rows=5,794 loops=1)

  • Output: vc_3.cln_visit_id, vc_3.actual_amount, vc_3.package_amount, vc_3.account_amount, vc_3.credits_amount, vc_3.gift_amount, vc_3.coupon_amount
  • Inner Unique: true
62. 93.364 635.300 ↓ 21.0 72,972 1

Hash Join (cost=19,895.40..105,667.60 rows=3,470 width=28) (actual time=18.497..635.300 rows=72,972 loops=1)

  • Output: vc_3.cln_visit_id, vc_3.actual_amount, vc_3.package_amount, vc_3.account_amount, vc_3.credits_amount, vc_3.gift_amount, vc_3.coupon_amount
  • Hash Cond: (vc_3.cln_item_id = itm_3.cln_item_id)
63. 149.956 523.846 ↓ 4.5 261,222 1

Nested Loop (cost=0.84..85,518.68 rows=58,576 width=36) (actual time=0.085..523.846 rows=261,222 loops=1)

  • Output: vc_3.cln_visit_id, vc_3.actual_amount, vc_3.package_amount, vc_3.account_amount, vc_3.credits_amount, vc_3.gift_amount, vc_3.coupon_amount, vc_3.cln_item_id
64. 0.058 0.058 ↑ 1.0 2 1

Index Scan using ndx_unique_cliniccode on public.ptf_topclinic ptf_topclinic_3 (cost=0.28..4.58 rows=2 width=8) (actual time=0.040..0.058 rows=2 loops=1)

  • Output: ptf_topclinic_3.ptf_topclinic_id, ptf_topclinic_3.clinic_code, ptf_topclinic_3.clinic_desc, ptf_topclinic_3.clinic_desc_lang1, ptf_topclinic_3.short_code, ptf_topclinic_3.ptf_city_id, ptf_topclinic_3.picture_file_id, ptf_topclinic_3.logo_file_id, ptf_topclinic_3.largelogo_file_id, ptf_topclinic_3.address_detail, ptf_topclinic_3.intro_main, ptf_topclinic_3.intro_main_lang1, ptf_topclinic_3.online_ind, ptf_topclinic_3.sysmodel_code, ptf_topclinic_3.remarks, ptf_topclinic_3.defunct_ind, ptf_topclinic_3.created_by, ptf_topclinic_3.created_datetime, ptf_topclinic_3.last_updated_by, ptf_topclinic_3.last_updated_datetime, ptf_topclinic_3.version, ptf_topclinic_3.offline_datetime, ptf_topclinic_3.institution_code, ptf_topclinic_3.source_type, ptf_topclinic_3.longitude, ptf_topclinic_3.latitude, ptf_topclinic_3.phone_no, ptf_topclinic_3.cln_filegroup_id, ptf_topclinic_3.byname, ptf_topclinic_3.last_session_token, ptf_topclinic_3.last_request_no, ptf_topclinic_3.clinic_type, ptf_topclinic_3.psm_id, ptf_topclinic_3.clinic_state, ptf_topclinic_3.abnormal_ind, ptf_topclinic_3.ptf_kbjperson_id, ptf_topclinic_3.start_datetime, ptf_topclinic_3.train_datetime, ptf_topclinic_3.open_datetime, ptf_topclinic_3.sign_datetime, ptf_topclinic_3.reset_datetime, ptf_topclinic_3.close_datetime, ptf_topclinic_3.abnormal_datetime, ptf_topclinic_3.old_clinic_status
  • Index Cond: ((ptf_topclinic_3.clinic_code)::text = ANY ('{xpg001,shzr001}'::text[]))
65. 373.832 373.832 ↓ 2.5 130,611 2

Index Scan using ndx_vstcharge_datatopid_chargedt on public.cln_visitcharge vc_3 (cost=0.56..42,235.05 rows=52,200 width=44) (actual time=0.030..186.916 rows=130,611 loops=2)

  • Output: vc_3.cln_visitcharge_id, vc_3.ptf_clinic_id, vc_3.cln_visit_id, vc_3.charge_no, vc_3.charge_status, vc_3.cln_item_id, vc_3.order_entered_doc_id, vc_3.qty, vc_3.unit, vc_3.unit_type, vc_3.price, vc_3.base_sale_price, vc_3.charge_amount, vc_3.discount_amount, vc_3.discounted_amount, vc_3.reduce_amount, vc_3.actual_amount, vc_3.total_favorable_amount, vc_3.cln_visitorderentryitem_id, vc_3.charged_by, vc_3.charged_datetime, vc_3.payment_ind, vc_3.cln_receipt_id, vc_3.parent_visitcharge_id, vc_3.executed_ind, vc_3.data_top_id, vc_3.created_by, vc_3.created_datetime, vc_3.last_updated_by, vc_3.last_updated_datetime, vc_3.version, vc_3.business_type, vc_3.package_amount, vc_3.account_amount, vc_3.credits_amount, vc_3.cln_visitopscharge_id, vc_3.source_type, vc_3.cost_amount, vc_3.gift_amount, vc_3.coupon_amount, vc_3.hear_about_path, vc_3.cln_hearaboutpath_id, vc_3.platform, vc_3.platform_order, vc_3.last_session_token, vc_3.last_request_no, vc_3.discount_rate, vc_3.coupon_charge_amount, vc_3.cln_visitchargeprescription_id, vc_3.seq_no_in_form, vc_3.cln_patientpost_id
  • Index Cond: (vc_3.data_top_id = ptf_topclinic_3.ptf_topclinic_id)
  • Filter: (vc_3.payment_ind = 'Y'::bpchar)
  • Rows Removed by Filter: 6,110
66. 1.220 18.090 ↑ 11.4 3,212 1

Hash (cost=19,435.18..19,435.18 rows=36,751 width=8) (actual time=18.090..18.090 rows=3,212 loops=1)

  • Output: itm_3.cln_item_id
  • Buckets: 65,536 Batches: 1 Memory Usage: 638kB
67. 3.659 16.870 ↑ 11.4 3,212 1

Nested Loop (cost=1.00..19,435.18 rows=36,751 width=8) (actual time=0.064..16.870 rows=3,212 loops=1)

  • Output: itm_3.cln_item_id
68. 1.653 5.177 ↑ 1.2 2,678 1

Nested Loop (cost=0.57..1,228.56 rows=3,340 width=8) (actual time=0.046..5.177 rows=2,678 loops=1)

  • Output: isb_3.cln_itemsubcat_id
69. 0.386 0.386 ↑ 1.1 523 1

Index Scan using ndx_clnic_icid on public.cln_itemcat imc_3 (cost=0.29..231.57 rows=552 width=8) (actual time=0.029..0.386 rows=523 loops=1)

  • Output: imc_3.cln_itemcat_id
  • Index Cond: (imc_3.ptf_itemcat_id = 4)
70. 3.138 3.138 ↑ 1.6 5 523

Index Scan using ndx_itmsubcat_catid on public.cln_itemsubcat isb_3 (cost=0.29..1.73 rows=8 width=16) (actual time=0.004..0.006 rows=5 loops=523)

  • Output: isb_3.cln_itemsubcat_id, isb_3.cln_itemcat_id, isb_3.itemsubcat_code, isb_3.itemsubcat_desc, isb_3.itemsubcat_desc_lang1, isb_3.short_code, isb_3.ptf_itemsubcat_id, isb_3.custom_ind, isb_3.seq_no, isb_3.remarks, isb_3.data_top_id, isb_3.defunct_ind, isb_3.created_by, isb_3.created_datetime, isb_3.last_updated_by, isb_3.last_updated_datetime, isb_3.version, isb_3.source_type, isb_3.code_rule, isb_3.last_session_token, isb_3.last_request_no
  • Index Cond: (isb_3.cln_itemcat_id = imc_3.cln_itemcat_id)
71. 8.034 8.034 ↑ 167.0 1 2,678

Index Scan using ndx_clnitm_isbid on public.cln_item itm_3 (cost=0.42..3.78 rows=167 width=16) (actual time=0.002..0.003 rows=1 loops=2,678)

  • Output: itm_3.cln_item_id, itm_3.cln_itemsubcat_id, itm_3.item_code, itm_3.item_desc, itm_3.item_desc_lang1, itm_3.short_code, itm_3.other_item_desc, itm_3.other_item_desc_lang1, itm_3.order_item_ind, itm_3.charge_item_ind, itm_3.material_item_ind, itm_3.specs, itm_3.specs_lang1, itm_3.sale_price, itm_3.adjust_price_ind, itm_3.min_sale_price, itm_3.max_sale_price, itm_3.price_unit, itm_3.base_dose, itm_3.dose_unit, itm_3.openpack_unit, itm_3.conversion_rate, itm_3.dosage_form, itm_3.poisonous_item_ind, itm_3.skin_test_ind, itm_3.infusion_ind, itm_3.notice_lang1, itm_3.notice, itm_3.antibiotic_ind, itm_3.antibiotic_grade, itm_3.ptf_frequency_id, itm_3.usage, itm_3.ptf_item_id, itm_3.ptf_topclinic_id, itm_3.custom_ind, itm_3.remarks, itm_3.data_top_id, itm_3.defunct_ind, itm_3.created_by, itm_3.created_datetime, itm_3.last_updated_by, itm_3.last_updated_datetime, itm_3.version, itm_3.factory, itm_3.approval_no, itm_3.top_ind, itm_3.producing_area, itm_3.exec_mode, itm_3.exec_records_required, itm_3.components, itm_3.efficacy, itm_3.picture_file_id, itm_3.entered_ind, itm_3.review_days, itm_3.release_hours, itm_3.warning_cycle, itm_3.self_ind, itm_3.self_clinic_id, itm_3.group_lab_ind, itm_3.source_type, itm_3.days, itm_3.cln_organization_id, itm_3.cost_price, itm_3.per_dose, itm_3.psychotropic_type, itm_3.bar_code, itm_3.credits_ind, itm_3.stop_ind, itm_3.last_session_token, itm_3.last_request_no, itm_3.dose_ceil_type, itm_3.common_unit_type, itm_3.herbxitem_desc, itm_3.herbxitem_doseunit, itm_3.granule_dose, itm_3.herbx_shortcode, itm_3.gifts_ind, itm_3.common_herb_remarks, itm_3.exec_notice, itm_3.tax_rate, itm_3.special_description, itm_3.backup_description
  • Index Cond: (itm_3.cln_itemsubcat_id = isb_3.cln_itemsubcat_id)
72. 364.835 364.860 ↓ 0.0 0 72,972

Index Scan using pk_cln_visit on public.cln_visit v_3 (cost=4.77..4.83 rows=1 width=8) (actual time=0.005..0.005 rows=0 loops=72,972)

  • Output: v_3.cln_visit_id
  • Index Cond: (v_3.cln_visit_id = vc_3.cln_visit_id)
  • Filter: (((v_3.visit_status)::text >= 'VST3'::text) AND (NOT (hashed SubPlan 6)) AND (v_3.visit_datetime >= to_timestamp(concat(to_date('2020-07-01'::text, 'yyyy-MM-dd'::text), ' 00:00:00'), 'yyyy-MM-dd HH24:MI:SS'::text)) AND (v_3.visit_datetime <= (to_timestamp(concat(to_date('2020-09-10'::text, 'yyyy-MM-dd'::text), ' 00:00:00'), 'yyyy-MM-dd HH24:MI:SS'::text) + '1 day'::interval)))
  • Rows Removed by Filter: 1
73.          

SubPlan (for Index Scan)

74. 0.025 0.025 ↓ 1.7 5 1

Index Scan using ndx_pcode_cat on public.ptf_codemstr ptf_codemstr_3 (cost=0.28..4.33 rows=3 width=32) (actual time=0.020..0.025 rows=5 loops=1)

  • Output: ((ptf_codemstr_3.parent_code_cat)::text || (ptf_codemstr_3.code_abbr)::text)
  • Index Cond: ((ptf_codemstr_3.code_cat)::text = 'VTN'::text)