explain.depesz.com

PostgreSQL's explain analyze made readable

Result: Hjuw

Settings
# exclusive inclusive rows x rows loops node
1. 0.001 27,497.142 ↓ 0.0 0 1

Limit (cost=701.61..77,738.85 rows=20 width=3,181) (actual time=27,497.142..27,497.142 rows=0 loops=1)

2. 0.000 27,497.141 ↓ 0.0 0 1

Nested Loop Left Join (cost=701.61..10,612,581.49 rows=2,755 width=3,181) (actual time=27,497.141..27,497.141 rows=0 loops=1)

3. 0.001 27,497.141 ↓ 0.0 0 1

Nested Loop Left Join (cost=701.33..10,588,813.04 rows=2,755 width=2,688) (actual time=27,497.141..27,497.141 rows=0 loops=1)

4. 0.000 27,497.140 ↓ 0.0 0 1

Nested Loop Left Join (cost=701.05..10,587,990.84 rows=2,755 width=2,680) (actual time=27,497.140..27,497.140 rows=0 loops=1)

  • Join Filter: (sar.storeid = ks.id)
5. 0.000 27,497.140 ↓ 0.0 0 1

Nested Loop Left Join (cost=701.05..10,587,806.53 rows=2,755 width=2,648) (actual time=27,497.140..27,497.140 rows=0 loops=1)

  • Join Filter: (kx_salearea.saleareaid = ks.kxsaleareaid)
6. 0.001 27,497.140 ↓ 0.0 0 1

Nested Loop Left Join (cost=701.05..10,587,764.19 rows=2,755 width=2,624) (actual time=27,497.140..27,497.140 rows=0 loops=1)

7. 0.000 27,497.139 ↓ 0.0 0 1

Nested Loop Left Join (cost=700.76..10,586,887.86 rows=2,755 width=2,611) (actual time=27,497.139..27,497.139 rows=0 loops=1)

  • Join Filter: (ks.kaid = ka.id)
8. 28.234 27,497.139 ↓ 0.0 0 1

Nested Loop Semi Join (cost=700.76..10,586,843.93 rows=2,755 width=2,193) (actual time=27,497.139..27,497.139 rows=0 loops=1)

  • Join Filter: (ks.id = _sp.storeid)
9. 14,873.358 27,468.905 ↑ 2.7 188,288 1

Nested Loop Left Join (cost=2.10..2,669,047.70 rows=501,239 width=2,193) (actual time=5.139..27,468.905 rows=188,288 loops=1)

  • Join Filter: ((sl.codepath)::text ~~ ((csq.codepath)::text || '%'::text))
  • Rows Removed by Join Filter: 109,019,444
10. 85.789 8,076.635 ↓ 1.0 188,288 1

Nested Loop Left Join (cost=2.10..913,944.72 rows=187,379 width=2,267) (actual time=3.853..8,076.635 rows=188,288 loops=1)

11. 70.233 7,990.846 ↓ 1.0 188,288 1

Nested Loop Left Join (cost=1.82..856,149.68 rows=187,379 width=2,251) (actual time=3.852..7,990.846 rows=188,288 loops=1)

12. 76.886 7,732.325 ↓ 1.0 188,288 1

Nested Loop Left Join (cost=1.54..799,497.12 rows=187,379 width=2,212) (actual time=3.845..7,732.325 rows=188,288 loops=1)

13. 96.942 7,467.151 ↓ 1.0 188,288 1

Nested Loop Left Join (cost=1.26..741,702.08 rows=187,379 width=2,196) (actual time=3.842..7,467.151 rows=188,288 loops=1)

14. 119.324 7,181.921 ↓ 1.0 188,288 1

Nested Loop Left Join (cost=0.98..683,907.03 rows=187,379 width=2,180) (actual time=3.840..7,181.921 rows=188,288 loops=1)

15. 4,461.340 6,874.309 ↓ 1.0 188,288 1

Nested Loop Left Join (cost=0.71..626,111.99 rows=187,379 width=2,164) (actual time=3.832..6,874.309 rows=188,288 loops=1)

  • Join Filter: ((sl.codepath)::text ~~ ((sj.codepath)::text || '%'::text))
  • Rows Removed by Join Filter: 32,762,112
16. 496.054 1,094.953 ↓ 1.0 188,288 1

Nested Loop Left Join (cost=0.71..87,568.10 rows=187,379 width=2,151) (actual time=1.996..1,094.953 rows=188,288 loops=1)

  • Join Filter: ((sl.codepath)::text ~~ ((dq.codepath)::text || '%'::text))
  • Rows Removed by Join Filter: 3,200,896
17. 121.657 410.611 ↓ 1.0 188,288 1

Nested Loop Left Join (cost=0.71..76,964.95 rows=187,379 width=2,138) (actual time=0.024..410.611 rows=188,288 loops=1)

18. 100.666 100.666 ↓ 1.0 188,288 1

Index Scan Backward using idx_kx_kq_store_createtime on kx_kq_store ks (cost=0.42..17,362.81 rows=187,379 width=1,946) (actual time=0.016..100.666 rows=188,288 loops=1)

  • Filter: (((isself IS NULL) OR (isself = 0)) AND (status = 1) AND (platstatus = 1))
  • Rows Removed by Filter: 2,779
19. 188.288 188.288 ↑ 1.0 1 188,288

Index Scan using pl_orgstruct_pkey on pl_orgstruct sl (cost=0.29..0.32 rows=1 width=200) (actual time=0.001..0.001 rows=1 loops=188,288)

  • Index Cond: (ks.seleareaid = orgstructid)
  • Filter: (platstatus = 1)
20. 185.711 188.288 ↓ 6.0 18 188,288

Materialize (cost=0.00..765.76 rows=3 width=100) (actual time=0.000..0.001 rows=18 loops=188,288)

21. 2.577 2.577 ↓ 6.0 18 1

Seq Scan on pl_orgstruct dq (cost=0.00..765.75 rows=3 width=100) (actual time=1.656..2.577 rows=18 loops=1)

  • Filter: ((orgtypeid = 2) AND (platstatus = 1))
  • Rows Removed by Filter: 13,256
22. 1,315.471 1,318.016 ↓ 1.1 175 188,288

Materialize (cost=0.00..766.57 rows=164 width=100) (actual time=0.000..0.007 rows=175 loops=188,288)

23. 2.545 2.545 ↓ 1.1 175 1

Seq Scan on pl_orgstruct sj (cost=0.00..765.75 rows=164 width=100) (actual time=0.068..2.545 rows=175 loops=1)

  • Filter: ((orgtypeid = 3) AND (platstatus = 1))
  • Rows Removed by Filter: 13,099
24. 188.288 188.288 ↑ 1.0 1 188,288

Index Scan using idx_pl_dictionary_dickey on pl_dictionary ct (cost=0.28..0.30 rows=1 width=24) (actual time=0.001..0.001 rows=1 loops=188,288)

  • Index Cond: (ks.channeltype = dickey)
  • Filter: (platstatus = 1)
25. 188.288 188.288 ↑ 1.0 1 188,288

Index Scan using idx_pl_dictionary_dickey on pl_dictionary st (cost=0.28..0.30 rows=1 width=24) (actual time=0.001..0.001 rows=1 loops=188,288)

  • Index Cond: (ks.storetype = dickey)
  • Filter: (platstatus = 1)
26. 188.288 188.288 ↑ 1.0 1 188,288

Index Scan using idx_pl_dictionary_dickey on pl_dictionary sle (cost=0.28..0.30 rows=1 width=24) (actual time=0.001..0.001 rows=1 loops=188,288)

  • Index Cond: (ks.storelevel = dickey)
  • Filter: (platstatus = 1)
27. 188.288 188.288 ↑ 1.0 1 188,288

Index Scan using index_region_regionid on pl_region reg (cost=0.28..0.30 rows=1 width=47) (actual time=0.001..0.001 rows=1 loops=188,288)

  • Index Cond: (ks.regionid = regionid)
  • Filter: (platstatus = 1)
28. 0.000 0.000 ↓ 0.0 0 188,288

Index Scan using idx_pl_dictionary_dickey on pl_dictionary sm (cost=0.28..0.30 rows=1 width=24) (actual time=0.000..0.000 rows=0 loops=188,288)

  • Index Cond: (ks.supplymode = dickey)
  • Filter: (platstatus = 1)
29. 4,516.500 4,518.912 ↓ 1.1 580 188,288

Materialize (cost=0.00..768.42 rows=535 width=100) (actual time=0.000..0.024 rows=580 loops=188,288)

30. 2.412 2.412 ↓ 1.1 580 1

Seq Scan on pl_orgstruct csq (cost=0.00..765.75 rows=535 width=100) (actual time=0.395..2.412 rows=580 loops=1)

  • Filter: ((orgtypeid = 6) AND (platstatus = 1))
  • Rows Removed by Filter: 12,694
31. 0.000 0.000 ↓ 0.0 0 188,288

Materialize (cost=698.67..728.86 rows=1,053 width=8) (actual time=0.000..0.000 rows=0 loops=188,288)

32. 0.000 0.586 ↓ 0.0 0 1

Nested Loop (cost=698.67..723.60 rows=1,053 width=8) (actual time=0.586..0.586 rows=0 loops=1)

33. 0.000 0.586 ↓ 0.0 0 1

HashAggregate (cost=698.39..698.57 rows=18 width=8) (actual time=0.586..0.586 rows=0 loops=1)

  • Group Key: _b.orgstructid
34. 0.001 0.586 ↓ 0.0 0 1

Nested Loop (cost=0.00..698.34 rows=18 width=8) (actual time=0.585..0.586 rows=0 loops=1)

  • Join Filter: ((_b.tn_codepath)::text ~~ ((pl_parentmember.tn_codepath)::text || '%'::text))
35. 0.585 0.585 ↓ 0.0 0 1

Seq Scan on pl_parentmember (cost=0.00..326.89 rows=1 width=55) (actual time=0.585..0.585 rows=0 loops=1)

  • Filter: ((platstatus = 1) AND (orgstructid = '1249573380248702976'::bigint))
  • Rows Removed by Filter: 3,593
36. 0.000 0.000 ↓ 0.0 0

Seq Scan on pl_parentmember _b (cost=0.00..317.91 rows=3,569 width=63) (never executed)

  • Filter: (platstatus = 1)
37. 0.000 0.000 ↓ 0.0 0

Index Scan using idx_kx_kq_storerepresentative_representativeid on kx_kq_storerepresentative _sp (cost=0.28..1.17 rows=22 width=16) (never executed)

  • Index Cond: (representativeid = _b.orgstructid)
  • Filter: (platstatus = 1)
38. 0.000 0.000 ↓ 0.0 0

Materialize (cost=0.00..2.60 rows=1 width=426) (never executed)

39. 0.000 0.000 ↓ 0.0 0

Seq Scan on kx_kq_ka ka (cost=0.00..2.60 rows=1 width=426) (never executed)

  • Filter: (platstatus = 1)
40. 0.000 0.000 ↓ 0.0 0

Index Scan using pl_orgstruct_pkey on pl_orgstruct co (cost=0.29..0.32 rows=1 width=21) (never executed)

  • Index Cond: (orgstructid = ks.createop)
  • Filter: (platstatus = 1)
41. 0.000 0.000 ↓ 0.0 0

Materialize (cost=0.00..1.02 rows=1 width=40) (never executed)

42. 0.000 0.000 ↓ 0.0 0

Seq Scan on kx_salearea (cost=0.00..1.01 rows=1 width=40) (never executed)

  • Filter: (platstatus = 1)
43. 0.000 0.000 ↓ 0.0 0

Materialize (cost=0.00..19.02 rows=4 width=40) (never executed)

44. 0.000 0.000 ↓ 0.0 0

Seq Scan on kx_store_autotags_result sar (cost=0.00..19.00 rows=4 width=40) (never executed)

  • Filter: (platstatus = 1)
45. 0.000 0.000 ↓ 0.0 0

Index Scan using pl_dictionary_pkey on pl_dictionary pd (cost=0.28..0.30 rows=1 width=24) (never executed)

  • Index Cond: (dictionaryid = ks.tn_two_level)
  • Filter: (platstatus = 1)
46. 0.000 0.000 ↓ 0.0 0

Index Scan using ka_kq_channelcustomers_pkey on ka_kq_channelcustomers kkc (cost=0.28..0.31 rows=1 width=55) (never executed)

  • Index Cond: (id = ks.distributor)
  • Filter: (platstatus = 1)
47.          

SubPlan (for Nested Loop Left Join)

48. 0.000 0.000 ↓ 0.0 0

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

49. 0.000 0.000 ↓ 0.0 0

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

  • Sort Key: vc.updatetime DESC
50. 0.000 0.000 ↓ 0.0 0

Index Scan using idx_kx_visit_customerstatus_customerid on kx_visit_customerstatus vc (cost=0.28..8.31 rows=1 width=12) (never executed)

  • Index Cond: (customerid = ks.id)
  • Filter: ((visitdate >= '2020-07-07'::date) AND (visitdate <= '2020-07-07'::date) AND (userid = '1249573380248702976'::bigint) AND (customertype = '905324680615956480'::bigint) AND (platstatus = 1))
Planning time : 69.022 ms
Execution time : 27,510.015 ms