explain.depesz.com

PostgreSQL's explain analyze made readable

Result: hYc

Settings
# exclusive inclusive rows x rows loops node
1. 0.000 0.000 ↓ 0.0

Limit (cost=265,428.32..265,430.52 rows=880 width=996) (actual rows= loops=)

  • Output: ml.customerid, ml.customername, ml.ctype, ml.customerlevel, ml.address, ml.customerstatus, ml.plandate, ml.userid, ml.customertype, ml.seq, ml.actualvisittime, ml.visittype, ml.status, ml.isplan, ((ml.seq + 0))
2. 0.000 0.000 ↓ 0.0

Sort (cost=265,428.32..265,430.52 rows=880 width=996) (actual rows= loops=)

  • Output: ml.customerid, ml.customername, ml.ctype, ml.customerlevel, ml.address, ml.customerstatus, ml.plandate, ml.userid, ml.customertype, ml.seq, ml.actualvisittime, ml.visittype, ml.status, ml.isplan, ((ml.seq + 0))
  • Sort Key: ((ml.seq + 0)), ml.customername
3. 0.000 0.000 ↓ 0.0

Subquery Scan on ml (cost=265,365.48..265,385.28 rows=880 width=996) (actual rows= loops=)

  • Output: ml.customerid, ml.customername, ml.ctype, ml.customerlevel, ml.address, ml.customerstatus, ml.plandate, ml.userid, ml.customertype, ml.seq, ml.actualvisittime, ml.visittype, ml.status, ml.isplan, (ml.seq + 0)
4. 0.000 0.000 ↓ 0.0

HashAggregate (cost=265,365.48..265,374.28 rows=880 width=988) (actual rows= loops=)

  • Output: "*SELECT* 1".id, "*SELECT* 1".customername, "*SELECT* 1".store_channeltype, "*SELECT* 1".storelevelname, "*SELECT* 1".address, "*SELECT* 1".status, p.plandate, p.userid, p.customertype, p.seq, a.actualvisittime, a.visittype, ((SubPlan 2)), (1)
  • Group Key: "*SELECT* 1".id, "*SELECT* 1".customername, "*SELECT* 1".store_channeltype, "*SELECT* 1".storelevelname, "*SELECT* 1".address, "*SELECT* 1".status, p.plandate, p.userid, p.customertype, p.seq, a.actualvisittime, a.visittype, ((SubPlan 2)), (1)
5. 0.000 0.000 ↓ 0.0

Append (cost=221.77..265,334.68 rows=880 width=988) (actual rows= loops=)

6. 0.000 0.000 ↓ 0.0

Nested Loop (cost=221.77..138,552.43 rows=872 width=242) (actual rows= loops=)

  • Output: "*SELECT* 1".id, "*SELECT* 1".customername, "*SELECT* 1".store_channeltype, "*SELECT* 1".storelevelname, "*SELECT* 1".address, "*SELECT* 1".status, p.plandate, p.userid, p.customertype, p.seq, a.actualvisittime, a.visittype, (SubPlan 2), 1
7. 0.000 0.000 ↓ 0.0

Nested Loop Left Join (cost=0.58..33.34 rows=1 width=46) (actual rows= loops=)

  • Output: p.plandate, p.userid, p.customertype, p.seq, p.customerid, a.actualvisittime, a.visittype
8. 0.000 0.000 ↓ 0.0

Nested Loop Left Join (cost=0.29..17.37 rows=1 width=46) (actual rows= loops=)

  • Output: p.plandate, p.userid, p.customertype, p.seq, p.customerid, a.actualvisittime, a.visittype
9. 0.000 0.000 ↓ 0.0

Seq Scan on public.kx_visit_planvisit p (cost=0.00..1.36 rows=1 width=36) (actual rows= loops=)

  • Output: p.platcreatetime, p.platupdatetime, p.platcreateop, p.platupdateop, p.platstatus, p.planid, p.plandate, p.userid, p.account, p.customerid, p.customertype, p.seq, p.status, p.tn_source
  • Filter: ((p.plandate >= '2020-05-19'::date) AND (p.plandate <= '2020-05-19'::date) AND (p.platstatus = 1) AND (p.userid = '1211917550779240448'::bigint))
10. 0.000 0.000 ↓ 0.0

Index Scan using idx_kx_visit_actual_customerid on public.kx_visit_actual a (cost=0.29..16.00 rows=1 width=26) (actual rows= loops=)

  • Output: a.platcreatetime, a.platupdatetime, a.platcreateop, a.platupdateop, a.platstatus, a.actualvisitid, a.actualvisitdate, a.userid, a.customerid, a.visittype, a.actualvisittime, a.customertype
  • Index Cond: (a.customerid = p.customerid)
  • Filter: ((a.actualvisittime >= '2020-05-19 00:00:00'::timestamp without time zone) AND (a.actualvisittime <= '2020-05-19 23:59:59'::timestamp without time zone) AND (a.userid = '1211917550779240448'::bigint) AND (a.platstatus = 1) AND (a.userid = p.userid))
11. 0.000 0.000 ↓ 0.0

Index Scan using idx_kx_visit_customerstatus_customerid on public.kx_visit_customerstatus vc (cost=0.29..15.96 rows=1 width=24) (actual rows= loops=)

  • Output: vc.platcreatetime, vc.platupdatetime, vc.platcreateop, vc.platupdateop, vc.platstatus, vc.id, vc.customerid, vc.customertype, vc.status, vc.userid, vc.visitdate, vc.isplan, vc.workid, vc.createtime, vc.updatetime
  • Index Cond: (vc.customerid = p.customerid)
  • Filter: ((vc.visitdate >= '2020-05-19'::date) AND (vc.visitdate <= '2020-05-19'::date) AND (vc.userid = '1211917550779240448'::bigint) AND (vc.platstatus = 1) AND (vc.userid = p.userid) AND (vc.customertype = p.customertype))
12. 0.000 0.000 ↓ 0.0

Append (cost=221.18..126,637.63 rows=3 width=196) (actual rows= loops=)

13. 0.000 0.000 ↓ 0.0

Subquery Scan on *SELECT* 1 (cost=221.18..126,115.73 rows=1 width=196) (actual rows= loops=)

  • Output: "*SELECT* 1".id, "*SELECT* 1".customername, "*SELECT* 1".store_channeltype, "*SELECT* 1".storelevelname, "*SELECT* 1".address, "*SELECT* 1".status
  • Filter: (p.customerid = "*SELECT* 1".id)
14. 0.000 0.000 ↓ 0.0

Hash Left Join (cost=221.18..123,944.70 rows=173,682 width=1,672) (actual rows= loops=)

  • Output: ks.id, NULL::character varying(200), ks.storename, NULL::character varying(200), NULL::bigint, NULL::character varying(1000), f_sfa_split_salearea(ps.fullname, 3, 1), NULL::character varying, pd2.dicvalue, pd3.dicvalue, NULL::bigint, ks.status, ks.address, NULL::bigint, NULL::bigint, NULL::timestamp(0) without time zone, NULL::timestamp(0) without time zone, NULL::integer, NULL::bigint
  • Hash Cond: (ks.storelevel = pd3.dickey)
15. 0.000 0.000 ↓ 0.0

Hash Left Join (cost=183.26..79,834.97 rows=173,682 width=256) (actual rows= loops=)

  • Output: ks.id, ks.storename, ks.status, ks.address, ks.storelevel, ps.fullname, pd2.dicvalue
  • Hash Cond: (ks.storetype = pd2.dickey)
16. 0.000 0.000 ↓ 0.0

Hash Left Join (cost=145.34..77,408.92 rows=173,682 width=250) (actual rows= loops=)

  • Output: ks.id, ks.storename, ks.status, ks.address, ks.storetype, ks.storelevel, ps.fullname
  • Hash Cond: (ks.channeltype = pd1.dickey)
17. 0.000 0.000 ↓ 0.0

Hash Left Join (cost=107.42..74,982.87 rows=173,682 width=258) (actual rows= loops=)

  • Output: ks.id, ks.storename, ks.status, ks.address, ks.channeltype, ks.storetype, ks.storelevel, ps.fullname
  • Inner Unique: true
  • Hash Cond: (ks.seleareaid = ps.orgstructid)
18. 0.000 0.000 ↓ 0.0

Seq Scan on public.kx_kq_store ks (cost=0.00..74,418.43 rows=173,682 width=200) (actual rows= loops=)

  • Output: ks.platcreatetime, ks.platupdatetime, ks.platcreateop, ks.platupdateop, ks.platstatus, ks.id, ks.storecode, ks.storename, ks.storeshortname, ks.seleareaid, ks.channeltype, ks.storetype, ks.storelevel, ks.contactname, ks.contactphone, ks.contactposition, ks.address, ks.regionid, ks.officephone, ks.iskeystore, ks.remark, ks.status, ks.longitude, ks.latitude, ks.presentative, ks.brand, ks.category, ks.supplier, ks.recentvisittime, ks.supplymode, ks.kaid, ks.customertype, ks.createop, ks.createtime, ks.updatetime, ks.updateop, ks.isvalidaddress, ks.validlocationstatus, ks.kaname, ks.disabletime, ks.approvestatus, ks.auto_tags, ks.manual_tags, ks.distributor, ks.pmmrepresentative, ks.approvetime, ks.kxsaleareaid, ks.isself, ks.tn_iskastore, ks.tn_erp_systemcode, ks.tn_historycode, ks.tn_isdirectsale, ks.tn_recenthelpvisittime, ks.tn_locationtype, ks.tn_storearea, ks.tn_displaypic, ks.tn_gatepic, ks.tn_approval_comments
  • Filter: ((ks.platstatus = 1) AND ((ks.customertype = '905324680615956480'::bigint) OR (ks.customertype = '905324761813487616'::bigint)))
19. 0.000 0.000 ↓ 0.0

Hash (cost=85.52..85.52 rows=1,752 width=74) (actual rows= loops=)

  • Output: ps.fullname, ps.orgstructid
20. 0.000 0.000 ↓ 0.0

Seq Scan on public.pl_orgstruct ps (cost=0.00..85.52 rows=1,752 width=74) (actual rows= loops=)

  • Output: ps.fullname, ps.orgstructid
21. 0.000 0.000 ↓ 0.0

Hash (cost=28.52..28.52 rows=752 width=8) (actual rows= loops=)

  • Output: pd1.dickey
22. 0.000 0.000 ↓ 0.0

Seq Scan on public.pl_dictionary pd1 (cost=0.00..28.52 rows=752 width=8) (actual rows= loops=)

  • Output: pd1.dickey
23. 0.000 0.000 ↓ 0.0

Hash (cost=28.52..28.52 rows=752 width=22) (actual rows= loops=)

  • Output: pd2.dicvalue, pd2.dickey
24. 0.000 0.000 ↓ 0.0

Seq Scan on public.pl_dictionary pd2 (cost=0.00..28.52 rows=752 width=22) (actual rows= loops=)

  • Output: pd2.dicvalue, pd2.dickey
25. 0.000 0.000 ↓ 0.0

Hash (cost=28.52..28.52 rows=752 width=22) (actual rows= loops=)

  • Output: pd3.dicvalue, pd3.dickey
26. 0.000 0.000 ↓ 0.0

Seq Scan on public.pl_dictionary pd3 (cost=0.00..28.52 rows=752 width=22) (actual rows= loops=)

  • Output: pd3.dicvalue, pd3.dickey
27. 0.000 0.000 ↓ 0.0

Subquery Scan on *SELECT* 2_1 (cost=145.34..513.73 rows=1 width=215) (actual rows= loops=)

  • Output: "*SELECT* 2_1".id, "*SELECT* 2_1".customername, "*SELECT* 2_1".store_channeltype, "*SELECT* 2_1".storelevelname, "*SELECT* 2_1".address, "*SELECT* 2_1".status
  • Filter: (p.customerid = "*SELECT* 2_1".id)
28. 0.000 0.000 ↓ 0.0

Hash Left Join (cost=145.34..504.10 rows=770 width=1,691) (actual rows= loops=)

  • Output: kc.id, NULL::character varying(200), kc.channelname, NULL::character varying(200), NULL::bigint, NULL::character varying(1000), f_sfa_split_salearea(ps_1.fullname, 3, 1), NULL::character varying, pd1_1.dicvalue, ''::character varying, NULL::bigint, kc.status, kc.address, NULL::bigint, NULL::bigint, NULL::timestamp(0) without time zone, NULL::timestamp(0) without time zone, NULL::integer, NULL::bigint
  • Hash Cond: (kc.channeltype = pd1_1.dickey)
29. 0.000 0.000 ↓ 0.0

Hash Left Join (cost=107.42..264.92 rows=770 width=243) (actual rows= loops=)

  • Output: kc.id, kc.channelname, kc.status, kc.address, kc.channeltype, ps_1.fullname
  • Inner Unique: true
  • Hash Cond: (kc.saleareaid = ps_1.orgstructid)
30. 0.000 0.000 ↓ 0.0

Seq Scan on public.ka_kq_channelcustomers kc (cost=0.00..155.47 rows=770 width=185) (actual rows= loops=)

  • Output: kc.platcreatetime, kc.platupdatetime, kc.platcreateop, kc.platupdateop, kc.platstatus, kc.channelcode, kc.channelname, kc.saleareaid, kc.contactname, kc.contactphone, kc.contactposition, kc.regionid, kc.address, kc.officephone, kc.fax, kc.email, kc.cooperationtime, kc.remark, kc.status, kc.superiorcustomer, kc.recentvisittime, kc.brand, kc.category, kc.supplier, kc.bizmanager, kc.id, kc.channeltype, kc.customertype, kc.createtime, kc.updatetime, kc.createop, kc.updateop, kc.validlocationstatus, kc.superiorcustomername, kc.supplymode, kc.disabletime, kc.maxaccountnum, kc.cooperativestatus, kc.ishaveadmiaccount, kc.approvestatus, kc.approvetime, kc.kxsaleareaid, kc.channel_archives_id, kc.channel_operatorinfo_id, kc.authorizationinfo_id, kc.channel_addressinfo_id
  • Filter: ((kc.platstatus = 1) AND ((kc.customertype = '905324680615956480'::bigint) OR (kc.customertype = '905324761813487616'::bigint)))
31. 0.000 0.000 ↓ 0.0

Hash (cost=85.52..85.52 rows=1,752 width=74) (actual rows= loops=)

  • Output: ps_1.fullname, ps_1.orgstructid
32. 0.000 0.000 ↓ 0.0

Seq Scan on public.pl_orgstruct ps_1 (cost=0.00..85.52 rows=1,752 width=74) (actual rows= loops=)

  • Output: ps_1.fullname, ps_1.orgstructid
33. 0.000 0.000 ↓ 0.0

Hash (cost=28.52..28.52 rows=752 width=22) (actual rows= loops=)

  • Output: pd1_1.dicvalue, pd1_1.dickey
34. 0.000 0.000 ↓ 0.0

Seq Scan on public.pl_dictionary pd1_1 (cost=0.00..28.52 rows=752 width=22) (actual rows= loops=)

  • Output: pd1_1.dicvalue, pd1_1.dickey
35. 0.000 0.000 ↓ 0.0

Index Scan using kx_kq_ka_pkey on public.kx_kq_ka ka (cost=0.14..8.17 rows=1 width=91) (actual rows= loops=)

  • Output: ka.id, ka.kasystemname, ''::character varying, ''::character varying, ka.address, ka.status
  • Index Cond: (ka.id = p.customerid)
  • Filter: ((ka.platstatus = 1) AND ((ka.customertype = '905324680615956480'::bigint) OR (ka.customertype = '905324761813487616'::bigint)))
36.          

SubPlan (for Nested Loop)

37. 0.000 0.000 ↓ 0.0

Limit (cost=13.62..13.63 rows=1 width=12) (actual rows= loops=)

  • Output: vc_2.status, vc_2.updatetime
38. 0.000 0.000 ↓ 0.0

Sort (cost=13.62..13.63 rows=1 width=12) (actual rows= loops=)

  • Output: vc_2.status, vc_2.updatetime
  • Sort Key: vc_2.updatetime DESC
39. 0.000 0.000 ↓ 0.0

Bitmap Heap Scan on public.kx_visit_customerstatus vc_2 (cost=9.59..13.61 rows=1 width=12) (actual rows= loops=)

  • Output: vc_2.status, vc_2.updatetime
  • Recheck Cond: ((vc_2.customerid = p.customerid) AND (vc_2.userid = p.userid))