explain.depesz.com

PostgreSQL's explain analyze made readable

Result: bZg4

Settings
# exclusive inclusive rows x rows loops node
1. 0.001 10,502.549 ↑ 9.0 2 1

Limit (cost=651,518.42..651,518.46 rows=18 width=1,498) (actual time=10,502.548..10,502.549 rows=2 loops=1)

2. 0.014 10,502.548 ↑ 9.0 2 1

Sort (cost=651,518.42..651,518.46 rows=18 width=1,498) (actual time=10,502.548..10,502.548 rows=2 loops=1)

  • Sort Key: vc.status DESC, a.actualvisittime DESC
  • Sort Method: quicksort Memory: 26kB
3. 0.019 10,502.534 ↑ 9.0 2 1

HashAggregate (cost=651,517.68..651,517.86 rows=18 width=1,498) (actual time=10,502.533..10,502.534 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 kte.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(kte.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 10,502.515 ↑ 9.0 2 1

Append (cost=312,720.01..651,516.69 rows=18 width=1,498) (actual time=10,420.034..10,502.515 rows=2 loops=1)

5. 0.000 0.074 ↓ 0.0 0 1

Hash Left Join (cost=312,720.01..326,158.40 rows=15 width=334) (actual time=0.074..0.074 rows=0 loops=1)

  • Hash Cond: (kte.id = kvw.customerid)
6. 0.025 0.074 ↓ 0.0 0 1

Hash Right Join (cost=285,405.92..298,843.94 rows=15 width=310) (actual time=0.073..0.074 rows=0 loops=1)

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

Hash Left Join (cost=7.28..10,285.09 rows=280,895 width=32) (never executed)

  • Hash Cond: (tvd.tn_adminid = tva.tn_id)
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.000 0.000 ↓ 0.0 0

Hash (cost=5.14..5.14 rows=171 width=16) (never executed)

10. 0.000 0.000 ↓ 0.0 0

Seq Scan on tn_visit_administration tva (cost=0.00..5.14 rows=171 width=16) (never executed)

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

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

  • Buckets: 1024 Batches: 1 Memory Usage: 8kB
12. 0.000 0.049 ↓ 0.0 0 1

Nested Loop Left Join (cost=249.40..285,398.38 rows=15 width=326) (actual time=0.049..0.049 rows=0 loops=1)

13. 0.000 0.049 ↓ 0.0 0 1

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

14. 0.001 0.049 ↓ 0.0 0 1

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

15. 0.000 0.048 ↓ 0.0 0 1

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

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

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

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

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

18. 0.001 0.048 ↓ 0.0 0 1

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

19. 0.047 0.047 ↓ 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.047..0.047 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
20. 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)
21. 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))
22. 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))
23. 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)
24. 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)
25. 0.000 0.000 ↓ 0.0 0

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

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

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

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

34. 0.000 0.000 ↓ 0.0 0

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

35. 0.000 0.000 ↓ 0.0 0

Seq Scan on pl_dictionary pd1 (cost=0.00..20.92 rows=392 width=8) (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 pd2 (cost=0.00..20.92 rows=392 width=20) (never executed)

38. 0.000 0.000 ↓ 0.0 0

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

39. 0.000 0.000 ↓ 0.0 0

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

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

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

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

46. 0.000 0.000 ↓ 0.0 0

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

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

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

Nested Loop (cost=0.84..2.10 rows=1 width=48) (never executed)

50. 0.000 0.000 ↓ 0.0 0

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

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

Index Scan using ix_kx_kq_storerepresentative_storeid on kx_kq_storerepresentative ksp (cost=0.42..0.52 rows=1 width=8) (never executed)

  • Index Cond: (storeid = kte.id)
  • Filter: (representativeid = '1160102352791932928'::bigint)
52. 0.000 0.000 ↓ 0.0 0

Hash (cost=27,311.59..27,311.59 rows=200 width=16) (never executed)

53. 0.000 0.000 ↓ 0.0 0

GroupAggregate (cost=27,298.43..27,309.59 rows=200 width=16) (never executed)

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

GroupAggregate (cost=27,298.43..27,304.15 rows=229 width=36) (never executed)

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

Sort (cost=27,298.43..27,299.00 rows=229 width=28) (never executed)

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

Nested Loop (cost=18,054.81..27,289.45 rows=229 width=28) (never executed)

  • Join Filter: ((kvd2.tn_start <= kvw.recorddate) AND (kvd2.tn_end >= kvw.recorddate) AND (ksp_1.storeid = kvw.customerid))
57. 0.000 0.000 ↓ 0.0 0

Hash Join (cost=18,054.38..26,609.80 rows=471 width=32) (never executed)

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

Nested Loop (cost=16.91..5,898.87 rows=524 width=40) (never executed)

59. 0.000 0.000 ↓ 0.0 0

Bitmap Heap Scan on kx_kq_storerepresentative ksp_1 (cost=16.48..1,660.31 rows=524 width=8) (never executed)

  • Recheck Cond: (representativeid = '1160102352791932928'::bigint)
60. 0.000 0.000 ↓ 0.0 0

Bitmap Index Scan on idx_kx_kq_storerepresentative_representativeid (cost=0.00..16.35 rows=524 width=0) (never executed)

  • Index Cond: (representativeid = '1160102352791932928'::bigint)
61. 0.000 0.000 ↓ 0.0 0

Index Scan using kx_kq_store_pkey on kx_kq_store kks (cost=0.42..8.09 rows=1 width=32) (never executed)

  • Index Cond: (id = ksp_1.storeid)
62. 0.000 0.000 ↓ 0.0 0

Hash (cost=10,926.81..10,926.81 rows=280,895 width=40) (never executed)

63. 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))
64. 0.000 0.000 ↓ 0.0 0

Index Scan using idx_kx_visit_workrecord_customerid on kx_visit_workrecord kvw (cost=0.43..1.18 rows=15 width=12) (never executed)

  • Index Cond: (customerid = kks.id)
  • Filter: (platstatus = 1)
65. 0.003 10,502.439 ↑ 1.5 2 1

Subquery Scan on *SELECT* 2 (cost=312,667.31..325,358.14 rows=3 width=354) (actual time=10,419.959..10,502.439 rows=2 loops=1)

66. 0.036 10,502.436 ↑ 1.5 2 1

Nested Loop Anti Join (cost=312,667.31..325,358.11 rows=3 width=350) (actual time=10,419.958..10,502.436 rows=2 loops=1)

  • Join Filter: ((kx_visit_planvisit.userid = p_1.userid) AND (kx_visit_planvisit.customerid = p_1.customerid))
67. 0.027 10,502.340 ↑ 1.5 2 1

Hash Left Join (cost=312,667.02..325,349.67 rows=3 width=294) (actual time=10,419.880..10,502.340 rows=2 loops=1)

  • Hash Cond: (kte_1.id = kvw_1.customerid)
68. 0.012 10,256.617 ↑ 1.5 2 1

Nested Loop Left Join (cost=285,352.94..298,035.57 rows=3 width=294) (actual time=10,174.161..10,256.617 rows=2 loops=1)

69. 27.250 10,256.597 ↑ 1.5 2 1

Hash Right Join (cost=285,352.79..298,035.08 rows=3 width=294) (actual time=10,174.149..10,256.597 rows=2 loops=1)

  • Hash Cond: ((tvd_1.tn_leveldevision = kte_1.tn_storedevicion) AND (tvd_1.tn_storelevel = kte_1.storelevel) AND (tvd_1.tn_area = kte_1.seleareaid))
70. 55.249 55.249 ↑ 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..55.249 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
71. 0.015 10,174.098 ↑ 1.5 2 1

Hash (cost=285,352.74..285,352.74 rows=3 width=310) (actual time=10,174.098..10,174.098 rows=2 loops=1)

  • Buckets: 1024 Batches: 1 Memory Usage: 9kB
72. 0.017 10,174.083 ↑ 1.5 2 1

Nested Loop Left Join (cost=249.11..285,352.74 rows=3 width=310) (actual time=344.926..10,174.083 rows=2 loops=1)

73. 0.010 10,173.998 ↑ 1.5 2 1

Nested Loop (cost=248.26..285,346.41 rows=3 width=262) (actual time=344.862..10,173.998 rows=2 loops=1)

74. 0.004 4.102 ↓ 2.0 2 1

Nested Loop Left Join (cost=1.69..41.85 rows=1 width=67) (actual time=1.197..4.102 rows=2 loops=1)

75. 0.005 4.078 ↓ 2.0 2 1

Nested Loop Left Join (cost=1.42..33.53 rows=1 width=66) (actual time=1.186..4.078 rows=2 loops=1)

  • Join Filter: (po_1.orgstructid = p_1.userid)
76. 0.007 4.045 ↓ 2.0 2 1

Nested Loop Left Join (cost=1.14..25.22 rows=1 width=58) (actual time=1.169..4.045 rows=2 loops=1)

  • Join Filter: ((vc_1.userid = p_1.userid) AND (vc_1.customerid = p_1.customerid))
  • Rows Removed by Join Filter: 2
77. 0.010 1.282 ↓ 2.0 2 1

Nested Loop Left Join (cost=0.71..16.76 rows=1 width=50) (actual time=0.646..1.282 rows=2 loops=1)

78. 1.250 1.250 ↓ 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.631..1.250 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: 2841
79. 0.022 0.022 ↓ 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.011..0.011 rows=0 loops=2)

  • Index Cond: (id = p_1.customerid)
  • Filter: (platstatus = 1)
80. 2.756 2.756 ↓ 2.0 2 2

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.538..1.378 rows=2 loops=2)

  • Index Cond: ((visitdate >= '2019-11-02'::date) AND (visitdate <= '2019-11-02'::date))
  • Filter: ((userid = '1160102352791932928'::bigint) AND (platstatus = 1))
  • Rows Removed by Filter: 2841
81. 0.028 0.028 ↑ 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.014..0.014 rows=1 loops=2)

  • Index Cond: (orgstructid = '1160102352791932928'::bigint)
  • Filter: (platstatus = 1)
82. 0.020 0.020 ↑ 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.010..0.010 rows=1 loops=2)

  • Index Cond: (po_1.userinfoid = userinfoid)
  • Filter: (platstatus = 1)
83. 0.032 10,169.886 ↑ 3.0 1 2

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

84. 72.452 9,921.584 ↓ 0.0 0 2

Subquery Scan on *SELECT* 1_1 (cost=246.57..280,053.40 rows=1 width=195) (actual time=2,714.700..4,960.792 rows=0 loops=2)

  • Filter: (p_1.customerid = "*SELECT* 1_1".id)
  • Rows Removed by Filter: 395372
85. 8,489.558 9,849.132 ↑ 1.2 395,372 2

Hash Left Join (cost=246.57..274,166.30 rows=470,968 width=1,707) (actual time=0.933..4,924.566 rows=395,372 loops=2)

  • Hash Cond: (ks_1.storelevel = pd3_1.dickey)
86. 215.875 1,359.458 ↑ 1.2 395,372 2

Hash Left Join (cost=220.75..150,147.79 rows=470,968 width=279) (actual time=0.805..679.729 rows=395,372 loops=2)

  • Hash Cond: (ks_1.storetype = pd2_1.dickey)
87. 139.984 1,143.436 ↑ 1.2 395,372 2

Hash Left Join (cost=194.93..143,646.16 rows=470,968 width=275) (actual time=0.721..571.718 rows=395,372 loops=2)

  • Hash Cond: (ks_1.channeltype = pd1_2.dickey)
88. 211.948 1,003.302 ↑ 1.2 395,372 2

Hash Left Join (cost=169.11..141,854.20 rows=470,968 width=283) (actual time=0.638..501.651 rows=395,372 loops=2)

  • Hash Cond: (ks_1.seleareaid = ps_2.orgstructid)
89. 790.118 790.118 ↑ 1.2 395,372 2

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

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

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

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

92. 0.056 0.150 ↓ 1.0 395 1

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

  • Buckets: 1024 Batches: 1 Memory Usage: 24kB
93. 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)

94. 0.079 0.147 ↓ 1.0 395 1

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

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

96. 0.064 0.116 ↓ 1.0 395 1

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

  • Buckets: 1024 Batches: 1 Memory Usage: 24kB
97. 0.052 0.052 ↓ 1.0 395 1

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

98. 1.782 248.258 ↓ 0.0 0 2

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

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

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

  • Hash Cond: (kc_3.channelcustomersort = pd1_3.dickey)
100. 7.637 26.766 ↓ 1.0 9,259 2

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

  • Hash Cond: (kc_3.saleareaid = ps_3.orgstructid)
101. 17.298 17.298 ↓ 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.007..8.649 rows=9,259 loops=2)

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

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

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

104. 0.120 0.276 ↓ 1.0 395 1

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

  • Buckets: 1024 Batches: 1 Memory Usage: 30kB
105. 0.156 0.156 ↓ 1.0 395 1

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

106. 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)))
107. 0.007 0.068 ↓ 0.0 0 2

Nested Loop (cost=0.84..2.10 rows=1 width=48) (actual time=0.034..0.034 rows=0 loops=2)

108. 0.032 0.032 ↓ 0.0 0 2

Index Scan using kx_kq_store_pkey on kx_kq_store kte_1 (cost=0.42..1.56 rows=1 width=48) (actual time=0.016..0.016 rows=0 loops=2)

  • Index Cond: (id = "*SELECT* 1_1".id)
109. 0.029 0.029 ↑ 1.0 1 1

Index Scan using ix_kx_kq_storerepresentative_storeid on kx_kq_storerepresentative ksp_2 (cost=0.42..0.52 rows=1 width=8) (actual time=0.028..0.029 rows=1 loops=1)

  • Index Cond: (storeid = kte_1.id)
  • Filter: (representativeid = '1160102352791932928'::bigint)
110. 0.008 0.008 ↓ 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.004..0.004 rows=0 loops=2)

  • Index Cond: (tn_id = tvd_1.tn_adminid)
  • Filter: (platstatus = 1)
111. 0.009 245.696 ↑ 8.3 24 1

Hash (cost=27,311.59..27,311.59 rows=200 width=16) (actual time=245.696..245.696 rows=24 loops=1)

  • Buckets: 1024 Batches: 1 Memory Usage: 10kB
112. 0.016 245.687 ↑ 8.3 24 1

GroupAggregate (cost=27,298.43..27,309.59 rows=200 width=16) (actual time=245.615..245.687 rows=24 loops=1)

  • Group Key: kvw_1.customerid
113. 0.057 245.671 ↑ 3.4 68 1

GroupAggregate (cost=27,298.43..27,304.15 rows=229 width=36) (actual time=245.606..245.671 rows=68 loops=1)

  • Group Key: kvw_1.customerid, kvw_1.recorddate, kvd2_1.tn_start, kvd2_1.tn_end
114. 0.134 245.614 ↓ 1.2 281 1

Sort (cost=27,298.43..27,299.00 rows=229 width=28) (actual time=245.600..245.614 rows=281 loops=1)

  • Sort Key: kvw_1.customerid, kvw_1.recorddate, kvd2_1.tn_start, kvd2_1.tn_end
  • Sort Method: quicksort Memory: 46kB
115. 0.077 245.480 ↓ 1.2 281 1

Nested Loop (cost=18,054.81..27,289.45 rows=229 width=28) (actual time=220.577..245.480 rows=281 loops=1)

  • Join Filter: ((kvd2_1.tn_start <= kvw_1.recorddate) AND (kvd2_1.tn_end >= kvw_1.recorddate) AND (ksp_3.storeid = kvw_1.customerid))
  • Rows Removed by Join Filter: 3
116. 24.571 244.878 ↑ 13.5 35 1

Hash Join (cost=18,054.38..26,609.80 rows=471 width=32) (actual time=220.468..244.878 rows=35 loops=1)

  • Hash Cond: ((kks_1.seleareaid = kvd2_1.tn_area) AND (kks_1.tn_storedevicion = kvd2_1.tn_leveldevision) AND (kks_1.storelevel = kvd2_1.tn_storelevel))
117. 0.054 0.390 ↑ 12.2 43 1

Nested Loop (cost=16.91..5,898.87 rows=524 width=40) (actual time=0.058..0.390 rows=43 loops=1)

118. 0.098 0.121 ↑ 12.2 43 1

Bitmap Heap Scan on kx_kq_storerepresentative ksp_3 (cost=16.48..1,660.31 rows=524 width=8) (actual time=0.038..0.121 rows=43 loops=1)

  • Recheck Cond: (representativeid = '1160102352791932928'::bigint)
  • Heap Blocks: exact=32
119. 0.023 0.023 ↑ 7.7 68 1

Bitmap Index Scan on idx_kx_kq_storerepresentative_representativeid (cost=0.00..16.35 rows=524 width=0) (actual time=0.023..0.023 rows=68 loops=1)

  • Index Cond: (representativeid = '1160102352791932928'::bigint)
120. 0.215 0.215 ↑ 1.0 1 43

Index Scan using kx_kq_store_pkey on kx_kq_store kks_1 (cost=0.42..8.09 rows=1 width=32) (actual time=0.005..0.005 rows=1 loops=43)

  • Index Cond: (id = ksp_3.storeid)
121. 81.072 219.917 ↑ 1.0 280,858 1

Hash (cost=10,926.81..10,926.81 rows=280,895 width=40) (actual time=219.917..219.917 rows=280,858 loops=1)

  • Buckets: 65536 Batches: 8 Memory Usage: 2987kB
122. 138.845 138.845 ↑ 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.011..138.845 rows=280,858 loops=1)

  • Filter: ((platstatus = 1) AND (tn_end >= CURRENT_DATE) AND (tn_start <= CURRENT_DATE))
  • Rows Removed by Filter: 67
123. 0.525 0.525 ↑ 1.9 8 35

Index Scan using idx_kx_visit_workrecord_customerid on kx_visit_workrecord kvw_1 (cost=0.43..1.18 rows=15 width=12) (actual time=0.007..0.015 rows=8 loops=35)

  • Index Cond: (customerid = kks_1.id)
  • Filter: (platstatus = 1)
124. 0.004 0.060 ↓ 0.0 0 2

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

125. 0.056 0.056 ↓ 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.056..0.056 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.705 ms
Execution time : 10,503.919 ms