explain.depesz.com

PostgreSQL's explain analyze made readable

Result: c6pm

Settings
# exclusive inclusive rows x rows loops node
1. 0.002 16,940.176 ↑ 9.0 2 1

Limit (cost=1,440,481.49..1,440,481.54 rows=18 width=1,498) (actual time=16,940.175..16,940.176 rows=2 loops=1)

2. 0.013 16,940.174 ↑ 9.0 2 1

Sort (cost=1,440,481.49..1,440,481.54 rows=18 width=1,498) (actual time=16,940.173..16,940.174 rows=2 loops=1)

  • Sort Key: vc.status DESC, a.actualvisittime DESC
  • Sort Method: quicksort Memory: 26kB
3. 0.021 16,940.161 ↑ 9.0 2 1

HashAggregate (cost=1,440,480.76..1,440,480.94 rows=18 width=1,498) (actual time=16,940.160..16,940.161 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
4. 0.002 16,940.140 ↑ 9.0 2 1

Append (cost=696,515.03..1,440,479.77 rows=18 width=1,498) (actual time=11,947.529..16,940.140 rows=2 loops=1)

5. 0.001 0.085 ↓ 0.0 0 1

Nested Loop Left Join (cost=696,515.03..717,473.34 rows=15 width=334) (actual time=0.085..0.085 rows=0 loops=1)

  • Join Filter: (kvw.customerid = kqs.id)
6. 0.000 0.084 ↓ 0.0 0 1

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

7. 0.006 0.084 ↓ 0.0 0 1

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

  • Hash Cond: ((tvd.tn_leveldevision = kqs.tn_storedevicion) AND (tvd.tn_storelevel = kqs.storelevel) AND (tvd.tn_area = kqs.seleareaid))
8. 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))
9. 0.001 0.078 ↓ 0.0 0 1

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

  • Buckets: 1024 Batches: 1 Memory Usage: 8kB
10. 0.000 0.077 ↓ 0.0 0 1

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

11. 0.001 0.077 ↓ 0.0 0 1

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

12. 0.000 0.076 ↓ 0.0 0 1

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

13. 0.000 0.076 ↓ 0.0 0 1

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

  • Join Filter: (po.orgstructid = p.userid)
14. 0.001 0.076 ↓ 0.0 0 1

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

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

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

16. 0.000 0.075 ↓ 0.0 0 1

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

17. 0.075 0.075 ↓ 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.075..0.075 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
18. 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)
19. 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))
20. 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))
21. 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)
22. 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)
23. 0.000 0.000 ↓ 0.0 0

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

24. 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)
25. 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)
26. 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)
27. 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)
28. 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)
29. 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)))
30. 0.000 0.000 ↓ 0.0 0

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

31. 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)

32. 0.000 0.000 ↓ 0.0 0

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

33. 0.000 0.000 ↓ 0.0 0

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

34. 0.000 0.000 ↓ 0.0 0

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

35. 0.000 0.000 ↓ 0.0 0

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

36. 0.000 0.000 ↓ 0.0 0

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

37. 0.000 0.000 ↓ 0.0 0

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

38. 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)
39. 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)
40. 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)
41. 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)))
42. 0.000 0.000 ↓ 0.0 0

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

43. 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)

44. 0.000 0.000 ↓ 0.0 0

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

45. 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)

46. 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)))
47. 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)
48. 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)
49. 0.000 0.000 ↓ 0.0 0

Materialize (cost=411,124.37..419,353.13 rows=200 width=16) (never executed)

50. 0.000 0.000 ↓ 0.0 0

GroupAggregate (cost=411,124.37..419,350.13 rows=200 width=16) (never executed)

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

GroupAggregate (cost=411,124.37..416,264.22 rows=205,594 width=36) (never executed)

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

Sort (cost=411,124.37..411,638.35 rows=205,594 width=28) (never executed)

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

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

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

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

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

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

56. 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))
57. 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
58. 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))
59. 0.000 0.000 ↓ 0.0 0

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

60. 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
61. 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)
62. 0.003 16,940.053 ↑ 1.5 2 1

Subquery Scan on *SELECT* 2 (cost=427,748.41..723,006.28 rows=3 width=354) (actual time=11,947.443..16,940.053 rows=2 loops=1)

63. 0.047 16,940.050 ↑ 1.5 2 1

Nested Loop Anti Join (cost=427,748.41..723,006.24 rows=3 width=350) (actual time=11,947.441..16,940.050 rows=2 loops=1)

  • Join Filter: ((kx_visit_planvisit.userid = p_1.userid) AND (kx_visit_planvisit.customerid = p_1.customerid))
64. 0.012 16,939.951 ↑ 1.5 2 1

Nested Loop Left Join (cost=427,748.12..722,997.80 rows=3 width=294) (actual time=11,947.364..16,939.951 rows=2 loops=1)

65. 4.528 16,939.917 ↑ 1.5 2 1

Hash Left Join (cost=427,747.97..722,997.31 rows=3 width=294) (actual time=11,947.348..16,939.917 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))
66. 29.047 16,802.502 ↑ 1.5 2 1

Nested Loop Left Join (cost=411,389.12..704,712.77 rows=3 width=310) (actual time=11,814.354..16,802.502 rows=2 loops=1)

  • Join Filter: (kvw_1.customerid = kqs_1.id)
  • Rows Removed by Join Filter: 383731
67. 0.010 12,096.623 ↑ 1.5 2 1

Nested Loop Left Join (cost=264.76..285,351.15 rows=3 width=310) (actual time=7,138.036..12,096.623 rows=2 loops=1)

68. 0.010 12,096.591 ↑ 1.5 2 1

Nested Loop (cost=264.33..285,346.45 rows=3 width=262) (actual time=7,138.022..12,096.591 rows=2 loops=1)

69. 0.005 3.137 ↓ 2.0 2 1

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

70. 0.005 3.116 ↓ 2.0 2 1

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

  • Join Filter: (po_1.orgstructid = p_1.userid)
71. 0.010 3.089 ↓ 2.0 2 1

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

72. 0.014 3.063 ↓ 2.0 2 1

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

  • Merge Cond: (p_1.customerid = vc_1.customerid)
  • Join Filter: (vc_1.userid = p_1.userid)
73. 0.009 1.541 ↓ 2.0 2 1

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

  • Sort Key: p_1.customerid
  • Sort Method: quicksort Memory: 25kB
74. 1.532 1.532 ↓ 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.676..1.532 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: 2512
75. 0.008 1.508 ↓ 2.0 2 1

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

  • Sort Key: vc_1.customerid
  • Sort Method: quicksort Memory: 25kB
76. 1.500 1.500 ↓ 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.458..1.500 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: 2512
77. 0.016 0.016 ↓ 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.008..0.008 rows=0 loops=2)

  • Index Cond: (id = p_1.customerid)
  • Filter: (platstatus = 1)
78. 0.022 0.022 ↑ 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.011..0.011 rows=1 loops=2)

  • Index Cond: (orgstructid = '1160102352791932928'::bigint)
  • Filter: (platstatus = 1)
79. 0.016 0.016 ↑ 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.008..0.008 rows=1 loops=2)

  • Index Cond: (po_1.userinfoid = userinfoid)
  • Filter: (platstatus = 1)
80. 0.034 12,093.444 ↑ 3.0 1 2

Append (cost=246.57..285,304.54 rows=3 width=195) (actual time=3,751.013..6,046.722 rows=1 loops=2)

81. 74.660 11,828.828 ↓ 0.0 0 2

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

  • Filter: (p_1.customerid = "*SELECT* 1_1".id)
  • Rows Removed by Filter: 395332
82. 10,237.508 11,754.168 ↑ 1.2 395,333 2

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

  • Hash Cond: (ks_1.storelevel = pd3_1.dickey)
83. 248.907 1,516.480 ↑ 1.2 395,333 2

Hash Left Join (cost=220.75..150,147.79 rows=470,968 width=279) (actual time=1.184..758.240 rows=395,333 loops=2)

  • Hash Cond: (ks_1.storetype = pd2_1.dickey)
84. 160.666 1,267.346 ↑ 1.2 395,333 2

Hash Left Join (cost=194.93..143,646.16 rows=470,968 width=275) (actual time=1.066..633.673 rows=395,333 loops=2)

  • Hash Cond: (ks_1.channeltype = pd1_2.dickey)
85. 236.142 1,106.446 ↑ 1.2 395,333 2

Hash Left Join (cost=169.11..141,854.20 rows=470,968 width=283) (actual time=0.945..553.223 rows=395,333 loops=2)

  • Hash Cond: (ks_1.seleareaid = ps_2.orgstructid)
86. 868.440 868.440 ↑ 1.2 395,333 2

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

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

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

  • Buckets: 4096 Batches: 1 Memory Usage: 147kB
88. 1.316 1.316 ↓ 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.007..1.316 rows=2,322 loops=1)

89. 0.084 0.234 ↓ 1.0 395 1

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

  • Buckets: 1024 Batches: 1 Memory Usage: 24kB
90. 0.150 0.150 ↓ 1.0 395 1

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

91. 0.109 0.227 ↓ 1.0 395 1

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

  • Buckets: 1024 Batches: 1 Memory Usage: 30kB
92. 0.118 0.118 ↓ 1.0 395 1

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

93. 0.083 0.180 ↓ 1.0 395 1

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

  • Buckets: 1024 Batches: 1 Memory Usage: 24kB
94. 0.097 0.097 ↓ 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.097 rows=395 loops=1)

95. 1.814 264.568 ↓ 0.0 0 2

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

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

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

  • Hash Cond: (kc_3.channelcustomersort = pd1_3.dickey)
97. 8.637 28.682 ↓ 1.0 9,259 2

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

  • Hash Cond: (kc_3.saleareaid = ps_3.orgstructid)
98. 18.896 18.896 ↓ 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..9.448 rows=9,259 loops=2)

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

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

  • Buckets: 4096 Batches: 1 Memory Usage: 147kB
100. 0.854 0.854 ↓ 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.854 rows=2,322 loops=1)

101. 0.057 0.170 ↓ 1.0 395 1

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

  • Buckets: 1024 Batches: 1 Memory Usage: 30kB
102. 0.113 0.113 ↓ 1.0 395 1

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

103. 0.014 0.014 ↓ 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.007..0.007 rows=0 loops=2)

  • Index Cond: (id = p_1.customerid)
  • Filter: ((platstatus = 1) AND ((customertype = '905324680615956480'::bigint) OR (customertype = '905324761813487616'::bigint)))
104. 0.022 0.022 ↓ 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.011..0.011 rows=0 loops=2)

  • Index Cond: (id = "*SELECT* 1_1".id)
  • Filter: (platstatus = 1)
105. 65.058 4,676.832 ↓ 959.3 191,866 2

Materialize (cost=411,124.37..419,353.13 rows=200 width=16) (actual time=1,879.178..2,338.416 rows=191,866 loops=2)

106. 113.865 4,611.774 ↓ 1,018.2 203,644 1

GroupAggregate (cost=411,124.37..419,350.13 rows=200 width=16) (actual time=3,758.324..4,611.774 rows=203,644 loops=1)

  • Group Key: kvw_1.customerid
107. 431.954 4,497.909 ↓ 2.6 525,991 1

GroupAggregate (cost=411,124.37..416,264.22 rows=205,594 width=36) (actual time=3,758.305..4,497.909 rows=525,991 loops=1)

  • Group Key: kvw_1.customerid, kvw_1.recorddate, kvd2_1.tn_start, kvd2_1.tn_end
108. 1,352.993 4,065.955 ↓ 9.3 1,920,810 1

Sort (cost=411,124.37..411,638.35 rows=205,594 width=28) (actual time=3,758.296..4,065.955 rows=1,920,810 loops=1)

  • Sort Key: kvw_1.customerid, kvw_1.recorddate, kvd2_1.tn_start, kvd2_1.tn_end
  • Sort Method: external merge Disk: 79024kB
109. 925.143 2,712.962 ↓ 9.3 1,920,810 1

Hash Join (cost=258,178.72..388,060.27 rows=205,594 width=28) (actual time=1,306.515..2,712.962 rows=1,920,810 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
110. 481.568 481.568 ↑ 1.0 2,051,932 1

Seq Scan on kx_visit_workrecord kvw_1 (cost=0.00..71,779.88 rows=2,061,750 width=12) (actual time=0.008..481.568 rows=2,051,932 loops=1)

  • Filter: (platstatus = 1)
111. 71.126 1,306.251 ↑ 1.2 354,460 1

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

  • Buckets: 65536 Batches: 8 Memory Usage: 2933kB
112. 93.502 1,235.125 ↑ 1.2 354,460 1

Merge Join (cost=237,767.26..250,412.05 rows=423,014 width=24) (actual time=995.489..1,235.125 rows=354,460 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))
113. 260.110 380.561 ↑ 1.0 280,485 1

Sort (cost=44,029.85..44,732.09 rows=280,895 width=40) (actual time=337.593..380.561 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
114. 120.451 120.451 ↑ 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..120.451 rows=280,858 loops=1)

  • Filter: ((platstatus = 1) AND (tn_end >= CURRENT_DATE) AND (tn_start <= CURRENT_DATE))
  • Rows Removed by Filter: 67
115. 38.165 761.062 ↑ 1.3 376,712 1

Materialize (cost=193,737.41..196,092.25 rows=470,968 width=32) (actual time=657.804..761.062 rows=376,712 loops=1)

116. 313.835 722.897 ↑ 1.3 376,712 1

Sort (cost=193,737.41..194,914.83 rows=470,968 width=32) (actual time=657.800..722.897 rows=376,712 loops=1)

  • Sort Key: kks_1.storelevel, kks_1.seleareaid, kks_1.tn_storedevicion
  • Sort Method: external merge Disk: 16128kB
117. 409.062 409.062 ↑ 1.2 395,333 1

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

  • Filter: (platstatus = 1)
  • Rows Removed by Filter: 367
118. 62.593 132.887 ↑ 1.0 280,858 1

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

  • Buckets: 65536 Batches: 8 Memory Usage: 2714kB
119. 70.294 70.294 ↑ 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..70.294 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
120. 0.022 0.022 ↓ 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.011..0.011 rows=0 loops=2)

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

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

122. 0.050 0.050 ↓ 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.050..0.050 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
Planning time : 21.903 ms
Execution time : 16,972.017 ms