explain.depesz.com

PostgreSQL's explain analyze made readable

Result: 6I6H

Settings
# exclusive inclusive rows x rows loops node
1. 0.001 23.337 ↑ 1.0 1 1

Limit (cost=1,530.53..1,626.21 rows=1 width=3,209) (actual time=23.337..23.337 rows=1 loops=1)

2. 0.003 23.336 ↑ 141,118.0 1 1

Nested Loop Left Join (cost=1,530.53..13,504,784.45 rows=141,118 width=3,209) (actual time=23.336..23.336 rows=1 loops=1)

3. 0.003 23.332 ↑ 141,118.0 1 1

Nested Loop Left Join (cost=1,530.24..13,461,406.46 rows=141,118 width=2,688) (actual time=23.332..23.332 rows=1 loops=1)

4. 0.002 23.328 ↑ 141,118.0 1 1

Nested Loop Left Join (cost=1,529.97..13,419,291.34 rows=141,118 width=2,680) (actual time=23.328..23.328 rows=1 loops=1)

  • Join Filter: (sar.storeid = ks.id)
5. 0.002 23.324 ↑ 141,118.0 1 1

Nested Loop Left Join (cost=1,529.97..13,410,805.25 rows=141,118 width=2,648) (actual time=23.324..23.324 rows=1 loops=1)

  • Join Filter: (kx_salearea.saleareaid = ks.kxsaleareaid)
6. 0.002 23.317 ↑ 141,118.0 1 1

Nested Loop Left Join (cost=1,529.97..13,408,687.47 rows=141,118 width=2,624) (actual time=23.317..23.317 rows=1 loops=1)

7. 0.005 23.309 ↑ 141,118.0 1 1

Nested Loop Left Join (cost=1,529.68..13,363,800.38 rows=141,118 width=2,611) (actual time=23.309..23.309 rows=1 loops=1)

  • Join Filter: (ks.kaid = ka.id)
  • Rows Removed by Join Filter: 48
8. 0.590 23.278 ↑ 141,118.0 1 1

Nested Loop Semi Join (cost=1,529.68..13,361,681.01 rows=141,118 width=2,193) (actual time=23.278..23.278 rows=1 loops=1)

  • Join Filter: (((_sp.platstatus = 1) AND (hashed SubPlan 1)) OR ((_sp.representativeid = '1249573380248702976'::bigint) AND (_sp.storeid = ks.id)))
  • Rows Removed by Join Filter: 88
9. 6.684 21.531 ↑ 5,632.4 89 1

Nested Loop Left Join (cost=2.10..2,669,303.27 rows=501,287 width=2,193) (actual time=5.932..21.531 rows=89 loops=1)

  • Join Filter: ((sl.codepath)::text ~~ ((csq.codepath)::text || '%'::text))
  • Rows Removed by Join Filter: 50,999
10. 0.055 9.774 ↑ 2,105.6 89 1

Nested Loop Left Join (cost=2.10..914,031.77 rows=187,397 width=2,267) (actual time=4.297..9.774 rows=89 loops=1)

11. 0.083 9.719 ↑ 2,105.6 89 1

Nested Loop Left Join (cost=1.82..856,231.19 rows=187,397 width=2,251) (actual time=4.294..9.719 rows=89 loops=1)

12. 0.058 9.547 ↑ 2,105.6 89 1

Nested Loop Left Join (cost=1.54..799,573.23 rows=187,397 width=2,212) (actual time=4.284..9.547 rows=89 loops=1)

13. 0.045 9.400 ↑ 2,105.6 89 1

Nested Loop Left Join (cost=1.26..741,772.65 rows=187,397 width=2,196) (actual time=4.279..9.400 rows=89 loops=1)

14. 0.080 9.355 ↑ 2,105.6 89 1

Nested Loop Left Join (cost=0.98..683,972.08 rows=187,397 width=2,180) (actual time=4.276..9.355 rows=89 loops=1)

15. 2.140 9.186 ↑ 2,105.6 89 1

Nested Loop Left Join (cost=0.71..626,171.50 rows=187,397 width=2,164) (actual time=4.264..9.186 rows=89 loops=1)

  • Join Filter: ((sl.codepath)::text ~~ ((sj.codepath)::text || '%'::text))
  • Rows Removed by Join Filter: 15,370
16. 0.212 3.486 ↑ 2,105.6 89 1

Nested Loop Left Join (cost=0.71..87,575.95 rows=187,397 width=2,151) (actual time=2.140..3.486 rows=89 loops=1)

  • Join Filter: ((sl.codepath)::text ~~ ((dq.codepath)::text || '%'::text))
  • Rows Removed by Join Filter: 1,501
17. 0.011 0.426 ↑ 2,105.6 89 1

Nested Loop Left Join (cost=0.71..76,971.85 rows=187,397 width=2,138) (actual time=0.034..0.426 rows=89 loops=1)

18. 0.237 0.237 ↑ 2,105.6 89 1

Index Scan Backward using idx_kx_kq_store_createtime on kx_kq_store ks (cost=0.42..17,364.23 rows=187,397 width=1,946) (actual time=0.024..0.237 rows=89 loops=1)

  • Filter: (((isself IS NULL) OR (isself = 0)) AND (status = 1) AND (platstatus = 1))
19. 0.178 0.178 ↑ 1.0 1 89

Index Scan using pl_orgstruct_pkey on pl_orgstruct sl (cost=0.29..0.32 rows=1 width=200) (actual time=0.002..0.002 rows=1 loops=89)

  • Index Cond: (ks.seleareaid = orgstructid)
  • Filter: (platstatus = 1)
20. 0.126 2.848 ↓ 6.0 18 89

Materialize (cost=0.00..765.76 rows=3 width=100) (actual time=0.020..0.032 rows=18 loops=89)

21. 2.722 2.722 ↓ 6.0 18 1

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

  • Filter: ((orgtypeid = 2) AND (platstatus = 1))
  • Rows Removed by Filter: 13,256
22. 0.640 3.560 ↓ 1.1 174 89

Materialize (cost=0.00..766.57 rows=164 width=100) (actual time=0.001..0.040 rows=174 loops=89)

23. 2.920 2.920 ↓ 1.1 175 1

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

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

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=89)

  • Index Cond: (ks.channeltype = dickey)
  • Filter: (platstatus = 1)
25. 0.000 0.000 ↓ 0.0 0 89

Index Scan using idx_pl_dictionary_dickey on pl_dictionary st (cost=0.28..0.30 rows=1 width=24) (actual time=0.000..0.000 rows=0 loops=89)

  • Index Cond: (ks.storetype = dickey)
  • Filter: (platstatus = 1)
26. 0.089 0.089 ↑ 1.0 1 89

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=89)

  • Index Cond: (ks.storelevel = dickey)
  • Filter: (platstatus = 1)
27. 0.089 0.089 ↑ 1.0 1 89

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=89)

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

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=89)

  • Index Cond: (ks.supplymode = dickey)
  • Filter: (platstatus = 1)
29. 2.196 5.073 ↓ 1.1 574 89

Materialize (cost=0.00..768.42 rows=535 width=100) (actual time=0.006..0.057 rows=574 loops=89)

30. 2.877 2.877 ↓ 1.1 580 1

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

  • Filter: ((orgtypeid = 6) AND (platstatus = 1))
  • Rows Removed by Filter: 12,694
31. 0.000 1.157 ↑ 1,319.0 1 89

Materialize (cost=763.79..910.22 rows=1,319 width=20) (actual time=0.013..0.013 rows=1 loops=89)

32. 0.000 1.166 ↑ 1,319.0 1 1

Seq Scan on kx_kq_storerepresentative _sp (cost=763.79..903.62 rows=1,319 width=20) (actual time=1.165..1.166 rows=1 loops=1)

  • Filter: (((platstatus = 1) AND (hashed SubPlan 1)) OR (representativeid = '1249573380248702976'::bigint))
  • Rows Removed by Filter: 4,603
33.          

SubPlan (for Seq Scan)

34. 0.000 1.176 ↓ 0.0 0 2

Nested Loop (cost=0.00..763.73 rows=26 width=8) (actual time=0.588..0.588 rows=0 loops=2)

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

Seq Scan on pl_parentmember (cost=0.00..350.02 rows=1 width=55) (actual time=0.588..0.588 rows=0 loops=2)

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

Seq Scan on pl_parentmember _b (cost=0.00..337.19 rows=5,101 width=63) (never executed)

  • Filter: (platstatus = 1)
37. 0.009 0.026 ↓ 48.0 48 1

Materialize (cost=0.00..2.60 rows=1 width=426) (actual time=0.011..0.026 rows=48 loops=1)

38. 0.017 0.017 ↓ 48.0 48 1

Seq Scan on kx_kq_ka ka (cost=0.00..2.60 rows=1 width=426) (actual time=0.009..0.017 rows=48 loops=1)

  • Filter: (platstatus = 1)
39. 0.006 0.006 ↑ 1.0 1 1

Index Scan using pl_orgstruct_pkey on pl_orgstruct co (cost=0.29..0.32 rows=1 width=21) (actual time=0.006..0.006 rows=1 loops=1)

  • Index Cond: (orgstructid = ks.createop)
  • Filter: (platstatus = 1)
40. 0.001 0.005 ↑ 1.0 1 1

Materialize (cost=0.00..1.02 rows=1 width=40) (actual time=0.005..0.005 rows=1 loops=1)

41. 0.004 0.004 ↑ 1.0 1 1

Seq Scan on kx_salearea (cost=0.00..1.01 rows=1 width=40) (actual time=0.004..0.004 rows=1 loops=1)

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

Materialize (cost=0.00..19.02 rows=4 width=40) (actual time=0.002..0.002 rows=0 loops=1)

43. 0.002 0.002 ↓ 0.0 0 1

Seq Scan on kx_store_autotags_result sar (cost=0.00..19.00 rows=4 width=40) (actual time=0.002..0.002 rows=0 loops=1)

  • Filter: (platstatus = 1)
44. 0.001 0.001 ↓ 0.0 0 1

Index Scan using pl_dictionary_pkey on pl_dictionary pd (cost=0.28..0.30 rows=1 width=24) (actual time=0.001..0.001 rows=0 loops=1)

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

Index Scan using ka_kq_channelcustomers_pkey on ka_kq_channelcustomers kkc (cost=0.28..0.31 rows=1 width=55) (actual time=0.001..0.001 rows=0 loops=1)

  • Index Cond: (id = ks.distributor)
  • Filter: (platstatus = 1)
Planning time : 62.459 ms
Execution time : 23.673 ms