explain.depesz.com

PostgreSQL's explain analyze made readable

Result: hWwH : 22

Settings
# exclusive inclusive rows x rows loops node
1. 2.700 85,284.459 ↓ 2.1 5,470 1

Limit (cost=53,507.09..26,421,026.91 rows=2,633 width=370) (actual time=520.600..85,284.459 rows=5,470 loops=1)

2. 860.992 85,281.759 ↓ 2.1 5,470 1

Result (cost=53,507.09..26,421,026.91 rows=2,633 width=370) (actual time=520.598..85,281.759 rows=5,470 loops=1)

3. 42.818 500.027 ↓ 2.1 5,470 1

Sort (cost=53,507.09..53,513.67 rows=2,633 width=210) (actual time=494.937..500.027 rows=5,470 loops=1)

  • Sort Key: vl.tn_linecode
  • Sort Method: quicksort Memory: 1,644kB
4. 2.650 457.209 ↓ 2.1 5,470 1

Hash Left Join (cost=5,282.17..53,357.50 rows=2,633 width=210) (actual time=257.365..457.209 rows=5,470 loops=1)

  • Hash Cond: (vl.tn_channeltype = ct.dictionaryid)
5. 4.178 454.236 ↓ 2.1 5,470 1

Nested Loop Left Join (cost=5,246.21..53,294.88 rows=2,633 width=190) (actual time=257.024..454.236 rows=5,470 loops=1)

6. 1.563 433.648 ↓ 2.1 5,470 1

Hash Left Join (cost=5,245.80..39,136.77 rows=2,633 width=118) (actual time=257.010..433.648 rows=5,470 loops=1)

  • Hash Cond: (vl.tn_alliance = cc.id)
7. 0.221 424.129 ↓ 2.1 5,470 1

Nested Loop Left Join (cost=4,034.95..37,919.01 rows=2,633 width=92) (actual time=248.841..424.129 rows=5,470 loops=1)

8. 2.378 407.498 ↓ 2.1 5,470 1

Nested Loop Left Join (cost=4,034.54..28,758.34 rows=2,633 width=87) (actual time=248.833..407.498 rows=5,470 loops=1)

9. 3.241 388.710 ↓ 2.1 5,470 1

Hash Join (cost=4,034.12..14,600.23 rows=2,633 width=66) (actual time=248.815..388.710 rows=5,470 loops=1)

  • Hash Cond: (vl.tn_saleareaid = pl_orgstruct.orgstructid)
10. 136.694 136.694 ↑ 1.0 5,470 1

Seq Scan on tn_visit_line vl (cost=0.00..10,519.10 rows=5,513 width=66) (actual time=0.009..136.694 rows=5,470 loops=1)

  • Filter: ((tn_status = 1) AND (platstatus = 1))
  • Rows Removed by Filter: 123
11. 38.231 248.775 ↓ 2.0 21,636 1

Hash (cost=3,899.02..3,899.02 rows=10,808 width=16) (actual time=248.775..248.775 rows=21,636 loops=1)

  • Buckets: 32,768 (originally 16384) Batches: 1 (originally 1) Memory Usage: 1,271kB
12. 5.936 119.900 ↓ 46.2 22,661 1

Recursive Union (cost=0.28..2,475.12 rows=491 width=8) (actual time=0.358..119.900 rows=22,661 loops=1)

13. 0.357 0.357 ↑ 1.0 1 1

Index Scan using ix_pl_saleorg_resp_memberid on pl_saleorg_resp _s (cost=0.28..8.30 rows=1 width=8) (actual time=0.356..0.357 rows=1 loops=1)

  • Index Cond: (memberid = '1254942620636024832'::bigint)
14. 19.219 113.607 ↓ 51.4 2,518 9

Nested Loop (cost=0.41..245.70 rows=49 width=8) (actual time=0.458..12.623 rows=2,518 loops=9)

15. 3.744 3.744 ↓ 251.8 2,518 9

WorkTable Scan on _r _r_1 (cost=0.00..0.20 rows=10 width=8) (actual time=0.000..0.416 rows=2,518 loops=9)

16. 90.644 90.644 ↑ 5.0 1 22,661

Index Scan using ix_pl_orgstruct_parentorgstructid on pl_orgstruct _a (cost=0.41..24.50 rows=5 width=24) (actual time=0.003..0.004 rows=1 loops=22,661)

  • Index Cond: (parentorgstructid = _r_1.orgstructid)
17. 90.644 90.644 ↑ 1.0 1 22,661

Index Scan using ix_pl_orgstruct_orgstructid on pl_orgstruct (cost=0.41..8.25 rows=1 width=8) (actual time=0.004..0.004 rows=1 loops=22,661)

  • Index Cond: (orgstructid = _r.orgstructid)
  • Filter: (platstatus = 1)
  • Rows Removed by Filter: 0
18. 16.410 16.410 ↑ 1.0 1 5,470

Index Scan using pl_orgstruct_pkey on pl_orgstruct m (cost=0.41..5.38 rows=1 width=29) (actual time=0.003..0.003 rows=1 loops=5,470)

  • Index Cond: (vl.tn_memberid = orgstructid)
  • Filter: (platstatus = 1)
  • Rows Removed by Filter: 0
19. 16.410 16.410 ↑ 1.0 1 5,470

Index Scan using pl_orgstruct_pkey on pl_orgstruct pt (cost=0.41..3.48 rows=1 width=21) (actual time=0.003..0.003 rows=1 loops=5,470)

  • Index Cond: (orgstructid = m.parentorgstructid)
  • Filter: (platstatus = 1)
  • Rows Removed by Filter: 0
20. 2.232 7.956 ↑ 1.6 13,304 1

Hash (cost=948.06..948.06 rows=21,023 width=34) (actual time=7.956..7.956 rows=13,304 loops=1)

  • Buckets: 32,768 Batches: 1 Memory Usage: 1,187kB
21. 5.724 5.724 ↑ 1.6 13,304 1

Seq Scan on ka_kq_channelcustomers cc (cost=0.00..948.06 rows=21,023 width=34) (actual time=0.012..5.724 rows=13,304 loops=1)

  • Filter: (platstatus = 1)
  • Rows Removed by Filter: 1,682
22. 16.410 16.410 ↑ 1.0 1 5,470

Index Scan using pl_orgstruct_pkey on pl_orgstruct sl (cost=0.41..5.38 rows=1 width=80) (actual time=0.003..0.003 rows=1 loops=5,470)

  • Index Cond: (vl.tn_saleareaid = orgstructid)
  • Filter: (platstatus = 1)
23. 0.088 0.323 ↑ 1.0 597 1

Hash (cost=28.50..28.50 rows=597 width=22) (actual time=0.323..0.323 rows=597 loops=1)

  • Buckets: 1,024 Batches: 1 Memory Usage: 42kB
24. 0.235 0.235 ↑ 1.0 597 1

Seq Scan on pl_dictionary ct (cost=0.00..28.50 rows=597 width=22) (actual time=0.010..0.235 rows=597 loops=1)

  • Filter: (platstatus = 1)
  • Rows Removed by Filter: 3
25.          

SubPlan (for Result)

26. 21.880 18,417.490 ↑ 1.0 1 5,470

Aggregate (cost=1,072.29..1,072.30 rows=1 width=8) (actual time=3.367..3.367 rows=1 loops=5,470)

27. 18,395.610 18,395.610 ↑ 1.7 3 5,470

Seq Scan on kx_visit_line a1 (cost=0.00..1,072.28 rows=5 width=0) (actual time=2.213..3.363 rows=3 loops=5,470)

  • Filter: ((userid = vl.tn_memberid) AND (platstatus = 1))
  • Rows Removed by Filter: 26,961
28. 21.880 20,682.070 ↑ 1.0 1 5,470

Aggregate (cost=1,207.70..1,207.71 rows=1 width=8) (actual time=3.781..3.781 rows=1 loops=5,470)

29. 20,660.190 20,660.190 ↑ 1.0 1 5,470

Seq Scan on kx_visit_line a1_1 (cost=0.00..1,207.70 rows=1 width=0) (actual time=3.311..3.777 rows=1 loops=5,470)

  • Filter: ((userid = vl.tn_memberid) AND (linestatus = 1) AND (tn_linetype = 1) AND (platstatus = 1))
  • Rows Removed by Filter: 26,963
30. 21.880 20,550.790 ↑ 1.0 1 5,470

Aggregate (cost=1,207.71..1,207.72 rows=1 width=8) (actual time=3.757..3.757 rows=1 loops=5,470)

31. 20,528.910 20,528.910 ↑ 2.0 2 5,470

Seq Scan on kx_visit_line a1_2 (cost=0.00..1,207.70 rows=4 width=0) (actual time=2.706..3.753 rows=2 loops=5,470)

  • Filter: ((userid = vl.tn_memberid) AND (linestatus = 1) AND (platstatus = 1) AND (tn_linetype = 2))
  • Rows Removed by Filter: 26,962
32. 366.490 16,798.370 ↑ 1.0 1 5,470

Aggregate (cost=3,262.22..3,262.23 rows=1 width=8) (actual time=3.071..3.071 rows=1 loops=5,470)

33. 831.906 16,431.880 ↑ 1.1 280 5,470

Nested Loop (cost=11.82..3,261.42 rows=319 width=8) (actual time=0.202..3.004 rows=280 loops=5,470)

34. 8,631.660 9,107.550 ↑ 1.2 297 5,470

Bitmap Heap Scan on tn_visit_linecustomer t (cost=11.39..1,410.59 rows=346 width=8) (actual time=0.137..1.665 rows=297 loops=5,470)

  • Recheck Cond: (tn_lineid = vl.tn_id)
  • Filter: (platstatus = 1)
  • Rows Removed by Filter: 29
  • Heap Blocks: exact=946,673
35. 475.890 475.890 ↑ 1.2 327 5,470

Bitmap Index Scan on tn_lineid (cost=0.00..11.31 rows=384 width=0) (actual time=0.087..0.087 rows=327 loops=5,470)

  • Index Cond: (tn_lineid = vl.tn_id)
36. 6,492.424 6,492.424 ↑ 1.0 1 1,623,106

Index Only Scan using idx_kx_kq_store_id_createtime on kx_kq_store s (cost=0.43..5.35 rows=1 width=8) (actual time=0.004..0.004 rows=1 loops=1,623,106)

  • Index Cond: (id = t.tn_storeid)
  • Heap Fetches: 1,218,373
37. 355.550 7,472.020 ↑ 1.0 1 5,470

Aggregate (cost=3,262.22..3,262.23 rows=1 width=8) (actual time=1.366..1.366 rows=1 loops=5,470)

38. 420.172 7,116.470 ↑ 1.1 280 5,470

Nested Loop (cost=11.82..3,261.42 rows=319 width=8) (actual time=0.082..1.301 rows=280 loops=5,470)

39. 1,668.350 1,826.980 ↑ 1.2 297 5,470

Bitmap Heap Scan on tn_visit_linecustomer t_1 (cost=11.39..1,410.59 rows=346 width=8) (actual time=0.049..0.334 rows=297 loops=5,470)

  • Recheck Cond: (tn_lineid = vl.tn_id)
  • Filter: (platstatus = 1)
  • Rows Removed by Filter: 29
  • Heap Blocks: exact=946,673
40. 158.630 158.630 ↑ 1.2 327 5,470

Bitmap Index Scan on tn_lineid (cost=0.00..11.31 rows=384 width=0) (actual time=0.029..0.029 rows=327 loops=5,470)

  • Index Cond: (tn_lineid = vl.tn_id)
41. 4,869.318 4,869.318 ↑ 1.0 1 1,623,106

Index Only Scan using idx_kx_kq_store_id_createtime on kx_kq_store ss (cost=0.43..5.35 rows=1 width=8) (actual time=0.003..0.003 rows=1 loops=1,623,106)

  • Index Cond: (id = t_1.tn_storeid)
  • Heap Fetches: 1,218,373
Planning time : 21.342 ms
Execution time : 85,289.361 ms