explain.depesz.com

PostgreSQL's explain analyze made readable

Result: Jhq4

Settings
# exclusive inclusive rows x rows loops node
1. 0.000 0.000 ↓ 0.0

Limit (cost=242,465.65..242,467.44 rows=715 width=996) (actual rows= loops=)

2. 0.000 0.000 ↓ 0.0

Sort (cost=242,465.65..242,467.44 rows=715 width=996) (actual rows= loops=)

  • Sort Key: ((ml.seq + 0)), ml.customername
3. 0.000 0.000 ↓ 0.0

Subquery Scan on ml (cost=242,415.67..242,431.76 rows=715 width=996) (actual rows= loops=)

4. 0.000 0.000 ↓ 0.0

HashAggregate (cost=242,415.67..242,422.82 rows=715 width=988) (actual rows= loops=)

  • Group Key: "*SELECT* 1".id, "*SELECT* 1".customername, "*SELECT* 1".store_channeltype, "*SELECT* 1".storelevelname, "*SELECT* 1".address, "*SELECT* 1".status, p.plandate, p.userid, p.customertype, p.seq, a.actualvisittime, a.visittype, ((SubPlan 2)), (1)
5. 0.000 0.000 ↓ 0.0

Append (cost=219.97..242,390.64 rows=715 width=988) (actual rows= loops=)

6. 0.000 0.000 ↓ 0.0

Nested Loop (cost=219.97..125,973.32 rows=708 width=242) (actual rows= loops=)

7. 0.000 0.000 ↓ 0.0

Nested Loop Left Join (cost=0.58..33.34 rows=1 width=46) (actual rows= loops=)

8. 0.000 0.000 ↓ 0.0

Nested Loop Left Join (cost=0.29..17.37 rows=1 width=46) (actual rows= loops=)

9. 0.000 0.000 ↓ 0.0

Seq Scan on kx_visit_planvisit p (cost=0.00..1.36 rows=1 width=36) (actual rows= loops=)

  • Filter: ((plandate >= '2020-05-19'::date) AND (plandate <= '2020-05-19'::date) AND (platstatus = 1) AND (userid = '1211917550779240448'::bigint))
10. 0.000 0.000 ↓ 0.0

Index Scan using idx_kx_visit_actual_customerid on kx_visit_actual a (cost=0.29..16.00 rows=1 width=26) (actual rows= loops=)

  • Index Cond: (customerid = p.customerid)
  • Filter: ((actualvisittime >= '2020-05-19 00:00:00'::timestamp without time zone) AND (actualvisittime <= '2020-05-19 23:59:59'::timestamp without time zone) AND (userid = '1211917550779240448'::bigint) AND (platstatus = 1) AND (userid = p.userid))
11. 0.000 0.000 ↓ 0.0

Index Scan using idx_kx_visit_customerstatus_customerid on kx_visit_customerstatus vc (cost=0.29..15.96 rows=1 width=24) (actual rows= loops=)

  • Index Cond: (customerid = p.customerid)
  • Filter: ((visitdate >= '2020-05-19'::date) AND (visitdate <= '2020-05-19'::date) AND (userid = '1211917550779240448'::bigint) AND (platstatus = 1) AND (userid = p.userid) AND (customertype = p.customertype))
12. 0.000 0.000 ↓ 0.0

Append (cost=219.38..116,293.09 rows=3 width=196) (actual rows= loops=)

13. 0.000 0.000 ↓ 0.0

Subquery Scan on *SELECT* 1 (cost=219.38..115,772.99 rows=1 width=196) (actual rows= loops=)

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

Hash Left Join (cost=219.38..114,013.79 rows=140,736 width=1,672) (actual rows= loops=)

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

Hash Left Join (cost=181.46..78,264.10 rows=140,736 width=256) (actual rows= loops=)

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

Hash Left Join (cost=143.54..76,291.06 rows=140,736 width=250) (actual rows= loops=)

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

Hash Left Join (cost=105.62..74,318.02 rows=140,736 width=258) (actual rows= loops=)

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

Seq Scan on kx_kq_store ks (cost=0.00..73,842.04 rows=140,736 width=200) (actual rows= loops=)

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

Hash (cost=84.72..84.72 rows=1,672 width=74) (actual rows= loops=)

20. 0.000 0.000 ↓ 0.0

Seq Scan on pl_orgstruct ps (cost=0.00..84.72 rows=1,672 width=74) (actual rows= loops=)

21. 0.000 0.000 ↓ 0.0

Hash (cost=28.52..28.52 rows=752 width=8) (actual rows= loops=)

22. 0.000 0.000 ↓ 0.0

Seq Scan on pl_dictionary pd1 (cost=0.00..28.52 rows=752 width=8) (actual rows= loops=)

23. 0.000 0.000 ↓ 0.0

Hash (cost=28.52..28.52 rows=752 width=22) (actual rows= loops=)

24. 0.000 0.000 ↓ 0.0

Seq Scan on pl_dictionary pd2 (cost=0.00..28.52 rows=752 width=22) (actual rows= loops=)

25. 0.000 0.000 ↓ 0.0

Hash (cost=28.52..28.52 rows=752 width=22) (actual rows= loops=)

26. 0.000 0.000 ↓ 0.0

Seq Scan on pl_dictionary pd3 (cost=0.00..28.52 rows=752 width=22) (actual rows= loops=)

27. 0.000 0.000 ↓ 0.0

Subquery Scan on *SELECT* 2_1 (cost=143.54..511.93 rows=1 width=215) (actual rows= loops=)

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

Hash Left Join (cost=143.54..502.30 rows=770 width=1,691) (actual rows= loops=)

  • Hash Cond: (kc.channeltype = pd1_1.dickey)
29. 0.000 0.000 ↓ 0.0

Hash Left Join (cost=105.62..263.12 rows=770 width=243) (actual rows= loops=)

  • Hash Cond: (kc.saleareaid = ps_1.orgstructid)
30. 0.000 0.000 ↓ 0.0

Seq Scan on ka_kq_channelcustomers kc (cost=0.00..155.47 rows=770 width=185) (actual rows= loops=)

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

Hash (cost=84.72..84.72 rows=1,672 width=74) (actual rows= loops=)

32. 0.000 0.000 ↓ 0.0

Seq Scan on pl_orgstruct ps_1 (cost=0.00..84.72 rows=1,672 width=74) (actual rows= loops=)

33. 0.000 0.000 ↓ 0.0

Hash (cost=28.52..28.52 rows=752 width=22) (actual rows= loops=)

34. 0.000 0.000 ↓ 0.0

Seq Scan on pl_dictionary pd1_1 (cost=0.00..28.52 rows=752 width=22) (actual rows= loops=)

35. 0.000 0.000 ↓ 0.0

Index Scan using kx_kq_ka_pkey on kx_kq_ka ka (cost=0.14..8.17 rows=1 width=91) (actual rows= loops=)

  • Index Cond: (id = p.customerid)
  • Filter: ((platstatus = 1) AND ((customertype = '905324680615956480'::bigint) OR (customertype = '905324761813487616'::bigint)))
36.          

SubPlan (for Nested Loop)

37. 0.000 0.000 ↓ 0.0

Limit (cost=13.62..13.63 rows=1 width=12) (actual rows= loops=)

38. 0.000 0.000 ↓ 0.0

Sort (cost=13.62..13.63 rows=1 width=12) (actual rows= loops=)

  • Sort Key: vc_2.updatetime DESC
39. 0.000 0.000 ↓ 0.0

Bitmap Heap Scan on kx_visit_customerstatus vc_2 (cost=9.59..13.61 rows=1 width=12) (actual rows= loops=)

  • Recheck Cond: ((customerid = p.customerid) AND (userid = p.userid))
  • Filter: ((visitdate >= '2020-05-19'::date) AND (visitdate <= '2020-05-19'::date) AND (customertype = p.customertype) AND (platstatus = 1))
40. 0.000 0.000 ↓ 0.0

BitmapAnd (cost=9.59..9.59 rows=1 width=0) (actual rows= loops=)

41. 0.000 0.000 ↓ 0.0

Bitmap Index Scan on idx_kx_visit_customerstatus_customerid (cost=0.00..4.32 rows=3 width=0) (actual rows= loops=)

  • Index Cond: (customerid = p.customerid)
42. 0.000 0.000 ↓ 0.0

Bitmap Index Scan on idx_kx_visit_customerstatus_userid (cost=0.00..5.02 rows=97 width=0) (actual rows= loops=)

  • Index Cond: (userid = p.userid)
43. 0.000 0.000 ↓ 0.0

Subquery Scan on *SELECT* 2 (cost=235.65..116,410.25 rows=7 width=242) (actual rows= loops=)

44. 0.000 0.000 ↓ 0.0

Nested Loop (cost=235.65..116,410.16 rows=7 width=238) (actual rows= loops=)

45. 0.000 0.000 ↓ 0.0

Nested Loop Anti Join (cost=16.27..21.66 rows=1 width=38) (actual rows= loops=)

  • Join Filter: ((kx_visit_planvisit.userid = p_1.userid) AND (kx_visit_planvisit.customerid = p_1.customerid))
46. 0.000 0.000 ↓ 0.0

Bitmap Heap Scan on kx_visit_actual p_1 (cost=16.27..20.29 rows=1 width=38) (actual rows= loops=)

  • Recheck Cond: ((actualvisitdate >= '2020-05-19'::date) AND (actualvisitdate <= '2020-05-19'::date) AND (userid = '1211917550779240448'::bigint))
  • Filter: (platstatus = 1)
47. 0.000 0.000 ↓ 0.0

BitmapAnd (cost=16.27..16.27 rows=1 width=0) (actual rows= loops=)

48. 0.000 0.000 ↓ 0.0

Bitmap Index Scan on idx_kx_visit_actual_actualvisitdate (cost=0.00..5.41 rows=112 width=0) (actual rows= loops=)

  • Index Cond: ((actualvisitdate >= '2020-05-19'::date) AND (actualvisitdate <= '2020-05-19'::date))
49. 0.000 0.000 ↓ 0.0

Bitmap Index Scan on idx_kx_visit_actual_userid (cost=0.00..10.60 rows=308 width=0) (actual rows= loops=)

  • Index Cond: (userid = '1211917550779240448'::bigint)
50. 0.000 0.000 ↓ 0.0

Seq Scan on kx_visit_planvisit (cost=0.00..1.36 rows=1 width=16) (actual rows= loops=)

  • Filter: ((plandate >= '2020-05-19'::date) AND (plandate <= '2020-05-19'::date) AND (userid = '1211917550779240448'::bigint) AND (platstatus = 1))
51. 0.000 0.000 ↓ 0.0

Append (cost=219.38..116,293.09 rows=3 width=196) (actual rows= loops=)

52. 0.000 0.000 ↓ 0.0

Subquery Scan on *SELECT* 1_1 (cost=219.38..115,772.99 rows=1 width=196) (actual rows= loops=)

  • Filter: (p_1.customerid = "*SELECT* 1_1".id)
53. 0.000 0.000 ↓ 0.0

Hash Left Join (cost=219.38..114,013.79 rows=140,736 width=1,672) (actual rows= loops=)

  • Hash Cond: (ks_1.storelevel = pd3_1.dickey)
54. 0.000 0.000 ↓ 0.0

Hash Left Join (cost=181.46..78,264.10 rows=140,736 width=256) (actual rows= loops=)

  • Hash Cond: (ks_1.storetype = pd2_1.dickey)
55. 0.000 0.000 ↓ 0.0

Hash Left Join (cost=143.54..76,291.06 rows=140,736 width=250) (actual rows= loops=)

  • Hash Cond: (ks_1.channeltype = pd1_2.dickey)
56. 0.000 0.000 ↓ 0.0

Hash Left Join (cost=105.62..74,318.02 rows=140,736 width=258) (actual rows= loops=)

  • Hash Cond: (ks_1.seleareaid = ps_2.orgstructid)
57. 0.000 0.000 ↓ 0.0

Seq Scan on kx_kq_store ks_1 (cost=0.00..73,842.04 rows=140,736 width=200) (actual rows= loops=)

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

Hash (cost=84.72..84.72 rows=1,672 width=74) (actual rows= loops=)

59. 0.000 0.000 ↓ 0.0

Seq Scan on pl_orgstruct ps_2 (cost=0.00..84.72 rows=1,672 width=74) (actual rows= loops=)

60. 0.000 0.000 ↓ 0.0

Hash (cost=28.52..28.52 rows=752 width=8) (actual rows= loops=)

61. 0.000 0.000 ↓ 0.0

Seq Scan on pl_dictionary pd1_2 (cost=0.00..28.52 rows=752 width=8) (actual rows= loops=)

62. 0.000 0.000 ↓ 0.0

Hash (cost=28.52..28.52 rows=752 width=22) (actual rows= loops=)

63. 0.000 0.000 ↓ 0.0

Seq Scan on pl_dictionary pd2_1 (cost=0.00..28.52 rows=752 width=22) (actual rows= loops=)

64. 0.000 0.000 ↓ 0.0

Hash (cost=28.52..28.52 rows=752 width=22) (actual rows= loops=)

65. 0.000 0.000 ↓ 0.0

Seq Scan on pl_dictionary pd3_1 (cost=0.00..28.52 rows=752 width=22) (actual rows= loops=)

66. 0.000 0.000 ↓ 0.0

Subquery Scan on *SELECT* 2_2 (cost=143.54..511.93 rows=1 width=215) (actual rows= loops=)

  • Filter: (p_1.customerid = "*SELECT* 2_2".id)
67. 0.000 0.000 ↓ 0.0

Hash Left Join (cost=143.54..502.30 rows=770 width=1,691) (actual rows= loops=)

  • Hash Cond: (kc_1.channeltype = pd1_3.dickey)
68. 0.000 0.000 ↓ 0.0

Hash Left Join (cost=105.62..263.12 rows=770 width=243) (actual rows= loops=)

  • Hash Cond: (kc_1.saleareaid = ps_3.orgstructid)
69. 0.000 0.000 ↓ 0.0

Seq Scan on ka_kq_channelcustomers kc_1 (cost=0.00..155.47 rows=770 width=185) (actual rows= loops=)

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

Hash (cost=84.72..84.72 rows=1,672 width=74) (actual rows= loops=)

71. 0.000 0.000 ↓ 0.0

Seq Scan on pl_orgstruct ps_3 (cost=0.00..84.72 rows=1,672 width=74) (actual rows= loops=)

72. 0.000 0.000 ↓ 0.0

Hash (cost=28.52..28.52 rows=752 width=22) (actual rows= loops=)

73. 0.000 0.000 ↓ 0.0

Seq Scan on pl_dictionary pd1_3 (cost=0.00..28.52 rows=752 width=22) (actual rows= loops=)

74. 0.000 0.000 ↓ 0.0

Index Scan using kx_kq_ka_pkey on kx_kq_ka ka_1 (cost=0.14..8.17 rows=1 width=91) (actual rows= loops=)

  • Index Cond: (id = p_1.customerid)
  • Filter: ((platstatus = 1) AND ((customertype = '905324680615956480'::bigint) OR (customertype = '905324761813487616'::bigint)))
75.          

SubPlan (for Nested Loop)

76. 0.000 0.000 ↓ 0.0

Limit (cost=13.62..13.63 rows=1 width=12) (actual rows= loops=)

77. 0.000 0.000 ↓ 0.0

Sort (cost=13.62..13.63 rows=1 width=12) (actual rows= loops=)

  • Sort Key: vc_1.updatetime DESC
78. 0.000 0.000 ↓ 0.0

Bitmap Heap Scan on kx_visit_customerstatus vc_1 (cost=9.59..13.61 rows=1 width=12) (actual rows= loops=)

  • Recheck Cond: ((customerid = p_1.customerid) AND (userid = p_1.userid))
  • Filter: ((visitdate >= '2020-05-19'::date) AND (visitdate <= '2020-05-19'::date) AND (customertype = p_1.customertype) AND (platstatus = 1))
79. 0.000 0.000 ↓ 0.0

BitmapAnd (cost=9.59..9.59 rows=1 width=0) (actual rows= loops=)

80. 0.000 0.000 ↓ 0.0

Bitmap Index Scan on idx_kx_visit_customerstatus_customerid (cost=0.00..4.32 rows=3 width=0) (actual rows= loops=)

  • Index Cond: (customerid = p_1.customerid)
81. 0.000 0.000 ↓ 0.0

Bitmap Index Scan on idx_kx_visit_customerstatus_userid (cost=0.00..5.02 rows=97 width=0) (actual rows= loops=)

  • Index Cond: (userid = p_1.userid)