explain.depesz.com

PostgreSQL's explain analyze made readable

Result: raEv

Settings
# exclusive inclusive rows x rows loops node
1. 0.006 6,267.143 ↑ 119.2 6 1

Limit (cost=242,465.65..242,467.44 rows=715 width=996) (actual time=6,267.138..6,267.143 rows=6 loops=1)

2. 0.055 6,267.137 ↑ 119.2 6 1

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

  • Sort Key: ((ml.seq + 0)), ml.customername
  • Sort Method: quicksort Memory: 26kB
3. 0.002 6,267.082 ↑ 119.2 6 1

Subquery Scan on ml (cost=242,415.67..242,431.76 rows=715 width=996) (actual time=6,267.075..6,267.082 rows=6 loops=1)

4. 0.039 6,267.080 ↑ 119.2 6 1

HashAggregate (cost=242,415.67..242,422.82 rows=715 width=988) (actual time=6,267.073..6,267.080 rows=6 loops=1)

  • 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.005 6,267.041 ↑ 119.2 6 1

Append (cost=219.97..242,390.64 rows=715 width=988) (actual time=24.204..6,267.041 rows=6 loops=1)

6. 0.001 0.013 ↓ 0.0 0 1

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

7. 0.000 0.012 ↓ 0.0 0 1

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

8. 0.000 0.012 ↓ 0.0 0 1

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

9. 0.012 0.012 ↓ 0.0 0 1

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

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

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

  • 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 0

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

  • 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 0

Append (cost=219.38..116,293.09 rows=3 width=196) (never executed)

13. 0.000 0.000 ↓ 0.0 0

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

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

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

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

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

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

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

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

Hash Left Join (cost=105.62..74,318.02 rows=140,736 width=258) (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..73,842.04 rows=140,736 width=200) (never executed)

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

Hash (cost=84.72..84.72 rows=1,672 width=74) (never executed)

20. 0.000 0.000 ↓ 0.0 0

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

21. 0.000 0.000 ↓ 0.0 0

Hash (cost=28.52..28.52 rows=752 width=8) (never executed)

22. 0.000 0.000 ↓ 0.0 0

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

23. 0.000 0.000 ↓ 0.0 0

Hash (cost=28.52..28.52 rows=752 width=22) (never executed)

24. 0.000 0.000 ↓ 0.0 0

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

25. 0.000 0.000 ↓ 0.0 0

Hash (cost=28.52..28.52 rows=752 width=22) (never executed)

26. 0.000 0.000 ↓ 0.0 0

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

27. 0.000 0.000 ↓ 0.0 0

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

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

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

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

Hash Left Join (cost=105.62..263.12 rows=770 width=243) (never executed)

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

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

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

Hash (cost=84.72..84.72 rows=1,672 width=74) (never executed)

32. 0.000 0.000 ↓ 0.0 0

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

33. 0.000 0.000 ↓ 0.0 0

Hash (cost=28.52..28.52 rows=752 width=22) (never executed)

34. 0.000 0.000 ↓ 0.0 0

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

35. 0.000 0.000 ↓ 0.0 0

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

  • 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 0

Limit (cost=13.62..13.63 rows=1 width=12) (never executed)

38. 0.000 0.000 ↓ 0.0 0

Sort (cost=13.62..13.63 rows=1 width=12) (never executed)

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

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

  • 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 0

BitmapAnd (cost=9.59..9.59 rows=1 width=0) (never executed)

41. 0.000 0.000 ↓ 0.0 0

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

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

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

  • Index Cond: (userid = p.userid)
43. 0.008 6,267.023 ↑ 1.2 6 1

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

44. 0.046 6,267.015 ↑ 1.2 6 1

Nested Loop (cost=235.65..116,410.16 rows=7 width=238) (actual time=24.189..6,267.015 rows=6 loops=1)

45. 0.020 0.269 ↓ 6.0 6 1

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

  • Join Filter: ((kx_visit_planvisit.userid = p_1.userid) AND (kx_visit_planvisit.customerid = p_1.customerid))
46. 0.058 0.225 ↓ 6.0 6 1

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

  • Recheck Cond: ((actualvisitdate >= '2020-05-19'::date) AND (actualvisitdate <= '2020-05-19'::date) AND (userid = '1211917550779240448'::bigint))
  • Filter: (platstatus = 1)
  • Heap Blocks: exact=6
47. 0.008 0.167 ↓ 0.0 0 1

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

48. 0.082 0.082 ↓ 10.0 1,122 1

Bitmap Index Scan on idx_kx_visit_actual_actualvisitdate (cost=0.00..5.41 rows=112 width=0) (actual time=0.082..0.082 rows=1,122 loops=1)

  • Index Cond: ((actualvisitdate >= '2020-05-19'::date) AND (actualvisitdate <= '2020-05-19'::date))
49. 0.077 0.077 ↓ 1.1 348 1

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

  • Index Cond: (userid = '1211917550779240448'::bigint)
50. 0.024 0.024 ↓ 0.0 0 6

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

  • Filter: ((plandate >= '2020-05-19'::date) AND (plandate <= '2020-05-19'::date) AND (userid = '1211917550779240448'::bigint) AND (platstatus = 1))
  • Rows Removed by Filter: 18
51. 0.084 6,266.112 ↑ 3.0 1 6

Append (cost=219.38..116,293.09 rows=3 width=196) (actual time=81.560..1,044.352 rows=1 loops=6)

52. 108.834 6,233.874 ↑ 1.0 1 6

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

  • Filter: (p_1.customerid = "*SELECT* 1_1".id)
  • Rows Removed by Filter: 140488
53. 4,599.289 6,125.040 ↑ 1.0 140,489 6

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

  • Hash Cond: (ks_1.storelevel = pd3_1.dickey)
54. 249.961 1,525.500 ↑ 1.0 140,489 6

Hash Left Join (cost=181.46..78,264.10 rows=140,736 width=256) (actual time=0.253..254.250 rows=140,489 loops=6)

  • Hash Cond: (ks_1.storetype = pd2_1.dickey)
55. 253.673 1,275.288 ↑ 1.0 140,489 6

Hash Left Join (cost=143.54..76,291.06 rows=140,736 width=250) (actual time=0.208..212.548 rows=140,489 loops=6)

  • Hash Cond: (ks_1.channeltype = pd1_2.dickey)
56. 266.650 1,021.272 ↑ 1.0 140,489 6

Hash Left Join (cost=105.62..74,318.02 rows=140,736 width=258) (actual time=0.149..170.212 rows=140,489 loops=6)

  • Hash Cond: (ks_1.seleareaid = ps_2.orgstructid)
57. 753.750 753.750 ↑ 1.0 140,489 6

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

  • Filter: ((platstatus = 1) AND ((customertype = '905324680615956480'::bigint) OR (customertype = '905324761813487616'::bigint)))
  • Rows Removed by Filter: 8
58. 0.264 0.872 ↓ 1.0 1,679 1

Hash (cost=84.72..84.72 rows=1,672 width=74) (actual time=0.871..0.872 rows=1,679 loops=1)

  • Buckets: 2048 Batches: 1 Memory Usage: 87kB
59. 0.608 0.608 ↓ 1.0 1,679 1

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

60. 0.113 0.343 ↑ 1.0 738 1

Hash (cost=28.52..28.52 rows=752 width=8) (actual time=0.343..0.343 rows=738 loops=1)

  • Buckets: 1024 Batches: 1 Memory Usage: 37kB
61. 0.230 0.230 ↑ 1.0 738 1

Seq Scan on pl_dictionary pd1_2 (cost=0.00..28.52 rows=752 width=8) (actual time=0.007..0.230 rows=738 loops=1)

62. 0.136 0.251 ↑ 1.0 738 1

Hash (cost=28.52..28.52 rows=752 width=22) (actual time=0.251..0.251 rows=738 loops=1)

  • Buckets: 1024 Batches: 1 Memory Usage: 50kB
63. 0.115 0.115 ↑ 1.0 738 1

Seq Scan on pl_dictionary pd2_1 (cost=0.00..28.52 rows=752 width=22) (actual time=0.003..0.115 rows=738 loops=1)

64. 0.138 0.251 ↑ 1.0 738 1

Hash (cost=28.52..28.52 rows=752 width=22) (actual time=0.251..0.251 rows=738 loops=1)

  • Buckets: 1024 Batches: 1 Memory Usage: 50kB
65. 0.113 0.113 ↑ 1.0 738 1

Seq Scan on pl_dictionary pd3_1 (cost=0.00..28.52 rows=752 width=22) (actual time=0.001..0.113 rows=738 loops=1)

66. 0.606 32.100 ↓ 0.0 0 6

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

  • Filter: (p_1.customerid = "*SELECT* 2_2".id)
  • Rows Removed by Filter: 773
67. 26.169 31.494 ↓ 1.0 773 6

Hash Left Join (cost=143.54..502.30 rows=770 width=1,691) (actual time=0.196..5.249 rows=773 loops=6)

  • Hash Cond: (kc_1.channeltype = pd1_3.dickey)
68. 1.316 5.028 ↓ 1.0 773 6

Hash Left Join (cost=105.62..263.12 rows=770 width=243) (actual time=0.132..0.838 rows=773 loops=6)

  • Hash Cond: (kc_1.saleareaid = ps_3.orgstructid)
69. 2.970 2.970 ↓ 1.0 773 6

Seq Scan on ka_kq_channelcustomers kc_1 (cost=0.00..155.47 rows=770 width=185) (actual time=0.005..0.495 rows=773 loops=6)

  • Filter: ((platstatus = 1) AND ((customertype = '905324680615956480'::bigint) OR (customertype = '905324761813487616'::bigint)))
70. 0.268 0.742 ↓ 1.0 1,679 1

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

  • Buckets: 2048 Batches: 1 Memory Usage: 87kB
71. 0.474 0.474 ↓ 1.0 1,679 1

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

72. 0.135 0.297 ↑ 1.0 738 1

Hash (cost=28.52..28.52 rows=752 width=22) (actual time=0.297..0.297 rows=738 loops=1)

  • Buckets: 1024 Batches: 1 Memory Usage: 50kB
73. 0.162 0.162 ↑ 1.0 738 1

Seq Scan on pl_dictionary pd1_3 (cost=0.00..28.52 rows=752 width=22) (actual time=0.006..0.162 rows=738 loops=1)

74. 0.054 0.054 ↓ 0.0 0 6

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

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

SubPlan (for Nested Loop)

76. 0.012 0.588 ↑ 1.0 1 6

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

77. 0.090 0.576 ↑ 1.0 1 6

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

  • Sort Key: vc_1.updatetime DESC
  • Sort Method: quicksort Memory: 25kB
78. 0.108 0.486 ↑ 1.0 1 6

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

  • 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))
  • Rows Removed by Filter: 2
  • Heap Blocks: exact=17
79. 0.024 0.378 ↓ 0.0 0 6

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

80. 0.102 0.102 ↓ 3.3 10 6

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

  • Index Cond: (customerid = p_1.customerid)
81. 0.252 0.252 ↓ 3.6 345 6

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

  • Index Cond: (userid = p_1.userid)
Planning time : 2.997 ms
Execution time : 6,267.495 ms