explain.depesz.com

PostgreSQL's explain analyze made readable

Result: IhqTX : Optimization for: plan #gKKW

Settings

Optimization path:

# exclusive inclusive rows x rows loops node
1. 0.005 14,494.873 ↓ 25.5 51 1

Limit (cost=1,387,592.76..1,387,592.76 rows=2 width=1,506) (actual time=14,494.863..14,494.873 rows=51 loops=1)

2. 0.124 14,494.868 ↓ 25.5 51 1

Sort (cost=1,387,592.76..1,387,592.76 rows=2 width=1,506) (actual time=14,494.862..14,494.868 rows=51 loops=1)

  • Sort Key: ((ml.seq + 0)), ml.customername
  • Sort Method: quicksort Memory: 51kB
3. 0.019 14,494.744 ↓ 25.5 51 1

Subquery Scan on ml (cost=1,387,592.61..1,387,592.75 rows=2 width=1,506) (actual time=14,494.702..14,494.744 rows=51 loops=1)

4. 0.026 14,494.725 ↓ 25.5 51 1

Unique (cost=1,387,592.61..1,387,592.72 rows=2 width=1,498) (actual time=14,494.699..14,494.725 rows=51 loops=1)

5. 0.278 14,494.699 ↓ 25.5 51 1

Sort (cost=1,387,592.61..1,387,592.61 rows=2 width=1,498) (actual time=14,494.697..14,494.699 rows=51 loops=1)

  • Sort Key: p.tn_source, "*SELECT* 1".id, "*SELECT* 1".customername, (CASE WHEN (p.customertype = '905324761813487616'::bigint) THEN kc.contactname WHEN (p.customertype = '905324680615956480'::bigint) THEN "*SELECT* 1".contactname ELSE NULL::character varying END), ((to_date(to_char((CURRENT_DATE)::timestamp with time zone, 'yyyy-mm-dd'::text), 'yyyy-mm-dd'::text) - to_date(to_char("*SELECT* 1".recentvisittime, 'yyyy-mm-dd'::text), 'yyyy-mm-dd'::text))), tva.tn_times, (CASE WHEN ((count((count(kvw.customerid)))) IS NULL) THEN '0'::bigint ELSE (count((count(kvw.customerid)))) END), "*SELECT* 1".store_channeltype, "*SELECT* 1".store_channeltype, "*SELECT* 1".storelevelname, "*SELECT* 1".address, "*SELECT* 1".status, p.plandate, p.userid, pu.userinfoname, p.customertype, p.seq, a.actualvisittime, a.visittype, vc.status, vc.isplan, kc.tn_isdistributor
  • Sort Method: quicksort Memory: 51kB
6. 0.051 14,494.421 ↓ 25.5 51 1

Append (cost=662,504.18..1,387,592.60 rows=2 width=1,498) (actual time=5,848.581..14,494.421 rows=51 loops=1)

7. 2.795 8,267.755 ↓ 44.0 44 1

Merge Left Join (cost=662,504.18..694,221.73 rows=1 width=354) (actual time=5,848.580..8,267.755 rows=44 loops=1)

  • Merge Cond: ("*SELECT* 1".id = kvw.customerid)
8. 0.134 2,428.018 ↓ 44.0 44 1

Nested Loop Left Join (cost=1,173.45..12,222.75 rows=1 width=322) (actual time=56.248..2,428.018 rows=44 loops=1)

9. 0.150 2,427.488 ↓ 44.0 44 1

Nested Loop Left Join (cost=1,173.17..12,222.44 rows=1 width=322) (actual time=56.208..2,427.488 rows=44 loops=1)

10. 0.142 2,426.986 ↓ 44.0 44 1

Nested Loop Left Join (cost=1,172.89..12,214.13 rows=1 width=321) (actual time=56.190..2,426.986 rows=44 loops=1)

  • Join Filter: (po.orgstructid = p.userid)
11. 0.154 2,426.448 ↓ 44.0 44 1

Nested Loop Left Join (cost=1,172.61..12,205.82 rows=1 width=313) (actual time=56.166..2,426.448 rows=44 loops=1)

12. 0.264 2,423.786 ↓ 44.0 44 1

Nested Loop Left Join (cost=1,172.18..12,165.42 rows=1 width=311) (actual time=56.014..2,423.786 rows=44 loops=1)

13. 0.613 2,423.038 ↓ 44.0 44 1

Nested Loop (cost=1,171.90..12,157.11 rows=1 width=299) (actual time=55.996..2,423.038 rows=44 loops=1)

  • Join Filter: (p.customerid = "*SELECT* 1".id)
  • Rows Removed by Join Filter: 1892
14. 0.102 1.149 ↓ 44.0 44 1

Merge Left Join (cost=16.79..16.82 rows=1 width=48) (actual time=1.002..1.149 rows=44 loops=1)

  • Merge Cond: ((p.customerid = vc.customerid) AND (p.customertype = vc.customertype))
  • Join Filter: (vc.userid = p.userid)
15. 0.075 0.169 ↓ 44.0 44 1

Sort (cost=8.33..8.33 rows=1 width=40) (actual time=0.126..0.169 rows=44 loops=1)

  • Sort Key: p.customerid, p.customertype
  • Sort Method: quicksort Memory: 28kB
16. 0.094 0.094 ↓ 44.0 44 1

Index Scan using idx_kx_visit_planvisit_plandate on kx_visit_planvisit p (cost=0.29..8.32 rows=1 width=40) (actual time=0.076..0.094 rows=44 loops=1)

  • Index Cond: ((plandate >= '2020-03-19'::date) AND (plandate <= '2020-03-19'::date))
  • Filter: ((userid = '1141896956096942080'::bigint) AND (platstatus = 1))
  • Rows Removed by Filter: 160
17. 0.017 0.878 ↓ 15.0 15 1

Sort (cost=8.46..8.47 rows=1 width=32) (actual time=0.873..0.878 rows=15 loops=1)

  • Sort Key: vc.customerid, vc.customertype
  • Sort Method: quicksort Memory: 26kB
18. 0.861 0.861 ↓ 15.0 15 1

Index Scan using idx_kx_visit_customerstatus_visitdate on kx_visit_customerstatus vc (cost=0.43..8.45 rows=1 width=32) (actual time=0.060..0.861 rows=15 loops=1)

  • Index Cond: ((visitdate >= '2020-03-19'::date) AND (visitdate <= '2020-03-19'::date))
  • Filter: ((userid = '1141896956096942080'::bigint) AND (platstatus = 1))
  • Rows Removed by Filter: 1378
19. 404.666 2,421.276 ↑ 2.0 44 44

Hash Right Join (cost=1,155.11..12,139.18 rows=89 width=251) (actual time=31.514..55.029 rows=44 loops=44)

  • Hash Cond: ((tvd.tn_leveldevision = "*SELECT* 1".tn_storedevicion) AND (tvd.tn_storelevel = "*SELECT* 1".storelevel) AND (tvd.tn_area = "*SELECT* 1".saleareaid))
20. 2,011.020 2,011.020 ↓ 1.1 100,504 44

Seq Scan on tn_visit_detail tvd (cost=0.00..9,992.94 rows=88,100 width=32) (actual time=26.894..45.705 rows=100,504 loops=44)

  • Filter: ((tn_start <= '2020-03-19 11:44:24'::timestamp without time zone) AND (tn_end >= '2020-03-19 11:44:24'::timestamp without time zone) AND (platstatus = 1))
  • Rows Removed by Filter: 191624
21. 0.042 5.590 ↑ 2.0 44 1

Hash (cost=1,153.55..1,153.55 rows=89 width=268) (actual time=5.590..5.590 rows=44 loops=1)

  • Buckets: 1024 Batches: 1 Memory Usage: 21kB
22. 0.006 5.548 ↑ 2.0 44 1

Append (cost=585.49..1,153.55 rows=89 width=268) (actual time=4.840..5.548 rows=44 loops=1)

23. 0.011 5.362 ↑ 1.0 44 1

Subquery Scan on *SELECT* 1 (cost=585.49..626.36 rows=44 width=250) (actual time=4.839..5.362 rows=44 loops=1)

24. 1.615 5.351 ↑ 1.0 44 1

Hash Left Join (cost=585.49..625.92 rows=44 width=1,722) (actual time=4.838..5.351 rows=44 loops=1)

  • Hash Cond: (ks.storetype = pd2.dickey)
25. 0.050 3.485 ↑ 1.0 44 1

Hash Left Join (cost=554.62..583.44 rows=44 width=324) (actual time=3.447..3.485 rows=44 loops=1)

  • Hash Cond: (ks.seleareaid = ps.orgstructid)
26. 0.073 1.446 ↑ 1.0 44 1

Hash Right Join (cost=378.05..406.76 rows=44 width=246) (actual time=1.421..1.446 rows=44 loops=1)

  • Hash Cond: (pd1.dickey = ks.channeltype)
27. 0.243 0.243 ↓ 1.1 460 1

Seq Scan on pl_dictionary pd1 (cost=0.00..25.44 rows=435 width=8) (actual time=0.007..0.243 rows=460 loops=1)

  • Filter: (platstatus = 1)
28. 0.041 1.130 ↑ 1.0 44 1

Hash (cost=377.50..377.50 rows=44 width=254) (actual time=1.130..1.130 rows=44 loops=1)

  • Buckets: 1024 Batches: 1 Memory Usage: 21kB
29. 0.027 1.089 ↑ 1.0 44 1

Merge Right Join (cost=376.57..377.50 rows=44 width=254) (actual time=1.062..1.089 rows=44 loops=1)

  • Merge Cond: (pd3.dickey = ks.storelevel)
30. 0.134 0.134 ↑ 5.8 75 1

Index Scan using idx_pl_dictionary_dickey on pl_dictionary pd3 (cost=0.27..58.56 rows=435 width=20) (actual time=0.036..0.134 rows=75 loops=1)

  • Filter: (platstatus = 1)
31. 0.059 0.928 ↑ 1.0 44 1

Sort (cost=366.47..366.58 rows=44 width=242) (actual time=0.924..0.928 rows=44 loops=1)

  • Sort Key: ks.storelevel
  • Sort Method: quicksort Memory: 47kB
32. 0.869 0.869 ↑ 1.0 44 1

Index Scan using kx_kq_store_pkey on kx_kq_store ks (cost=0.42..365.27 rows=44 width=242) (actual time=0.039..0.869 rows=44 loops=1)

  • Index Cond: (id = ANY ('{1143719072949735424,1146248587626287104,1143747364620734464,1168739460070903808,1193089498376966144,1181762607237959680,1181766424218177536,1153669655429255168,1143757970706927616,1153672085181173760,1153668264887455744,1146252216273866752,1169175771118440448,1143747884458577920,1153572438336999424,1178151430628970496,1145527366819385344,1174220717374443520,1145526489563926528,1146253444861005824,1152412801990529024,1151692483076427776,1152411965247852544,1146247889102704640,1146247021267652608,1178157951328849920,1143761143400108032,1152413255315099648,1168743866799624192,1181763474636804096,1143735965899165696,1153574978705297408,1188003950582960128,1150713023149772800,1181881025056346112,1203258489686855680,1156036613806100480,1143775915944316928,1153671560066895872,1192643434595356672,1153669996531027968,1153572867770814464,1169188008906330112,1153571908093087744}'::bigint[]))
  • Filter: ((platstatus = 1) AND ((customertype = '905324680615956480'::bigint) OR (customertype = '905324761813487616'::bigint)))
33. 0.389 1.989 ↓ 1.0 2,420 1

Hash (cost=147.29..147.29 rows=2,342 width=86) (actual time=1.989..1.989 rows=2,420 loops=1)

  • Buckets: 4096 Batches: 1 Memory Usage: 150kB
34. 1.600 1.600 ↓ 1.0 2,420 1

Seq Scan on pl_orgstruct ps (cost=0.00..147.29 rows=2,342 width=86) (actual time=0.012..1.600 rows=2,420 loops=1)

  • Filter: (platstatus = 1)
  • Rows Removed by Filter: 81
35. 0.083 0.251 ↓ 1.1 460 1

Hash (cost=25.44..25.44 rows=435 width=20) (actual time=0.251..0.251 rows=460 loops=1)

  • Buckets: 1024 Batches: 1 Memory Usage: 33kB
36. 0.168 0.168 ↓ 1.1 460 1

Seq Scan on pl_dictionary pd2 (cost=0.00..25.44 rows=435 width=20) (actual time=0.006..0.168 rows=460 loops=1)

  • Filter: (platstatus = 1)
37. 0.000 0.177 ↓ 0.0 0 1

Subquery Scan on *SELECT* 2_1 (cost=340.32..515.00 rows=44 width=269) (actual time=0.177..0.177 rows=0 loops=1)

38. 0.000 0.177 ↓ 0.0 0 1

Hash Left Join (cost=340.32..514.56 rows=44 width=1,741) (actual time=0.177..0.177 rows=0 loops=1)

  • Hash Cond: (kc_1.channelcustomersort = pd1_1.dickey)
39. 0.001 0.177 ↓ 0.0 0 1

Hash Left Join (cost=309.44..472.08 rows=44 width=295) (actual time=0.177..0.177 rows=0 loops=1)

  • Hash Cond: (kc_1.saleareaid = ps_1.orgstructid)
40. 0.006 0.176 ↓ 0.0 0 1

Bitmap Heap Scan on ka_kq_channelcustomers kc_1 (cost=132.88..295.40 rows=44 width=217) (actual time=0.176..0.176 rows=0 loops=1)

  • Recheck Cond: (id = ANY ('{1143719072949735424,1146248587626287104,1143747364620734464,1168739460070903808,1193089498376966144,1181762607237959680,1181766424218177536,1153669655429255168,1143757970706927616,1153672085181173760,1153668264887455744,1146252216273866752,1169175771118440448,1143747884458577920,1153572438336999424,1178151430628970496,1145527366819385344,1174220717374443520,1145526489563926528,1146253444861005824,1152412801990529024,1151692483076427776,1152411965247852544,1146247889102704640,1146247021267652608,1178157951328849920,1143761143400108032,1152413255315099648,1168743866799624192,1181763474636804096,1143735965899165696,1153574978705297408,1188003950582960128,1150713023149772800,1181881025056346112,1203258489686855680,1156036613806100480,1143775915944316928,1153671560066895872,1192643434595356672,1153669996531027968,1153572867770814464,1169188008906330112,1153571908093087744}'::bigint[]))
  • Filter: ((platstatus = 1) AND ((customertype = '905324680615956480'::bigint) OR (customertype = '905324761813487616'::bigint)))
41. 0.170 0.170 ↓ 0.0 0 1

Bitmap Index Scan on ka_kq_channelcustomers_pkey (cost=0.00..132.87 rows=44 width=0) (actual time=0.170..0.170 rows=0 loops=1)

  • Index Cond: (id = ANY ('{1143719072949735424,1146248587626287104,1143747364620734464,1168739460070903808,1193089498376966144,1181762607237959680,1181766424218177536,1153669655429255168,1143757970706927616,1153672085181173760,1153668264887455744,1146252216273866752,1169175771118440448,1143747884458577920,1153572438336999424,1178151430628970496,1145527366819385344,1174220717374443520,1145526489563926528,1146253444861005824,1152412801990529024,1151692483076427776,1152411965247852544,1146247889102704640,1146247021267652608,1178157951328849920,1143761143400108032,1152413255315099648,1168743866799624192,1181763474636804096,1143735965899165696,1153574978705297408,1188003950582960128,1150713023149772800,1181881025056346112,1203258489686855680,1156036613806100480,1143775915944316928,1153671560066895872,1192643434595356672,1153669996531027968,1153572867770814464,1169188008906330112,1153571908093087744}'::bigint[]))
42. 0.000 0.000 ↓ 0.0 0

Hash (cost=147.29..147.29 rows=2,342 width=86) (never executed)

43. 0.000 0.000 ↓ 0.0 0

Seq Scan on pl_orgstruct ps_1 (cost=0.00..147.29 rows=2,342 width=86) (never executed)

  • Filter: (platstatus = 1)
44. 0.000 0.000 ↓ 0.0 0

Hash (cost=25.44..25.44 rows=435 width=20) (never executed)

45. 0.000 0.000 ↓ 0.0 0

Seq Scan on pl_dictionary pd1_1 (cost=0.00..25.44 rows=435 width=20) (never executed)

  • Filter: (platstatus = 1)
46. 0.001 0.003 ↓ 0.0 0 1

Subquery Scan on *SELECT* 3 (cost=0.00..12.19 rows=1 width=976) (actual time=0.003..0.003 rows=0 loops=1)

47. 0.002 0.002 ↓ 0.0 0 1

Seq Scan on kx_kq_ka ka (cost=0.00..12.18 rows=1 width=1,964) (actual time=0.002..0.002 rows=0 loops=1)

  • Filter: ((platstatus = 1) AND ((customertype = '905324680615956480'::bigint) OR (customertype = '905324761813487616'::bigint)) AND (id = ANY ('{1143719072949735424,1146248587626287104,1143747364620734464,1168739460070903808,1193089498376966144,1181762607237959680,1181766424218177536,1153669655429255168,1143757970706927616,1153672085181173760,1153668264887455744,1146252216273866752,1169175771118440448,1143747884458577920,1153572438336999424,1178151430628970496,1145527366819385344,1174220717374443520,1145526489563926528,1146253444861005824,1152412801990529024,1151692483076427776,1152411965247852544,1146247889102704640,1146247021267652608,1178157951328849920,1143761143400108032,1152413255315099648,1168743866799624192,1181763474636804096,1143735965899165696,1153574978705297408,1188003950582960128,1150713023149772800,1181881025056346112,1203258489686855680,1156036613806100480,1143775915944316928,1153671560066895872,1192643434595356672,1153669996531027968,1153572867770814464,1169188008906330112,1153571908093087744}'::bigint[])))
48. 0.484 0.484 ↓ 0.0 0 44

Index Scan using ka_kq_channelcustomers_pkey on ka_kq_channelcustomers kc (cost=0.29..8.30 rows=1 width=20) (actual time=0.011..0.011 rows=0 loops=44)

  • Index Cond: (id = p.customerid)
  • Filter: (platstatus = 1)
49. 2.508 2.508 ↓ 0.0 0 44

Index Scan using idx_kx_visit_actual_customerid on kx_visit_actual a (cost=0.43..40.39 rows=1 width=26) (actual time=0.057..0.057 rows=0 loops=44)

  • Index Cond: (customerid = p.customerid)
  • Filter: ((actualvisittime >= '2020-03-19 00:00:00'::timestamp without time zone) AND (actualvisittime <= '2020-03-19 23:59:59'::timestamp without time zone) AND (userid = '1141896956096942080'::bigint) AND (platstatus = 1) AND (userid = p.userid))
  • Rows Removed by Filter: 7
50. 0.396 0.396 ↑ 1.0 1 44

Index Scan using ix_pl_orgstruct_orgstructid on pl_orgstruct po (cost=0.28..8.30 rows=1 width=16) (actual time=0.009..0.009 rows=1 loops=44)

  • Index Cond: (orgstructid = '1141896956096942080'::bigint)
  • Filter: (platstatus = 1)
51. 0.352 0.352 ↑ 1.0 1 44

Index Scan using pl_userinfo_pkey on pl_userinfo pu (cost=0.28..8.30 rows=1 width=17) (actual time=0.008..0.008 rows=1 loops=44)

  • Index Cond: (po.userinfoid = userinfoid)
  • Filter: (platstatus = 1)
52. 0.396 0.396 ↑ 1.0 1 44

Index Scan using pk_tn_visit_administration_1 on tn_visit_administration tva (cost=0.28..0.31 rows=1 width=16) (actual time=0.009..0.009 rows=1 loops=44)

  • Index Cond: (tn_id = tvd.tn_adminid)
  • Filter: (platstatus = 1)
53. 8.816 5,836.942 ↓ 135.9 27,172 1

GroupAggregate (cost=661,330.73..681,996.45 rows=200 width=16) (actual time=5,792.173..5,836.942 rows=27,172 loops=1)

  • Group Key: kvw.customerid
54. 21.668 5,828.126 ↑ 17.8 29,067 1

GroupAggregate (cost=661,330.73..674,245.55 rows=516,593 width=36) (actual time=5,792.165..5,828.126 rows=29,067 loops=1)

  • Group Key: kvw.customerid, kvw.recorddate, kvd2.tn_start, kvd2.tn_end
55. 83.730 5,806.458 ↑ 4.5 113,605 1

Sort (cost=661,330.73..662,622.21 rows=516,593 width=28) (actual time=5,792.156..5,806.458 rows=113,605 loops=1)

  • Sort Key: kvw.customerid, kvw.recorddate, kvd2.tn_start, kvd2.tn_end
  • Sort Method: external merge Disk: 4800kB
56. 137.356 5,722.728 ↑ 4.4 116,471 1

Hash Join (cost=567,749.02..599,947.49 rows=516,593 width=28) (actual time=5,446.512..5,722.728 rows=116,471 loops=1)

  • Hash Cond: (ste.storeid = kst.id)
57. 139.601 139.601 ↑ 1.0 425,856 1

Seq Scan on kx_kq_storerepresentative ste (cost=0.00..18,038.31 rows=441,395 width=8) (actual time=0.016..139.601 rows=425,856 loops=1)

  • Filter: (platstatus = 1)
  • Rows Removed by Filter: 166263
58. 28.090 5,445.771 ↑ 4.3 116,471 1

Hash (cost=557,638.67..557,638.67 rows=497,708 width=36) (actual time=5,445.771..5,445.771 rows=116,471 loops=1)

  • Buckets: 65536 Batches: 16 Memory Usage: 1034kB
59. 2,052.151 5,417.681 ↑ 4.3 116,471 1

Hash Join (cost=225,972.82..557,638.67 rows=497,708 width=36) (actual time=2,059.516..5,417.681 rows=116,471 loops=1)

  • Hash Cond: (kvw.customerid = kst.id)
  • Join Filter: ((kvd2.tn_start <= kvw.recorddate) AND (kvd2.tn_end >= kvw.recorddate))
  • Rows Removed by Join Filter: 5071371
60. 1,646.890 1,646.890 ↓ 1.0 5,508,981 1

Seq Scan on kx_visit_workrecord kvw (cost=0.00..189,671.24 rows=5,381,059 width=12) (actual time=0.013..1,646.890 rows=5,508,981 loops=1)

  • Filter: (platstatus = 1)
61. 98.958 1,718.640 ↓ 1.0 366,549 1

Hash (cost=219,472.82..219,472.82 rows=354,000 width=24) (actual time=1,718.640..1,718.640 rows=366,549 loops=1)

  • Buckets: 65536 Batches: 8 Memory Usage: 3020kB
62. 117.074 1,619.682 ↓ 1.0 366,549 1

Merge Join (cost=210,018.88..219,472.82 rows=354,000 width=24) (actual time=1,319.293..1,619.682 rows=366,549 loops=1)

  • Merge Cond: ((kvd2.tn_leveldevision = kst.tn_storedevicion) AND (kvd2.tn_area = kst.seleareaid) AND (kvd2.tn_storelevel = kst.storelevel))
63. 98.360 188.258 ↓ 1.1 100,504 1

Sort (cost=20,370.17..20,590.42 rows=88,100 width=40) (actual time=171.425..188.258 rows=100,504 loops=1)

  • Sort Key: kvd2.tn_leveldevision, kvd2.tn_area, kvd2.tn_storelevel
  • Sort Method: external merge Disk: 4928kB
64. 89.898 89.898 ↓ 1.1 100,504 1

Seq Scan on tn_visit_detail kvd2 (cost=0.00..10,722.64 rows=88,100 width=40) (actual time=42.276..89.898 rows=100,504 loops=1)

  • Filter: ((tn_end >= CURRENT_DATE) AND (tn_start <= CURRENT_DATE))
  • Rows Removed by Filter: 191624
65. 53.746 1,314.350 ↑ 1.1 404,048 1

Materialize (cost=189,648.69..191,773.00 rows=424,862 width=32) (actual time=1,147.848..1,314.350 rows=404,048 loops=1)

66. 384.215 1,260.604 ↑ 1.1 404,048 1

Sort (cost=189,648.69..190,710.84 rows=424,862 width=32) (actual time=1,147.842..1,260.604 rows=404,048 loops=1)

  • Sort Key: kst.tn_storedevicion, kst.seleareaid, kst.storelevel
  • Sort Method: external merge Disk: 17488kB
67. 876.389 876.389 ↓ 1.0 425,267 1

Seq Scan on kx_kq_store kst (cost=0.00..139,763.74 rows=424,862 width=32) (actual time=0.023..876.389 rows=425,267 loops=1)

  • Filter: (platstatus = 1)
  • Rows Removed by Filter: 371
68. 0.013 6,226.615 ↓ 7.0 7 1

Subquery Scan on *SELECT* 2 (cost=661,686.12..693,370.86 rows=1 width=403) (actual time=5,259.665..6,226.615 rows=7 loops=1)

69. 0.201 6,226.602 ↓ 7.0 7 1

Nested Loop Anti Join (cost=661,686.12..693,370.84 rows=1 width=399) (actual time=5,259.663..6,226.602 rows=7 loops=1)

  • Join Filter: ((kx_visit_planvisit.userid = p_1.userid) AND (kx_visit_planvisit.customerid = p_1.customerid))
  • Rows Removed by Join Filter: 308
70. 2.025 6,225.932 ↓ 7.0 7 1

Merge Left Join (cost=661,685.83..693,362.49 rows=1 width=344) (actual time=5,259.562..6,225.932 rows=7 loops=1)

  • Merge Cond: ("*SELECT* 1_1".id = kvw_1.customerid)
71. 0.034 943.398 ↓ 7.0 7 1

Nested Loop Left Join (cost=355.10..11,363.53 rows=1 width=336) (actual time=33.154..943.398 rows=7 loops=1)

72. 0.024 943.308 ↓ 7.0 7 1

Nested Loop Left Join (cost=354.82..11,363.22 rows=1 width=336) (actual time=33.142..943.308 rows=7 loops=1)

73. 0.022 943.214 ↓ 7.0 7 1

Nested Loop Left Join (cost=354.54..11,354.91 rows=1 width=335) (actual time=33.123..943.214 rows=7 loops=1)

  • Join Filter: (po_1.orgstructid = p_1.userid)
74. 0.043 943.122 ↓ 7.0 7 1

Nested Loop Left Join (cost=354.26..11,346.60 rows=1 width=327) (actual time=33.109..943.122 rows=7 loops=1)

75. 0.141 943.002 ↓ 7.0 7 1

Nested Loop (cost=353.98..11,338.29 rows=1 width=315) (actual time=33.090..943.002 rows=7 loops=1)

  • Join Filter: (p_1.customerid = "*SELECT* 1_1".id)
  • Rows Removed by Join Filter: 98
76. 0.099 1.401 ↓ 15.0 15 1

Merge Left Join (cost=16.93..16.95 rows=1 width=46) (actual time=1.271..1.401 rows=15 loops=1)

  • Merge Cond: (p_1.customerid = vc_1.customerid)
  • Join Filter: (vc_1.userid = p_1.userid)
77. 0.036 0.753 ↓ 15.0 15 1

Sort (cost=8.46..8.47 rows=1 width=38) (actual time=0.738..0.753 rows=15 loops=1)

  • Sort Key: p_1.customerid
  • Sort Method: quicksort Memory: 26kB
78. 0.717 0.717 ↓ 15.0 15 1

Index Scan using idx_kx_visit_actual_actualvisitdate on kx_visit_actual p_1 (cost=0.43..8.45 rows=1 width=38) (actual time=0.086..0.717 rows=15 loops=1)

  • Index Cond: ((actualvisitdate >= '2020-03-19'::date) AND (actualvisitdate <= '2020-03-19'::date))
  • Filter: ((userid = '1141896956096942080'::bigint) AND (platstatus = 1))
  • Rows Removed by Filter: 1378
79. 0.029 0.549 ↓ 15.0 15 1

Sort (cost=8.46..8.47 rows=1 width=24) (actual time=0.528..0.549 rows=15 loops=1)

  • Sort Key: vc_1.customerid
  • Sort Method: quicksort Memory: 26kB
80. 0.520 0.520 ↓ 15.0 15 1

Index Scan using idx_kx_visit_customerstatus_visitdate on kx_visit_customerstatus vc_1 (cost=0.43..8.45 rows=1 width=24) (actual time=0.024..0.520 rows=15 loops=1)

  • Index Cond: ((visitdate >= '2020-03-19'::date) AND (visitdate <= '2020-03-19'::date))
  • Filter: ((userid = '1141896956096942080'::bigint) AND (platstatus = 1))
  • Rows Removed by Filter: 1378
81. 163.743 941.460 ↑ 2.1 7 15

Hash Right Join (cost=337.05..11,321.15 rows=15 width=269) (actual time=35.327..62.764 rows=7 loops=15)

  • Hash Cond: ((tvd_1.tn_leveldevision = "*SELECT* 1_1".tn_storedevicion) AND (tvd_1.tn_storelevel = "*SELECT* 1_1".storelevel) AND (tvd_1.tn_area = "*SELECT* 1_1".saleareaid))
82. 776.985 776.985 ↓ 1.1 100,504 15

Seq Scan on tn_visit_detail tvd_1 (cost=0.00..9,992.94 rows=88,100 width=32) (actual time=29.712..51.799 rows=100,504 loops=15)

  • Filter: ((tn_start <= '2020-03-19 11:44:24'::timestamp without time zone) AND (tn_end >= '2020-03-19 11:44:24'::timestamp without time zone) AND (platstatus = 1))
  • Rows Removed by Filter: 191624
83. 0.007 0.732 ↑ 2.1 7 1

Hash (cost=336.79..336.79 rows=15 width=285) (actual time=0.731..0.732 rows=7 loops=1)

  • Buckets: 1024 Batches: 1 Memory Usage: 10kB
84. 0.002 0.725 ↑ 2.1 7 1

Append (cost=96.35..336.79 rows=15 width=285) (actual time=0.521..0.725 rows=7 loops=1)

85. 0.003 0.685 ↑ 1.0 7 1

Subquery Scan on *SELECT* 1_1 (cost=96.35..182.40 rows=7 width=238) (actual time=0.520..0.685 rows=7 loops=1)

86. 0.107 0.682 ↑ 1.0 7 1

Nested Loop Left Join (cost=96.35..182.33 rows=7 width=1,742) (actual time=0.519..0.682 rows=7 loops=1)

87. 0.051 0.554 ↑ 1.0 7 1

Hash Right Join (cost=96.07..126.48 rows=7 width=238) (actual time=0.455..0.554 rows=7 loops=1)

  • Hash Cond: (pd2_1.dickey = ks_1.storetype)
88. 0.132 0.132 ↓ 1.1 460 1

Seq Scan on pl_dictionary pd2_1 (cost=0.00..25.44 rows=435 width=20) (actual time=0.003..0.132 rows=460 loops=1)

  • Filter: (platstatus = 1)
89. 0.006 0.371 ↑ 1.0 7 1

Hash (cost=95.99..95.99 rows=7 width=234) (actual time=0.371..0.371 rows=7 loops=1)

  • Buckets: 1024 Batches: 1 Memory Usage: 10kB
90. 0.054 0.365 ↑ 1.0 7 1

Hash Right Join (cost=68.91..95.99 rows=7 width=234) (actual time=0.360..0.365 rows=7 loops=1)

  • Hash Cond: (pd1_2.dickey = ks_1.channeltype)
91. 0.154 0.154 ↓ 1.1 460 1

Seq Scan on pl_dictionary pd1_2 (cost=0.00..25.44 rows=435 width=8) (actual time=0.011..0.154 rows=460 loops=1)

  • Filter: (platstatus = 1)
92. 0.004 0.157 ↑ 1.0 7 1

Hash (cost=68.82..68.82 rows=7 width=242) (actual time=0.157..0.157 rows=7 loops=1)

  • Buckets: 1024 Batches: 1 Memory Usage: 11kB
93. 0.011 0.153 ↑ 1.0 7 1

Merge Right Join (cost=68.44..68.82 rows=7 width=242) (actual time=0.147..0.153 rows=7 loops=1)

  • Merge Cond: (pd3_1.dickey = ks_1.storelevel)
94. 0.051 0.051 ↑ 5.8 75 1

Index Scan using idx_pl_dictionary_dickey on pl_dictionary pd3_1 (cost=0.27..58.56 rows=435 width=8) (actual time=0.014..0.051 rows=75 loops=1)

  • Filter: (platstatus = 1)
95. 0.009 0.091 ↑ 1.0 7 1

Sort (cost=58.34..58.36 rows=7 width=242) (actual time=0.090..0.091 rows=7 loops=1)

  • Sort Key: ks_1.storelevel
  • Sort Method: quicksort Memory: 28kB
96. 0.082 0.082 ↑ 1.0 7 1

Index Scan using kx_kq_store_pkey on kx_kq_store ks_1 (cost=0.42..58.24 rows=7 width=242) (actual time=0.028..0.082 rows=7 loops=1)

  • Index Cond: (id = ANY ('{1144512386758217728,1182523023119486976,1144511853465047040,1144511277008293888,1146674242176290816,1144509872893726720,1148493418301886464}'::bigint[]))
  • Filter: ((platstatus = 1) AND ((customertype = '905324680615956480'::bigint) OR (customertype = '905324761813487616'::bigint)))
97. 0.021 0.021 ↑ 1.0 1 7

Index Scan using ix_pl_orgstruct_orgstructid on pl_orgstruct ps_2 (cost=0.28..7.73 rows=1 width=86) (actual time=0.003..0.003 rows=1 loops=7)

  • Index Cond: (ks_1.seleareaid = orgstructid)
  • Filter: (platstatus = 1)
98. 0.001 0.034 ↓ 0.0 0 1

Subquery Scan on *SELECT* 2_2 (cost=57.54..143.59 rows=7 width=237) (actual time=0.034..0.034 rows=0 loops=1)

99. 0.000 0.033 ↓ 0.0 0 1

Nested Loop Left Join (cost=57.54..143.52 rows=7 width=1,741) (actual time=0.033..0.033 rows=0 loops=1)

100. 0.006 0.033 ↓ 0.0 0 1

Hash Right Join (cost=57.26..87.66 rows=7 width=221) (actual time=0.033..0.033 rows=0 loops=1)

  • Hash Cond: (pd1_3.dickey = kc_3.channelcustomersort)
101. 0.000 0.000 ↓ 0.0 0

Seq Scan on pl_dictionary pd1_3 (cost=0.00..25.44 rows=435 width=20) (never executed)

  • Filter: (platstatus = 1)
102. 0.001 0.027 ↓ 0.0 0 1

Hash (cost=57.18..57.18 rows=7 width=217) (actual time=0.027..0.027 rows=0 loops=1)

  • Buckets: 1024 Batches: 1 Memory Usage: 8kB
103. 0.005 0.026 ↓ 0.0 0 1

Bitmap Heap Scan on ka_kq_channelcustomers kc_3 (cost=30.05..57.18 rows=7 width=217) (actual time=0.026..0.026 rows=0 loops=1)

  • Recheck Cond: (id = ANY ('{1144512386758217728,1182523023119486976,1144511853465047040,1144511277008293888,1146674242176290816,1144509872893726720,1148493418301886464}'::bigint[]))
  • Filter: ((platstatus = 1) AND ((customertype = '905324680615956480'::bigint) OR (customertype = '905324761813487616'::bigint)))
104. 0.021 0.021 ↓ 0.0 0 1

Bitmap Index Scan on ka_kq_channelcustomers_pkey (cost=0.00..30.05 rows=7 width=0) (actual time=0.021..0.021 rows=0 loops=1)

  • Index Cond: (id = ANY ('{1144512386758217728,1182523023119486976,1144511853465047040,1144511277008293888,1146674242176290816,1144509872893726720,1148493418301886464}'::bigint[]))
105. 0.000 0.000 ↓ 0.0 0

Index Scan using ix_pl_orgstruct_orgstructid on pl_orgstruct ps_3 (cost=0.28..7.73 rows=1 width=86) (never executed)

  • Index Cond: (kc_3.saleareaid = orgstructid)
  • Filter: (platstatus = 1)
106. 0.001 0.004 ↓ 0.0 0 1

Subquery Scan on *SELECT* 3_1 (cost=0.00..10.80 rows=1 width=944) (actual time=0.004..0.004 rows=0 loops=1)

107. 0.003 0.003 ↓ 0.0 0 1

Seq Scan on kx_kq_ka ka_1 (cost=0.00..10.79 rows=1 width=1,964) (actual time=0.003..0.003 rows=0 loops=1)

  • Filter: ((platstatus = 1) AND ((customertype = '905324680615956480'::bigint) OR (customertype = '905324761813487616'::bigint)) AND (id = ANY ('{1144512386758217728,1182523023119486976,1144511853465047040,1144511277008293888,1146674242176290816,1144509872893726720,1148493418301886464}'::bigint[])))
108. 0.077 0.077 ↓ 0.0 0 7

Index Scan using ka_kq_channelcustomers_pkey on ka_kq_channelcustomers kc_2 (cost=0.29..8.30 rows=1 width=20) (actual time=0.011..0.011 rows=0 loops=7)

  • Index Cond: (id = p_1.customerid)
  • Filter: (platstatus = 1)
109. 0.070 0.070 ↑ 1.0 1 7

Index Scan using ix_pl_orgstruct_orgstructid on pl_orgstruct po_1 (cost=0.28..8.30 rows=1 width=16) (actual time=0.010..0.010 rows=1 loops=7)

  • Index Cond: (orgstructid = '1141896956096942080'::bigint)
  • Filter: (platstatus = 1)
110. 0.070 0.070 ↑ 1.0 1 7

Index Scan using pl_userinfo_pkey on pl_userinfo pu_1 (cost=0.28..8.30 rows=1 width=17) (actual time=0.010..0.010 rows=1 loops=7)

  • Index Cond: (po_1.userinfoid = userinfoid)
  • Filter: (platstatus = 1)
111. 0.056 0.056 ↑ 1.0 1 7

Index Scan using pk_tn_visit_administration_1 on tn_visit_administration tva_1 (cost=0.28..0.31 rows=1 width=16) (actual time=0.008..0.008 rows=1 loops=7)

  • Index Cond: (tn_id = tvd_1.tn_adminid)
  • Filter: (platstatus = 1)
112. 10.321 5,280.509 ↓ 126.6 25,321 1

GroupAggregate (cost=661,330.73..681,996.45 rows=200 width=16) (actual time=5,226.177..5,280.509 rows=25,321 loops=1)

  • Group Key: kvw_1.customerid
113. 26.436 5,270.188 ↑ 19.1 27,100 1

GroupAggregate (cost=661,330.73..674,245.55 rows=516,593 width=36) (actual time=5,226.172..5,270.188 rows=27,100 loops=1)

  • Group Key: kvw_1.customerid, kvw_1.recorddate, kvd2_1.tn_start, kvd2_1.tn_end
114. 83.135 5,243.752 ↑ 4.9 105,984 1

Sort (cost=661,330.73..662,622.21 rows=516,593 width=28) (actual time=5,226.163..5,243.752 rows=105,984 loops=1)

  • Sort Key: kvw_1.customerid, kvw_1.recorddate, kvd2_1.tn_start, kvd2_1.tn_end
  • Sort Method: external merge Disk: 4800kB
115. 133.721 5,160.617 ↑ 4.4 116,471 1

Hash Join (cost=567,749.02..599,947.49 rows=516,593 width=28) (actual time=4,912.469..5,160.617 rows=116,471 loops=1)

  • Hash Cond: (ste_1.storeid = kst_1.id)
116. 114.900 114.900 ↑ 1.0 425,856 1

Seq Scan on kx_kq_storerepresentative ste_1 (cost=0.00..18,038.31 rows=441,395 width=8) (actual time=0.010..114.900 rows=425,856 loops=1)

  • Filter: (platstatus = 1)
  • Rows Removed by Filter: 166263
117. 30.183 4,911.996 ↑ 4.3 116,471 1

Hash (cost=557,638.67..557,638.67 rows=497,708 width=36) (actual time=4,911.996..4,911.996 rows=116,471 loops=1)

  • Buckets: 65536 Batches: 16 Memory Usage: 1034kB
118. 2,226.804 4,881.813 ↑ 4.3 116,471 1

Hash Join (cost=225,972.82..557,638.67 rows=497,708 width=36) (actual time=1,603.612..4,881.813 rows=116,471 loops=1)

  • Hash Cond: (kvw_1.customerid = kst_1.id)
  • Join Filter: ((kvd2_1.tn_start <= kvw_1.recorddate) AND (kvd2_1.tn_end >= kvw_1.recorddate))
  • Rows Removed by Join Filter: 5071371
119. 1,369.663 1,369.663 ↓ 1.0 5,508,981 1

Seq Scan on kx_visit_workrecord kvw_1 (cost=0.00..189,671.24 rows=5,381,059 width=12) (actual time=0.006..1,369.663 rows=5,508,981 loops=1)

  • Filter: (platstatus = 1)
120. 79.056 1,285.346 ↓ 1.0 366,549 1

Hash (cost=219,472.82..219,472.82 rows=354,000 width=24) (actual time=1,285.346..1,285.346 rows=366,549 loops=1)

  • Buckets: 65536 Batches: 8 Memory Usage: 3020kB
121. 88.923 1,206.290 ↓ 1.0 366,549 1

Merge Join (cost=210,018.88..219,472.82 rows=354,000 width=24) (actual time=977.384..1,206.290 rows=366,549 loops=1)

  • Merge Cond: ((kvd2_1.tn_leveldevision = kst_1.tn_storedevicion) AND (kvd2_1.tn_area = kst_1.seleareaid) AND (kvd2_1.tn_storelevel = kst_1.storelevel))
122. 96.001 181.083 ↓ 1.1 100,504 1

Sort (cost=20,370.17..20,590.42 rows=88,100 width=40) (actual time=167.782..181.083 rows=100,504 loops=1)

  • Sort Key: kvd2_1.tn_leveldevision, kvd2_1.tn_area, kvd2_1.tn_storelevel
  • Sort Method: external merge Disk: 4928kB
123. 85.082 85.082 ↓ 1.1 100,504 1

Seq Scan on tn_visit_detail kvd2_1 (cost=0.00..10,722.64 rows=88,100 width=40) (actual time=41.788..85.082 rows=100,504 loops=1)

  • Filter: ((tn_end >= CURRENT_DATE) AND (tn_start <= CURRENT_DATE))
  • Rows Removed by Filter: 191624
124. 44.714 936.284 ↑ 1.1 404,048 1

Materialize (cost=189,648.69..191,773.00 rows=424,862 width=32) (actual time=809.585..936.284 rows=404,048 loops=1)

125. 366.795 891.570 ↑ 1.1 404,048 1

Sort (cost=189,648.69..190,710.84 rows=424,862 width=32) (actual time=809.580..891.570 rows=404,048 loops=1)

  • Sort Key: kst_1.tn_storedevicion, kst_1.seleareaid, kst_1.storelevel
  • Sort Method: external merge Disk: 17488kB
126. 524.775 524.775 ↓ 1.0 425,267 1

Seq Scan on kx_kq_store kst_1 (cost=0.00..139,763.74 rows=424,862 width=32) (actual time=0.015..524.775 rows=425,267 loops=1)

  • Filter: (platstatus = 1)
  • Rows Removed by Filter: 371
127. 0.469 0.469 ↓ 44.0 44 7

Index Scan using idx_kx_visit_planvisit_plandate on kx_visit_planvisit (cost=0.29..8.32 rows=1 width=16) (actual time=0.051..0.067 rows=44 loops=7)

  • Index Cond: ((plandate >= '2020-03-19'::date) AND (plandate <= '2020-03-19'::date))
  • Filter: ((userid = '1141896956096942080'::bigint) AND (platstatus = 1))
  • Rows Removed by Filter: 160
Planning time : 25.846 ms
Execution time : 14,512.845 ms