explain.depesz.com

PostgreSQL's explain analyze made readable

Result: TCuY : Optimization for: Optimization for: plan #gKKW; plan #i55M

Settings

Optimization path:

Optimization(s) for this plan:

# exclusive inclusive rows x rows loops node
1. 0.002 5,402.076 ↑ 42.4 5 1

Limit (cost=255,447.29..255,447.82 rows=212 width=64) (actual time=5,402.075..5,402.076 rows=5 loops=1)

2. 0.022 5,402.074 ↑ 42.4 5 1

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

  • Sort Key: kvl.seq
  • Sort Method: quicksort Memory: 25kB
3. 0.013 5,402.052 ↑ 42.4 5 1

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

4. 24.403 5,401.989 ↑ 42.4 5 1

Hash Join (cost=220,835.22..255,375.29 rows=212 width=62) (actual time=5,265.838..5,401.989 rows=5 loops=1)

  • Hash Cond: (kvl.customerid = k.id)
5. 0.008 1,181.642 ↑ 15.6 5 1

Nested Loop (cost=8,042.20..36,947.85 rows=78 width=54) (actual time=29.869..1,181.642 rows=5 loops=1)

6. 0.030 29.733 ↑ 1.0 1 1

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

  • Join Filter: (po.userinfoid = pu.userinfoid)
  • Rows Removed by Join Filter: 560
7. 0.001 29.576 ↑ 1.0 1 1

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

  • Join Filter: (po.orgstructid = kv.userid)
8. 0.003 29.563 ↑ 1.0 1 1

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

9. 0.018 29.539 ↑ 1.0 1 1

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

  • 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. 29.521 29.521 ↓ 0.0 0 1

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

  • Filter: ((tn_plandate = '2020-01-15'::date) AND (tn_memberid = '1141909626430820352'::bigint) AND (platstatus = 1))
  • Rows Removed by Filter: 237223
12. 0.021 0.021 ↑ 1.0 1 1

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

  • Filter: ((tn_lineid = '1214167615757488128'::bigint) AND (platstatus = 1) AND (tn_seq = 1))
  • Rows Removed by Filter: 92
13. 0.012 0.012 ↑ 1.0 1 1

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

  • Index Cond: (orgstructid = '1141909626430820352'::bigint)
  • Filter: (platstatus = 1)
14. 0.127 0.127 ↑ 2.0 561 1

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

  • Filter: (platstatus = 1)
15. 1,151.901 1,151.901 ↑ 15.6 5 1

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

  • Filter: ((lineid = '1214167615757488128'::bigint) AND (platstatus = 1))
  • Rows Removed by Filter: 654431
16. 85.552 4,195.944 ↑ 1.0 383,581 1

Hash (cost=206,104.86..206,104.86 rows=384,732 width=16) (actual time=4,195.944..4,195.944 rows=383,581 loops=1)

  • Buckets: 131072 Batches: 8 Memory Usage: 3277kB
17. 55.248 4,110.392 ↑ 1.0 383,581 1

Subquery Scan on k (cost=199.96..206,104.86 rows=384,732 width=16) (actual time=1.247..4,110.392 rows=383,581 loops=1)

18. 37.147 4,055.144 ↑ 1.0 383,581 1

Limit (cost=199.96..202,257.54 rows=384,732 width=980) (actual time=1.247..4,055.144 rows=383,581 loops=1)

19. 51.433 4,017.997 ↑ 1.0 383,581 1

Result (cost=199.96..202,257.54 rows=384,732 width=980) (actual time=1.245..4,017.997 rows=383,581 loops=1)

20. 33.493 3,966.564 ↑ 1.0 383,581 1

Append (cost=199.96..198,410.22 rows=384,732 width=16) (actual time=1.245..3,966.564 rows=383,581 loops=1)

21. 45.904 3,848.592 ↑ 1.0 373,092 1

Subquery Scan on *SELECT* 1 (cost=199.96..192,920.37 rows=374,237 width=16) (actual time=1.244..3,848.592 rows=373,092 loops=1)

22. 2,649.726 3,802.688 ↑ 1.0 373,092 1

Hash Left Join (cost=199.96..189,178.00 rows=374,237 width=2,496) (actual time=1.243..3,802.688 rows=373,092 loops=1)

  • Hash Cond: (ks.storelevel = pd3.dickey)
23. 77.152 1,152.871 ↑ 1.0 373,092 1

Hash Left Join (cost=179.84..90,487.56 rows=374,237 width=102) (actual time=1.086..1,152.871 rows=373,092 loops=1)

  • Hash Cond: (ks.storetype = pd2.dickey)
24. 53.268 1,075.629 ↑ 1.0 373,092 1

Hash Left Join (cost=159.73..85,321.68 rows=374,237 width=110) (actual time=0.985..1,075.629 rows=373,092 loops=1)

  • Hash Cond: (ks.channeltype = pd1.dickey)
25. 80.398 1,022.258 ↑ 1.0 373,092 1

Hash Left Join (cost=139.62..83,898.17 rows=374,237 width=118) (actual time=0.865..1,022.258 rows=373,092 loops=1)

  • Hash Cond: (ks.seleareaid = ps.orgstructid)
26. 941.024 941.024 ↑ 1.0 373,092 1

Seq Scan on kx_kq_store ks (cost=0.00..82,774.44 rows=374,237 width=40) (actual time=0.009..941.024 rows=373,092 loops=1)

  • Filter: (status = 1)
  • Rows Removed by Filter: 49236
27. 0.298 0.836 ↑ 1.0 2,383 1

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

  • Buckets: 4096 Batches: 1 Memory Usage: 150kB
28. 0.538 0.538 ↑ 1.0 2,383 1

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

29. 0.045 0.103 ↑ 1.0 405 1

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

  • Buckets: 1024 Batches: 1 Memory Usage: 24kB
30. 0.058 0.058 ↑ 1.0 405 1

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

31. 0.048 0.090 ↑ 1.0 405 1

Hash (cost=15.05..15.05 rows=405 width=8) (actual time=0.089..0.090 rows=405 loops=1)

  • Buckets: 1024 Batches: 1 Memory Usage: 24kB
32. 0.042 0.042 ↑ 1.0 405 1

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

33. 0.049 0.091 ↑ 1.0 405 1

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

  • Buckets: 1024 Batches: 1 Memory Usage: 24kB
34. 0.042 0.042 ↑ 1.0 405 1

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

35. 1.367 84.476 ↑ 1.0 10,489 1

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

36. 73.158 83.109 ↑ 1.0 10,489 1

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

  • Hash Cond: (kc.channelcustomersort = pd1_1.dickey)
37. 4.456 9.841 ↑ 1.0 10,489 1

Hash Left Join (cost=139.62..2,586.63 rows=10,494 width=102) (actual time=0.873..9.841 rows=10,489 loops=1)

  • Hash Cond: (kc.saleareaid = ps_1.orgstructid)
38. 4.541 4.541 ↑ 1.0 10,489 1

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

  • Filter: (status = 1)
  • Rows Removed by Filter: 340
39. 0.311 0.844 ↑ 1.0 2,383 1

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

  • Buckets: 4096 Batches: 1 Memory Usage: 150kB
40. 0.533 0.533 ↑ 1.0 2,383 1

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

41. 0.050 0.110 ↑ 1.0 405 1

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

  • Buckets: 1024 Batches: 1 Memory Usage: 24kB
42. 0.060 0.060 ↑ 1.0 405 1

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

43. 0.003 0.003 ↓ 0.0 0 1

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

  • Filter: (status = 1)
44. 0.050 0.050 ↑ 1.0 1 5

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

  • Index Cond: (orgstructid = k.saleareaid)
  • Filter: (platstatus = 1)
Planning time : 1.985 ms
Execution time : 5,402.249 ms