explain.depesz.com

PostgreSQL's explain analyze made readable

Result: PUts

Settings
# exclusive inclusive rows x rows loops node
1. 0.001 11,638.724 ↑ 9.0 2 1

Limit (cost=651,518.42..651,518.46 rows=18 width=1,498) (actual time=11,638.723..11,638.724 rows=2 loops=1)

2. 0.010 11,638.723 ↑ 9.0 2 1

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

  • Sort Key: vc.status DESC, a.actualvisittime DESC
  • Sort Method: quicksort Memory: 26kB
3. 0.018 11,638.713 ↑ 9.0 2 1

HashAggregate (cost=651,517.68..651,517.86 rows=18 width=1,498) (actual time=11,638.711..11,638.713 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 11,638.695 ↑ 9.0 2 1

Append (cost=312,720.01..651,516.69 rows=18 width=1,498) (actual time=11,557.979..11,638.695 rows=2 loops=1)

5. 0.001 0.055 ↓ 0.0 0 1

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

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

Hash Right Join (cost=285,405.92..298,843.94 rows=15 width=310) (actual time=0.054..0.054 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.048 ↓ 0.0 0 1

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

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

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

13. 0.001 0.048 ↓ 0.0 0 1

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

14. 0.000 0.047 ↓ 0.0 0 1

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

15. 0.000 0.047 ↓ 0.0 0 1

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

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

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

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

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

18. 0.000 0.046 ↓ 0.0 0 1

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

19. 0.046 0.046 ↓ 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.046..0.046 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.004 11,638.638 ↑ 1.5 2 1

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

66. 0.037 11,638.634 ↑ 1.5 2 1

Nested Loop Anti Join (cost=312,667.31..325,358.11 rows=3 width=350) (actual time=11,557.922..11,638.634 rows=2 loops=1)

  • Join Filter: ((kx_visit_planvisit.userid = p_1.userid) AND (kx_visit_planvisit.customerid = p_1.customerid))
67. 0.015 11,638.551 ↑ 1.5 2 1

Hash Left Join (cost=312,667.02..325,349.67 rows=3 width=294) (actual time=11,557.854..11,638.551 rows=2 loops=1)

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

Nested Loop Left Join (cost=285,352.94..298,035.57 rows=3 width=294) (actual time=11,331.218..11,411.913 rows=2 loops=1)

69. 26.594 11,411.891 ↑ 1.5 2 1

Hash Right Join (cost=285,352.79..298,035.08 rows=3 width=294) (actual time=11,331.205..11,411.891 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. 54.153 54.153 ↑ 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.015..54.153 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.011 11,331.144 ↑ 1.5 2 1

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

  • Buckets: 1024 Batches: 1 Memory Usage: 9kB
72. 0.010 11,331.133 ↑ 1.5 2 1

Nested Loop Left Join (cost=249.11..285,352.74 rows=3 width=310) (actual time=493.072..11,331.133 rows=2 loops=1)

73. 0.009 11,331.079 ↑ 1.5 2 1

Nested Loop (cost=248.26..285,346.41 rows=3 width=262) (actual time=493.037..11,331.079 rows=2 loops=1)

74. 0.006 3.662 ↓ 2.0 2 1

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

75. 0.005 3.644 ↓ 2.0 2 1

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

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

Nested Loop Left Join (cost=1.14..25.22 rows=1 width=58) (actual time=1.114..3.623 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.009 1.304 ↓ 2.0 2 1

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

78. 1.283 1.283 ↓ 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.545..1.283 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: 2851
79. 0.012 0.012 ↓ 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.006..0.006 rows=0 loops=2)

  • Index Cond: (id = p_1.customerid)
  • Filter: (platstatus = 1)
80. 2.310 2.310 ↓ 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.441..1.155 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: 2851
81. 0.016 0.016 ↑ 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.008..0.008 rows=1 loops=2)

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

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

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

84. 72.414 11,082.752 ↓ 0.0 0 2

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

  • Filter: (p_1.customerid = "*SELECT* 1_1".id)
  • Rows Removed by Filter: 395374
85. 9,627.894 11,010.338 ↑ 1.2 395,374 2

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

  • Hash Cond: (ks_1.storelevel = pd3_1.dickey)
86. 228.727 1,382.344 ↑ 1.2 395,374 2

Hash Left Join (cost=220.75..150,147.79 rows=470,968 width=279) (actual time=0.741..691.172 rows=395,374 loops=2)

  • Hash Cond: (ks_1.storetype = pd2_1.dickey)
87. 140.209 1,153.470 ↑ 1.2 395,374 2

Hash Left Join (cost=194.93..143,646.16 rows=470,968 width=275) (actual time=0.665..576.735 rows=395,374 loops=2)

  • Hash Cond: (ks_1.channeltype = pd1_2.dickey)
88. 213.879 1,013.120 ↑ 1.2 395,374 2

Hash Left Join (cost=169.11..141,854.20 rows=470,968 width=283) (actual time=0.591..506.560 rows=395,374 loops=2)

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

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

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

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

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

92. 0.044 0.141 ↓ 1.0 395 1

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

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

94. 0.069 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.078 0.078 ↓ 1.0 395 1

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

96. 0.043 0.100 ↓ 1.0 395 1

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

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

98. 1.590 244.614 ↓ 0.0 0 2

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

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

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

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

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

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

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

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

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

104. 0.059 0.163 ↓ 1.0 395 1

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

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

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

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

108. 0.022 0.022 ↓ 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.011..0.011 rows=0 loops=2)

  • Index Cond: (id = "*SELECT* 1_1".id)
109. 0.015 0.015 ↑ 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.015..0.015 rows=1 loops=1)

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

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

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

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

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

  • Group Key: kvw_1.customerid
113. 0.058 226.597 ↑ 3.4 68 1

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

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

Sort (cost=27,298.43..27,299.00 rows=229 width=28) (actual time=226.525..226.539 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.100 226.413 ↓ 1.2 281 1

Nested Loop (cost=18,054.81..27,289.45 rows=229 width=28) (actual time=203.172..226.413 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. 22.652 225.823 ↑ 13.5 35 1

Hash Join (cost=18,054.38..26,609.80 rows=471 width=32) (actual time=203.072..225.823 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.068 0.353 ↑ 12.2 43 1

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

118. 0.093 0.113 ↑ 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.033..0.113 rows=43 loops=1)

  • Recheck Cond: (representativeid = '1160102352791932928'::bigint)
  • Heap Blocks: exact=32
119. 0.020 0.020 ↑ 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.020..0.020 rows=68 loops=1)

  • Index Cond: (representativeid = '1160102352791932928'::bigint)
120. 0.172 0.172 ↑ 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.004..0.004 rows=1 loops=43)

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

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

  • Buckets: 65536 Batches: 8 Memory Usage: 2987kB
122. 128.794 128.794 ↑ 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.010..128.794 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.490 0.490 ↑ 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.014 rows=8 loops=35)

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

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

125. 0.045 0.045 ↓ 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.045..0.045 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 : 14.025 ms
Execution time : 11,639.543 ms