explain.depesz.com

PostgreSQL's explain analyze made readable

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

Settings

Optimization path:

# exclusive inclusive rows x rows loops node
1. 0.001 4,869.244 ↓ 0.0 0 1

Limit (cost=37,629.30..153,674.26 rows=113 width=64) (actual time=4,869.244..4,869.244 rows=0 loops=1)

2. 0.005 4,869.243 ↓ 0.0 0 1

Nested Loop (cost=37,629.30..153,674.26 rows=113 width=64) (actual time=4,869.243..4,869.243 rows=0 loops=1)

3. 0.042 4,869.223 ↑ 22.6 5 1

Merge Join (cost=37,628.88..153,531.86 rows=113 width=80) (actual time=4,783.712..4,869.223 rows=5 loops=1)

  • Merge Cond: (po.userinfoid = pu.userinfoid)
4. 0.008 4,868.593 ↑ 41.0 5 1

Nested Loop (cost=37,527.85..247,781.68 rows=205 width=79) (actual time=4,783.085..4,868.593 rows=5 loops=1)

5. 0.002 30.570 ↑ 1.0 1 1

Nested Loop (cost=8,042.20..8,478.07 rows=1 width=28) (actual time=29.940..30.570 rows=1 loops=1)

6. 0.954 0.954 ↑ 1.0 1 1

Index Scan using ix_pl_orgstruct_userinfoid on pl_orgstruct po (cost=0.28..423.61 rows=1 width=16) (actual time=0.330..0.954 rows=1 loops=1)

  • Filter: ((platstatus = 1) AND (orgstructid = '1141909626430820352'::bigint))
  • Rows Removed by Filter: 2,382
7. 0.002 29.614 ↑ 1.0 1 1

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

8. 0.016 29.595 ↑ 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.594..29.595 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))
9.          

SubPlan (for Index Scan)

10. 29.579 29.579 ↓ 0.0 0 1

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

  • Filter: ((tn_plandate = '2020-01-15'::date) AND (tn_memberid = '1141909626430820352'::bigint) AND (platstatus = 1))
  • Rows Removed by Filter: 237,223
11. 0.017 0.017 ↑ 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.013..0.017 rows=1 loops=1)

  • Filter: ((platstatus = 1) AND (tn_seq = 1) AND (tn_lineid = '1214167615757488128'::bigint))
  • Rows Removed by Filter: 92
12. 0.008 4,838.015 ↑ 41.0 5 1

Nested Loop (cost=29,485.64..239,301.56 rows=205 width=67) (actual time=4,753.140..4,838.015 rows=5 loops=1)

13. 37.220 4,837.987 ↑ 42.4 5 1

Hash Join (cost=29,485.36..239,237.75 rows=212 width=65) (actual time=4,753.126..4,837.987 rows=5 loops=1)

  • Hash Cond: ("*SELECT* 1".id = kvl.customerid)
14. 34.557 4,168.719 ↑ 1.0 383,581 1

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

15. 52.343 4,134.162 ↑ 1.0 383,581 1

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

16. 34.627 4,081.819 ↑ 1.0 383,581 1

Append (cost=199.96..198,410.22 rows=384,732 width=16) (actual time=1.284..4,081.819 rows=383,581 loops=1)

17. 49.690 3,963.723 ↑ 1.0 373,092 1

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

18. 2,616.723 3,914.033 ↑ 1.0 373,092 1

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

  • Hash Cond: (ks.storelevel = pd3.dickey)
19. 77.082 1,297.219 ↑ 1.0 373,092 1

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

  • Hash Cond: (ks.storetype = pd2.dickey)
20. 55.482 1,220.049 ↑ 1.0 373,092 1

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

  • Hash Cond: (ks.channeltype = pd1.dickey)
21. 80.866 1,164.461 ↑ 1.0 373,092 1

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

  • Hash Cond: (ks.seleareaid = ps.orgstructid)
22. 1,082.746 1,082.746 ↑ 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.020..1,082.746 rows=373,092 loops=1)

  • Filter: (status = 1)
  • Rows Removed by Filter: 49,236
23. 0.313 0.849 ↑ 1.0 2,383 1

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

  • Buckets: 4,096 Batches: 1 Memory Usage: 150kB
24. 0.536 0.536 ↑ 1.0 2,383 1

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

25. 0.047 0.106 ↑ 1.0 405 1

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

  • Buckets: 1,024 Batches: 1 Memory Usage: 24kB
26. 0.059 0.059 ↑ 1.0 405 1

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

27. 0.047 0.088 ↑ 1.0 405 1

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

  • Buckets: 1,024 Batches: 1 Memory Usage: 24kB
28. 0.041 0.041 ↑ 1.0 405 1

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

29. 0.050 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: 1,024 Batches: 1 Memory Usage: 24kB
30. 0.041 0.041 ↑ 1.0 405 1

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

31. 1.457 83.466 ↑ 1.0 10,489 1

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

32. 72.270 82.009 ↑ 1.0 10,489 1

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

  • Hash Cond: (kc_1.channelcustomersort = pd1_1.dickey)
33. 4.493 9.633 ↑ 1.0 10,489 1

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

  • Hash Cond: (kc_1.saleareaid = ps_1.orgstructid)
34. 4.301 4.301 ↑ 1.0 10,489 1

Seq Scan on ka_kq_channelcustomers kc_1 (cost=0.00..2,419.43 rows=10,494 width=24) (actual time=0.004..4.301 rows=10,489 loops=1)

  • Filter: (status = 1)
  • Rows Removed by Filter: 340
35. 0.308 0.839 ↑ 1.0 2,383 1

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

  • Buckets: 4,096 Batches: 1 Memory Usage: 150kB
36. 0.531 0.531 ↑ 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.531 rows=2,383 loops=1)

37. 0.048 0.106 ↑ 1.0 405 1

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

  • Buckets: 1,024 Batches: 1 Memory Usage: 24kB
38. 0.058 0.058 ↑ 1.0 405 1

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

39. 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)
40. 0.012 632.048 ↑ 1.2 5 1

Hash (cost=29,285.33..29,285.33 rows=6 width=49) (actual time=632.048..632.048 rows=5 loops=1)

  • Buckets: 1,024 Batches: 1 Memory Usage: 9kB
41. 0.023 632.036 ↑ 1.2 5 1

Nested Loop (cost=0.29..29,285.33 rows=6 width=49) (actual time=0.144..632.036 rows=5 loops=1)

42. 631.983 631.983 ↑ 15.6 5 1

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

  • Filter: ((platstatus = 1) AND (lineid = '1214167615757488128'::bigint))
  • Rows Removed by Filter: 654,431
43. 0.030 0.030 ↑ 1.0 1 5

Index Scan using ka_kq_channelcustomers_pkey on ka_kq_channelcustomers kc (cost=0.29..5.89 rows=1 width=8) (actual time=0.006..0.006 rows=1 loops=5)

  • Index Cond: (id = kvl.customerid)
  • Filter: (platstatus = 1)
44. 0.020 0.020 ↑ 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.004..0.004 rows=1 loops=5)

  • Index Cond: (orgstructid = "*SELECT* 1".saleareaid)
  • Filter: (platstatus = 1)
45. 0.306 0.588 ↑ 1.9 597 1

Sort (cost=101.03..103.81 rows=1,110 width=17) (actual time=0.563..0.588 rows=597 loops=1)

  • Sort Key: pu.userinfoid
  • Sort Method: quicksort Memory: 131kB
46. 0.282 0.282 ↑ 1.0 1,110 1

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

  • Filter: (platstatus = 1)
  • Rows Removed by Filter: 1
47. 0.015 0.015 ↓ 0.0 0 5

Index Scan using kx_kq_store_pkey on kx_kq_store kqs (cost=0.42..1.26 rows=1 width=8) (actual time=0.003..0.003 rows=0 loops=5)

  • Index Cond: (id = "*SELECT* 1".id)
  • Filter: (platstatus = 1)
Planning time : 16.380 ms
Execution time : 4,869.582 ms