explain.depesz.com

PostgreSQL's explain analyze made readable

Result: DyJ4

Settings
# exclusive inclusive rows x rows loops node
1. 5.016 4,974.048 ↓ 755.8 3,023 1

Sort (cost=146,346.27..146,346.28 rows=4 width=270) (actual time=4,973.519..4,974.048 rows=3,023 loops=1)

  • Sort Key: ko.createtime DESC
  • Sort Method: quicksort Memory: 1,608kB
2. 2.732 4,969.032 ↓ 755.8 3,023 1

Nested Loop Left Join (cost=6,925.38..146,346.23 rows=4 width=270) (actual time=995.469..4,969.032 rows=3,023 loops=1)

3. 1.751 4,963.277 ↓ 755.8 3,023 1

Nested Loop Left Join (cost=6,925.11..146,345.04 rows=4 width=237) (actual time=995.443..4,963.277 rows=3,023 loops=1)

4. 0.515 4,937.358 ↓ 755.2 3,021 1

Nested Loop Left Join (cost=6,924.83..146,342.67 rows=4 width=240) (actual time=995.366..4,937.358 rows=3,021 loops=1)

5. 0.000 4,930.801 ↓ 755.2 3,021 1

Nested Loop (cost=6,924.55..146,341.49 rows=4 width=235) (actual time=995.337..4,930.801 rows=3,021 loops=1)

6. 300.357 1,991.895 ↓ 463.7 423,327 1

Hash Join (cost=6,924.26..146,016.74 rows=913 width=100) (actual time=994.926..1,991.895 rows=423,327 loops=1)

  • Hash Cond: ("*SELECT* 1".id = kks.id)
7. 41.065 697.541 ↓ 1.0 439,261 1

Append (cost=96.18..137,540.72 rows=437,015 width=8) (actual time=0.913..697.541 rows=439,261 loops=1)

8. 42.140 646.170 ↓ 1.0 424,668 1

Subquery Scan on *SELECT* 1 (cost=96.18..136,532.24 rows=422,472 width=8) (actual time=0.912..646.170 rows=424,668 loops=1)

9. 98.125 604.030 ↓ 1.0 424,668 1

Hash Left Join (cost=96.18..132,307.52 rows=422,472 width=2,412) (actual time=0.912..604.030 rows=424,668 loops=1)

  • Hash Cond: (ks.storelevel = pd3.dickey)
10. 86.457 505.697 ↓ 1.0 424,668 1

Hash Left Join (cost=64.12..130,690.63 rows=422,472 width=16) (actual time=0.691..505.697 rows=424,668 loops=1)

  • Hash Cond: (ks.storetype = pd2.dickey)
11. 120.768 419.037 ↓ 1.0 424,668 1

Hash Left Join (cost=32.06..125,419.21 rows=422,472 width=24) (actual time=0.476..419.037 rows=424,668 loops=1)

  • Hash Cond: (ks.channeltype = pd1.dickey)
12. 297.818 297.818 ↓ 1.0 424,668 1

Seq Scan on kx_kq_store ks (cost=0.00..119,919.09 rows=422,472 width=40) (actual time=0.013..297.818 rows=424,668 loops=1)

  • Filter: (platstatus = 1)
  • Rows Removed by Filter: 208,779
13. 0.107 0.451 ↑ 1.1 535 1

Hash (cost=24.88..24.88 rows=574 width=8) (actual time=0.451..0.451 rows=535 loops=1)

  • Buckets: 1,024 Batches: 1 Memory Usage: 29kB
14. 0.344 0.344 ↑ 1.1 535 1

Index Only Scan using idx_pl_dictionary_dickey on pl_dictionary pd1 (cost=0.28..24.88 rows=574 width=8) (actual time=0.064..0.344 rows=535 loops=1)

  • Heap Fetches: 426
15. 0.078 0.203 ↑ 1.1 535 1

Hash (cost=24.88..24.88 rows=574 width=8) (actual time=0.203..0.203 rows=535 loops=1)

  • Buckets: 1,024 Batches: 1 Memory Usage: 29kB
16. 0.125 0.125 ↑ 1.1 535 1

Index Only Scan using idx_pl_dictionary_dickey on pl_dictionary pd2 (cost=0.28..24.88 rows=574 width=8) (actual time=0.014..0.125 rows=535 loops=1)

  • Heap Fetches: 426
17. 0.084 0.208 ↑ 1.1 535 1

Hash (cost=24.88..24.88 rows=574 width=8) (actual time=0.208..0.208 rows=535 loops=1)

  • Buckets: 1,024 Batches: 1 Memory Usage: 29kB
18. 0.124 0.124 ↑ 1.1 535 1

Index Only Scan using idx_pl_dictionary_dickey on pl_dictionary pd3 (cost=0.28..24.88 rows=574 width=8) (actual time=0.013..0.124 rows=535 loops=1)

  • Heap Fetches: 426
19. 1.268 10.282 ↓ 1.0 14,543 1

Subquery Scan on *SELECT* 2 (cost=32.06..1,005.85 rows=14,542 width=8) (actual time=0.325..10.282 rows=14,543 loops=1)

20. 3.693 9.014 ↓ 1.0 14,543 1

Hash Left Join (cost=32.06..860.43 rows=14,542 width=2,412) (actual time=0.324..9.014 rows=14,543 loops=1)

  • Hash Cond: (kc.channeltype = pd1_1.dickey)
21. 5.039 5.039 ↓ 1.0 14,543 1

Seq Scan on ka_kq_channelcustomers kc (cost=0.00..773.77 rows=14,542 width=24) (actual time=0.015..5.039 rows=14,543 loops=1)

  • Filter: (platstatus = 1)
22. 0.079 0.282 ↑ 1.1 535 1

Hash (cost=24.88..24.88 rows=574 width=8) (actual time=0.281..0.282 rows=535 loops=1)

  • Buckets: 1,024 Batches: 1 Memory Usage: 29kB
23. 0.203 0.203 ↑ 1.1 535 1

Index Only Scan using idx_pl_dictionary_dickey on pl_dictionary pd1_1 (cost=0.28..24.88 rows=574 width=8) (actual time=0.026..0.203 rows=535 loops=1)

  • Heap Fetches: 426
24. 0.024 0.024 ↓ 50.0 50 1

Seq Scan on kx_kq_ka ka (cost=0.00..2.64 rows=1 width=8) (actual time=0.013..0.024 rows=50 loops=1)

  • Filter: (platstatus = 1)
  • Rows Removed by Filter: 1
25. 135.726 993.997 ↓ 322.2 423,327 1

Hash (cost=6,811.66..6,811.66 rows=1,314 width=108) (actual time=993.997..993.997 rows=423,327 loops=1)

  • Buckets: 524,288 (originally 2048) Batches: 1 (originally 1) Memory Usage: 62,486kB
26. 80.175 858.271 ↓ 322.2 423,327 1

Nested Loop (cost=2,817.06..6,811.66 rows=1,314 width=108) (actual time=85.888..858.271 rows=423,327 loops=1)

27. 0.775 91.606 ↓ 35.9 1,470 1

Nested Loop (cost=2,816.63..3,125.31 rows=41 width=87) (actual time=85.854..91.606 rows=1,470 loops=1)

28. 1.132 86.421 ↓ 35.9 1,470 1

HashAggregate (cost=2,816.35..2,816.76 rows=41 width=8) (actual time=85.824..86.421 rows=1,470 loops=1)

  • Group Key: _r.orgstructid
29. 85.289 85.289 ↓ 35.9 1,470 1

CTE Scan on _r (cost=2,815.01..2,815.83 rows=41 width=8) (actual time=0.531..85.289 rows=1,470 loops=1)

30.          

CTE _r

31. 0.256 84.931 ↓ 35.9 1,470 1

Recursive Union (cost=0.00..2,815.01 rows=41 width=8) (actual time=0.530..84.931 rows=1,470 loops=1)

32. 0.685 0.685 ↑ 1.0 1 1

Seq Scan on pl_saleorg_resp _s (cost=0.00..97.63 rows=1 width=8) (actual time=0.529..0.685 rows=1 loops=1)

  • Filter: ((platstatus = 1) AND (memberid = '1271009751085486080'::bigint))
  • Rows Removed by Filter: 3,255
33. 9.000 83.990 ↓ 73.5 294 5

Nested Loop (cost=0.29..271.66 rows=4 width=8) (actual time=15.542..16.798 rows=294 loops=5)

  • Join Filter: (_a.orgtypeid = _t.orgtypeid)
  • Rows Removed by Join Filter: 24,300
34. 0.045 0.045 ↓ 13.0 13 5

Seq Scan on pl_orgtype _t (cost=0.00..1.20 rows=1 width=8) (actual time=0.004..0.009 rows=13 loops=5)

  • Filter: (orgtypecategory = 1)
  • Rows Removed by Filter: 6
35. 16.185 74.945 ↓ 33.8 1,892 65

Nested Loop (cost=0.29..269.76 rows=56 width=16) (actual time=0.003..1.153 rows=1,892 loops=65)

36. 1.430 1.430 ↓ 29.4 294 65

WorkTable Scan on _r _r_1 (cost=0.00..0.20 rows=10 width=8) (actual time=0.000..0.022 rows=294 loops=65)

37. 57.330 57.330 ↑ 1.0 6 19,110

Index Scan using ix_pl_orgstruct_parentorgstructid on pl_orgstruct _a (cost=0.29..26.90 rows=6 width=24) (actual time=0.001..0.003 rows=6 loops=19,110)

  • Index Cond: (parentorgstructid = _r_1.orgstructid)
38. 4.410 4.410 ↑ 1.0 1 1,470

Index Scan using pl_orgstruct_pkey on pl_orgstruct ps (cost=0.29..7.52 rows=1 width=79) (actual time=0.003..0.003 rows=1 loops=1,470)

  • Index Cond: (orgstructid = _r.orgstructid)
  • Filter: (platstatus = 1)
39. 686.490 686.490 ↑ 4.5 288 1,470

Index Scan using idx_kx_kq_store_seleareaid on kx_kq_store kks (cost=0.42..76.81 rows=1,310 width=45) (actual time=0.007..0.467 rows=288 loops=1,470)

  • Index Cond: (seleareaid = ps.orgstructid)
  • Filter: (platstatus = 1)
  • Rows Removed by Filter: 127
40. 2,963.289 2,963.289 ↓ 0.0 0 423,327

Index Scan using ix_kx_order_customercode on kx_order ko (cost=0.29..0.35 rows=1 width=161) (actual time=0.007..0.007 rows=0 loops=423,327)

  • Index Cond: ((customercode)::text = (kks.storecode)::text)
  • Filter: ((ordertime >= '2020-07-06 00:00:00'::timestamp without time zone) AND (ordertime <= '2020-07-06 23:59:59'::timestamp without time zone) AND (ordergroup = 1) AND (platstatus = 1) AND (isdirectsupply = 0))
  • Rows Removed by Filter: 0
41. 6.042 6.042 ↑ 1.0 1 3,021

Index Scan using pl_dictionary_pkey on pl_dictionary ctype (cost=0.28..0.30 rows=1 width=21) (actual time=0.002..0.002 rows=1 loops=3,021)

  • Index Cond: (dictionaryid = kks.channeltype)
  • Filter: (platstatus = 1)
42. 24.168 24.168 ↑ 1.0 1 3,021

Index Scan using ix_pl_userinfo_orgstructid on pl_userinfo u (cost=0.28..0.58 rows=1 width=22) (actual time=0.006..0.008 rows=1 loops=3,021)

  • Index Cond: (ko.orgstructid = orgstructid)
  • Filter: ((platstatus = 1) AND ((ko.salesman)::text = (userinfoname)::text))
  • Rows Removed by Filter: 9
43. 3.023 3.023 ↑ 1.0 1 3,023

Index Scan using pl_dictionary_pkey on pl_dictionary stype (cost=0.28..0.30 rows=1 width=21) (actual time=0.001..0.001 rows=1 loops=3,023)

  • Index Cond: (dictionaryid = kks.storetype)
  • Filter: (platstatus = 1)
Planning time : 13.431 ms
Execution time : 4,975.724 ms