explain.depesz.com

PostgreSQL's explain analyze made readable

Result: Kl2f

Settings
# exclusive inclusive rows x rows loops node
1. 0.002 9,678.217 ↑ 5.3 3 1

Limit (cost=1,381,549.75..1,381,549.79 rows=16 width=1,498) (actual time=9,678.216..9,678.217 rows=3 loops=1)

2. 0.017 9,678.215 ↑ 5.3 3 1

Sort (cost=1,381,549.75..1,381,549.79 rows=16 width=1,498) (actual time=9,678.215..9,678.215 rows=3 loops=1)

  • Sort Key: vc.status DESC, a.actualvisittime DESC
  • Sort Method: quicksort Memory: 26kB
3. 0.029 9,678.198 ↑ 5.3 3 1

HashAggregate (cost=1,381,549.11..1,381,549.27 rows=16 width=1,498) (actual time=9,678.196..9,678.198 rows=3 loops=1)

  • Group Key: p.tn_source, ks.id, ks.storename, (CASE WHEN (p.customertype = '905324761813487616'::bigint) THEN kc.contactname WHEN (p.customertype = '905324680615956480'::bigint) THEN ks.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(ks.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), pd2.dicvalue, pd2.dicvalue, pd3.dicvalue, ks.address, ks.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 9,678.169 ↑ 5.3 3 1

Append (cost=414,428.32..1,381,548.23 rows=16 width=1,498) (actual time=5,472.482..9,678.169 rows=3 loops=1)

5. 0.000 0.064 ↓ 0.0 0 1

Nested Loop Left Join (cost=414,428.32..690,965.91 rows=13 width=673) (actual time=0.064..0.064 rows=0 loops=1)

6. 0.001 0.064 ↓ 0.0 0 1

Hash Left Join (cost=414,428.18..690,963.47 rows=13 width=653) (actual time=0.063..0.064 rows=0 loops=1)

  • Hash Cond: ((ks.tn_storedevicion = tvd.tn_leveldevision) AND (ks.storelevel = tvd.tn_storelevel) AND (ks.seleareaid = tvd.tn_area))
7. 0.000 0.063 ↓ 0.0 0 1

Hash Left Join (cost=398,069.33..672,668.11 rows=13 width=669) (actual time=0.063..0.063 rows=0 loops=1)

  • Hash Cond: ((p.userid = vc.userid) AND (p.customerid = vc.customerid) AND (p.customertype = vc.customertype))
8. 0.000 0.063 ↓ 0.0 0 1

Nested Loop Left Join (cost=398,060.86..672,659.48 rows=13 width=669) (actual time=0.063..0.063 rows=0 loops=1)

9. 0.001 0.063 ↓ 0.0 0 1

Nested Loop Left Join (cost=398,060.44..672,393.37 rows=13 width=659) (actual time=0.062..0.063 rows=0 loops=1)

10. 0.000 0.062 ↓ 0.0 0 1

Hash Left Join (cost=398,060.15..672,285.40 rows=13 width=647) (actual time=0.062..0.062 rows=0 loops=1)

  • Hash Cond: (p.userid = po.orgstructid)
11. 0.000 0.062 ↓ 0.0 0 1

Nested Loop Left Join (cost=398,043.53..672,268.60 rows=13 width=638) (actual time=0.062..0.062 rows=0 loops=1)

  • Join Filter: (kvw.customerid = ks.id)
12. 0.012 0.062 ↓ 0.0 0 1

Hash Join (cost=254.90..266,199.15 rows=13 width=630) (actual time=0.062..0.062 rows=0 loops=1)

  • Hash Cond: (ks.id = p.customerid)
13. 0.000 0.000 ↓ 0.0 0

Append (cost=246.57..260,626.01 rows=404,704 width=1,586) (never executed)

14. 0.000 0.000 ↓ 0.0 0

Hash Left Join (cost=246.57..251,441.15 rows=395,448 width=442) (never executed)

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

Hash Left Join (cost=220.75..147,304.94 rows=395,448 width=394) (never executed)

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

Hash Left Join (cost=194.93..141,841.71 rows=395,448 width=390) (never executed)

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

Hash Left Join (cost=169.11..140,332.95 rows=395,448 width=378) (never executed)

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

Seq Scan on kx_kq_store ks (cost=0.00..139,123.89 rows=395,448 width=302) (never executed)

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

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

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

21. 0.000 0.000 ↓ 0.0 0

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

22. 0.000 0.000 ↓ 0.0 0

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

23. 0.000 0.000 ↓ 0.0 0

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

24. 0.000 0.000 ↓ 0.0 0

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

25. 0.000 0.000 ↓ 0.0 0

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

26. 0.000 0.000 ↓ 0.0 0

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

27. 0.000 0.000 ↓ 0.0 0

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

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

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

  • Hash Cond: (kc_1.saleareaid = ps_1.orgstructid)
29. 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=255) (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_1 (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=20) (never executed)

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

34. 0.000 0.000 ↓ 0.0 0

Subquery Scan on *SELECT* 3 (cost=0.00..10.54 rows=1 width=1,972) (never executed)

35. 0.000 0.000 ↓ 0.0 0

Seq Scan on kx_kq_ka ka (cost=0.00..10.53 rows=1 width=1,968) (never executed)

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

Hash (cost=8.31..8.31 rows=1 width=40) (actual time=0.050..0.050 rows=0 loops=1)

  • Buckets: 1024 Batches: 1 Memory Usage: 8kB
37. 0.050 0.050 ↓ 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.050..0.050 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
38. 0.000 0.000 ↓ 0.0 0

Materialize (cost=397,788.63..406,030.95 rows=200 width=16) (never executed)

39. 0.000 0.000 ↓ 0.0 0

GroupAggregate (cost=397,788.63..406,027.95 rows=200 width=16) (never executed)

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

GroupAggregate (cost=397,788.63..402,936.95 rows=205,933 width=36) (never executed)

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

Sort (cost=397,788.63..398,303.46 rows=205,933 width=28) (never executed)

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

Hash Join (cost=244,991.40..374,685.17 rows=205,933 width=28) (never executed)

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

Seq Scan on kx_visit_workrecord kvw (cost=0.00..71,898.45 rows=2,065,156 width=12) (never executed)

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

Hash (cost=238,469.60..238,469.60 rows=355,184 width=24) (never executed)

45. 0.000 0.000 ↓ 0.0 0

Merge Join (cost=227,401.99..238,469.60 rows=355,184 width=24) (never executed)

  • Merge Cond: ((kvd2.tn_storelevel = kks.storelevel) AND (kvd2.tn_area = kks.seleareaid) AND (kvd2.tn_leveldevision = kks.tn_storedevicion))
46. 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
47. 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))
48. 0.000 0.000 ↓ 0.0 0

Materialize (cost=183,372.14..185,349.38 rows=395,448 width=32) (never executed)

49. 0.000 0.000 ↓ 0.0 0

Sort (cost=183,372.14..184,360.76 rows=395,448 width=32) (never executed)

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

Seq Scan on kx_kq_store kks (cost=0.00..137,145.06 rows=395,448 width=32) (never executed)

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

Hash (cost=16.61..16.61 rows=1 width=17) (never executed)

52. 0.000 0.000 ↓ 0.0 0

Nested Loop Left Join (cost=0.56..16.61 rows=1 width=17) (never executed)

53. 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)
54. 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)
55. 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)
56. 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))
57. 0.000 0.000 ↓ 0.0 0

Hash (cost=8.45..8.45 rows=1 width=32) (never executed)

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

Hash (cost=9,522.19..9,522.19 rows=280,895 width=32) (never executed)

60. 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 18:33:04'::timestamp without time zone) AND (tn_end >= '2019-11-02 18:33:04'::timestamp without time zone) AND (platstatus = 1))
61. 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)
62. 0.005 9,678.103 ↑ 1.0 3 1

Subquery Scan on *SELECT* 2 (cost=414,412.25..690,582.19 rows=3 width=673) (actual time=5,472.417..9,678.103 rows=3 loops=1)

63. 0.055 9,678.098 ↑ 1.0 3 1

Nested Loop Anti Join (cost=414,412.25..690,582.15 rows=3 width=669) (actual time=5,472.415..9,678.098 rows=3 loops=1)

  • Join Filter: ((kx_visit_planvisit.userid = p_1.userid) AND (kx_visit_planvisit.customerid = p_1.customerid))
64. 0.015 9,677.983 ↑ 1.0 3 1

Nested Loop Left Join (cost=414,411.96..690,573.72 rows=3 width=617) (actual time=5,472.334..9,677.983 rows=3 loops=1)

65. 7.993 9,677.944 ↑ 1.0 3 1

Hash Left Join (cost=414,411.82..690,573.22 rows=3 width=617) (actual time=5,472.318..9,677.944 rows=3 loops=1)

  • Hash Cond: ((ks_1.tn_storedevicion = tvd_1.tn_leveldevision) AND (ks_1.storelevel = tvd_1.tn_storelevel) AND (ks_1.seleareaid = tvd_1.tn_area))
66. 0.031 9,536.587 ↑ 1.0 3 1

Hash Left Join (cost=398,052.97..672,288.71 rows=3 width=633) (actual time=4,855.037..9,536.587 rows=3 loops=1)

  • Hash Cond: ((p_1.userid = vc_1.userid) AND (p_1.customerid = vc_1.customerid))
67. 41.578 9,535.376 ↑ 1.0 3 1

Nested Loop Left Join (cost=398,044.50..672,280.21 rows=3 width=625) (actual time=4,853.837..9,535.376 rows=3 loops=1)

  • Join Filter: (kvw_1.customerid = ks_1.id)
  • Rows Removed by Join Filter: 587752
68. 0.015 4,859.383 ↑ 1.0 3 1

Nested Loop Left Join (cost=255.88..266,240.76 rows=3 width=617) (actual time=312.946..4,859.383 rows=3 loops=1)

69. 0.016 4,859.335 ↑ 1.0 3 1

Nested Loop Left Join (cost=255.59..266,215.84 rows=3 width=605) (actual time=312.933..4,859.335 rows=3 loops=1)

  • Join Filter: (po_1.orgstructid = p_1.userid)
70. 42.577 4,859.253 ↑ 1.0 3 1

Hash Join (cost=255.03..266,199.18 rows=3 width=596) (actual time=312.870..4,859.253 rows=3 loops=1)

  • Hash Cond: (ks_1.id = p_1.customerid)
71. 39.804 4,815.528 ↑ 1.0 404,655 1

Append (cost=246.57..260,626.01 rows=404,704 width=1,586) (actual time=1.826..4,815.528 rows=404,655 loops=1)

72. 3,922.710 4,670.254 ↑ 1.0 395,396 1

Hash Left Join (cost=246.57..251,441.15 rows=395,448 width=442) (actual time=1.826..4,670.254 rows=395,396 loops=1)

  • Hash Cond: (ks_1.storelevel = pd3_1.dickey)
73. 125.985 747.416 ↑ 1.0 395,396 1

Hash Left Join (cost=220.75..147,304.94 rows=395,448 width=394) (actual time=1.599..747.416 rows=395,396 loops=1)

  • Hash Cond: (ks_1.storetype = pd2_1.dickey)
74. 89.922 621.300 ↑ 1.0 395,396 1

Hash Left Join (cost=194.93..141,841.71 rows=395,448 width=390) (actual time=1.456..621.300 rows=395,396 loops=1)

  • Hash Cond: (ks_1.channeltype = pd1_2.dickey)
75. 152.870 531.203 ↑ 1.0 395,396 1

Hash Left Join (cost=169.11..140,332.95 rows=395,448 width=378) (actual time=1.265..531.203 rows=395,396 loops=1)

  • Hash Cond: (ks_1.seleareaid = ps_2.orgstructid)
76. 377.103 377.103 ↑ 1.0 395,396 1

Seq Scan on kx_kq_store ks_1 (cost=0.00..139,123.89 rows=395,448 width=302) (actual time=0.007..377.103 rows=395,396 loops=1)

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

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

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

79. 0.077 0.175 ↓ 1.0 395 1

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

  • Buckets: 1024 Batches: 1 Memory Usage: 30kB
80. 0.098 0.098 ↓ 1.0 395 1

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

81. 0.075 0.131 ↓ 1.0 395 1

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

  • Buckets: 1024 Batches: 1 Memory Usage: 30kB
82. 0.056 0.056 ↓ 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.056 rows=395 loops=1)

83. 0.072 0.128 ↓ 1.0 395 1

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

  • Buckets: 1024 Batches: 1 Memory Usage: 30kB
84. 0.056 0.056 ↓ 1.0 395 1

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

85. 92.400 105.464 ↓ 1.0 9,259 1

Hash Left Join (cost=194.93..5,127.29 rows=9,255 width=497) (actual time=1.310..105.464 rows=9,259 loops=1)

  • Hash Cond: (kc_3.channelcustomersort = pd1_3.dickey)
86. 3.731 12.904 ↓ 1.0 9,259 1

Hash Left Join (cost=169.11..2,660.46 rows=9,255 width=331) (actual time=1.115..12.904 rows=9,259 loops=1)

  • Hash Cond: (kc_3.saleareaid = ps_3.orgstructid)
87. 8.083 8.083 ↓ 1.0 9,259 1

Seq Scan on ka_kq_channelcustomers kc_3 (cost=0.00..2,467.01 rows=9,255 width=255) (actual time=0.010..8.083 rows=9,259 loops=1)

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

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

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

90. 0.057 0.160 ↓ 1.0 395 1

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

  • Buckets: 1024 Batches: 1 Memory Usage: 30kB
91. 0.103 0.103 ↓ 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.103 rows=395 loops=1)

92. 0.001 0.006 ↓ 0.0 0 1

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

93. 0.005 0.005 ↓ 0.0 0 1

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

  • Filter: ((platstatus = 1) AND ((customertype = '905324680615956480'::bigint) OR (customertype = '905324761813487616'::bigint)))
94. 0.007 1.148 ↓ 3.0 3 1

Hash (cost=8.45..8.45 rows=1 width=38) (actual time=1.148..1.148 rows=3 loops=1)

  • Buckets: 1024 Batches: 1 Memory Usage: 9kB
95. 1.141 1.141 ↓ 3.0 3 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.112..1.141 rows=3 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: 3179
96. 0.017 0.066 ↑ 1.0 1 3

Materialize (cost=0.56..16.62 rows=1 width=17) (actual time=0.020..0.022 rows=1 loops=3)

97. 0.004 0.049 ↑ 1.0 1 1

Nested Loop Left Join (cost=0.56..16.61 rows=1 width=17) (actual time=0.044..0.049 rows=1 loops=1)

98. 0.032 0.032 ↑ 1.0 1 1

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

  • Index Cond: (orgstructid = '1160102352791932928'::bigint)
  • Filter: (platstatus = 1)
99. 0.013 0.013 ↑ 1.0 1 1

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

  • Index Cond: (po_1.userinfoid = userinfoid)
  • Filter: (platstatus = 1)
100. 0.033 0.033 ↓ 0.0 0 3

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=3)

  • Index Cond: (id = p_1.customerid)
  • Filter: (platstatus = 1)
101. 80.883 4,634.415 ↓ 979.6 195,918 3

Materialize (cost=397,788.63..406,030.95 rows=200 width=16) (actual time=1,259.672..1,544.805 rows=195,918 loops=3)

102. 99.868 4,553.532 ↓ 1,019.0 203,804 1

GroupAggregate (cost=397,788.63..406,027.95 rows=200 width=16) (actual time=3,778.950..4,553.532 rows=203,804 loops=1)

  • Group Key: kvw_1.customerid
103. 383.779 4,453.664 ↓ 2.6 526,576 1

GroupAggregate (cost=397,788.63..402,936.95 rows=205,933 width=36) (actual time=3,778.936..4,453.664 rows=526,576 loops=1)

  • Group Key: kvw_1.customerid, kvw_1.recorddate, kvd2_1.tn_start, kvd2_1.tn_end
104. 1,351.740 4,069.885 ↓ 9.3 1,922,901 1

Sort (cost=397,788.63..398,303.46 rows=205,933 width=28) (actual time=3,778.927..4,069.885 rows=1,922,901 loops=1)

  • Sort Key: kvw_1.customerid, kvw_1.recorddate, kvd2_1.tn_start, kvd2_1.tn_end
  • Sort Method: external merge Disk: 79112kB
105. 938.156 2,718.145 ↓ 9.3 1,922,901 1

Hash Join (cost=244,991.40..374,685.17 rows=205,933 width=28) (actual time=1,294.812..2,718.145 rows=1,922,901 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
106. 485.716 485.716 ↑ 1.0 2,054,320 1

Seq Scan on kx_visit_workrecord kvw_1 (cost=0.00..71,898.45 rows=2,065,156 width=12) (actual time=0.012..485.716 rows=2,054,320 loops=1)

  • Filter: (platstatus = 1)
107. 72.208 1,294.273 ↑ 1.0 354,521 1

Hash (cost=238,469.60..238,469.60 rows=355,184 width=24) (actual time=1,294.273..1,294.273 rows=354,521 loops=1)

  • Buckets: 65536 Batches: 8 Memory Usage: 2933kB
108. 95.834 1,222.065 ↑ 1.0 354,521 1

Merge Join (cost=227,401.99..238,469.60 rows=355,184 width=24) (actual time=977.240..1,222.065 rows=354,521 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))
109. 267.759 391.250 ↑ 1.0 280,485 1

Sort (cost=44,029.85..44,732.09 rows=280,895 width=40) (actual time=349.304..391.250 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
110. 123.491 123.491 ↑ 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..123.491 rows=280,858 loops=1)

  • Filter: ((platstatus = 1) AND (tn_end >= CURRENT_DATE) AND (tn_start <= CURRENT_DATE))
  • Rows Removed by Filter: 67
111. 37.720 734.981 ↑ 1.0 376,776 1

Materialize (cost=183,372.14..185,349.38 rows=395,448 width=32) (actual time=627.847..734.981 rows=376,776 loops=1)

112. 300.214 697.261 ↑ 1.0 376,776 1

Sort (cost=183,372.14..184,360.76 rows=395,448 width=32) (actual time=627.839..697.261 rows=376,776 loops=1)

  • Sort Key: kks_1.storelevel, kks_1.seleareaid, kks_1.tn_storedevicion
  • Sort Method: external merge Disk: 16136kB
113. 397.047 397.047 ↑ 1.0 395,396 1

Seq Scan on kx_kq_store kks_1 (cost=0.00..137,145.06 rows=395,448 width=32) (actual time=0.012..397.047 rows=395,396 loops=1)

  • Filter: (platstatus = 1)
  • Rows Removed by Filter: 367
114. 0.004 1.180 ↓ 3.0 3 1

Hash (cost=8.45..8.45 rows=1 width=24) (actual time=1.180..1.180 rows=3 loops=1)

  • Buckets: 1024 Batches: 1 Memory Usage: 9kB
115. 1.176 1.176 ↓ 3.0 3 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.121..1.176 rows=3 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: 3179
116. 63.725 133.364 ↑ 1.0 280,858 1

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

  • Buckets: 65536 Batches: 8 Memory Usage: 2714kB
117. 69.639 69.639 ↑ 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.013..69.639 rows=280,858 loops=1)

  • Filter: ((tn_start <= '2019-11-02 18:33:04'::timestamp without time zone) AND (tn_end >= '2019-11-02 18:33:04'::timestamp without time zone) AND (platstatus = 1))
  • Rows Removed by Filter: 67
118. 0.024 0.024 ↓ 0.0 0 3

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.008..0.008 rows=0 loops=3)

  • Index Cond: (tn_id = tvd_1.tn_adminid)
  • Filter: (platstatus = 1)
119. 0.001 0.060 ↓ 0.0 0 3

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

120. 0.059 0.059 ↓ 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.059..0.059 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 : 16.898 ms
Execution time : 9,710.309 ms