explain.depesz.com

PostgreSQL's explain analyze made readable

Result: 7RrD : Optimization for: Optimization for: plan #gKKW; plan #KbzG

Settings

Optimization path:

# exclusive inclusive rows x rows loops node
1. 0.071 51,277.339 ↓ 6.4 500 1

Limit (cost=298,834.36..298,834.56 rows=78 width=334) (actual time=51,277.245..51,277.339 rows=500 loops=1)

2. 9.607 51,277.268 ↓ 6.4 500 1

Sort (cost=298,834.36..298,834.56 rows=78 width=334) (actual time=51,277.243..51,277.268 rows=500 loops=1)

  • Sort Key: z.pinyin
  • Sort Method: top-N heapsort Memory: 345kB
3. 5.505 51,267.661 ↓ 164.9 12,866 1

Subquery Scan on z (cost=298,164.70..298,831.91 rows=78 width=334) (actual time=51,204.993..51,267.661 rows=12,866 loops=1)

  • Filter: (z.seq = 1)
  • Rows Removed by Filter: 59518
4. 36.441 51,262.156 ↓ 4.6 72,384 1

WindowAgg (cost=298,164.70..298,635.67 rows=15,699 width=342) (actual time=51,204.991..51,262.156 rows=72,384 loops=1)

5. 137.888 51,225.715 ↓ 4.6 72,384 1

Sort (cost=298,164.70..298,203.95 rows=15,699 width=278) (actual time=51,204.983..51,225.715 rows=72,384 loops=1)

  • Sort Key: m.orgstructid, "*SELECT* 1".id
  • Sort Method: external merge Disk: 19256kB
6. 27.001 51,087.827 ↓ 4.6 72,384 1

Hash Left Join (cost=36,479.39..295,030.11 rows=15,699 width=278) (actual time=168.909..51,087.827 rows=72,384 loops=1)

  • Hash Cond: (p.parentpositionid = pp.orgstructid)
7. 17.046 51,051.505 ↓ 2.7 42,290 1

Hash Join (cost=34,998.66..293,489.74 rows=15,699 width=264) (actual time=159.564..51,051.505 rows=42,290 loops=1)

  • Hash Cond: ("*SELECT* 1".saleareaid = saleareaid.orgstructid)
8. 19.689 51,030.196 ↓ 1.8 42,290 1

Hash Left Join (cost=34,336.31..292,767.31 rows=22,875 width=272) (actual time=155.285..51,030.196 rows=42,290 loops=1)

  • Hash Cond: ((m.orgstructid = kx_visit_workrecord.userid) AND ("*SELECT* 1".id = kx_visit_workrecord.customerid))
9. 20,828.613 50,888.403 ↓ 1.8 42,290 1

Nested Loop (cost=3,402.47..261,713.38 rows=22,875 width=264) (actual time=33.089..50,888.403 rows=42,290 loops=1)

  • Join Filter: (("*SELECT* 1".id = stp.storeid) OR ("*SELECT* 1".id = chm.channelid))
  • Rows Removed by Join Filter: 191961940
10. 5.263 205.845 ↓ 1.0 6,765 1

Hash Left Join (cost=757.82..6,395.44 rows=6,763 width=75) (actual time=4.418..205.845 rows=6,765 loops=1)

  • Hash Cond: ("*SELECT* 1".customertype = dict_ct.dictionaryid)
11. 1.956 200.375 ↓ 1.0 6,765 1

Append (cost=731.03..6,350.72 rows=6,763 width=71) (actual time=4.203..200.375 rows=6,765 loops=1)

12. 4.296 176.627 ↓ 1.0 6,081 1

Subquery Scan on *SELECT* 1 (cost=731.03..5,299.17 rows=6,079 width=70) (actual time=4.203..176.627 rows=6,081 loops=1)

13. 130.739 172.331 ↓ 1.0 6,081 1

Hash Left Join (cost=731.03..5,238.38 rows=6,079 width=1,180) (actual time=4.202..172.331 rows=6,081 loops=1)

  • Hash Cond: (ks.storelevel = pd3.dickey)
14. 4.812 41.464 ↓ 1.0 6,081 1

Hash Left Join (cost=705.31..3,620.45 rows=6,079 width=199) (actual time=4.008..41.464 rows=6,081 loops=1)

  • Hash Cond: (ks.storetype = pd2.dickey)
15. 4.511 36.509 ↓ 1.0 6,081 1

Hash Left Join (cost=679.59..3,541.08 rows=6,079 width=195) (actual time=3.857..36.509 rows=6,081 loops=1)

  • Hash Cond: (ks.channeltype = pd1.dickey)
16. 11.521 31.828 ↓ 1.0 6,081 1

Hash Left Join (cost=653.87..3,459.32 rows=6,079 width=203) (actual time=3.678..31.828 rows=6,081 loops=1)

  • Hash Cond: (ks.seleareaid = ps.orgstructid)
17. 16.663 16.663 ↓ 1.0 6,081 1

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

  • Filter: (platstatus = 1)
  • Rows Removed by Filter: 119
18. 1.049 3.644 ↑ 1.0 7,861 1

Hash (cost=555.61..555.61 rows=7,861 width=129) (actual time=3.644..3.644 rows=7,861 loops=1)

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

Seq Scan on pl_orgstruct ps (cost=0.00..555.61 rows=7,861 width=129) (actual time=0.005..2.595 rows=7,861 loops=1)

20. 0.067 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.103 0.103 ↓ 1.1 494 1

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

22. 0.071 0.143 ↓ 1.1 494 1

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

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

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

24. 0.058 0.128 ↓ 1.1 494 1

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

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

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

26. 0.345 21.771 ↑ 1.0 683 1

Subquery Scan on *SELECT* 2 (cost=679.59..1,050.54 rows=683 width=74) (actual time=4.325..21.771 rows=683 loops=1)

27. 14.356 21.426 ↑ 1.0 683 1

Hash Left Join (cost=679.59..1,043.71 rows=683 width=1,184) (actual time=4.324..21.426 rows=683 loops=1)

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

Hash Left Join (cost=653.87..842.42 rows=683 width=191) (actual time=4.088..6.871 rows=683 loops=1)

  • Hash Cond: (kc.saleareaid = ps_1.orgstructid)
29. 1.486 1.486 ↑ 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.486 rows=683 loops=1)

  • Filter: (platstatus = 1)
  • Rows Removed by Filter: 17
30. 1.041 4.052 ↑ 1.0 7,861 1

Hash (cost=555.61..555.61 rows=7,861 width=129) (actual time=4.052..4.052 rows=7,861 loops=1)

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

Seq Scan on pl_orgstruct ps_1 (cost=0.00..555.61 rows=7,861 width=129) (actual time=0.009..3.011 rows=7,861 loops=1)

32. 0.069 0.199 ↓ 1.1 494 1

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

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

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

34. 0.021 0.021 ↑ 1.0 1 1

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

  • Filter: (platstatus = 1)
35. 0.067 0.207 ↓ 1.1 493 1

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

  • Buckets: 1024 Batches: 1 Memory Usage: 35kB
36. 0.140 0.140 ↓ 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.140 rows=493 loops=1)

  • Filter: (platstatus = 1)
  • Rows Removed by Filter: 1
37. 29,816.138 29,853.945 ↓ 13.3 28,382 6,765

Materialize (cost=2,644.65..3,469.14 rows=2,128 width=205) (actual time=0.006..4.413 rows=28,382 loops=6,765)

38. 8.426 37.807 ↓ 13.3 28,382 1

Hash Right Join (cost=2,644.65..3,458.50 rows=2,128 width=205) (actual time=25.241..37.807 rows=28,382 loops=1)

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

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

  • Filter: (platstatus = 1)
  • Rows Removed by Filter: 5616
40. 2.347 25.226 ↓ 4.1 5,397 1

Hash (cost=2,628.25..2,628.25 rows=1,312 width=193) (actual time=25.226..25.226 rows=5,397 loops=1)

  • Buckets: 8192 (originally 2048) Batches: 1 (originally 1) Memory Usage: 1197kB
41. 1.178 22.879 ↓ 4.1 5,397 1

Hash Right Join (cost=2,559.53..2,628.25 rows=1,312 width=193) (actual time=21.317..22.879 rows=5,397 loops=1)

  • Hash Cond: (chm.managerid = m.orgstructid)
42. 0.405 0.405 ↑ 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.405 rows=1,617 loops=1)

  • Filter: (platstatus = 1)
  • Rows Removed by Filter: 459
43. 1.850 21.296 ↓ 3.2 4,193 1

Hash (cost=2,543.13..2,543.13 rows=1,312 width=181) (actual time=21.296..21.296 rows=4,193 loops=1)

  • Buckets: 8192 (originally 2048) Batches: 1 (originally 1) Memory Usage: 896kB
44. 1.088 19.446 ↓ 3.2 4,193 1

Hash Join (cost=1,904.38..2,543.13 rows=1,312 width=181) (actual time=12.815..19.446 rows=4,193 loops=1)

  • Hash Cond: (p.parentorgstructid = sa.orgstructid)
45. 1.135 14.701 ↓ 3.1 4,193 1

Hash Join (cost=1,233.11..1,868.34 rows=1,343 width=68) (actual time=9.098..14.701 rows=4,193 loops=1)

  • Hash Cond: (m.parentorgstructid = p.orgstructid)
46. 1.226 9.058 ↓ 3.0 4,193 1

Hash Join (cost=561.85..1,193.47 rows=1,375 width=49) (actual time=4.538..9.058 rows=4,193 loops=1)

  • Hash Cond: (m.userinfoid = aa.userinfoid)
47. 1.231 6.045 ↓ 1.8 4,536 1

Hash Join (cost=336.73..942.16 rows=2,488 width=65) (actual time=2.716..6.045 rows=4,536 loops=1)

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

Seq Scan on pl_orgstruct m (cost=0.00..594.91 rows=4,001 width=35) (actual time=0.005..2.154 rows=4,616 loops=1)

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

Hash (cost=278.41..278.41 rows=4,666 width=30) (actual time=2.660..2.660 rows=4,327 loops=1)

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

Seq Scan on pl_userinfo u (cost=0.00..278.41 rows=4,666 width=30) (actual time=0.015..1.809 rows=4,327 loops=1)

  • Filter: ((status = 1) AND (platstatus = 1))
  • Rows Removed by Filter: 26
51. 0.547 1.787 ↓ 1.0 3,943 1

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

  • Buckets: 4096 Batches: 1 Memory Usage: 187kB
52. 1.240 1.240 ↓ 1.0 3,956 1

Seq Scan on pl_appaccount aa (cost=0.00..176.54 rows=3,886 width=8) (actual time=0.010..1.240 rows=3,956 loops=1)

  • Filter: ((status = 1) AND (platstatus = 1))
  • Rows Removed by Filter: 614
53. 1.777 4.508 ↑ 1.0 7,680 1

Hash (cost=575.26..575.26 rows=7,680 width=35) (actual time=4.507..4.508 rows=7,680 loops=1)

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

Seq Scan on pl_orgstruct p (cost=0.00..575.26 rows=7,680 width=35) (actual time=0.004..2.731 rows=7,680 loops=1)

  • Filter: (platstatus = 1)
  • Rows Removed by Filter: 181
55. 1.344 3.657 ↑ 1.0 7,680 1

Hash (cost=575.26..575.26 rows=7,680 width=129) (actual time=3.657..3.657 rows=7,680 loops=1)

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

Seq Scan on pl_orgstruct sa (cost=0.00..575.26 rows=7,680 width=129) (actual time=0.004..2.313 rows=7,680 loops=1)

  • Filter: (platstatus = 1)
  • Rows Removed by Filter: 181
57. 1.003 122.104 ↑ 3.4 3,387 1

Hash (cost=30,761.95..30,761.95 rows=11,459 width=24) (actual time=122.104..122.104 rows=3,387 loops=1)

  • Buckets: 16384 Batches: 1 Memory Usage: 314kB
58. 5.490 121.101 ↑ 3.4 3,387 1

HashAggregate (cost=30,532.77..30,647.36 rows=11,459 width=24) (actual time=120.332..121.101 rows=3,387 loops=1)

  • Group Key: kx_visit_workrecord.userid, kx_visit_workrecord.customerid
59. 115.611 115.611 ↓ 1.3 16,520 1

Seq Scan on kx_visit_workrecord (cost=0.00..30,439.68 rows=12,412 width=16) (actual time=86.477..115.611 rows=16,520 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: 671510
60. 0.725 4.263 ↑ 1.0 5,353 1

Hash (cost=594.91..594.91 rows=5,395 width=8) (actual time=4.263..4.263 rows=5,353 loops=1)

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

Seq Scan on pl_orgstruct saleareaid (cost=0.00..594.91 rows=5,395 width=8) (actual time=0.011..3.538 rows=5,353 loops=1)

  • Filter: (((codepath)::text ~~ '1.%'::text) AND (platstatus = 1))
  • Rows Removed by Filter: 2508
62. 0.526 9.321 ↓ 12.0 2,289 1

Hash (cost=1,478.36..1,478.36 rows=190 width=30) (actual time=9.321..9.321 rows=2,289 loops=1)

  • Buckets: 4096 (originally 1024) Batches: 1 (originally 1) Memory Usage: 179kB
63. 0.683 8.795 ↓ 12.0 2,289 1

Hash Join (cost=853.93..1,478.36 rows=190 width=30) (actual time=4.909..8.795 rows=2,289 loops=1)

  • Hash Cond: (pm.userinfoid = aa_1.userinfoid)
64. 0.549 6.287 ↓ 7.6 2,608 1

Hash Join (cost=628.82..1,249.62 rows=344 width=38) (actual time=3.053..6.287 rows=2,608 loops=1)

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

Seq Scan on pl_orgstruct pm (cost=0.00..614.57 rows=2,376 width=27) (actual time=0.013..2.725 rows=2,643 loops=1)

  • Filter: ((orgtypeid = 5) AND (status = 1) AND (platstatus = 1))
  • Rows Removed by Filter: 5218
66. 0.155 3.013 ↑ 1.5 780 1

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

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

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

  • Filter: ((orgtypeid = 4) AND (status = 1) AND (platstatus = 1))
  • Rows Removed by Filter: 7081
68. 0.550 1.825 ↓ 1.0 3,943 1

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

  • Buckets: 4096 Batches: 1 Memory Usage: 187kB
69. 1.275 1.275 ↓ 1.0 3,956 1

Seq Scan on pl_appaccount aa_1 (cost=0.00..176.54 rows=3,886 width=8) (actual time=0.015..1.275 rows=3,956 loops=1)

  • Filter: ((status = 1) AND (platstatus = 1))
  • Rows Removed by Filter: 614
Planning time : 24.589 ms
Execution time : 51,284.343 ms