explain.depesz.com

PostgreSQL's explain analyze made readable

Result: vch9

Settings
# exclusive inclusive rows x rows loops node
1. 0.000 55.012 ↑ 11.4 71 1

Sort (cost=146.63..148.65 rows=808 width=138) (actual time=55.009..55.012 rows=71 loops=1)

2. 16,299.184 16,299.184 ↑ 9.0 2 1

Sort Key: n.nspname, pLimit (cost=1,440,392.94..1,440,392.98 rows=18 width=1,498) (actual time=16,299.184..16,299.184 rows=2 loops=1)

3. 0.000 16,299.183 ↑ 9.0 2 1

Sort (cost=1,440,392.94..1,440,392.98 rows=18 width=1,498) (actual time=16,299.182..16,299.183 rows=2 loops=1)

  • Sort Key: vc.status DESC, a.actualvisittime DESC
  • Sort Method: quicksort Memory: 26kB
  • Sort Method: quicksort Memory: 43kB
4. 0.024 16,299.164 ↑ 9.0 2 1

HashAggregate (cost=1,440,392.20..1,440,392.38 rows=18 width=1,498) (actual time=16,299.162..16,299.164 rows=2 loops=1)

  • Group 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 kqs.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(kqs.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
5. 0.002 16,299.140 ↑ 9.0 2 1

Append (cost=696,473.11..1,440,391.21 rows=18 width=1,498) (actual time=10,525.323..16,299.140 rows=2 loops=1)

6. 0.000 0.069 ↓ 0.0 0 1

Nested Loop Left Join (cost=696,473.11..717,429.06 rows=15 width=334) (actual time=0.069..0.069 rows=0 loops=1)

  • Join Filter: (kvw.customerid = kqs.id)
7. 0.001 0.069 ↓ 0.0 0 1

Nested Loop Left Join (cost=285,390.66..298,075.38 rows=15 width=310) (actual time=0.069..0.069 rows=0 loops=1)

8. 0.015 0.068 ↓ 0.0 0 1

Hash Right Join (cost=285,390.51..298,072.90 rows=15 width=310) (actual time=0.068..0.068 rows=0 loops=1)

  • Hash Cond: ((tvd.tn_leveldevision = kqs.tn_storedevicion) AND (tvd.tn_storelevel = kqs.storelevel) AND (tvd.tn_area = kqs.seleareaid))
9. 0.000 0.000 ↓ 0.0 0

Seq Scan on tn_visit_detail tvd (cost=0.00..9,522.19 rows=280,895 width=32) (never executed)

  • Filter: ((tn_start <= '2019-11-02 16:09:19'::timestamp without time zone) AND (tn_end >= '2019-11-02 16:09:19'::timestamp without time zone) AND (platstatus = 1))
10. 0.000 0.053 ↓ 0.0 0 1

Hash (cost=285,390.25..285,390.25 rows=15 width=326) (actual time=0.053..0.053 rows=0 loops=1)

  • Buckets: 1024 Batches: 1 Memory Usage: 8kB
11. 0.000 0.053 ↓ 0.0 0 1

Nested Loop Left Join (cost=248.98..285,390.25 rows=15 width=326) (actual time=0.053..0.053 rows=0 loops=1)

12. 0.001 0.053 ↓ 0.0 0 1

Nested Loop (cost=248.55..285,366.75 rows=15 width=278) (actual time=0.052..0.053 rows=0 loops=1)

13. 0.000 0.052 ↓ 0.0 0 1

Nested Loop Left Join (cost=1.98..62.18 rows=1 width=79) (actual time=0.052..0.052 rows=0 loops=1)

14. 0.000 0.052 ↓ 0.0 0 1

Nested Loop Left Join (cost=1.71..53.87 rows=1 width=78) (actual time=0.052..0.052 rows=0 loops=1)

  • Join Filter: (po.orgstructid = p.userid)
15. 0.000 0.052 ↓ 0.0 0 1

Nested Loop Left Join (cost=1.43..45.56 rows=1 width=70) (actual time=0.052..0.052 rows=0 loops=1)

  • Join Filter: ((vc.userid = p.userid) AND (vc.customerid = p.customerid) AND (vc.customertype = p.customertype))
16. 0.000 0.052 ↓ 0.0 0 1

Nested Loop Left Join (cost=1.00..37.09 rows=1 width=62) (actual time=0.052..0.052 rows=0 loops=1)

17. 0.001 0.052 ↓ 0.0 0 1

Nested Loop Left Join (cost=0.57..16.62 rows=1 width=52) (actual time=0.051..0.052 rows=0 loops=1)

18. 0.051 0.051 ↓ 0.0 0 1

Index Scan using idx_kx_visit_planvisit_plandate on kx_visit_planvisit p (cost=0.29..8.31 rows=1 width=40) (actual time=0.051..0.051 rows=0 loops=1)

  • Index Cond: ((plandate >= '2019-11-02'::date) AND (plandate <= '2019-11-02'::date))
  • Filter: ((platstatus = 1) AND (userid = '1160102352791932928'::bigint))
  • Rows Removed by Filter: 151
19. 0.000 0.000 ↓ 0.0 0

Index Scan using ka_kq_channelcustomers_pkey on ka_kq_channelcustomers kc (cost=0.29..8.30 rows=1 width=20) (never executed)

  • Index Cond: (id = p.customerid)
  • Filter: (platstatus = 1)
20. 0.000 0.000 ↓ 0.0 0

Index Scan using idx_kx_visit_actual_customerid on kx_visit_actual a (cost=0.42..20.46 rows=1 width=26) (never executed)

  • Index Cond: (customerid = p.customerid)
  • Filter: ((actualvisittime >= '2019-11-02 00:00:00'::timestamp without time zone) AND (actualvisittime <= '2019-11-02 23:59:59'::timestamp without time zone) AND (userid = '1160102352791932928'::bigint) AND (platstatus = 1) AND (userid = p.userid))
21. 0.000 0.000 ↓ 0.0 0

Index Scan using idx_kx_visit_customerstatus_visitdate on kx_visit_customerstatus vc (cost=0.42..8.45 rows=1 width=32) (never executed)

  • Index Cond: ((visitdate >= '2019-11-02'::date) AND (visitdate <= '2019-11-02'::date))
  • Filter: ((userid = '1160102352791932928'::bigint) AND (platstatus = 1))
22. 0.000 0.000 ↓ 0.0 0

Index Scan using ix_pl_orgstruct_orgstructid on pl_orgstruct po (cost=0.28..8.30 rows=1 width=16) (never executed)

  • Index Cond: (orgstructid = '1160102352791932928'::bigint)
  • Filter: (platstatus = 1)
23. 0.000 0.000 ↓ 0.0 0

Index Scan using pl_userinfo_pkey on pl_userinfo pu (cost=0.28..8.30 rows=1 width=17) (never executed)

  • Index Cond: (po.userinfoid = userinfoid)
  • Filter: (platstatus = 1)
24. 0.000 0.000 ↓ 0.0 0

Append (cost=246.57..285,304.54 rows=3 width=207) (never executed)

25. 0.000 0.000 ↓ 0.0 0

Subquery Scan on *SELECT* 1 (cost=246.57..280,053.40 rows=1 width=207) (never executed)

  • Filter: (p.customerid = "*SELECT* 1".id)
26. 0.000 0.000 ↓ 0.0 0

Hash Left Join (cost=246.57..274,166.30 rows=470,968 width=1,687) (never executed)

  • Hash Cond: (ks.storelevel = pd3.dickey)
27. 0.000 0.000 ↓ 0.0 0

Hash Left Join (cost=220.75..150,147.79 rows=470,968 width=279) (never executed)

  • Hash Cond: (ks.storetype = pd2.dickey)
28. 0.000 0.000 ↓ 0.0 0

Hash Left Join (cost=194.93..143,646.16 rows=470,968 width=275) (never executed)

  • Hash Cond: (ks.channeltype = pd1.dickey)
29. 0.000 0.000 ↓ 0.0 0

Hash Left Join (cost=169.11..141,854.20 rows=470,968 width=283) (never executed)

  • Hash Cond: (ks.seleareaid = ps.orgstructid)
30. 0.000 0.000 ↓ 0.0 0

Seq Scan on kx_kq_store ks (cost=0.00..140,446.54 rows=470,968 width=215) (never executed)

  • Filter: ((platstatus = 1) AND ((customertype = '905324680615956480'::bigint) OR (customertype = '905324761813487616'::bigint)))
31. 0.000 0.000 ↓ 0.0 0

Hash (cost=140.16..140.16 rows=2,316 width=84) (never executed)

32. 0.000 0.000 ↓ 0.0 0

Seq Scan on pl_orgstruct ps (cost=0.00..140.16 rows=2,316 width=84) (never executed)

33. 0.000 0.000 ↓ 0.0 0

Hash (cost=20.92..20.92 rows=392 width=8) (never executed)

34. 0.000 0.000 ↓ 0.0 0

Seq Scan on pl_dictionary pd1 (cost=0.00..20.92 rows=392 width=8) (never executed)

35. 0.000 0.000 ↓ 0.0 0

Hash (cost=20.92..20.92 rows=392 width=20) (never executed)

36. 0.000 0.000 ↓ 0.0 0

Seq Scan on pl_dictionary pd2 (cost=0.00..20.92 rows=392 width=20) (never executed)

37. 0.000 0.000 ↓ 0.0 0

Hash (cost=20.92..20.92 rows=392 width=20) (never executed)

38. 0.000 0.000 ↓ 0.0 0

Seq Scan on pl_dictionary pd3 (cost=0.00..20.92 rows=392 width=20) (never executed)

39. 0.000 0.000 ↓ 0.0 0

Subquery Scan on *SELECT* 2_1 (cost=194.93..5,242.98 rows=1 width=227) (never executed)

  • Filter: (p.customerid = "*SELECT* 2_1".id)
40. 0.000 0.000 ↓ 0.0 0

Hash Left Join (cost=194.93..5,127.29 rows=9,255 width=1,707) (never executed)

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

Hash Left Join (cost=169.11..2,660.46 rows=9,255 width=267) (never executed)

  • Hash Cond: (kc_1.saleareaid = ps_1.orgstructid)
42. 0.000 0.000 ↓ 0.0 0

Seq Scan on ka_kq_channelcustomers kc_1 (cost=0.00..2,467.01 rows=9,255 width=199) (never executed)

  • Filter: ((platstatus = 1) AND ((customertype = '905324680615956480'::bigint) OR (customertype = '905324761813487616'::bigint)))
43. 0.000 0.000 ↓ 0.0 0

Hash (cost=140.16..140.16 rows=2,316 width=84) (never executed)

44. 0.000 0.000 ↓ 0.0 0

Seq Scan on pl_orgstruct ps_1 (cost=0.00..140.16 rows=2,316 width=84) (never executed)

45. 0.000 0.000 ↓ 0.0 0

Hash (cost=20.92..20.92 rows=392 width=20) (never executed)

46. 0.000 0.000 ↓ 0.0 0

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

47. 0.000 0.000 ↓ 0.0 0

Index Scan using kx_kq_ka_pkey on kx_kq_ka ka (cost=0.14..8.16 rows=1 width=912) (never executed)

  • Index Cond: (id = p.customerid)
  • Filter: ((platstatus = 1) AND ((customertype = '905324680615956480'::bigint) OR (customertype = '905324761813487616'::bigint)))
48. 0.000 0.000 ↓ 0.0 0

Index Scan using kx_kq_store_pkey on kx_kq_store kqs (cost=0.42..1.57 rows=1 width=48) (never executed)

  • Index Cond: (id = "*SELECT* 1".id)
  • Filter: (platstatus = 1)
49. 0.000 0.000 ↓ 0.0 0

Index Scan using pk_tn_visit_administration_1 on tn_visit_administration tva (cost=0.14..0.17 rows=1 width=16) (never executed)

  • Index Cond: (tn_id = tvd.tn_adminid)
  • Filter: (platstatus = 1)
50. 0.000 0.000 ↓ 0.0 0

Materialize (cost=411,082.45..419,308.85 rows=200 width=16) (never executed)

51. 0.000 0.000 ↓ 0.0 0

GroupAggregate (cost=411,082.45..419,305.85 rows=200 width=16) (never executed)

  • Group Key: kvw.customerid
52. 0.000 0.000 ↓ 0.0 0

GroupAggregate (cost=411,082.45..416,220.82 rows=205,535 width=36) (never executed)

  • Group Key: kvw.customerid, kvw.recorddate, kvd2.tn_start, kvd2.tn_end
53. 0.000 0.000 ↓ 0.0 0

Sort (cost=411,082.45..411,596.28 rows=205,535 width=28) (never executed)

  • Sort Key: kvw.customerid, kvw.recorddate, kvd2.tn_start, kvd2.tn_end
54. 0.000 0.000 ↓ 0.0 0

Hash Join (cost=258,178.72..388,023.99 rows=205,535 width=28) (never executed)

  • Hash Cond: (kvw.customerid = kks.id)
  • Join Filter: ((kvd2.tn_start <= kvw.recorddate) AND (kvd2.tn_end >= kvw.recorddate))
55. 0.000 0.000 ↓ 0.0 0

Seq Scan on kx_visit_workrecord kvw (cost=0.00..71,759.60 rows=2,061,168 width=12) (never executed)

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

Hash (cost=250,412.05..250,412.05 rows=423,014 width=24) (never executed)

57. 0.000 0.000 ↓ 0.0 0

Merge Join (cost=237,767.26..250,412.05 rows=423,014 width=24) (never executed)

  • Merge Cond: ((kvd2.tn_storelevel = kks.storelevel) AND (kvd2.tn_area = kks.seleareaid) AND (kvd2.tn_leveldevision = kks.tn_storedevicion))
58. 0.000 0.000 ↓ 0.0 0

Sort (cost=44,029.85..44,732.09 rows=280,895 width=40) (never executed)

  • Sort Key: kvd2.tn_storelevel, kvd2.tn_area, kvd2.tn_leveldevision
59. 0.000 0.000 ↓ 0.0 0

Seq Scan on tn_visit_detail kvd2 (cost=0.00..10,926.81 rows=280,895 width=40) (never executed)

  • Filter: ((platstatus = 1) AND (tn_end >= CURRENT_DATE) AND (tn_start <= CURRENT_DATE))
60. 0.000 0.000 ↓ 0.0 0

Materialize (cost=193,737.41..196,092.25 rows=470,968 width=32) (never executed)

61. 0.000 0.000 ↓ 0.0 0

Sort (cost=193,737.41..194,914.83 rows=470,968 width=32) (never executed)

  • Sort Key: kks.storelevel, kks.seleareaid, kks.tn_storedevicion
62. 0.000 0.000 ↓ 0.0 0

Seq Scan on kx_kq_store kks (cost=0.00..138,089.81 rows=470,968 width=32) (never executed)

  • Filter: (platstatus = 1)
63. 0.004 16,299.069 ↑ 1.5 2 1

Subquery Scan on *SELECT* 2 (cost=427,706.49..722,962.00 rows=3 width=354) (actual time=10,525.253..16,299.069 rows=2 loops=1)

64. 0.044 16,299.065 ↑ 1.5 2 1

Nested Loop Anti Join (cost=427,706.49..722,961.96 rows=3 width=350) (actual time=10,525.251..16,299.065 rows=2 loops=1)

  • Join Filter: ((kx_visit_planvisit.userid = p_1.userid) AND (kx_visit_planvisit.customerid = p_1.customerid))
65. 0.011 16,298.967 ↑ 1.5 2 1

Nested Loop Left Join (cost=427,706.20..722,953.52 rows=3 width=294) (actual time=10,525.176..16,298.967 rows=2 loops=1)

66. 4.668 16,298.930 ↑ 1.5 2 1

Hash Left Join (cost=427,706.05..722,953.03 rows=3 width=294) (actual time=10,525.158..16,298.930 rows=2 loops=1)

  • Hash Cond: ((kqs_1.tn_storedevicion = tvd_1.tn_leveldevision) AND (kqs_1.storelevel = tvd_1.tn_storelevel) AND (kqs_1.seleareaid = tvd_1.tn_area))
67. 27.354 16,161.888 ↑ 1.5 2 1

Nested Loop Left Join (cost=411,347.20..704,668.49 rows=3 width=310) (actual time=10,392.685..16,161.888 rows=2 loops=1)

  • Join Filter: (kvw_1.customerid = kqs_1.id)
  • Rows Removed by Join Filter: 383674
68. 0.012 11,419.498 ↑ 1.5 2 1

Nested Loop Left Join (cost=264.76..285,351.15 rows=3 width=310) (actual time=5,679.086..11,419.498 rows=2 loops=1)

69. 0.011 11,419.458 ↑ 1.5 2 1

Nested Loop (cost=264.33..285,346.45 rows=3 width=262) (actual time=5,679.066..11,419.458 rows=2 loops=1)

70. 0.005 1.885 ↓ 2.0 2 1

Nested Loop Left Join (cost=17.76..41.88 rows=1 width=67) (actual time=1.842..1.885 rows=2 loops=1)

71. 0.005 1.852 ↓ 2.0 2 1

Nested Loop Left Join (cost=17.49..33.57 rows=1 width=66) (actual time=1.819..1.852 rows=2 loops=1)

  • Join Filter: (po_1.orgstructid = p_1.userid)
72. 0.008 1.815 ↓ 2.0 2 1

Nested Loop Left Join (cost=17.21..25.25 rows=1 width=58) (actual time=1.794..1.815 rows=2 loops=1)

73. 0.012 1.789 ↓ 2.0 2 1

Merge Left Join (cost=16.92..16.95 rows=1 width=46) (actual time=1.778..1.789 rows=2 loops=1)

  • Merge Cond: (p_1.customerid = vc_1.customerid)
  • Join Filter: (vc_1.userid = p_1.userid)
74. 0.015 0.908 ↓ 2.0 2 1

Sort (cost=8.46..8.47 rows=1 width=38) (actual time=0.907..0.908 rows=2 loops=1)

  • Sort Key: p_1.customerid
  • Sort Method: quicksort Memory: 25kB
75. 0.893 0.893 ↓ 2.0 2 1

Index Scan using idx_kx_visit_actual_actualvisitdate on kx_visit_actual p_1 (cost=0.42..8.45 rows=1 width=38) (actual time=0.338..0.893 rows=2 loops=1)

  • Index Cond: ((actualvisitdate >= '2019-11-02'::date) AND (actualvisitdate <= '2019-11-02'::date))
  • Filter: ((platstatus = 1) AND (userid = '1160102352791932928'::bigint))
  • Rows Removed by Filter: 2361
76. 0.015 0.869 ↓ 2.0 2 1

Sort (cost=8.46..8.47 rows=1 width=24) (actual time=0.867..0.869 rows=2 loops=1)

  • Sort Key: vc_1.customerid
  • Sort Method: quicksort Memory: 25kB
77. 0.854 0.854 ↓ 2.0 2 1

Index Scan using idx_kx_visit_customerstatus_visitdate on kx_visit_customerstatus vc_1 (cost=0.42..8.45 rows=1 width=24) (actual time=0.229..0.854 rows=2 loops=1)

  • Index Cond: ((visitdate >= '2019-11-02'::date) AND (visitdate <= '2019-11-02'::date))
  • Filter: ((userid = '1160102352791932928'::bigint) AND (platstatus = 1))
  • Rows Removed by Filter: 2361
78. 0.018 0.018 ↓ 0.0 0 2

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.009..0.009 rows=0 loops=2)

  • Index Cond: (id = p_1.customerid)
  • Filter: (platstatus = 1)
79. 0.032 0.032 ↑ 1.0 1 2

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

  • Index Cond: (orgstructid = '1160102352791932928'::bigint)
  • Filter: (platstatus = 1)
80. 0.028 0.028 ↑ 1.0 1 2

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

  • Index Cond: (po_1.userinfoid = userinfoid)
  • Filter: (platstatus = 1)
81. 0.028 11,417.562 ↑ 3.0 1 2

Append (cost=246.57..285,304.54 rows=3 width=195) (actual time=3,010.041..5,708.781 rows=1 loops=2)

82. 69.940 11,178.154 ↓ 0.0 0 2

Subquery Scan on *SELECT* 1_1 (cost=246.57..280,053.40 rows=1 width=195) (actual time=3,000.781..5,589.077 rows=0 loops=2)

  • Filter: (p_1.customerid = "*SELECT* 1_1".id)
  • Rows Removed by Filter: 395320
83. 9,686.605 11,108.214 ↑ 1.2 395,321 2

Hash Left Join (cost=246.57..274,166.30 rows=470,968 width=1,707) (actual time=0.898..5,554.107 rows=395,321 loops=2)

  • Hash Cond: (ks_1.storelevel = pd3_1.dickey)
84. 234.491 1,421.472 ↑ 1.2 395,321 2

Hash Left Join (cost=220.75..150,147.79 rows=470,968 width=279) (actual time=0.777..710.736 rows=395,321 loops=2)

  • Hash Cond: (ks_1.storetype = pd2_1.dickey)
85. 159.125 1,186.844 ↑ 1.2 395,321 2

Hash Left Join (cost=194.93..143,646.16 rows=470,968 width=275) (actual time=0.700..593.422 rows=395,321 loops=2)

  • Hash Cond: (ks_1.channeltype = pd1_2.dickey)
86. 217.476 1,027.568 ↑ 1.2 395,321 2

Hash Left Join (cost=169.11..141,854.20 rows=470,968 width=283) (actual time=0.617..513.784 rows=395,321 loops=2)

  • Hash Cond: (ks_1.seleareaid = ps_2.orgstructid)
87. 808.898 808.898 ↑ 1.2 395,321 2

Seq Scan on kx_kq_store ks_1 (cost=0.00..140,446.54 rows=470,968 width=215) (actual time=0.005..404.449 rows=395,321 loops=2)

  • Filter: ((platstatus = 1) AND ((customertype = '905324680615956480'::bigint) OR (customertype = '905324761813487616'::bigint)))
  • Rows Removed by Filter: 367
88. 0.371 1.194 ↓ 1.0 2,322 1

Hash (cost=140.16..140.16 rows=2,316 width=84) (actual time=1.194..1.194 rows=2,322 loops=1)

  • Buckets: 4096 Batches: 1 Memory Usage: 147kB
89. 0.823 0.823 ↓ 1.0 2,322 1

Seq Scan on pl_orgstruct ps_2 (cost=0.00..140.16 rows=2,316 width=84) (actual time=0.004..0.823 rows=2,322 loops=1)

90. 0.057 0.151 ↓ 1.0 395 1

Hash (cost=20.92..20.92 rows=392 width=8) (actual time=0.151..0.151 rows=395 loops=1)

  • Buckets: 1024 Batches: 1 Memory Usage: 24kB
91. 0.094 0.094 ↓ 1.0 395 1

Seq Scan on pl_dictionary pd1_2 (cost=0.00..20.92 rows=392 width=8) (actual time=0.006..0.094 rows=395 loops=1)

92. 0.079 0.137 ↓ 1.0 395 1

Hash (cost=20.92..20.92 rows=392 width=20) (actual time=0.136..0.137 rows=395 loops=1)

  • Buckets: 1024 Batches: 1 Memory Usage: 30kB
93. 0.058 0.058 ↓ 1.0 395 1

Seq Scan on pl_dictionary pd2_1 (cost=0.00..20.92 rows=392 width=20) (actual time=0.002..0.058 rows=395 loops=1)

94. 0.074 0.137 ↓ 1.0 395 1

Hash (cost=20.92..20.92 rows=392 width=8) (actual time=0.137..0.137 rows=395 loops=1)

  • Buckets: 1024 Batches: 1 Memory Usage: 24kB
95. 0.063 0.063 ↓ 1.0 395 1

Seq Scan on pl_dictionary pd3_1 (cost=0.00..20.92 rows=392 width=8) (actual time=0.003..0.063 rows=395 loops=1)

96. 1.592 239.368 ↓ 0.0 0 2

Subquery Scan on *SELECT* 2_2 (cost=194.93..5,242.98 rows=1 width=195) (actual time=68.860..119.684 rows=0 loops=2)

  • Filter: (p_1.customerid = "*SELECT* 2_2".id)
  • Rows Removed by Filter: 9258
97. 213.562 237.776 ↓ 1.0 9,259 2

Hash Left Join (cost=194.93..5,127.29 rows=9,255 width=1,707) (actual time=0.734..118.888 rows=9,259 loops=2)

  • Hash Cond: (kc_3.channelcustomersort = pd1_3.dickey)
98. 6.989 24.042 ↓ 1.0 9,259 2

Hash Left Join (cost=169.11..2,660.46 rows=9,255 width=267) (actual time=0.619..12.021 rows=9,259 loops=2)

  • Hash Cond: (kc_3.saleareaid = ps_3.orgstructid)
99. 15.868 15.868 ↓ 1.0 9,259 2

Seq Scan on ka_kq_channelcustomers kc_3 (cost=0.00..2,467.01 rows=9,255 width=199) (actual time=0.006..7.934 rows=9,259 loops=2)

  • Filter: ((platstatus = 1) AND ((customertype = '905324680615956480'::bigint) OR (customertype = '905324761813487616'::bigint)))
  • Rows Removed by Filter: 3
100. 0.375 1.185 ↓ 1.0 2,322 1

Hash (cost=140.16..140.16 rows=2,316 width=84) (actual time=1.185..1.185 rows=2,322 loops=1)

  • Buckets: 4096 Batches: 1 Memory Usage: 147kB
101. 0.810 0.810 ↓ 1.0 2,322 1

Seq Scan on pl_orgstruct ps_3 (cost=0.00..140.16 rows=2,316 width=84) (actual time=0.007..0.810 rows=2,322 loops=1)

102. 0.072 0.172 ↓ 1.0 395 1

Hash (cost=20.92..20.92 rows=392 width=20) (actual time=0.172..0.172 rows=395 loops=1)

  • Buckets: 1024 Batches: 1 Memory Usage: 30kB
103. 0.100 0.100 ↓ 1.0 395 1

Seq Scan on pl_dictionary pd1_3 (cost=0.00..20.92 rows=392 width=20) (actual time=0.008..0.100 rows=395 loops=1)

104. 0.012 0.012 ↓ 0.0 0 2

Index Scan using kx_kq_ka_pkey on kx_kq_ka ka_1 (cost=0.14..8.16 rows=1 width=880) (actual time=0.006..0.006 rows=0 loops=2)

  • Index Cond: (id = p_1.customerid)
  • Filter: ((platstatus = 1) AND ((customertype = '905324680615956480'::bigint) OR (customertype = '905324761813487616'::bigint)))
105. 0.028 0.028 ↓ 0.0 0 2

Index Scan using kx_kq_store_pkey on kx_kq_store kqs_1 (cost=0.42..1.57 rows=1 width=48) (actual time=0.014..0.014 rows=0 loops=2)

  • Index Cond: (id = "*SELECT* 1_1".id)
  • Filter: (platstatus = 1)
106. 60.532 4,715.036 ↓ 959.2 191,838 2

Materialize (cost=411,082.45..419,308.85 rows=200 width=16) (actual time=1,935.005..2,357.518 rows=191,838 loops=2)

107. 103.946 4,654.504 ↓ 1,018.0 203,607 1

GroupAggregate (cost=411,082.45..419,305.85 rows=200 width=16) (actual time=3,869.961..4,654.504 rows=203,607 loops=1)

  • Group Key: kvw_1.customerid
108. 397.019 4,550.558 ↓ 2.6 525,857 1

GroupAggregate (cost=411,082.45..416,220.82 rows=205,535 width=36) (actual time=3,869.942..4,550.558 rows=525,857 loops=1)

  • Group Key: kvw_1.customerid, kvw_1.recorddate, kvd2_1.tn_start, kvd2_1.tn_end
109. 1,371.676 4,153.539 ↓ 9.3 1,920,349 1

Sort (cost=411,082.45..411,596.28 rows=205,535 width=28) (actual time=3,869.933..4,153.539 rows=1,920,349 loops=1)

  • Sort Key: kvw_1.customerid, kvw_1.recorddate, kvd2_1.tn_start, kvd2_1.tn_end
  • Sort Method: external merge Disk: 79008kB
110. 972.111 2,781.863 ↓ 9.3 1,920,349 1

Hash Join (cost=258,178.72..388,023.99 rows=205,535 width=28) (actual time=1,298.619..2,781.863 rows=1,920,349 loops=1)

  • Hash Cond: (kvw_1.customerid = kks_1.id)
  • Join Filter: ((kvd2_1.tn_start <= kvw_1.recorddate) AND (kvd2_1.tn_end >= kvw_1.recorddate))
  • Rows Removed by Join Filter: 3
111. 511.652 511.652 ↑ 1.0 2,051,399 1

Seq Scan on kx_visit_workrecord kvw_1 (cost=0.00..71,759.60 rows=2,061,168 width=12) (actual time=0.009..511.652 rows=2,051,399 loops=1)

  • Filter: (platstatus = 1)
112. 76.810 1,298.100 ↑ 1.2 354,447 1

Hash (cost=250,412.05..250,412.05 rows=423,014 width=24) (actual time=1,298.100..1,298.100 rows=354,447 loops=1)

  • Buckets: 65536 Batches: 8 Memory Usage: 2933kB
113. 97.274 1,221.290 ↑ 1.2 354,447 1

Merge Join (cost=237,767.26..250,412.05 rows=423,014 width=24) (actual time=971.279..1,221.290 rows=354,447 loops=1)

  • Merge Cond: ((kvd2_1.tn_storelevel = kks_1.storelevel) AND (kvd2_1.tn_area = kks_1.seleareaid) AND (kvd2_1.tn_leveldevision = kks_1.tn_storedevicion))
114. 261.467 381.389 ↑ 1.0 280,485 1

Sort (cost=44,029.85..44,732.09 rows=280,895 width=40) (actual time=337.411..381.389 rows=280,485 loops=1)

  • Sort Key: kvd2_1.tn_storelevel, kvd2_1.tn_area, kvd2_1.tn_leveldevision
  • Sort Method: external merge Disk: 13760kB
115. 119.922 119.922 ↑ 1.0 280,858 1

Seq Scan on tn_visit_detail kvd2_1 (cost=0.00..10,926.81 rows=280,895 width=40) (actual time=0.013..119.922 rows=280,858 loops=1)

  • Filter: ((platstatus = 1) AND (tn_end >= CURRENT_DATE) AND (tn_start <= CURRENT_DATE))
  • Rows Removed by Filter: 67
116. 41.243 742.627 ↑ 1.3 376,699 1

Materialize (cost=193,737.41..196,092.25 rows=470,968 width=32) (actual time=633.774..742.627 rows=376,699 loops=1)

117. 302.352 701.384 ↑ 1.3 376,699 1

Sort (cost=193,737.41..194,914.83 rows=470,968 width=32) (actual time=633.769..701.384 rows=376,699 loops=1)

  • Sort Key: kks_1.storelevel, kks_1.seleareaid, kks_1.tn_storedevicion
  • Sort Method: external merge Disk: 16136kB
118. 399.032 399.032 ↑ 1.2 395,321 1

Seq Scan on kx_kq_store kks_1 (cost=0.00..138,089.81 rows=470,968 width=32) (actual time=0.010..399.032 rows=395,321 loops=1)

  • Filter: (platstatus = 1)
  • Rows Removed by Filter: 367
119. 62.618 132.374 ↑ 1.0 280,858 1

Hash (cost=9,522.19..9,522.19 rows=280,895 width=32) (actual time=132.374..132.374 rows=280,858 loops=1)

  • Buckets: 65536 Batches: 8 Memory Usage: 2714kB
120. 69.756 69.756 ↑ 1.0 280,858 1

Seq Scan on tn_visit_detail tvd_1 (cost=0.00..9,522.19 rows=280,895 width=32) (actual time=0.016..69.756 rows=280,858 loops=1)

  • Filter: ((tn_start <= '2019-11-02 16:09:19'::timestamp without time zone) AND (tn_end >= '2019-11-02 16:09:19'::timestamp without time zone) AND (platstatus = 1))
  • Rows Removed by Filter: 67
121. 0.026 0.026 ↓ 0.0 0 2

Index Scan using pk_tn_visit_administration_1 on tn_visit_administration tva_1 (cost=0.14..0.17 rows=1 width=16) (actual time=0.013..0.013 rows=0 loops=2)

  • Index Cond: (tn_id = tvd_1.tn_adminid)
  • Filter: (platstatus = 1)
122. 0.002 0.054 ↓ 0.0 0 2

Materialize (cost=0.29..8.32 rows=1 width=16) (actual time=0.027..0.027 rows=0 loops=2)

123. 0.052 0.052 ↓ 0.0 0 1

Index Scan using idx_kx_visit_planvisit_plandate on kx_visit_planvisit (cost=0.29..8.31 rows=1 width=16) (actual time=0.052..0.052 rows=0 loops=1)

  • Index Cond: ((plandate >= '2019-11-02'::date) AND (plandate <= '2019-11-02'::date))
  • Filter: ((userid = '1160102352791932928'::bigint) AND (platstatus = 1))
  • Rows Removed by Filter: 151
124. 1.378 54.854 ↑ 11.4 71 1

Hash Join (cost=1.14..107.61 rows=808 width=138) (actual time=42.495..54.854 rows=71 loops=1)

  • Hash Cond: (p.pronamespace = n.oid)
125. 53.465 53.465 ↓ 3.0 2,402 1

Seq Scan on pg_proc p (cost=0.00..89.30 rows=808 width=78) (actual time=0.052..53.465 rows=2,402 loops=1)

  • Filter: pg_function_is_visible(oid)
126. 0.004 0.011 ↑ 1.0 4 1

Hash (cost=1.09..1.09 rows=4 width=68) (actual time=0.011..0.011 rows=4 loops=1)

  • Buckets: 1024 Batches: 1 Memory Usage: 1kB
127. 0.007 0.007 ↑ 1.0 4 1

Seq Scan on pg_namespace n (cost=0.00..1.09 rows=4 width=68) (actual time=0.005..0.007 rows=4 loops=1)

  • Filter: ((nspname <> 'pg_catalog'::name) AND (nspname <> 'information_schema'::name))
Planning time : 18.814 ms