explain.depesz.com

PostgreSQL's explain analyze made readable

Result: r5i

Settings
# exclusive inclusive rows x rows loops node
1. 0.006 304,778.956 ↓ 18.0 36 1

Limit (cost=1,387,007.72..1,387,007.72 rows=2 width=1,506) (actual time=304,778.948..304,778.956 rows=36 loops=1)

2. 0.230 304,778.950 ↓ 18.0 36 1

Sort (cost=1,387,007.72..1,387,007.72 rows=2 width=1,506) (actual time=304,778.946..304,778.950 rows=36 loops=1)

  • Sort Key: ((ml.seq + 0)), ml.customername
  • Sort Method: quicksort Memory: 43kB
3. 0.025 304,778.720 ↓ 18.0 36 1

Subquery Scan on ml (cost=1,387,007.57..1,387,007.71 rows=2 width=1,506) (actual time=304,778.672..304,778.720 rows=36 loops=1)

4. 0.026 304,778.695 ↓ 18.0 36 1

Unique (cost=1,387,007.57..1,387,007.68 rows=2 width=1,498) (actual time=304,778.667..304,778.695 rows=36 loops=1)

5. 0.235 304,778.669 ↓ 18.0 36 1

Sort (cost=1,387,007.57..1,387,007.57 rows=2 width=1,498) (actual time=304,778.666..304,778.669 rows=36 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: 43kB
6. 0.042 304,778.434 ↓ 18.0 36 1

Append (cost=662,110.55..1,387,007.56 rows=2 width=1,498) (actual time=137,557.686..304,778.434 rows=36 loops=1)

7. 132.809 168,025.405 ↓ 27.0 27 1

Merge Left Join (cost=662,110.55..693,422.97 rows=1 width=356) (actual time=137,557.684..168,025.405 rows=27 loops=1)

  • Merge Cond: ("*SELECT* 1".id = kvw.customerid)
8. 0.104 31,918.732 ↓ 27.0 27 1

Nested Loop Left Join (cost=939.74..11,988.67 rows=1 width=325) (actual time=1,669.517..31,918.732 rows=27 loops=1)

9. 0.106 31,918.601 ↓ 27.0 27 1

Nested Loop Left Join (cost=939.46..11,988.36 rows=1 width=325) (actual time=1,669.505..31,918.601 rows=27 loops=1)

10. 0.126 31,918.171 ↓ 27.0 27 1

Nested Loop Left Join (cost=939.18..11,980.05 rows=1 width=324) (actual time=1,669.468..31,918.171 rows=27 loops=1)

  • Join Filter: (po.orgstructid = p.userid)
11. 0.121 31,917.694 ↓ 27.0 27 1

Nested Loop Left Join (cost=938.90..11,971.74 rows=1 width=316) (actual time=1,669.425..31,917.694 rows=27 loops=1)

12. 0.217 31,916.817 ↓ 27.0 27 1

Nested Loop Left Join (cost=938.47..11,931.28 rows=1 width=314) (actual time=1,669.352..31,916.817 rows=27 loops=1)

13. 0.381 31,916.276 ↓ 27.0 27 1

Nested Loop (cost=938.19..11,922.98 rows=1 width=302) (actual time=1,669.311..31,916.276 rows=27 loops=1)

  • Join Filter: (p.customerid = "*SELECT* 1".id)
  • Rows Removed by Join Filter: 702
14. 0.150 4.622 ↓ 27.0 27 1

Merge Left Join (cost=42.78..42.81 rows=1 width=48) (actual time=4.433..4.622 rows=27 loops=1)

  • Merge Cond: ((p.customerid = vc.customerid) AND (p.customertype = vc.customertype))
  • Join Filter: (vc.userid = p.userid)
15. 0.050 0.280 ↓ 27.0 27 1

Sort (cost=34.32..34.32 rows=1 width=40) (actual time=0.256..0.280 rows=27 loops=1)

  • Sort Key: p.customerid, p.customertype
  • Sort Method: quicksort Memory: 27kB
16. 0.029 0.230 ↓ 27.0 27 1

Bitmap Heap Scan on kx_visit_planvisit p (cost=30.29..34.31 rows=1 width=40) (actual time=0.221..0.230 rows=27 loops=1)

  • Recheck Cond: ((userid = '1185018465094864896'::bigint) AND (plandate >= '2020-04-02'::date) AND (plandate <= '2020-04-02'::date))
  • Filter: (platstatus = 1)
  • Heap Blocks: exact=1
17. 0.003 0.201 ↓ 0.0 0 1

BitmapAnd (cost=30.29..30.29 rows=1 width=0) (actual time=0.201..0.201 rows=0 loops=1)

18. 0.082 0.082 ↓ 1.9 196 1

Bitmap Index Scan on idx_kx_visit_planvisit_userid (cost=0.00..5.20 rows=104 width=0) (actual time=0.082..0.082 rows=196 loops=1)

  • Index Cond: (userid = '1185018465094864896'::bigint)
19. 0.116 0.116 ↓ 1.0 866 1

Bitmap Index Scan on idx_kx_visit_planvisit_plandate (cost=0.00..24.84 rows=842 width=0) (actual time=0.116..0.116 rows=866 loops=1)

  • Index Cond: ((plandate >= '2020-04-02'::date) AND (plandate <= '2020-04-02'::date))
20. 0.057 4.192 ↓ 31.0 31 1

Sort (cost=8.46..8.47 rows=1 width=32) (actual time=4.169..4.192 rows=31 loops=1)

  • Sort Key: vc.customerid, vc.customertype
  • Sort Method: quicksort Memory: 27kB
21. 4.135 4.135 ↓ 31.0 31 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.404..4.135 rows=31 loops=1)

  • Index Cond: ((visitdate >= '2020-04-02'::date) AND (visitdate <= '2020-04-02'::date))
  • Filter: ((userid = '1185018465094864896'::bigint) AND (platstatus = 1))
  • Rows Removed by Filter: 5,260
22. 6,162.876 31,911.273 ↑ 2.0 27 27

Hash Right Join (cost=895.41..11,879.48 rows=55 width=254) (actual time=1,181.886..1,181.899 rows=27 loops=27)

  • Hash Cond: ((tvd.tn_leveldevision = "*SELECT* 1".tn_storedevicion) AND (tvd.tn_storelevel = "*SELECT* 1".storelevel) AND (tvd.tn_area = "*SELECT* 1".saleareaid))
23. 25,686.153 25,686.153 ↓ 1.1 100,504 27

Seq Scan on tn_visit_detail tvd (cost=0.00..9,992.94 rows=88,100 width=32) (actual time=574.057..951.339 rows=100,504 loops=27)

  • Filter: ((tn_start <= '2020-04-07 10:17:11'::timestamp without time zone) AND (tn_end >= '2020-04-07 10:17:11'::timestamp without time zone) AND (platstatus = 1))
  • Rows Removed by Filter: 191,624
24. 0.036 62.244 ↑ 2.0 27 1

Hash (cost=894.45..894.45 rows=55 width=271) (actual time=62.244..62.244 rows=27 loops=1)

  • Buckets: 1,024 Batches: 1 Memory Usage: 16kB
25. 0.006 62.208 ↑ 2.0 27 1

Append (cost=266.13..894.45 rows=55 width=271) (actual time=61.442..62.208 rows=27 loops=1)

26. 0.009 62.102 ↑ 1.0 27 1

Subquery Scan on *SELECT* 1 (cost=266.13..473.90 rows=27 width=248) (actual time=61.441..62.102 rows=27 loops=1)

27. 1.858 62.093 ↑ 1.0 27 1

Nested Loop Left Join (cost=266.13..473.63 rows=27 width=1,720) (actual time=61.439..62.093 rows=27 loops=1)

28. 0.119 60.127 ↑ 1.0 27 1

Hash Right Join (cost=265.85..298.78 rows=27 width=248) (actual time=60.112..60.127 rows=27 loops=1)

  • Hash Cond: (pd1.dickey = ks.channeltype)
29. 0.348 0.348 ↓ 1.0 544 1

Seq Scan on pl_dictionary pd1 (cost=0.00..29.61 rows=529 width=8) (actual time=0.007..0.348 rows=544 loops=1)

  • Filter: (platstatus = 1)
30. 0.037 59.660 ↑ 1.0 27 1

Hash (cost=265.51..265.51 rows=27 width=256) (actual time=59.660..59.660 rows=27 loops=1)

  • Buckets: 1,024 Batches: 1 Memory Usage: 16kB
31. 0.037 59.623 ↑ 1.0 27 1

Merge Right Join (cost=257.38..265.51 rows=27 width=256) (actual time=59.606..59.623 rows=27 loops=1)

  • Merge Cond: (pd2.dickey = ks.storetype)
32. 0.212 0.212 ↑ 2.7 196 1

Index Scan using idx_pl_dictionary_dickey on pl_dictionary pd2 (cost=0.28..59.95 rows=529 width=19) (actual time=0.053..0.212 rows=196 loops=1)

  • Filter: (platstatus = 1)
33. 0.054 59.374 ↑ 1.0 27 1

Sort (cost=234.51..234.58 rows=27 width=253) (actual time=59.372..59.374 rows=27 loops=1)

  • Sort Key: ks.storetype
  • Sort Method: quicksort Memory: 37kB
34. 0.028 59.320 ↑ 1.0 27 1

Merge Right Join (cost=233.23..233.87 rows=27 width=253) (actual time=59.303..59.320 rows=27 loops=1)

  • Merge Cond: (pd3.dickey = ks.storelevel)
35. 0.126 0.126 ↑ 7.1 74 1

Index Scan using idx_pl_dictionary_dickey on pl_dictionary pd3 (cost=0.28..59.95 rows=529 width=19) (actual time=0.014..0.126 rows=74 loops=1)

  • Filter: (platstatus = 1)
36. 0.055 59.166 ↑ 1.0 27 1

Sort (cost=224.65..224.72 rows=27 width=242) (actual time=59.164..59.166 rows=27 loops=1)

  • Sort Key: ks.storelevel
  • Sort Method: quicksort Memory: 36kB
37. 59.111 59.111 ↑ 1.0 27 1

Index Scan using kx_kq_store_pkey on kx_kq_store ks (cost=0.42..224.01 rows=27 width=242) (actual time=0.078..59.111 rows=27 loops=1)

  • Index Cond: (id = ANY ('{1184292006474682368,1191549541690773504,1184347402774122496,1184335296678989824,1184342493160935424,1184344282878840832,1184336833190957056,1184383707344146432,1184294405734010880,1191557238666432512,1182969398164590592,1184289855341989888,1184287163433488384,1184300446093807616,1184291057576316928,1184313261403803648,1184298845518041088,1184349362340368384,1184307001086316544,1189748088026304512,1184303158051082240,1184324904993034240,1184393994822619136,1184296264330776576,1192657845779304448,1184310730908897280,1184339764267061248}'::bigint[]))
  • Filter: ((platstatus = 1) AND ((customertype = '905324680615956480'::bigint) OR (customertype = '905324761813487616'::bigint)))
38. 0.108 0.108 ↑ 1.0 1 27

Index Scan using pl_orgstruct_pkey on pl_orgstruct ps (cost=0.28..6.23 rows=1 width=87) (actual time=0.004..0.004 rows=1 loops=27)

  • Index Cond: (ks.seleareaid = orgstructid)
  • Filter: (platstatus = 1)
39. 0.001 0.094 ↓ 0.0 0 1

Subquery Scan on *SELECT* 2_1 (cost=132.41..409.00 rows=27 width=268) (actual time=0.094..0.094 rows=0 loops=1)

40. 0.000 0.093 ↓ 0.0 0 1

Hash Left Join (cost=132.41..408.73 rows=27 width=1,740) (actual time=0.093..0.093 rows=0 loops=1)

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

Nested Loop Left Join (cost=96.18..365.38 rows=27 width=296) (actual time=0.093..0.093 rows=0 loops=1)

42. 0.005 0.092 ↓ 0.0 0 1

Bitmap Heap Scan on ka_kq_channelcustomers kc_1 (cost=95.90..197.28 rows=27 width=217) (actual time=0.092..0.092 rows=0 loops=1)

  • Recheck Cond: (id = ANY ('{1184292006474682368,1191549541690773504,1184347402774122496,1184335296678989824,1184342493160935424,1184344282878840832,1184336833190957056,1184383707344146432,1184294405734010880,1191557238666432512,1182969398164590592,1184289855341989888,1184287163433488384,1184300446093807616,1184291057576316928,1184313261403803648,1184298845518041088,1184349362340368384,1184307001086316544,1189748088026304512,1184303158051082240,1184324904993034240,1184393994822619136,1184296264330776576,1192657845779304448,1184310730908897280,1184339764267061248}'::bigint[]))
  • Filter: ((platstatus = 1) AND ((customertype = '905324680615956480'::bigint) OR (customertype = '905324761813487616'::bigint)))
43. 0.087 0.087 ↓ 0.0 0 1

Bitmap Index Scan on ka_kq_channelcustomers_pkey (cost=0.00..95.90 rows=27 width=0) (actual time=0.087..0.087 rows=0 loops=1)

  • Index Cond: (id = ANY ('{1184292006474682368,1191549541690773504,1184347402774122496,1184335296678989824,1184342493160935424,1184344282878840832,1184336833190957056,1184383707344146432,1184294405734010880,1191557238666432512,1182969398164590592,1184289855341989888,1184287163433488384,1184300446093807616,1184291057576316928,1184313261403803648,1184298845518041088,1184349362340368384,1184307001086316544,1189748088026304512,1184303158051082240,1184324904993034240,1184393994822619136,1184296264330776576,1192657845779304448,1184310730908897280,1184339764267061248}'::bigint[]))
44. 0.000 0.000 ↓ 0.0 0

Index Scan using pl_orgstruct_pkey on pl_orgstruct ps_1 (cost=0.28..6.23 rows=1 width=87) (never executed)

  • Index Cond: (kc_1.saleareaid = orgstructid)
  • Filter: (platstatus = 1)
45. 0.000 0.000 ↓ 0.0 0

Hash (cost=29.61..29.61 rows=529 width=19) (never executed)

46. 0.000 0.000 ↓ 0.0 0

Seq Scan on pl_dictionary pd1_1 (cost=0.00..29.61 rows=529 width=19) (never executed)

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

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

48. 0.006 0.006 ↓ 0.0 0 1

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

  • Filter: ((platstatus = 1) AND ((customertype = '905324680615956480'::bigint) OR (customertype = '905324761813487616'::bigint)) AND (id = ANY ('{1184292006474682368,1191549541690773504,1184347402774122496,1184335296678989824,1184342493160935424,1184344282878840832,1184336833190957056,1184383707344146432,1184294405734010880,1191557238666432512,1182969398164590592,1184289855341989888,1184287163433488384,1184300446093807616,1184291057576316928,1184313261403803648,1184298845518041088,1184349362340368384,1184307001086316544,1189748088026304512,1184303158051082240,1184324904993034240,1184393994822619136,1184296264330776576,1192657845779304448,1184310730908897280,1184339764267061248}'::bigint[])))
49. 0.324 0.324 ↓ 0.0 0 27

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

  • Index Cond: (id = p.customerid)
  • Filter: (platstatus = 1)
50. 0.756 0.756 ↑ 1.0 1 27

Index Scan using idx_kx_visit_actual_customerid on kx_visit_actual a (cost=0.43..40.45 rows=1 width=26) (actual time=0.027..0.028 rows=1 loops=27)

  • Index Cond: (customerid = p.customerid)
  • Filter: ((actualvisittime >= '2020-04-02 00:00:00'::timestamp without time zone) AND (actualvisittime <= '2020-04-02 23:59:59'::timestamp without time zone) AND (userid = '1185018465094864896'::bigint) AND (platstatus = 1) AND (userid = p.userid))
  • Rows Removed by Filter: 0
51. 0.351 0.351 ↑ 1.0 1 27

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

  • Index Cond: (orgstructid = '1185018465094864896'::bigint)
  • Filter: (platstatus = 1)
52. 0.324 0.324 ↑ 1.0 1 27

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

  • Index Cond: (po.userinfoid = userinfoid)
  • Filter: (platstatus = 1)
53. 0.027 0.027 ↓ 0.0 0 27

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.001..0.001 rows=0 loops=27)

  • Index Cond: (tn_id = tvd.tn_adminid)
  • Filter: (platstatus = 1)
54. 478.442 135,973.864 ↓ 402.4 80,482 1

GroupAggregate (cost=661,170.81..681,431.77 rows=200 width=16) (actual time=131,866.883..135,973.864 rows=80,482 loops=1)

  • Group Key: kvw.customerid
55. 2,226.190 135,495.422 ↑ 4.9 103,691 1

GroupAggregate (cost=661,170.81..673,832.66 rows=506,474 width=36) (actual time=131,866.871..135,495.422 rows=103,691 loops=1)

  • Group Key: kvw.customerid, kvw.recorddate, kvd2.tn_start, kvd2.tn_end
56. 5,049.485 133,269.232 ↑ 1.3 403,215 1

Sort (cost=661,170.81..662,436.99 rows=506,474 width=28) (actual time=131,866.859..133,269.232 rows=403,215 loops=1)

  • Sort Key: kvw.customerid, kvw.recorddate, kvd2.tn_start, kvd2.tn_end
  • Sort Method: external merge Disk: 17,592kB
57. 8,226.671 128,219.747 ↑ 1.2 427,474 1

Hash Join (cost=568,593.79..601,061.57 rows=506,474 width=28) (actual time=117,017.906..128,219.747 rows=427,474 loops=1)

  • Hash Cond: (ste.storeid = kst.id)
58. 2,976.324 2,976.324 ↑ 1.0 428,484 1

Seq Scan on kx_kq_storerepresentative ste (cost=0.00..18,484.17 rows=433,562 width=8) (actual time=0.020..2,976.324 rows=428,484 loops=1)

  • Filter: (platstatus = 1)
  • Rows Removed by Filter: 177,069
59. 2,057.963 117,016.752 ↑ 1.2 427,369 1

Hash (cost=558,441.02..558,441.02 rows=499,822 width=36) (actual time=117,016.752..117,016.752 rows=427,369 loops=1)

  • Buckets: 65,536 Batches: 16 Memory Usage: 2,424kB
60. 58,991.941 114,958.789 ↑ 1.2 427,369 1

Hash Join (cost=226,468.93..558,441.02 rows=499,822 width=36) (actual time=31,997.724..114,958.789 rows=427,369 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: 5,071,652
61. 29,783.814 29,783.814 ↓ 1.1 5,845,077 1

Seq Scan on kx_visit_workrecord kvw (cost=0.00..189,671.24 rows=5,381,059 width=12) (actual time=0.026..29,783.814 rows=5,845,077 loops=1)

  • Filter: (platstatus = 1)
62. 1,074.100 26,183.034 ↓ 1.0 367,894 1

Hash (cost=219,901.89..219,901.89 rows=357,683 width=24) (actual time=26,183.034..26,183.034 rows=367,894 loops=1)

  • Buckets: 65,536 Batches: 8 Memory Usage: 3,029kB
63. 1,136.934 25,108.934 ↓ 1.0 367,894 1

Merge Join (cost=210,369.12..219,901.89 rows=357,683 width=24) (actual time=21,566.220..25,108.934 rows=367,894 loops=1)

  • Merge Cond: ((kvd2.tn_leveldevision = kst.tn_storedevicion) AND (kvd2.tn_area = kst.seleareaid) AND (kvd2.tn_storelevel = kst.storelevel))
64. 1,897.084 3,955.364 ↓ 1.1 100,504 1

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

  • Sort Key: kvd2.tn_leveldevision, kvd2.tn_area, kvd2.tn_storelevel
  • Sort Method: external merge Disk: 4,928kB
65. 2,058.280 2,058.280 ↓ 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=1,036.812..2,058.280 rows=100,504 loops=1)

  • Filter: ((tn_end >= CURRENT_DATE) AND (tn_start <= CURRENT_DATE))
  • Rows Removed by Filter: 191,624
66. 780.162 20,016.636 ↑ 1.1 405,890 1

Materialize (cost=189,998.93..192,136.05 rows=427,425 width=32) (actual time=17,776.946..20,016.636 rows=405,890 loops=1)

67. 6,765.670 19,236.474 ↑ 1.1 405,890 1

Sort (cost=189,998.93..191,067.49 rows=427,425 width=32) (actual time=17,776.939..19,236.474 rows=405,890 loops=1)

  • Sort Key: kst.tn_storedevicion, kst.seleareaid, kst.storelevel
  • Sort Method: external merge Disk: 17,568kB
68. 12,470.804 12,470.804 ↑ 1.0 427,314 1

Seq Scan on kx_kq_store kst (cost=0.00..139,796.34 rows=427,425 width=32) (actual time=0.034..12,470.804 rows=427,314 loops=1)

  • Filter: (platstatus = 1)
  • Rows Removed by Filter: 371
69. 0.011 136,752.987 ↓ 9.0 9 1

Subquery Scan on *SELECT* 2 (cost=662,302.00..693,584.57 rows=1 width=365) (actual time=136,376.311..136,752.987 rows=9 loops=1)

70. 0.192 136,752.976 ↓ 9.0 9 1

Nested Loop Anti Join (cost=662,302.00..693,584.56 rows=1 width=361) (actual time=136,376.309..136,752.976 rows=9 loops=1)

71. 8.570 136,752.536 ↓ 31.0 31 1

Merge Left Join (cost=662,297.57..693,572.25 rows=1 width=306) (actual time=136,376.252..136,752.536 rows=31 loops=1)

  • Merge Cond: ("*SELECT* 1_1".id = kvw_1.customerid)
72. 0.040 1,128.188 ↓ 31.0 31 1

Nested Loop Left Join (cost=1,126.76..12,137.97 rows=1 width=298) (actual time=1,127.453..1,128.188 rows=31 loops=1)

73. 0.045 1,128.148 ↓ 31.0 31 1

Nested Loop Left Join (cost=1,126.48..12,137.66 rows=1 width=298) (actual time=1,127.447..1,128.148 rows=31 loops=1)

74. 0.038 1,128.010 ↓ 31.0 31 1

Nested Loop Left Join (cost=1,126.20..12,129.35 rows=1 width=297) (actual time=1,127.431..1,128.010 rows=31 loops=1)

  • Join Filter: (po_1.orgstructid = p_1.userid)
75. 0.048 1,127.848 ↓ 31.0 31 1

Nested Loop Left Join (cost=1,125.92..12,121.04 rows=1 width=289) (actual time=1,127.411..1,127.848 rows=31 loops=1)

76. 0.175 1,127.707 ↓ 31.0 31 1

Nested Loop (cost=1,125.63..12,112.73 rows=1 width=277) (actual time=1,127.391..1,127.707 rows=31 loops=1)

  • Join Filter: (p_1.customerid = "*SELECT* 1_1".id)
  • Rows Removed by Join Filter: 930
77. 0.049 4.123 ↓ 10.3 31 1

Merge Left Join (cost=136.14..136.17 rows=3 width=46) (actual time=4.062..4.123 rows=31 loops=1)

  • Merge Cond: (p_1.customerid = vc_1.customerid)
  • Join Filter: (vc_1.userid = p_1.userid)
78. 0.043 0.741 ↓ 10.3 31 1

Sort (cost=127.68..127.68 rows=3 width=38) (actual time=0.732..0.741 rows=31 loops=1)

  • Sort Key: p_1.customerid
  • Sort Method: quicksort Memory: 27kB
79. 0.105 0.698 ↓ 10.3 31 1

Bitmap Heap Scan on kx_visit_actual p_1 (cost=115.68..127.65 rows=3 width=38) (actual time=0.610..0.698 rows=31 loops=1)

  • Recheck Cond: ((userid = '1185018465094864896'::bigint) AND (actualvisitdate >= '2020-04-02'::date) AND (actualvisitdate <= '2020-04-02'::date))
  • Filter: (platstatus = 1)
  • Heap Blocks: exact=25
80. 0.015 0.593 ↓ 0.0 0 1

BitmapAnd (cost=115.68..115.68 rows=3 width=0) (actual time=0.593..0.593 rows=0 loops=1)

81. 0.086 0.086 ↑ 10.3 204 1

Bitmap Index Scan on idx_kx_visit_actual_userid (cost=0.00..48.12 rows=2,092 width=0) (actual time=0.086..0.086 rows=204 loops=1)

  • Index Cond: (userid = '1185018465094864896'::bigint)
82. 0.492 0.492 ↓ 2.0 5,291 1

Bitmap Index Scan on idx_kx_visit_actual_actualvisitdate (cost=0.00..67.31 rows=2,688 width=0) (actual time=0.492..0.492 rows=5,291 loops=1)

  • Index Cond: ((actualvisitdate >= '2020-04-02'::date) AND (actualvisitdate <= '2020-04-02'::date))
83. 0.031 3.333 ↓ 31.0 31 1

Sort (cost=8.46..8.47 rows=1 width=24) (actual time=3.322..3.333 rows=31 loops=1)

  • Sort Key: vc_1.customerid
  • Sort Method: quicksort Memory: 27kB
84. 3.302 3.302 ↓ 31.0 31 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.265..3.302 rows=31 loops=1)

  • Index Cond: ((visitdate >= '2020-04-02'::date) AND (visitdate <= '2020-04-02'::date))
  • Filter: ((userid = '1185018465094864896'::bigint) AND (platstatus = 1))
  • Rows Removed by Filter: 5,260
85. 0.116 1,123.409 ↑ 2.0 31 31

Materialize (cost=989.50..11,973.88 rows=63 width=231) (actual time=36.235..36.239 rows=31 loops=31)

86. 181.274 1,123.293 ↑ 2.0 31 1

Hash Right Join (cost=989.50..11,973.57 rows=63 width=231) (actual time=1,123.278..1,123.293 rows=31 loops=1)

  • 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))
87. 940.115 940.115 ↓ 1.1 100,504 1

Seq Scan on tn_visit_detail tvd_1 (cost=0.00..9,992.94 rows=88,100 width=32) (actual time=651.557..940.115 rows=100,504 loops=1)

  • Filter: ((tn_start <= '2020-04-07 10:17:11'::timestamp without time zone) AND (tn_end >= '2020-04-07 10:17:11'::timestamp without time zone) AND (platstatus = 1))
  • Rows Removed by Filter: 191,624
88. 0.025 1.904 ↑ 2.0 31 1

Hash (cost=988.39..988.39 rows=63 width=248) (actual time=1.904..1.904 rows=31 loops=1)

  • Buckets: 1,024 Batches: 1 Memory Usage: 17kB
89. 0.008 1.879 ↑ 2.0 31 1

Append (cost=299.71..988.39 rows=63 width=248) (actual time=1.053..1.879 rows=31 loops=1)

90. 0.008 1.801 ↑ 1.0 31 1

Subquery Scan on *SELECT* 1_1 (cost=299.71..525.72 rows=31 width=237) (actual time=1.052..1.801 rows=31 loops=1)

91. 0.745 1.793 ↑ 1.0 31 1

Nested Loop Left Join (cost=299.71..525.41 rows=31 width=1,741) (actual time=1.050..1.793 rows=31 loops=1)

92. 0.092 0.955 ↑ 1.0 31 1

Hash Right Join (cost=299.43..332.35 rows=31 width=237) (actual time=0.941..0.955 rows=31 loops=1)

  • Hash Cond: (pd1_2.dickey = ks_1.channeltype)
93. 0.278 0.278 ↓ 1.0 544 1

Seq Scan on pl_dictionary pd1_2 (cost=0.00..29.61 rows=529 width=8) (actual time=0.007..0.278 rows=544 loops=1)

  • Filter: (platstatus = 1)
94. 0.017 0.585 ↑ 1.0 31 1

Hash (cost=299.04..299.04 rows=31 width=245) (actual time=0.585..0.585 rows=31 loops=1)

  • Buckets: 1,024 Batches: 1 Memory Usage: 17kB
95. 0.035 0.568 ↑ 1.0 31 1

Merge Right Join (cost=290.85..299.04 rows=31 width=245) (actual time=0.551..0.568 rows=31 loops=1)

  • Merge Cond: (pd2_1.dickey = ks_1.storetype)
96. 0.152 0.152 ↑ 2.7 196 1

Index Scan using idx_pl_dictionary_dickey on pl_dictionary pd2_1 (cost=0.28..59.95 rows=529 width=19) (actual time=0.021..0.152 rows=196 loops=1)

  • Filter: (platstatus = 1)
97. 0.032 0.381 ↑ 1.0 31 1

Sort (cost=267.97..268.05 rows=31 width=242) (actual time=0.378..0.381 rows=31 loops=1)

  • Sort Key: ks_1.storetype
  • Sort Method: quicksort Memory: 37kB
98. 0.024 0.349 ↑ 1.0 31 1

Merge Right Join (cost=266.51..267.21 rows=31 width=242) (actual time=0.333..0.349 rows=31 loops=1)

  • Merge Cond: (pd3_1.dickey = ks_1.storelevel)
99. 0.067 0.067 ↑ 7.1 74 1

Index Scan using idx_pl_dictionary_dickey on pl_dictionary pd3_1 (cost=0.28..59.95 rows=529 width=8) (actual time=0.011..0.067 rows=74 loops=1)

  • Filter: (platstatus = 1)
100. 0.038 0.258 ↑ 1.0 31 1

Sort (cost=257.93..258.01 rows=31 width=242) (actual time=0.256..0.258 rows=31 loops=1)

  • Sort Key: ks_1.storelevel
  • Sort Method: quicksort Memory: 37kB
101. 0.220 0.220 ↑ 1.0 31 1

Index Scan using kx_kq_store_pkey on kx_kq_store ks_1 (cost=0.42..257.17 rows=31 width=242) (actual time=0.033..0.220 rows=31 loops=1)

  • Index Cond: (id = ANY ('{1184285539621277696,1184287163433488384,1184289855341989888,1189024630741012480,1184292006474682368,1184291057576316928,1184294405734010880,1184296264330776576,1189748088026304512,1182969398164590592,1184298845518041088,1191549541690773504,1184300446093807616,1184301873167994880,1184321907202330624,1184328413712879616,1184324904993034240,1182967978459795456,1184333700142338048,1245600946558472192,1184335296678989824,1191557238666432512,1184344282878840832,1184342493160935424,1184339764267061248,1184347402774122496,1184346584675127296,1184349362340368384,1184383707344146432,1184336833190957056,1192657845779304448}'::bigint[]))
  • Filter: ((platstatus = 1) AND ((customertype = '905324680615956480'::bigint) OR (customertype = '905324761813487616'::bigint)))
102. 0.093 0.093 ↑ 1.0 1 31

Index Scan using pl_orgstruct_pkey on pl_orgstruct ps_2 (cost=0.28..5.98 rows=1 width=87) (actual time=0.003..0.003 rows=1 loops=31)

  • Index Cond: (ks_1.seleareaid = orgstructid)
  • Filter: (platstatus = 1)
103. 0.000 0.064 ↓ 0.0 0 1

Subquery Scan on *SELECT* 2_2 (cost=141.58..450.98 rows=31 width=236) (actual time=0.064..0.064 rows=0 loops=1)

104. 0.001 0.064 ↓ 0.0 0 1

Hash Left Join (cost=141.58..450.67 rows=31 width=1,740) (actual time=0.064..0.064 rows=0 loops=1)

  • Hash Cond: (kc_3.channelcustomersort = pd1_3.dickey)
105. 0.001 0.063 ↓ 0.0 0 1

Nested Loop Left Join (cost=105.36..406.27 rows=31 width=296) (actual time=0.063..0.063 rows=0 loops=1)

106. 0.006 0.062 ↓ 0.0 0 1

Bitmap Heap Scan on ka_kq_channelcustomers kc_3 (cost=105.08..220.96 rows=31 width=217) (actual time=0.062..0.062 rows=0 loops=1)

  • Recheck Cond: (id = ANY ('{1184285539621277696,1184287163433488384,1184289855341989888,1189024630741012480,1184292006474682368,1184291057576316928,1184294405734010880,1184296264330776576,1189748088026304512,1182969398164590592,1184298845518041088,1191549541690773504,1184300446093807616,1184301873167994880,1184321907202330624,1184328413712879616,1184324904993034240,1182967978459795456,1184333700142338048,1245600946558472192,1184335296678989824,1191557238666432512,1184344282878840832,1184342493160935424,1184339764267061248,1184347402774122496,1184346584675127296,1184349362340368384,1184383707344146432,1184336833190957056,1192657845779304448}'::bigint[]))
  • Filter: ((platstatus = 1) AND ((customertype = '905324680615956480'::bigint) OR (customertype = '905324761813487616'::bigint)))
107. 0.056 0.056 ↓ 0.0 0 1

Bitmap Index Scan on ka_kq_channelcustomers_pkey (cost=0.00..105.07 rows=31 width=0) (actual time=0.056..0.056 rows=0 loops=1)

  • Index Cond: (id = ANY ('{1184285539621277696,1184287163433488384,1184289855341989888,1189024630741012480,1184292006474682368,1184291057576316928,1184294405734010880,1184296264330776576,1189748088026304512,1182969398164590592,1184298845518041088,1191549541690773504,1184300446093807616,1184301873167994880,1184321907202330624,1184328413712879616,1184324904993034240,1182967978459795456,1184333700142338048,1245600946558472192,1184335296678989824,1191557238666432512,1184344282878840832,1184342493160935424,1184339764267061248,1184347402774122496,1184346584675127296,1184349362340368384,1184383707344146432,1184336833190957056,1192657845779304448}'::bigint[]))
108. 0.000 0.000 ↓ 0.0 0

Index Scan using pl_orgstruct_pkey on pl_orgstruct ps_3 (cost=0.28..5.98 rows=1 width=87) (never executed)

  • Index Cond: (kc_3.saleareaid = orgstructid)
  • Filter: (platstatus = 1)
109. 0.000 0.000 ↓ 0.0 0

Hash (cost=29.61..29.61 rows=529 width=19) (never executed)

110. 0.000 0.000 ↓ 0.0 0

Seq Scan on pl_dictionary pd1_3 (cost=0.00..29.61 rows=529 width=19) (never executed)

  • Filter: (platstatus = 1)
111. 0.001 0.006 ↓ 0.0 0 1

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

112. 0.005 0.005 ↓ 0.0 0 1

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

  • Filter: ((platstatus = 1) AND ((customertype = '905324680615956480'::bigint) OR (customertype = '905324761813487616'::bigint)) AND (id = ANY ('{1184285539621277696,1184287163433488384,1184289855341989888,1189024630741012480,1184292006474682368,1184291057576316928,1184294405734010880,1184296264330776576,1189748088026304512,1182969398164590592,1184298845518041088,1191549541690773504,1184300446093807616,1184301873167994880,1184321907202330624,1184328413712879616,1184324904993034240,1182967978459795456,1184333700142338048,1245600946558472192,1184335296678989824,1191557238666432512,1184344282878840832,1184342493160935424,1184339764267061248,1184347402774122496,1184346584675127296,1184349362340368384,1184383707344146432,1184336833190957056,1192657845779304448}'::bigint[])))
113. 0.093 0.093 ↓ 0.0 0 31

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.003..0.003 rows=0 loops=31)

  • Index Cond: (id = p_1.customerid)
  • Filter: (platstatus = 1)
114. 0.124 0.124 ↑ 1.0 1 31

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

  • Index Cond: (orgstructid = '1185018465094864896'::bigint)
  • Filter: (platstatus = 1)
115. 0.093 0.093 ↑ 1.0 1 31

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

  • Index Cond: (po_1.userinfoid = userinfoid)
  • Filter: (platstatus = 1)
116. 0.000 0.000 ↓ 0.0 0 31

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.000..0.000 rows=0 loops=31)

  • Index Cond: (tn_id = tvd_1.tn_adminid)
  • Filter: (platstatus = 1)
117. 271.259 135,615.778 ↓ 426.7 85,347 1

GroupAggregate (cost=661,170.81..681,431.77 rows=200 width=16) (actual time=131,272.656..135,615.778 rows=85,347 loops=1)

  • Group Key: kvw_1.customerid
118. 3,012.190 135,344.519 ↑ 4.6 109,912 1

GroupAggregate (cost=661,170.81..673,832.66 rows=506,474 width=36) (actual time=131,272.648..135,344.519 rows=109,912 loops=1)

  • Group Key: kvw_1.customerid, kvw_1.recorddate, kvd2_1.tn_start, kvd2_1.tn_end
119. 5,240.604 132,332.329 ↑ 1.2 427,059 1

Sort (cost=661,170.81..662,436.99 rows=506,474 width=28) (actual time=131,272.637..132,332.329 rows=427,059 loops=1)

  • Sort Key: kvw_1.customerid, kvw_1.recorddate, kvd2_1.tn_start, kvd2_1.tn_end
  • Sort Method: external merge Disk: 17,592kB
120. 9,185.129 127,091.725 ↑ 1.2 427,474 1

Hash Join (cost=568,593.79..601,061.57 rows=506,474 width=28) (actual time=115,459.096..127,091.725 rows=427,474 loops=1)

  • Hash Cond: (ste_1.storeid = kst_1.id)
121. 2,540.838 2,540.838 ↑ 1.0 428,484 1

Seq Scan on kx_kq_storerepresentative ste_1 (cost=0.00..18,484.17 rows=433,562 width=8) (actual time=0.014..2,540.838 rows=428,484 loops=1)

  • Filter: (platstatus = 1)
  • Rows Removed by Filter: 177,069
122. 2,414.949 115,365.758 ↑ 1.2 427,369 1

Hash (cost=558,441.02..558,441.02 rows=499,822 width=36) (actual time=115,365.758..115,365.758 rows=427,369 loops=1)

  • Buckets: 65,536 Batches: 16 Memory Usage: 2,424kB
123. 55,131.734 112,950.809 ↑ 1.2 427,369 1

Hash Join (cost=226,468.93..558,441.02 rows=499,822 width=36) (actual time=29,407.827..112,950.809 rows=427,369 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: 5,071,652
124. 35,233.118 35,233.118 ↓ 1.1 5,845,077 1

Seq Scan on kx_visit_workrecord kvw_1 (cost=0.00..189,671.24 rows=5,381,059 width=12) (actual time=0.014..35,233.118 rows=5,845,077 loops=1)

  • Filter: (platstatus = 1)
125. 1,950.973 22,585.957 ↓ 1.0 367,894 1

Hash (cost=219,901.89..219,901.89 rows=357,683 width=24) (actual time=22,585.957..22,585.957 rows=367,894 loops=1)

  • Buckets: 65,536 Batches: 8 Memory Usage: 3,029kB
126. 1,159.603 20,634.984 ↓ 1.0 367,894 1

Merge Join (cost=210,369.12..219,901.89 rows=357,683 width=24) (actual time=17,027.069..20,634.984 rows=367,894 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))
127. 1,879.734 3,687.411 ↓ 1.1 100,504 1

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

  • Sort Key: kvd2_1.tn_leveldevision, kvd2_1.tn_area, kvd2_1.tn_storelevel
  • Sort Method: external merge Disk: 4,928kB
128. 1,807.677 1,807.677 ↓ 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=1,020.000..1,807.677 rows=100,504 loops=1)

  • Filter: ((tn_end >= CURRENT_DATE) AND (tn_start <= CURRENT_DATE))
  • Rows Removed by Filter: 191,624
129. 223.330 15,787.970 ↑ 1.1 405,890 1

Materialize (cost=189,998.93..192,136.05 rows=427,425 width=32) (actual time=13,733.688..15,787.970 rows=405,890 loops=1)

130. 7,935.139 15,564.640 ↑ 1.1 405,890 1

Sort (cost=189,998.93..191,067.49 rows=427,425 width=32) (actual time=13,733.682..15,564.640 rows=405,890 loops=1)

  • Sort Key: kst_1.tn_storedevicion, kst_1.seleareaid, kst_1.storelevel
  • Sort Method: external merge Disk: 17,568kB
131. 7,629.501 7,629.501 ↑ 1.0 427,314 1

Seq Scan on kx_kq_store kst_1 (cost=0.00..139,796.34 rows=427,425 width=32) (actual time=0.015..7,629.501 rows=427,314 loops=1)

  • Filter: (platstatus = 1)
  • Rows Removed by Filter: 371
132. 0.062 0.248 ↑ 1.0 1 31

Bitmap Heap Scan on kx_visit_planvisit (cost=4.43..12.28 rows=1 width=16) (actual time=0.008..0.008 rows=1 loops=31)

  • Recheck Cond: (customerid = p_1.customerid)
  • Filter: ((plandate >= '2020-04-02'::date) AND (plandate <= '2020-04-02'::date) AND (userid = '1185018465094864896'::bigint) AND (platstatus = 1) AND (userid = p_1.userid))
  • Heap Blocks: exact=22
133. 0.186 0.186 ↑ 2.0 1 31

Bitmap Index Scan on idx_kx_visit_planvisit_customerid (cost=0.00..4.43 rows=2 width=0) (actual time=0.006..0.006 rows=1 loops=31)

  • Index Cond: (customerid = p_1.customerid)
Planning time : 393.469 ms
Execution time : 305,275.943 ms