explain.depesz.com

PostgreSQL's explain analyze made readable

Result: JsBJ

Settings
# exclusive inclusive rows x rows loops node
1. 1.193 11,018.324 ↑ 1.0 1 1

Aggregate (cost=1,544.01..1,544.02 rows=1 width=8) (actual time=11,018.324..11,018.324 rows=1 loops=1)

  • Output: count(*)
2. 58.886 11,017.131 ↓ 2,896.5 5,793 1

Nested Loop Left Join (cost=1.50..1,544.01 rows=2 width=0) (actual time=7,389.746..11,017.131 rows=5,793 loops=1)

3. 5.579 10,958.245 ↓ 2,896.5 5,793 1

Nested Loop Left Join (cost=1.22..1,543.39 rows=2 width=8) (actual time=7,389.743..10,958.245 rows=5,793 loops=1)

  • Output: ks.storelevel
4. 46.824 10,888.943 ↓ 2,896.5 5,793 1

Nested Loop Left Join (cost=0.94..1,542.78 rows=2 width=16) (actual time=7,389.737..10,888.943 rows=5,793 loops=1)

  • Output: ks.storetype, ks.storelevel
5. 262.441 10,830.533 ↓ 2,896.5 5,793 1

Nested Loop (cost=0.67..1,542.17 rows=2 width=24) (actual time=7,389.726..10,830.533 rows=5,793 loops=1)

  • Output: ks.channeltype, ks.storetype, ks.storelevel
  • Inner Unique: true
6. 7,468.247 7,468.247 ↓ 19,999.0 99,995 1

Function Scan on public.f_get_stores_info i (cost=0.25..12.75 rows=5 width=8) (actual time=7,389.696..7,468.247 rows=99,995 loops=1)

  • Output: i.o_id, i.o_distance, i.platstatus
  • Function Call: f_get_stores_info('{"kaid": null, "range": null, "status": 1, "latitude": "34.31119155883789", "regionid": null, "auto_tags": "", "longitude": "109.076416015625", "storecode": "", "storename": "", "storetype": null, "__metaname": "kx_kq_store", "seleareaid": null, "storelevel": null, "channeltype": null, "manual_tags": "", "nowdatetime": "2020-05-21 00:00:00", "presentative": "", "approvestatus": null, "kaid__kasystemname": "", "storetype__keypath": "", "storelevel__keypath": "", "validlocationstatus": null, "seleareaid__codepath": ""}'::jsonb, 100000, 0)
  • Filter: (i.platstatus = 1)
  • Rows Removed by Filter: 5
7. 1,599.920 3,099.845 ↓ 0.0 0 99,995

Index Scan using kx_kq_store_pkey on public.kx_kq_store ks (cost=0.42..305.88 rows=1 width=48) (actual time=0.031..0.031 rows=0 loops=99,995)

  • 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
  • Index Cond: (ks.id = i.o_id)
  • Filter: ((ks.status = 1) AND (ks.platstatus = 1) AND (SubPlan 2))
  • Rows Removed by Filter: 1
8.          

SubPlan (for Index Scan)

9. 102.179 1,499.925 ↓ 0.0 0 99,995

Append (cost=0.42..891.48 rows=3 width=4) (actual time=0.015..0.015 rows=0 loops=99,995)

10. 1,099.945 1,099.945 ↓ 0.0 0 99,995

Index Scan using ix_kx_kq_storerepresentative_storeid on public.kx_kq_storerepresentative _sp (cost=0.42..8.44 rows=1 width=4) (actual time=0.011..0.011 rows=0 loops=99,995)

  • Output: 1
  • Index Cond: (_sp.storeid = ks.id)
  • Filter: ((_sp.platstatus = 1) AND (_sp.representativeid = '1187099276988256256'::bigint))
  • Rows Removed by Filter: 1
11. 198.534 297.801 ↓ 0.0 0 99,267

Subquery Scan on _b (cost=875.54..883.00 rows=2 width=4) (actual time=0.003..0.003 rows=0 loops=99,267)

  • Output: 1
12. 99.267 99.267 ↓ 0.0 0 99,267

CTE Scan on _r _r_1 (cost=875.54..882.98 rows=2 width=8) (actual time=0.001..0.001 rows=0 loops=99,267)

  • Output: NULL::bigint
  • Filter: (_r_1.orgstructid = ks.seleareaid)
  • Rows Removed by Filter: 1
13.          

CTE _r

14. 0.003 1.015 ↑ 331.0 1 1

Recursive Union (cost=0.00..875.54 rows=331 width=8) (actual time=0.064..1.015 rows=1 loops=1)

15. 0.238 0.238 ↑ 1.0 1 1

Seq Scan on public.pl_saleorg_resp _s (cost=0.00..34.53 rows=1 width=8) (actual time=0.062..0.238 rows=1 loops=1)

  • Output: _s.saleorgid
  • Filter: ((_s.platstatus = 1) AND (_s.memberid = '1187099276988256256'::bigint))
  • Rows Removed by Filter: 1301
16. 0.006 0.774 ↓ 0.0 0 1

Hash Join (cost=1.65..83.44 rows=33 width=8) (actual time=0.774..0.774 rows=0 loops=1)

  • Output: _a.orgstructid
  • Inner Unique: true
  • Hash Cond: (_a.orgtypeid = _t.orgtypeid)
17. 0.325 0.754 ↑ 8.8 6 1

Hash Join (cost=0.33..81.94 rows=53 width=16) (actual time=0.159..0.754 rows=6 loops=1)

  • Output: _a.orgstructid, _a.orgtypeid
  • Hash Cond: (_a.parentorgstructid = _r.orgstructid)
18. 0.425 0.425 ↑ 1.0 1,679 1

Seq Scan on public.pl_orgstruct _a (cost=0.00..74.79 rows=1,679 width=24) (actual time=0.008..0.425 rows=1,679 loops=1)

  • Output: _a.orgstructid, _a.parentorgstructid, _a.orgname, _a.status, _a.orgstructeffecttime, _a.orgstructexpiretime, _a.orgstructtypeid, _a.userinfoid, _a.positionid, _a.parentpositionid, _a.orgid, _a.orgtypeid, _a.orgstructdescr, _a.codepath, _a.createtime, _a.createop, _a.updatetime, _a.updateop, _a.fullname, _a.seq, _a.platcreatetime, _a.platupdatetime, _a.platcreateop, _a.platupdateop, _a.platstatus, _a.level, _a.parentmemberid
19. 0.003 0.004 ↑ 10.0 1 1

Hash (cost=0.20..0.20 rows=10 width=8) (actual time=0.004..0.004 rows=1 loops=1)

  • Output: _r.orgstructid
  • Buckets: 1024 Batches: 1 Memory Usage: 9kB
20. 0.001 0.001 ↑ 10.0 1 1

WorkTable Scan on _r (cost=0.00..0.20 rows=10 width=8) (actual time=0.001..0.001 rows=1 loops=1)

  • Output: _r.orgstructid
21. 0.004 0.014 ↑ 1.0 10 1

Hash (cost=1.20..1.20 rows=10 width=8) (actual time=0.014..0.014 rows=10 loops=1)

  • Output: _t.orgtypeid
  • Buckets: 1024 Batches: 1 Memory Usage: 9kB
22. 0.010 0.010 ↑ 1.0 10 1

Seq Scan on public.pl_orgtype _t (cost=0.00..1.20 rows=10 width=8) (actual time=0.007..0.010 rows=10 loops=1)

  • Output: _t.orgtypeid
  • Filter: (_t.orgtypecategory = 1)
  • Rows Removed by Filter: 6
23. 11.586 11.586 ↑ 1.0 1 5,793

Index Scan using idx_pl_dictionary_dickey on public.pl_dictionary ct (cost=0.28..0.30 rows=1 width=8) (actual time=0.002..0.002 rows=1 loops=5,793)

  • Output: ct.dictionaryid, ct.parentdictionaryid, ct.dictionarycode, ct.dickey, ct.dicvalue, ct.dicvalue2, ct.dicvalue3, ct.keypath, ct.nodetype, ct.canselect, ct.remark, ct.seq, ct.status, ct.createop, ct.updateop, ct.createtime, ct.updatetime, ct.parentdickey, ct.platcreatetime, ct.platupdatetime, ct.platcreateop, ct.platupdateop, ct.platstatus, ct.lookup, ct.dicvalue4, ct.dickeycode, ct.namepath, ct.level, ct.code
  • Index Cond: (ks.channeltype = ct.dickey)
  • Filter: (ct.platstatus = 1)
24. 63.723 63.723 ↑ 1.0 1 5,793

Index Scan using idx_pl_dictionary_dickey on public.pl_dictionary st (cost=0.28..0.30 rows=1 width=8) (actual time=0.011..0.011 rows=1 loops=5,793)

  • Output: st.dictionaryid, st.parentdictionaryid, st.dictionarycode, st.dickey, st.dicvalue, st.dicvalue2, st.dicvalue3, st.keypath, st.nodetype, st.canselect, st.remark, st.seq, st.status, st.createop, st.updateop, st.createtime, st.updatetime, st.parentdickey, st.platcreatetime, st.platupdatetime, st.platcreateop, st.platupdateop, st.platstatus, st.lookup, st.dicvalue4, st.dickeycode, st.namepath, st.level, st.code
  • Index Cond: (ks.storetype = st.dickey)
  • Filter: (st.platstatus = 1)
25. 0.000 0.000 ↓ 0.0 0 5,793

Index Scan using idx_pl_dictionary_dickey on public.pl_dictionary sle (cost=0.28..0.30 rows=1 width=8) (actual time=0.000..0.000 rows=0 loops=5,793)

  • Output: sle.dictionaryid, sle.parentdictionaryid, sle.dictionarycode, sle.dickey, sle.dicvalue, sle.dicvalue2, sle.dicvalue3, sle.keypath, sle.nodetype, sle.canselect, sle.remark, sle.seq, sle.status, sle.createop, sle.updateop, sle.createtime, sle.updatetime, sle.parentdickey, sle.platcreatetime, sle.platupdatetime, sle.platcreateop, sle.platupdateop, sle.platstatus, sle.lookup, sle.dicvalue4, sle.dickeycode, sle.namepath, sle.level, sle.code
  • Index Cond: (ks.storelevel = sle.dickey)
  • Filter: (sle.platstatus = 1)