explain.depesz.com

PostgreSQL's explain analyze made readable

Result: x68M : Optimization for: plan #JN95

Settings

Optimization path:

# exclusive inclusive rows x rows loops node
1. 0.002 10,330.544 ↓ 4.0 8 1

Limit (cost=510,302.14..510,302.14 rows=2 width=1,506) (actual time=10,330.542..10,330.544 rows=8 loops=1)

2. 0.035 10,330.542 ↓ 4.0 8 1

Sort (cost=510,302.14..510,302.14 rows=2 width=1,506) (actual time=10,330.541..10,330.542 rows=8 loops=1)

  • Sort Key: ((ml.seq + 0)), ml.customername
  • Sort Method: quicksort Memory: 29kB
3. 0.004 10,330.507 ↓ 4.0 8 1

Subquery Scan on ml (cost=510,301.99..510,302.13 rows=2 width=1,506) (actual time=10,330.499..10,330.507 rows=8 loops=1)

4. 0.006 10,330.503 ↓ 4.0 8 1

Unique (cost=510,301.99..510,302.10 rows=2 width=1,498) (actual time=10,330.497..10,330.503 rows=8 loops=1)

5. 0.032 10,330.497 ↓ 4.0 8 1

Sort (cost=510,301.99..510,301.99 rows=2 width=1,498) (actual time=10,330.496..10,330.497 rows=8 loops=1)

  • Sort Key: tn_source, id, customername, (CASE WHEN (customertype = '905324761813487616'::bigint) THEN contactname WHEN (customertype = '905324680615956480'::bigint) THEN contactname ELSE NULL::character varying END), ((to_date(to_char((CURRENT_DATE)::timestamp with time zone, 'yyyy-mm-dd'::text), 'yyyy-mm-dd'::text) - to_date(to_char(recentvisittime, 'yyyy-mm-dd'::text), 'yyyy-mm-dd'::text))), tn_times, (CASE WHEN (number IS NULL) THEN '0'::bigint ELSE number END), store_channeltype, store_channeltype, storelevelname, address, status, plandate, userid, userinfoname, customertype, seq, actualvisittime, visittype, status, isplan, tn_isdistributor
  • Sort Method: quicksort Memory: 29kB
6. 0.002 10,330.465 ↓ 4.0 8 1

Append (cost=0.00..510,301.98 rows=2 width=1,498) (actual time=9,852.089..10,330.465 rows=8 loops=1)

7. 0.001 0.001 ↓ 0.0 0 1

Result (cost=0.00..0.00 rows=0 width=673) (actual time=0.001..0.001 rows=0 loops=1)

  • One-Time Filter: false
8. 0.005 10,330.462 ↓ 8.0 8 1

Subquery Scan on *SELECT* 2 (cost=496,848.73..510,301.98 rows=1 width=673) (actual time=9,852.088..10,330.462 rows=8 loops=1)

9. 0.050 10,330.457 ↓ 8.0 8 1

Nested Loop Anti Join (cost=496,848.73..510,301.97 rows=1 width=669) (actual time=9,852.086..10,330.457 rows=8 loops=1)

  • Join Filter: ((kx_visit_planvisit.userid = p.userid) AND (kx_visit_planvisit.customerid = p.customerid))
10. 0.012 10,327.511 ↓ 8.0 8 1

Nested Loop Left Join (cost=496,848.44..510,293.61 rows=1 width=617) (actual time=9,851.643..10,327.511 rows=8 loops=1)

11. 0.011 10,327.499 ↓ 8.0 8 1

Nested Loop Left Join (cost=496,848.16..510,293.32 rows=1 width=617) (actual time=9,851.640..10,327.499 rows=8 loops=1)

12. 0.013 10,327.456 ↓ 8.0 8 1

Nested Loop Left Join (cost=496,847.88..510,285.00 rows=1 width=616) (actual time=9,851.626..10,327.456 rows=8 loops=1)

13. 0.013 10,327.411 ↓ 8.0 8 1

Nested Loop Left Join (cost=496,847.60..510,278.41 rows=1 width=608) (actual time=9,851.613..10,327.411 rows=8 loops=1)

14. 0.021 10,327.342 ↓ 8.0 8 1

Nested Loop Left Join (cost=496,847.18..510,250.07 rows=1 width=600) (actual time=9,851.596..10,327.342 rows=8 loops=1)

15. 9.954 10,327.265 ↓ 8.0 8 1

Merge Left Join (cost=496,846.89..510,242.53 rows=1 width=588) (actual time=9,851.574..10,327.265 rows=8 loops=1)

  • Merge Cond: (ks.id = kvw.customerid)
16. 0.016 56.434 ↓ 8.0 8 1

Sort (cost=5,141.05..5,141.05 rows=1 width=580) (actual time=56.432..56.434 rows=8 loops=1)

  • Sort Key: p.customerid
  • Sort Method: quicksort Memory: 28kB
17. 0.011 56.418 ↓ 8.0 8 1

Nested Loop (cost=356.24..5,141.04 rows=1 width=580) (actual time=56.372..56.418 rows=8 loops=1)

18. 14.178 56.351 ↑ 2.1 8 1

Hash Right Join (cost=355.81..4,659.52 rows=17 width=542) (actual time=56.346..56.351 rows=8 loops=1)

  • Hash Cond: ((tvd.tn_leveldevision = ks.tn_storedevicion) AND (tvd.tn_storelevel = ks.storelevel) AND (tvd.tn_area = ks.saleareaid))
19. 41.574 41.574 ↑ 1.0 93,636 1

Seq Scan on tn_visit_detail tvd (cost=0.00..3,249.90 rows=93,668 width=32) (actual time=0.008..41.574 rows=93,636 loops=1)

  • Filter: ((tn_start <= '2019-12-02 16:15:24'::timestamp without time zone) AND (tn_end >= '2019-12-02 16:15:24'::timestamp without time zone) AND (platstatus = 1))
  • Rows Removed by Filter: 2244
20. 0.009 0.599 ↑ 2.1 8 1

Hash (cost=355.51..355.51 rows=17 width=558) (actual time=0.599..0.599 rows=8 loops=1)

  • Buckets: 1024 Batches: 1 Memory Usage: 11kB
21. 0.003 0.590 ↑ 2.1 8 1

Subquery Scan on ks (cost=98.81..355.51 rows=17 width=558) (actual time=0.409..0.590 rows=8 loops=1)

22. 0.003 0.587 ↑ 2.1 8 1

Append (cost=98.81..355.34 rows=17 width=1,586) (actual time=0.408..0.587 rows=8 loops=1)

23. 0.000 0.054 ↓ 0.0 0 1

Nested Loop Left Join (cost=98.81..188.82 rows=8 width=443) (actual time=0.054..0.054 rows=0 loops=1)

24. 0.004 0.054 ↓ 0.0 0 1

Hash Right Join (cost=98.53..124.43 rows=8 width=331) (actual time=0.054..0.054 rows=0 loops=1)

  • Hash Cond: (pd2.dickey = ks_1.storetype)
25. 0.000 0.000 ↓ 0.0 0

Seq Scan on pl_dictionary pd2 (cost=0.00..20.92 rows=392 width=20) (never executed)

26. 0.000 0.050 ↓ 0.0 0 1

Hash (cost=98.43..98.43 rows=8 width=327) (actual time=0.050..0.050 rows=0 loops=1)

  • Buckets: 1024 Batches: 1 Memory Usage: 8kB
27. 0.004 0.050 ↓ 0.0 0 1

Hash Right Join (cost=76.03..98.43 rows=8 width=327) (actual time=0.049..0.050 rows=0 loops=1)

  • Hash Cond: (pd1.dickey = ks_1.channeltype)
28. 0.000 0.000 ↓ 0.0 0

Seq Scan on pl_dictionary pd1 (cost=0.00..20.92 rows=392 width=20) (never executed)

29. 0.001 0.046 ↓ 0.0 0 1

Hash (cost=75.93..75.93 rows=8 width=315) (actual time=0.045..0.046 rows=0 loops=1)

  • Buckets: 1024 Batches: 1 Memory Usage: 8kB
30. 0.002 0.045 ↓ 0.0 0 1

Merge Right Join (cost=75.57..75.93 rows=8 width=315) (actual time=0.045..0.045 rows=0 loops=1)

  • Merge Cond: (pd3.dickey = ks_1.storelevel)
31. 0.015 0.015 ↑ 392.0 1 1

Index Scan using idx_pl_dictionary_dickey on pl_dictionary pd3 (cost=0.15..46.93 rows=392 width=20) (actual time=0.015..0.015 rows=1 loops=1)

32. 0.003 0.028 ↓ 0.0 0 1

Sort (cost=66.77..66.79 rows=8 width=303) (actual time=0.028..0.028 rows=0 loops=1)

  • Sort Key: ks_1.storelevel
  • Sort Method: quicksort Memory: 25kB
33. 0.025 0.025 ↓ 0.0 0 1

Index Scan using kx_kq_store_pkey on kx_kq_store ks_1 (cost=0.42..66.65 rows=8 width=303) (actual time=0.025..0.025 rows=0 loops=1)

  • Index Cond: (id = ANY ('{1177391284491522408,1143488581524919345,1143488581524919343,1177391284491522491,1143522017862094940,1177391284491522511,1177391284491522530,1143522017862095204}'::bigint[]))
  • Filter: ((platstatus = 1) AND ((customertype = '905324680615956480'::bigint) OR (customertype = '905324761813487616'::bigint)))
34. 0.000 0.000 ↓ 0.0 0

Index Scan using ix_pl_orgstruct_orgstructid on pl_orgstruct ps (cost=0.28..7.80 rows=1 width=84) (never executed)

  • Index Cond: (ks_1.seleareaid = orgstructid)
35. 0.194 0.527 ↑ 1.0 8 1

Hash Left Join (cost=60.44..155.53 rows=8 width=497) (actual time=0.353..0.527 rows=8 loops=1)

  • Hash Cond: (kc_1.channelcustomersort = pd1_1.dickey)
36. 0.026 0.091 ↑ 1.0 8 1

Nested Loop Left Join (cost=34.62..127.60 rows=8 width=331) (actual time=0.036..0.091 rows=8 loops=1)

37. 0.028 0.049 ↑ 1.0 8 1

Bitmap Heap Scan on ka_kq_channelcustomers kc_1 (cost=34.34..65.21 rows=8 width=255) (actual time=0.027..0.049 rows=8 loops=1)

  • Recheck Cond: (id = ANY ('{1177391284491522408,1143488581524919345,1143488581524919343,1177391284491522491,1143522017862094940,1177391284491522511,1177391284491522530,1143522017862095204}'::bigint[]))
  • Filter: ((platstatus = 1) AND ((customertype = '905324680615956480'::bigint) OR (customertype = '905324761813487616'::bigint)))
  • Heap Blocks: exact=8
38. 0.021 0.021 ↑ 1.0 8 1

Bitmap Index Scan on ka_kq_channelcustomers_pkey (cost=0.00..34.34 rows=8 width=0) (actual time=0.021..0.021 rows=8 loops=1)

  • Index Cond: (id = ANY ('{1177391284491522408,1143488581524919345,1143488581524919343,1177391284491522491,1143522017862094940,1177391284491522511,1177391284491522530,1143522017862095204}'::bigint[]))
39. 0.016 0.016 ↑ 1.0 1 8

Index Scan using ix_pl_orgstruct_orgstructid on pl_orgstruct ps_1 (cost=0.28..7.80 rows=1 width=84) (actual time=0.002..0.002 rows=1 loops=8)

  • Index Cond: (kc_1.saleareaid = orgstructid)
40. 0.090 0.242 ↓ 1.0 395 1

Hash (cost=20.92..20.92 rows=392 width=20) (actual time=0.242..0.242 rows=395 loops=1)

  • Buckets: 1024 Batches: 1 Memory Usage: 30kB
41. 0.152 0.152 ↓ 1.0 395 1

Seq Scan on pl_dictionary pd1_1 (cost=0.00..20.92 rows=392 width=20) (actual time=0.007..0.152 rows=395 loops=1)

42. 0.001 0.003 ↓ 0.0 0 1

Subquery Scan on *SELECT* 3 (cost=0.00..10.84 rows=1 width=1,972) (actual time=0.003..0.003 rows=0 loops=1)

43. 0.002 0.002 ↓ 0.0 0 1

Seq Scan on kx_kq_ka ka (cost=0.00..10.82 rows=1 width=1,968) (actual time=0.002..0.002 rows=0 loops=1)

  • Filter: ((platstatus = 1) AND ((customertype = '905324680615956480'::bigint) OR (customertype = '905324761813487616'::bigint)) AND (id = ANY ('{1177391284491522408,1143488581524919345,1143488581524919343,1177391284491522491,1143522017862094940,1177391284491522511,1177391284491522530,1143522017862095204}'::bigint[])))
44. 0.056 0.056 ↑ 1.0 1 8

Index Scan using idx_kx_visit_actual_customerid on kx_visit_actual p (cost=0.42..28.31 rows=1 width=38) (actual time=0.007..0.007 rows=1 loops=8)

  • Index Cond: (customerid = ks.id)
  • Filter: ((actualvisitdate >= '2019-11-28'::date) AND (actualvisitdate <= '2019-11-28'::date) AND (platstatus = 1) AND (userid = '1143705983973986304'::bigint))
45. 72.338 10,260.877 ↓ 808.6 161,711 1

GroupAggregate (cost=491,705.85..505,098.97 rows=200 width=16) (actual time=9,795.135..10,260.877 rows=161,711 loops=1)

  • Group Key: kvw.customerid
46. 232.180 10,188.539 ↑ 1.1 302,243 1

GroupAggregate (cost=491,705.85..500,075.30 rows=334,778 width=36) (actual time=9,795.128..10,188.539 rows=302,243 loops=1)

  • Group Key: kvw.customerid, kvw.recorddate, kvd2.tn_start, kvd2.tn_end
47. 856.099 9,956.359 ↓ 3.3 1,110,042 1

Sort (cost=491,705.85..492,542.79 rows=334,778 width=28) (actual time=9,795.120..9,956.359 rows=1,110,042 loops=1)

  • Sort Key: kvw.customerid, kvw.recorddate, kvd2.tn_start, kvd2.tn_end
  • Sort Method: external merge Disk: 50168kB
48. 604.228 9,100.260 ↓ 3.6 1,219,416 1

Hash Join (cost=426,846.20..452,973.70 rows=334,778 width=28) (actual time=8,328.279..9,100.260 rows=1,219,416 loops=1)

  • Hash Cond: (ste.storeid = kst.id)
49. 168.434 168.434 ↑ 1.0 413,707 1

Seq Scan on kx_kq_storerepresentative ste (cost=0.00..15,373.20 rows=418,540 width=8) (actual time=0.013..168.434 rows=413,707 loops=1)

  • Filter: (platstatus = 1)
  • Rows Removed by Filter: 112488
50. 478.373 8,327.598 ↓ 3.7 1,207,779 1

Hash (cost=420,173.38..420,173.38 rows=328,465 width=36) (actual time=8,327.598..8,327.598 rows=1,207,779 loops=1)

  • Buckets: 65536 (originally 65536) Batches: 32 (originally 8) Memory Usage: 3585kB
51. 2,683.540 7,849.225 ↓ 3.7 1,207,779 1

Hash Join (cost=217,468.11..420,173.38 rows=328,465 width=36) (actual time=3,163.290..7,849.225 rows=1,207,779 loops=1)

  • Hash Cond: (kvw.customerid = kst.id)
  • Join Filter: ((kvd2.tn_start <= kvw.recorddate) AND (kvd2.tn_end >= kvw.recorddate))
  • Rows Removed by Join Filter: 1891852
52. 2,002.698 2,002.698 ↑ 1.0 3,322,228 1

Seq Scan on kx_visit_workrecord kvw (cost=0.00..110,669.51 rows=3,372,521 width=12) (actual time=0.008..2,002.698 rows=3,322,228 loops=1)

  • Filter: (platstatus = 1)
53. 114.093 3,162.987 ↑ 1.0 342,998 1

Hash (cost=210,858.69..210,858.69 rows=359,954 width=24) (actual time=3,162.987..3,162.987 rows=342,998 loops=1)

  • Buckets: 65536 Batches: 8 Memory Usage: 2862kB
54. 131.168 3,048.894 ↑ 1.0 342,998 1

Merge Join (cost=201,515.04..210,858.69 rows=359,954 width=24) (actual time=2,707.318..3,048.894 rows=342,998 loops=1)

  • Merge Cond: ((kvd2.tn_leveldevision = kst.tn_storedevicion) AND (kvd2.tn_area = kst.seleareaid) AND (kvd2.tn_storelevel = kst.storelevel))
55. 434.881 541.230 ↑ 1.0 93,636 1

Sort (cost=13,786.36..14,020.53 rows=93,668 width=40) (actual time=521.615..541.230 rows=93,636 loops=1)

  • Sort Key: kvd2.tn_leveldevision, kvd2.tn_area, kvd2.tn_storelevel
  • Sort Method: external merge Disk: 4584kB
56. 106.349 106.349 ↑ 1.0 93,636 1

Seq Scan on tn_visit_detail kvd2 (cost=0.00..3,489.60 rows=93,668 width=40) (actual time=0.012..106.349 rows=93,636 loops=1)

  • Filter: ((tn_end >= CURRENT_DATE) AND (tn_start <= CURRENT_DATE))
  • Rows Removed by Filter: 2244
57. 62.591 2,376.496 ↑ 1.1 390,593 1

Materialize (cost=187,671.69..189,723.97 rows=410,456 width=32) (actual time=2,183.710..2,376.496 rows=390,593 loops=1)

58. 1,034.209 2,313.905 ↑ 1.1 390,593 1

Sort (cost=187,671.69..188,697.83 rows=410,456 width=32) (actual time=2,183.705..2,313.905 rows=390,593 loops=1)

  • Sort Key: kst.tn_storedevicion, kst.seleareaid, kst.storelevel
  • Sort Method: external merge Disk: 16888kB
59. 1,279.696 1,279.696 ↓ 1.0 410,868 1

Seq Scan on kx_kq_store kst (cost=0.00..139,582.10 rows=410,456 width=32) (actual time=0.014..1,279.696 rows=410,868 loops=1)

  • Filter: (platstatus = 1)
  • Rows Removed by Filter: 371
60. 0.056 0.056 ↑ 1.0 1 8

Index Scan using ka_kq_channelcustomers_pkey on ka_kq_channelcustomers kc (cost=0.29..7.54 rows=1 width=20) (actual time=0.007..0.007 rows=1 loops=8)

  • Index Cond: (id = p.customerid)
  • Filter: (platstatus = 1)
61. 0.056 0.056 ↑ 1.0 1 8

Index Scan using idx_kx_visit_customerstatus_customerid on kx_visit_customerstatus vc (cost=0.42..28.32 rows=1 width=24) (actual time=0.007..0.007 rows=1 loops=8)

  • Index Cond: (customerid = p.customerid)
  • Filter: ((visitdate >= '2019-11-28'::date) AND (visitdate <= '2019-11-28'::date) AND (userid = '1143705983973986304'::bigint) AND (platstatus = 1) AND (userid = p.userid))
62. 0.032 0.032 ↑ 1.0 1 8

Index Scan using ix_pl_orgstruct_orgstructid on pl_orgstruct po (cost=0.28..6.59 rows=1 width=16) (actual time=0.004..0.004 rows=1 loops=8)

  • Index Cond: ((orgstructid = p.userid) AND (orgstructid = '1143705983973986304'::bigint))
  • Filter: (platstatus = 1)
63. 0.032 0.032 ↑ 1.0 1 8

Index Scan using pl_userinfo_pkey on pl_userinfo pu (cost=0.28..8.30 rows=1 width=17) (actual time=0.004..0.004 rows=1 loops=8)

  • Index Cond: (po.userinfoid = userinfoid)
  • Filter: (platstatus = 1)
64. 0.000 0.000 ↓ 0.0 0 8

Index Scan using pk_tn_visit_administration_1 on tn_visit_administration tva (cost=0.28..0.30 rows=1 width=16) (actual time=0.000..0.000 rows=0 loops=8)

  • Index Cond: (tn_id = tvd.tn_adminid)
  • Filter: (platstatus = 1)
65. 2.896 2.896 ↓ 0.0 0 8

Index Scan using idx_kx_visit_planvisit_plandate on kx_visit_planvisit (cost=0.29..8.31 rows=1 width=16) (actual time=0.362..0.362 rows=0 loops=8)

  • Index Cond: ((plandate >= '2019-11-28'::date) AND (plandate <= '2019-11-28'::date))
  • Filter: ((userid = '1143705983973986304'::bigint) AND (platstatus = 1))
  • Rows Removed by Filter: 1964
Planning time : 22.183 ms
Execution time : 10,351.462 ms