explain.depesz.com

PostgreSQL's explain analyze made readable

Result: KbzG : Optimization for: plan #gKKW

Settings

Optimization path:

Optimization(s) for this plan:

# exclusive inclusive rows x rows loops node
1. 0.069 50,455.797 ↓ 6.4 500 1

Limit (cost=298,684.85..298,685.04 rows=78 width=334) (actual time=50,455.704..50,455.797 rows=500 loops=1)

2. 9.830 50,455.728 ↓ 6.4 500 1

Sort (cost=298,684.85..298,685.04 rows=78 width=334) (actual time=50,455.703..50,455.728 rows=500 loops=1)

  • Sort Key: z.pinyin
  • Sort Method: top-N heapsort Memory: 345kB
3. 5.559 50,445.898 ↓ 164.9 12,864 1

Subquery Scan on z (cost=298,015.15..298,682.40 rows=78 width=334) (actual time=50,383.400..50,445.898 rows=12,864 loops=1)

  • Filter: (z.seq = 1)
  • Rows Removed by Filter: 59506
4. 36.272 50,440.339 ↓ 4.6 72,370 1

WindowAgg (cost=298,015.15..298,486.15 rows=15,700 width=342) (actual time=50,383.398..50,440.339 rows=72,370 loops=1)

5. 138.196 50,404.067 ↓ 4.6 72,370 1

Sort (cost=298,015.15..298,054.40 rows=15,700 width=278) (actual time=50,383.390..50,404.067 rows=72,370 loops=1)

  • Sort Key: m.orgstructid, "*SELECT* 1".id
  • Sort Method: external merge Disk: 19256kB
6. 26.958 50,265.871 ↓ 4.6 72,370 1

Hash Left Join (cost=36,329.83..294,880.48 rows=15,700 width=278) (actual time=168.026..50,265.871 rows=72,370 loops=1)

  • Hash Cond: (p.parentpositionid = pp.orgstructid)
7. 16.970 50,229.875 ↓ 2.7 42,276 1

Hash Join (cost=34,849.24..293,340.25 rows=15,700 width=264) (actual time=158.967..50,229.875 rows=42,276 loops=1)

  • Hash Cond: ("*SELECT* 1".saleareaid = saleareaid.orgstructid)
8. 20.533 50,208.688 ↓ 1.8 42,276 1

Hash Left Join (cost=34,186.99..292,617.91 rows=22,878 width=272) (actual time=154.703..50,208.688 rows=42,276 loops=1)

  • Hash Cond: ((m.orgstructid = kx_visit_workrecord.userid) AND ("*SELECT* 1".id = kx_visit_workrecord.customerid))
9. 21,067.922 50,065.821 ↓ 1.8 42,276 1

Nested Loop (cost=3,397.22..261,708.03 rows=22,878 width=264) (actual time=32.272..50,065.821 rows=42,276 loops=1)

  • Join Filter: (("*SELECT* 1".id = stp.storeid) OR ("*SELECT* 1".id = chm.channelid))
  • Rows Removed by Join Filter: 191830797
10. 5.403 214.571 ↑ 1.0 6,763 1

Hash Left Join (cost=757.73..6,395.26 rows=6,763 width=75) (actual time=4.750..214.571 rows=6,763 loops=1)

  • Hash Cond: ("*SELECT* 1".customertype = dict_ct.dictionaryid)
11. 1.899 208.945 ↑ 1.0 6,763 1

Append (cost=730.94..6,350.54 rows=6,763 width=71) (actual time=4.513..208.945 rows=6,763 loops=1)

12. 3.342 184.337 ↑ 1.0 6,079 1

Subquery Scan on *SELECT* 1 (cost=730.94..5,299.08 rows=6,079 width=70) (actual time=4.513..184.337 rows=6,079 loops=1)

13. 137.515 180.995 ↑ 1.0 6,079 1

Hash Left Join (cost=730.94..5,238.29 rows=6,079 width=1,180) (actual time=4.512..180.995 rows=6,079 loops=1)

  • Hash Cond: (ks.storelevel = pd3.dickey)
14. 5.502 43.327 ↑ 1.0 6,079 1

Hash Left Join (cost=705.22..3,620.36 rows=6,079 width=199) (actual time=4.263..43.327 rows=6,079 loops=1)

  • Hash Cond: (ks.storetype = pd2.dickey)
15. 4.457 37.663 ↑ 1.0 6,079 1

Hash Left Join (cost=679.50..3,540.99 rows=6,079 width=195) (actual time=4.088..37.663 rows=6,079 loops=1)

  • Hash Cond: (ks.channeltype = pd1.dickey)
16. 11.975 33.036 ↑ 1.0 6,079 1

Hash Left Join (cost=653.78..3,459.23 rows=6,079 width=203) (actual time=3.903..33.036 rows=6,079 loops=1)

  • Hash Cond: (ks.seleareaid = ps.orgstructid)
17. 17.222 17.222 ↑ 1.0 6,079 1

Seq Scan on kx_kq_store ks (cost=0.00..2,789.47 rows=6,079 width=82) (actual time=0.005..17.222 rows=6,079 loops=1)

  • Filter: (platstatus = 1)
  • Rows Removed by Filter: 119
18. 1.345 3.839 ↑ 1.0 7,857 1

Hash (cost=555.57..555.57 rows=7,857 width=129) (actual time=3.839..3.839 rows=7,857 loops=1)

  • Buckets: 8192 Batches: 1 Memory Usage: 539kB
19. 2.494 2.494 ↑ 1.0 7,857 1

Seq Scan on pl_orgstruct ps (cost=0.00..555.57 rows=7,857 width=129) (actual time=0.003..2.494 rows=7,857 loops=1)

20. 0.068 0.170 ↓ 1.1 494 1

Hash (cost=20.32..20.32 rows=432 width=8) (actual time=0.170..0.170 rows=494 loops=1)

  • Buckets: 1024 Batches: 1 Memory Usage: 28kB
21. 0.102 0.102 ↓ 1.1 494 1

Seq Scan on pl_dictionary pd1 (cost=0.00..20.32 rows=432 width=8) (actual time=0.007..0.102 rows=494 loops=1)

22. 0.086 0.162 ↓ 1.1 494 1

Hash (cost=20.32..20.32 rows=432 width=20) (actual time=0.162..0.162 rows=494 loops=1)

  • Buckets: 1024 Batches: 1 Memory Usage: 35kB
23. 0.076 0.076 ↓ 1.1 494 1

Seq Scan on pl_dictionary pd2 (cost=0.00..20.32 rows=432 width=20) (actual time=0.011..0.076 rows=494 loops=1)

24. 0.087 0.153 ↓ 1.1 494 1

Hash (cost=20.32..20.32 rows=432 width=8) (actual time=0.153..0.153 rows=494 loops=1)

  • Buckets: 1024 Batches: 1 Memory Usage: 28kB
25. 0.066 0.066 ↓ 1.1 494 1

Seq Scan on pl_dictionary pd3 (cost=0.00..20.32 rows=432 width=8) (actual time=0.002..0.066 rows=494 loops=1)

26. 0.391 22.691 ↑ 1.0 683 1

Subquery Scan on *SELECT* 2 (cost=679.50..1,050.45 rows=683 width=74) (actual time=4.151..22.691 rows=683 loops=1)

27. 15.330 22.300 ↑ 1.0 683 1

Hash Left Join (cost=679.50..1,043.62 rows=683 width=1,184) (actual time=4.149..22.300 rows=683 loops=1)

  • Hash Cond: (kc.channeltype = pd1_1.dickey)
28. 1.232 6.776 ↑ 1.0 683 1

Hash Left Join (cost=653.78..842.33 rows=683 width=191) (actual time=3.916..6.776 rows=683 loops=1)

  • Hash Cond: (kc.saleareaid = ps_1.orgstructid)
29. 1.663 1.663 ↑ 1.0 683 1

Seq Scan on ka_kq_channelcustomers kc (cost=0.00..186.75 rows=683 width=70) (actual time=0.008..1.663 rows=683 loops=1)

  • Filter: (platstatus = 1)
  • Rows Removed by Filter: 17
30. 1.052 3.881 ↑ 1.0 7,857 1

Hash (cost=555.57..555.57 rows=7,857 width=129) (actual time=3.881..3.881 rows=7,857 loops=1)

  • Buckets: 8192 Batches: 1 Memory Usage: 539kB
31. 2.829 2.829 ↑ 1.0 7,857 1

Seq Scan on pl_orgstruct ps_1 (cost=0.00..555.57 rows=7,857 width=129) (actual time=0.007..2.829 rows=7,857 loops=1)

32. 0.072 0.194 ↓ 1.1 494 1

Hash (cost=20.32..20.32 rows=432 width=20) (actual time=0.194..0.194 rows=494 loops=1)

  • Buckets: 1024 Batches: 1 Memory Usage: 35kB
33. 0.122 0.122 ↓ 1.1 494 1

Seq Scan on pl_dictionary pd1_1 (cost=0.00..20.32 rows=432 width=20) (actual time=0.010..0.122 rows=494 loops=1)

34. 0.018 0.018 ↑ 1.0 1 1

Seq Scan on kx_kq_ka ka (cost=0.00..1.01 rows=1 width=892) (actual time=0.016..0.018 rows=1 loops=1)

  • Filter: (platstatus = 1)
35. 0.087 0.223 ↓ 1.1 493 1

Hash (cost=21.40..21.40 rows=431 width=20) (actual time=0.223..0.223 rows=493 loops=1)

  • Buckets: 1024 Batches: 1 Memory Usage: 35kB
36. 0.136 0.136 ↓ 1.1 493 1

Seq Scan on pl_dictionary dict_ct (cost=0.00..21.40 rows=431 width=20) (actual time=0.003..0.136 rows=493 loops=1)

  • Filter: (platstatus = 1)
  • Rows Removed by Filter: 1
37. 28,747.055 28,783.328 ↓ 13.3 28,371 6,763

Materialize (cost=2,639.49..3,463.97 rows=2,128 width=205) (actual time=0.006..4.256 rows=28,371 loops=6,763)

38. 8.208 36.273 ↓ 13.3 28,371 1

Hash Right Join (cost=2,639.49..3,453.33 rows=2,128 width=205) (actual time=24.046..36.273 rows=28,371 loops=1)

  • Hash Cond: (stp.representativeid = m.orgstructid)
39. 4.040 4.040 ↓ 1.0 12,762 1

Seq Scan on kx_kq_storerepresentative stp (cost=0.00..744.70 rows=12,760 width=20) (actual time=0.007..4.040 rows=12,762 loops=1)

  • Filter: (platstatus = 1)
  • Rows Removed by Filter: 5616
40. 2.312 24.025 ↓ 4.1 5,393 1

Hash (cost=2,623.10..2,623.10 rows=1,311 width=193) (actual time=24.025..24.025 rows=5,393 loops=1)

  • Buckets: 8192 (originally 2048) Batches: 1 (originally 1) Memory Usage: 1197kB
41. 1.147 21.713 ↓ 4.1 5,393 1

Hash Right Join (cost=2,554.38..2,623.10 rows=1,311 width=193) (actual time=20.200..21.713 rows=5,393 loops=1)

  • Hash Cond: (chm.managerid = m.orgstructid)
42. 0.402 0.402 ↑ 1.0 1,617 1

Seq Scan on kx_kq_channelmanager chm (cost=0.00..59.95 rows=1,618 width=20) (actual time=0.010..0.402 rows=1,617 loops=1)

  • Filter: (platstatus = 1)
  • Rows Removed by Filter: 459
43. 1.874 20.164 ↓ 3.2 4,189 1

Hash (cost=2,538.00..2,538.00 rows=1,311 width=181) (actual time=20.164..20.164 rows=4,189 loops=1)

  • Buckets: 8192 (originally 2048) Batches: 1 (originally 1) Memory Usage: 895kB
44. 1.052 18.290 ↓ 3.2 4,189 1

Hash Join (cost=1,899.33..2,538.00 rows=1,311 width=181) (actual time=11.712..18.290 rows=4,189 loops=1)

  • Hash Cond: (p.parentorgstructid = sa.orgstructid)
45. 1.165 13.691 ↓ 3.1 4,189 1

Hash Join (cost=1,228.17..1,863.31 rows=1,342 width=68) (actual time=8.120..13.691 rows=4,189 loops=1)

  • Hash Cond: (m.parentorgstructid = p.orgstructid)
46. 1.237 8.837 ↓ 3.0 4,189 1

Hash Join (cost=557.01..1,188.54 rows=1,374 width=49) (actual time=4.384..8.837 rows=4,189 loops=1)

  • Hash Cond: (m.userinfoid = aa.userinfoid)
47. 1.247 5.838 ↓ 1.8 4,533 1

Hash Join (cost=331.89..937.26 rows=2,486 width=65) (actual time=2.584..5.838 rows=4,533 loops=1)

  • Hash Cond: (m.userinfoid = u.userinfoid)
48. 2.067 2.067 ↓ 1.2 4,613 1

Seq Scan on pl_orgstruct m (cost=0.00..594.86 rows=3,999 width=35) (actual time=0.005..2.067 rows=4,613 loops=1)

  • Filter: ((userinfoid IS NOT NULL) AND (status = 1) AND (platstatus = 1))
  • Rows Removed by Filter: 3244
49. 0.851 2.524 ↑ 1.1 4,325 1

Hash (cost=274.40..274.40 rows=4,599 width=30) (actual time=2.524..2.524 rows=4,325 loops=1)

  • Buckets: 8192 Batches: 1 Memory Usage: 339kB
50. 1.673 1.673 ↑ 1.1 4,325 1

Seq Scan on pl_userinfo u (cost=0.00..274.40 rows=4,599 width=30) (actual time=0.007..1.673 rows=4,325 loops=1)

  • Filter: ((status = 1) AND (platstatus = 1))
  • Rows Removed by Filter: 26
51. 0.559 1.762 ↓ 1.0 3,941 1

Hash (cost=176.54..176.54 rows=3,886 width=8) (actual time=1.762..1.762 rows=3,941 loops=1)

  • Buckets: 4096 Batches: 1 Memory Usage: 186kB
52. 1.203 1.203 ↓ 1.0 3,954 1

Seq Scan on pl_appaccount aa (cost=0.00..176.54 rows=3,886 width=8) (actual time=0.009..1.203 rows=3,954 loops=1)

  • Filter: ((status = 1) AND (platstatus = 1))
  • Rows Removed by Filter: 613
53. 1.472 3.689 ↑ 1.0 7,676 1

Hash (cost=575.21..575.21 rows=7,676 width=35) (actual time=3.689..3.689 rows=7,676 loops=1)

  • Buckets: 8192 Batches: 1 Memory Usage: 556kB
54. 2.217 2.217 ↑ 1.0 7,676 1

Seq Scan on pl_orgstruct p (cost=0.00..575.21 rows=7,676 width=35) (actual time=0.006..2.217 rows=7,676 loops=1)

  • Filter: (platstatus = 1)
  • Rows Removed by Filter: 181
55. 1.336 3.547 ↑ 1.0 7,676 1

Hash (cost=575.21..575.21 rows=7,676 width=129) (actual time=3.547..3.547 rows=7,676 loops=1)

  • Buckets: 8192 Batches: 1 Memory Usage: 528kB
56. 2.211 2.211 ↑ 1.0 7,676 1

Seq Scan on pl_orgstruct sa (cost=0.00..575.21 rows=7,676 width=129) (actual time=0.003..2.211 rows=7,676 loops=1)

  • Filter: (platstatus = 1)
  • Rows Removed by Filter: 181
57. 0.807 122.334 ↑ 3.4 3,369 1

Hash (cost=30,619.13..30,619.13 rows=11,376 width=24) (actual time=122.333..122.334 rows=3,369 loops=1)

  • Buckets: 16384 Batches: 1 Memory Usage: 313kB
58. 5.305 121.527 ↑ 3.4 3,369 1

HashAggregate (cost=30,391.61..30,505.37 rows=11,376 width=24) (actual time=120.907..121.527 rows=3,369 loops=1)

  • Group Key: kx_visit_workrecord.userid, kx_visit_workrecord.customerid
59. 116.222 116.222 ↓ 1.3 16,327 1

Seq Scan on kx_visit_workrecord (cost=0.00..30,299.19 rows=12,322 width=16) (actual time=87.908..116.222 rows=16,327 loops=1)

  • Filter: ((visittime >= '2019-11-01 23:59:59'::timestamp without time zone) AND (visittime <= '2019-11-28 23:59:59'::timestamp without time zone) AND (platstatus = 1) AND (((visitcontent)::text = '离店签退'::text) OR ((visitcontent)::text = '统计完成'::text)))
  • Rows Removed by Filter: 670408
60. 0.841 4.217 ↑ 1.0 5,352 1

Hash (cost=594.86..594.86 rows=5,392 width=8) (actual time=4.217..4.217 rows=5,352 loops=1)

  • Buckets: 8192 Batches: 1 Memory Usage: 274kB
61. 3.376 3.376 ↑ 1.0 5,352 1

Seq Scan on pl_orgstruct saleareaid (cost=0.00..594.86 rows=5,392 width=8) (actual time=0.007..3.376 rows=5,352 loops=1)

  • Filter: (((codepath)::text ~~ '1.%'::text) AND (platstatus = 1))
  • Rows Removed by Filter: 2505
62. 0.560 9.038 ↓ 12.0 2,287 1

Hash (cost=1,478.21..1,478.21 rows=190 width=30) (actual time=9.038..9.038 rows=2,287 loops=1)

  • Buckets: 4096 (originally 1024) Batches: 1 (originally 1) Memory Usage: 179kB
63. 0.638 8.478 ↓ 12.0 2,287 1

Hash Join (cost=853.86..1,478.21 rows=190 width=30) (actual time=4.772..8.478 rows=2,287 loops=1)

  • Hash Cond: (pm.userinfoid = aa_1.userinfoid)
64. 0.543 6.016 ↓ 7.6 2,607 1

Hash Join (cost=628.75..1,249.48 rows=344 width=38) (actual time=2.920..6.016 rows=2,607 loops=1)

  • Hash Cond: (pm.parentorgstructid = pp.orgstructid)
65. 2.595 2.595 ↓ 1.1 2,642 1

Seq Scan on pl_orgstruct pm (cost=0.00..614.50 rows=2,375 width=27) (actual time=0.014..2.595 rows=2,642 loops=1)

  • Filter: ((orgtypeid = 5) AND (status = 1) AND (platstatus = 1))
  • Rows Removed by Filter: 5215
66. 0.160 2.878 ↑ 1.5 780 1

Hash (cost=614.50..614.50 rows=1,140 width=19) (actual time=2.878..2.878 rows=780 loops=1)

  • Buckets: 2048 Batches: 1 Memory Usage: 60kB
67. 2.718 2.718 ↑ 1.5 780 1

Seq Scan on pl_orgstruct pp (cost=0.00..614.50 rows=1,140 width=19) (actual time=0.003..2.718 rows=780 loops=1)

  • Filter: ((orgtypeid = 4) AND (status = 1) AND (platstatus = 1))
  • Rows Removed by Filter: 7077
68. 0.585 1.824 ↓ 1.0 3,941 1

Hash (cost=176.54..176.54 rows=3,886 width=8) (actual time=1.824..1.824 rows=3,941 loops=1)

  • Buckets: 4096 Batches: 1 Memory Usage: 186kB
69. 1.239 1.239 ↓ 1.0 3,954 1

Seq Scan on pl_appaccount aa_1 (cost=0.00..176.54 rows=3,886 width=8) (actual time=0.012..1.239 rows=3,954 loops=1)

  • Filter: ((status = 1) AND (platstatus = 1))
  • Rows Removed by Filter: 613
Planning time : 28.997 ms
Execution time : 50,463.306 ms