explain.depesz.com

PostgreSQL's explain analyze made readable

Result: Mpar

Settings
# exclusive inclusive rows x rows loops node
1. 4.848 4,607.097 ↓ 604.6 3,023 1

Sort (cost=146,344.61..146,344.63 rows=5 width=270) (actual time=4,606.613..4,607.097 rows=3,023 loops=1)

  • Sort Key: ko.createtime DESC
  • Sort Method: quicksort Memory: 1,608kB
2. 2.628 4,602.249 ↓ 604.6 3,023 1

Nested Loop Left Join (cost=6,925.38..146,344.55 rows=5 width=270) (actual time=690.786..4,602.249 rows=3,023 loops=1)

3. 2.585 4,596.598 ↓ 604.6 3,023 1

Nested Loop Left Join (cost=6,925.11..146,343.07 rows=5 width=237) (actual time=690.773..4,596.598 rows=3,023 loops=1)

4. 3.167 4,572.866 ↓ 604.2 3,021 1

Nested Loop Left Join (cost=6,924.83..146,340.13 rows=5 width=240) (actual time=690.738..4,572.866 rows=3,021 loops=1)

5. 0.000 4,566.678 ↓ 604.2 3,021 1

Nested Loop (cost=6,924.55..146,338.65 rows=5 width=235) (actual time=690.719..4,566.678 rows=3,021 loops=1)

6. 290.920 1,631.862 ↓ 463.8 423,411 1

Hash Join (cost=6,924.26..146,016.74 rows=913 width=100) (actual time=690.263..1,631.862 rows=423,411 loops=1)

  • Hash Cond: ("*SELECT* 1".id = kks.id)
7. 41.748 651.461 ↓ 1.0 439,345 1

Append (cost=96.18..137,540.72 rows=437,015 width=8) (actual time=0.762..651.461 rows=439,345 loops=1)

8. 40.356 601.394 ↓ 1.0 424,752 1

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

9. 98.656 561.038 ↓ 1.0 424,752 1

Hash Left Join (cost=96.18..132,307.52 rows=422,472 width=2,412) (actual time=0.761..561.038 rows=424,752 loops=1)

  • Hash Cond: (ks.storelevel = pd3.dickey)
10. 88.420 462.166 ↓ 1.0 424,752 1

Hash Left Join (cost=64.12..130,690.63 rows=422,472 width=16) (actual time=0.532..462.166 rows=424,752 loops=1)

  • Hash Cond: (ks.storetype = pd2.dickey)
11. 126.376 373.530 ↓ 1.0 424,752 1

Hash Left Join (cost=32.06..125,419.21 rows=422,472 width=24) (actual time=0.301..373.530 rows=424,752 loops=1)

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

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

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

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

  • Buckets: 1,024 Batches: 1 Memory Usage: 29kB
14. 0.199 0.199 ↑ 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.032..0.199 rows=535 loops=1)

  • Heap Fetches: 426
15. 0.088 0.216 ↑ 1.1 535 1

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

  • Buckets: 1,024 Batches: 1 Memory Usage: 29kB
16. 0.128 0.128 ↑ 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.015..0.128 rows=535 loops=1)

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

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

  • Buckets: 1,024 Batches: 1 Memory Usage: 29kB
18. 0.132 0.132 ↑ 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.020..0.132 rows=535 loops=1)

  • Heap Fetches: 426
19. 1.274 8.301 ↓ 1.0 14,543 1

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

20. 3.704 7.027 ↓ 1.0 14,543 1

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

  • Hash Cond: (kc.channeltype = pd1_1.dickey)
21. 3.053 3.053 ↓ 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.008..3.053 rows=14,543 loops=1)

  • Filter: (platstatus = 1)
22. 0.089 0.270 ↑ 1.1 535 1

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

  • Buckets: 1,024 Batches: 1 Memory Usage: 29kB
23. 0.181 0.181 ↑ 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.016..0.181 rows=535 loops=1)

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

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

  • Filter: (platstatus = 1)
  • Rows Removed by Filter: 1
25. 134.263 689.481 ↓ 322.2 423,411 1

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

  • Buckets: 524,288 (originally 2048) Batches: 1 (originally 1) Memory Usage: 62,497kB
26. 75.775 555.218 ↓ 322.2 423,411 1

Nested Loop (cost=2,817.06..6,811.66 rows=1,314 width=108) (actual time=78.954..555.218 rows=423,411 loops=1)

27. 0.113 84.013 ↓ 35.9 1,470 1

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

28. 1.089 79.490 ↓ 35.9 1,470 1

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

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

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

30.          

CTE _r

31. 0.240 78.014 ↓ 35.9 1,470 1

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

32. 0.394 0.394 ↑ 1.0 1 1

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

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

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

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

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

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

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

36. 1.365 1.365 ↓ 29.4 294 65

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

37. 38.220 38.220 ↑ 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.002 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. 395.430 395.430 ↑ 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.004..0.269 rows=288 loops=1,470)

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

Index Scan using ind_kx_order_1 on kx_order ko (cost=0.29..0.34 rows=1 width=161) (actual time=0.007..0.007 rows=0 loops=423,411)

  • Index Cond: (((customercode)::text = (kks.storecode)::text) AND (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 (isdirectsupply = 0))
41. 3.021 3.021 ↑ 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.001..0.001 rows=1 loops=3,021)

  • Index Cond: (dictionaryid = kks.channeltype)
  • Filter: (platstatus = 1)
42. 21.147 21.147 ↑ 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.005..0.007 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 : 8.495 ms
Execution time : 4,607.964 ms