explain.depesz.com

PostgreSQL's explain analyze made readable

Result: i55M : Optimization for: plan #gKKW

Settings

Optimization path:

Optimization(s) for this plan:

# exclusive inclusive rows x rows loops node
1. 0.000 0.000 ↓ 0.0

Limit (cost=255,447.29..255,447.82 rows=212 width=64) (actual rows= loops=)

2. 0.000 0.000 ↓ 0.0

Sort (cost=255,447.29..255,447.82 rows=212 width=64) (actual rows= loops=)

  • Sort Key: kvl.seq
3. 0.000 0.000 ↓ 0.0

Nested Loop Left Join (cost=220,835.50..255,439.10 rows=212 width=64) (actual rows= loops=)

4. 0.000 0.000 ↓ 0.0

Hash Join (cost=220,835.22..255,375.29 rows=212 width=62) (actual rows= loops=)

  • Hash Cond: (kvl.customerid = k.id)
5. 0.000 0.000 ↓ 0.0

Nested Loop (cost=8,042.20..36,947.85 rows=78 width=54) (actual rows= loops=)

6. 0.000 0.000 ↓ 0.0

Nested Loop Left Join (cost=8,042.20..8,121.52 rows=1 width=29) (actual rows= loops=)

  • Join Filter: (po.userinfoid = pu.userinfoid)
7. 0.000 0.000 ↓ 0.0

Nested Loop Left Join (cost=8,042.20..8,062.76 rows=1 width=28) (actual rows= loops=)

  • Join Filter: (po.orgstructid = kv.userid)
8. 0.000 0.000 ↓ 0.0

Nested Loop (cost=8,041.93..8,054.45 rows=1 width=20) (actual rows= loops=)

9. 0.000 0.000 ↓ 0.0

Index Scan using kx_visit_line_pkey on kx_visit_line kv (cost=8,041.93..8,049.95 rows=1 width=20) (actual rows= loops=)

  • Index Cond: (lineid = '1214167615757488128'::bigint)
  • Filter: ((NOT (hashed SubPlan 1)) AND (tn_linetype = 1) AND (linestatus = 1) AND (platstatus = 1) AND (userid = '1141909626430820352'::bigint))
10.          

SubPlan (for Index Scan)

11. 0.000 0.000 ↓ 0.0

Seq Scan on tn_kx_visit_matual (cost=0.00..8,041.64 rows=1 width=8) (actual rows= loops=)

  • Filter: ((tn_plandate = '2020-01-15'::date) AND (tn_memberid = '1141909626430820352'::bigint) AND (platstatus = 1))
12. 0.000 0.000 ↓ 0.0

Seq Scan on tn_kx_visit_plan_line kvt (cost=0.00..4.49 rows=1 width=8) (actual rows= loops=)

  • Filter: ((tn_lineid = '1214167615757488128'::bigint) AND (platstatus = 1) AND (tn_seq = 1))
13. 0.000 0.000 ↓ 0.0

Index Scan using ix_pl_orgstruct_orgstructid on pl_orgstruct po (cost=0.28..8.30 rows=1 width=16) (actual rows= loops=)

  • Index Cond: (orgstructid = '1141909626430820352'::bigint)
  • Filter: (platstatus = 1)
14. 0.000 0.000 ↓ 0.0

Seq Scan on pl_userinfo pu (cost=0.00..44.89 rows=1,110 width=17) (actual rows= loops=)

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

Seq Scan on kx_visit_linecustomer kvl (cost=0.00..28,825.54 rows=78 width=41) (actual rows= loops=)

  • Filter: ((lineid = '1214167615757488128'::bigint) AND (platstatus = 1))
16. 0.000 0.000 ↓ 0.0

Hash (cost=206,104.86..206,104.86 rows=384,732 width=16) (actual rows= loops=)

17. 0.000 0.000 ↓ 0.0

Subquery Scan on k (cost=199.96..206,104.86 rows=384,732 width=16) (actual rows= loops=)

18. 0.000 0.000 ↓ 0.0

Limit (cost=199.96..202,257.54 rows=384,732 width=980) (actual rows= loops=)

19. 0.000 0.000 ↓ 0.0

Result (cost=199.96..202,257.54 rows=384,732 width=980) (actual rows= loops=)

20. 0.000 0.000 ↓ 0.0

Append (cost=199.96..198,410.22 rows=384,732 width=16) (actual rows= loops=)

21. 0.000 0.000 ↓ 0.0

Subquery Scan on *SELECT* 1 (cost=199.96..192,920.37 rows=374,237 width=16) (actual rows= loops=)

22. 0.000 0.000 ↓ 0.0

Hash Left Join (cost=199.96..189,178.00 rows=374,237 width=2,496) (actual rows= loops=)

  • Hash Cond: (ks.storelevel = pd3.dickey)
23. 0.000 0.000 ↓ 0.0

Hash Left Join (cost=179.84..90,487.56 rows=374,237 width=102) (actual rows= loops=)

  • Hash Cond: (ks.storetype = pd2.dickey)
24. 0.000 0.000 ↓ 0.0

Hash Left Join (cost=159.73..85,321.68 rows=374,237 width=110) (actual rows= loops=)

  • Hash Cond: (ks.channeltype = pd1.dickey)
25. 0.000 0.000 ↓ 0.0

Hash Left Join (cost=139.62..83,898.17 rows=374,237 width=118) (actual rows= loops=)

  • Hash Cond: (ks.seleareaid = ps.orgstructid)
26. 0.000 0.000 ↓ 0.0

Seq Scan on kx_kq_store ks (cost=0.00..82,774.44 rows=374,237 width=40) (actual rows= loops=)

  • Filter: (status = 1)
27. 0.000 0.000 ↓ 0.0

Hash (cost=109.83..109.83 rows=2,383 width=86) (actual rows= loops=)

28. 0.000 0.000 ↓ 0.0

Seq Scan on pl_orgstruct ps (cost=0.00..109.83 rows=2,383 width=86) (actual rows= loops=)

29. 0.000 0.000 ↓ 0.0

Hash (cost=15.05..15.05 rows=405 width=8) (actual rows= loops=)

30. 0.000 0.000 ↓ 0.0

Seq Scan on pl_dictionary pd1 (cost=0.00..15.05 rows=405 width=8) (actual rows= loops=)

31. 0.000 0.000 ↓ 0.0

Hash (cost=15.05..15.05 rows=405 width=8) (actual rows= loops=)

32. 0.000 0.000 ↓ 0.0

Seq Scan on pl_dictionary pd2 (cost=0.00..15.05 rows=405 width=8) (actual rows= loops=)

33. 0.000 0.000 ↓ 0.0

Hash (cost=15.05..15.05 rows=405 width=8) (actual rows= loops=)

34. 0.000 0.000 ↓ 0.0

Seq Scan on pl_dictionary pd3 (cost=0.00..15.05 rows=405 width=8) (actual rows= loops=)

35. 0.000 0.000 ↓ 0.0

Subquery Scan on *SELECT* 2 (cost=159.73..5,479.48 rows=10,494 width=16) (actual rows= loops=)

36. 0.000 0.000 ↓ 0.0

Hash Left Join (cost=159.73..5,374.54 rows=10,494 width=2,496) (actual rows= loops=)

  • Hash Cond: (kc.channelcustomersort = pd1_1.dickey)
37. 0.000 0.000 ↓ 0.0

Hash Left Join (cost=139.62..2,586.63 rows=10,494 width=102) (actual rows= loops=)

  • Hash Cond: (kc.saleareaid = ps_1.orgstructid)
38. 0.000 0.000 ↓ 0.0

Seq Scan on ka_kq_channelcustomers kc (cost=0.00..2,419.43 rows=10,494 width=24) (actual rows= loops=)

  • Filter: (status = 1)
39. 0.000 0.000 ↓ 0.0

Hash (cost=109.83..109.83 rows=2,383 width=86) (actual rows= loops=)

40. 0.000 0.000 ↓ 0.0

Seq Scan on pl_orgstruct ps_1 (cost=0.00..109.83 rows=2,383 width=86) (actual rows= loops=)

41. 0.000 0.000 ↓ 0.0

Hash (cost=15.05..15.05 rows=405 width=8) (actual rows= loops=)

42. 0.000 0.000 ↓ 0.0

Seq Scan on pl_dictionary pd1_1 (cost=0.00..15.05 rows=405 width=8) (actual rows= loops=)

43. 0.000 0.000 ↓ 0.0

Seq Scan on kx_kq_ka ka (cost=0.00..10.38 rows=1 width=16) (actual rows= loops=)

  • Filter: (status = 1)
44. 0.000 0.000 ↓ 0.0

Index Scan using ix_pl_orgstruct_orgstructid on pl_orgstruct pl (cost=0.28..0.30 rows=1 width=18) (actual rows= loops=)

  • Index Cond: (orgstructid = k.saleareaid)
  • Filter: (platstatus = 1)