explain.depesz.com

PostgreSQL's explain analyze made readable

Result: wb69

Settings
# exclusive inclusive rows x rows loops node
1. 89.939 46,864.512 ↓ 14,396.0 14,396 1

Nested Loop Left Join (cost=16,791.72..17,981.98 rows=1 width=431) (actual time=181.649..46,864.512 rows=14,396 loops=1)

2. 11.298 1,801.469 ↓ 14,396.0 14,396 1

Nested Loop (cost=16,791.44..16,814.90 rows=1 width=343) (actual time=175.930..1,801.469 rows=14,396 loops=1)

3. 22.602 1,718.191 ↓ 14,396.0 14,396 1

Nested Loop Left Join (cost=16,791.30..16,814.67 rows=1 width=367) (actual time=175.910..1,718.191 rows=14,396 loops=1)

4. 19.257 1,609.213 ↓ 14,396.0 14,396 1

Nested Loop (cost=16,791.02..16,814.37 rows=1 width=183) (actual time=175.875..1,609.213 rows=14,396 loops=1)

5. 14.107 1,128.586 ↓ 15,379.0 15,379 1

Nested Loop (cost=16,790.60..16,808.97 rows=1 width=185) (actual time=175.819..1,128.586 rows=15,379 loops=1)

6. 17.017 1,052.963 ↓ 15,379.0 15,379 1

Nested Loop Left Join (cost=16,790.45..16,808.77 rows=1 width=113) (actual time=175.803..1,052.963 rows=15,379 loops=1)

  • Join Filter: ((kvor.detailid = od.id) AND (kvor.detailid = od.id))
  • Rows Removed by Join Filter: 40
7. 16.637 698.070 ↓ 15,358.0 15,358 1

Nested Loop (cost=16,789.47..16,804.86 rows=1 width=113) (actual time=175.713..698.070 rows=15,358 loops=1)

  • Join Filter: (r.costid = od.costid)
  • Rows Removed by Join Filter: 16
8. 33.705 543.229 ↓ 15,356.0 15,356 1

Nested Loop (cost=16,789.04..16,801.68 rows=1 width=61) (actual time=175.667..543.229 rows=15,356 loops=1)

9. 14.013 257.916 ↓ 17,972.0 17,972 1

Subquery Scan on r (cost=16,788.62..16,793.23 rows=1 width=16) (actual time=171.902..257.916 rows=17,972 loops=1)

  • Filter: ((r.rownumber = 1) AND (r.auditstatus = ANY ('{2,7,8}'::integer[])))
  • Rows Removed by Filter: 506
10. 61.821 243.903 ↓ 155.3 18,478 1

WindowAgg (cost=16,788.62..16,791.30 rows=119 width=192) (actual time=171.884..243.903 rows=18,478 loops=1)

11. 27.366 182.082 ↓ 155.3 18,478 1

Sort (cost=16,788.62..16,788.92 rows=119 width=28) (actual time=171.865..182.082 rows=18,478 loops=1)

  • Sort Key: kx_cost_execrecord.costid, kx_cost_execrecord.execid, kx_cost_execrecord.examine_time DESC
  • Sort Method: quicksort Memory: 2,212kB
12. 15.269 154.716 ↓ 155.3 18,478 1

Hash Join (cost=5,079.83..16,784.52 rows=119 width=28) (actual time=54.450..154.716 rows=18,478 loops=1)

  • Hash Cond: (ko_1.orderid = kx_cost_execrecord.execid)
13. 87.252 100.913 ↓ 28.6 28,370 1

Bitmap Heap Scan on kx_order_detail ko_1 (cost=2,704.77..14,361.20 rows=991 width=8) (actual time=15.687..100.913 rows=28,370 loops=1)

  • Recheck Cond: ((costid IS NOT NULL) AND (isgift = 1))
  • Filter: (gpruleid IS NOT NULL)
  • Rows Removed by Filter: 2
  • Heap Blocks: exact=10,172
14. 13.661 13.661 ↓ 4.5 28,372 1

Bitmap Index Scan on ix_kx_order_detail_2 (cost=0.00..2,704.52 rows=6,367 width=0) (actual time=13.661..13.661 rows=28,372 loops=1)

  • Index Cond: ((costid IS NOT NULL) AND (isgift = 1))
15. 6.570 38.534 ↑ 1.0 18,449 1

Hash (cost=2,135.87..2,135.87 rows=19,135 width=28) (actual time=38.534..38.534 rows=18,449 loops=1)

  • Buckets: 32,768 Batches: 1 Memory Usage: 1,410kB
16. 31.964 31.964 ↑ 1.0 18,449 1

Seq Scan on kx_cost_execrecord (cost=0.00..2,135.87 rows=19,135 width=28) (actual time=0.077..31.964 rows=18,449 loops=1)

  • Filter: ((area = 2) AND (type = 1) AND (platstatus = 1))
  • Rows Removed by Filter: 27,848
17. 251.608 251.608 ↑ 1.0 1 17,972

Index Scan using kx_order_pkey on kx_order o (cost=0.42..8.45 rows=1 width=45) (actual time=0.014..0.014 rows=1 loops=17,972)

  • Index Cond: (id = r.execid)
  • Filter: ((platstatus = 1) AND (status = 50) AND (to_char(createtime, 'mm'::text) ~~ '%06%'::text))
  • Rows Removed by Filter: 0
18. 138.204 138.204 ↑ 1.0 1 15,356

Index Scan using idx_kx_order_detail_orderid on kx_order_detail od (cost=0.42..3.16 rows=1 width=68) (actual time=0.007..0.009 rows=1 loops=15,356)

  • Index Cond: (orderid = o.id)
  • Filter: ((gpruleid IS NOT NULL) AND (isgift = 1) AND (platstatus = 1))
  • Rows Removed by Filter: 2
19. 32.500 337.876 ↑ 1.0 1 15,358

Nested Loop (cost=0.98..3.89 rows=1 width=24) (actual time=0.015..0.022 rows=1 loops=15,358)

20. 32.950 184.296 ↑ 1.0 1 15,358

Nested Loop (cost=0.56..0.67 rows=1 width=24) (actual time=0.011..0.012 rows=1 loops=15,358)

21. 76.790 76.790 ↑ 1.0 1 15,358

Index Scan using pk_kx_cost_verification_order_orderid on kx_cost_verification_order kvor (cost=0.29..0.37 rows=1 width=24) (actual time=0.004..0.005 rows=1 loops=15,358)

  • Index Cond: (orderid = o.id)
22. 74.556 74.556 ↑ 1.0 1 18,639

Index Scan using pk_kx_cost_verification_1 on kx_cost_verification kvn (cost=0.28..0.30 rows=1 width=8) (actual time=0.004..0.004 rows=1 loops=18,639)

  • Index Cond: ((id = kvor.verificationid) AND (id IS NOT NULL))
  • Filter: ((costid IS NOT NULL) AND (platstatus = 1))
  • Rows Removed by Filter: 0
23. 121.080 121.080 ↑ 1.0 1 15,135

Index Scan using idx_kx_order_detail_orderid on kx_order_detail ko (cost=0.42..3.21 rows=1 width=8) (actual time=0.003..0.008 rows=1 loops=15,135)

  • Index Cond: (orderid = kvor.orderid)
  • Filter: ((costid IS NOT NULL) AND (gpruleid IS NOT NULL) AND (isgift = 1))
  • Rows Removed by Filter: 2
24. 61.516 61.516 ↑ 1.0 1 15,379

Index Scan using kx_cost_plan_pkey on kx_cost_plan p (cost=0.14..0.20 rows=1 width=72) (actual time=0.004..0.004 rows=1 loops=15,379)

  • Index Cond: (id = od.costid)
  • Filter: ((platstatus = 1) AND (costmodel = '1027741035423469568'::bigint))
25. 461.370 461.370 ↑ 1.0 1 15,379

Index Scan using ix_kx_kq_store_storecode on kx_kq_store kq (cost=0.42..5.38 rows=1 width=19) (actual time=0.029..0.030 rows=1 loops=15,379)

  • Index Cond: ((storecode)::text = (o.customercode)::text)
26. 86.376 86.376 ↓ 0.0 0 14,396

Index Scan using pl_orgstruct_pkey on pl_orgstruct ps (cost=0.28..0.30 rows=1 width=192) (actual time=0.006..0.006 rows=0 loops=14,396)

  • Index Cond: (orgstructid = kq.seleareaid)
  • Filter: (((codepath)::text ~~ '1.1141634195391647744.1141634198017282048.1141634207680958464.%'::text) AND (platstatus = 1))
  • Rows Removed by Filter: 1
27. 71.980 71.980 ↑ 1.0 1 14,396

Index Scan using idx_kx_promotion_product_costid on kx_promotion_product kp (cost=0.14..0.22 rows=1 width=16) (actual time=0.004..0.005 rows=1 loops=14,396)

  • Index Cond: (costid = od.costid)
  • Filter: ((goodstype = 2) AND (od.gpruleid = giftid))
  • Rows Removed by Filter: 1
28. 28.792 28.792 ↑ 1.0 1 14,396

Index Only Scan using pk_kx_cost_verification_1 on kx_cost_verification kv (cost=0.28..0.30 rows=1 width=8) (actual time=0.002..0.002 rows=1 loops=14,396)

  • Index Cond: (id = kvor.verificationid)
  • Heap Fetches: 13,902
29.          

SubPlan (for Nested Loop Left Join)

30. 0.000 0.000 ↓ 0.0 0

Aggregate (cost=587.60..587.61 rows=1 width=32) (never executed)

31. 0.000 0.000 ↓ 0.0 0

Result (cost=1.12..587.59 rows=1 width=8) (never executed)

  • One-Time Filter: (kv.id IS NOT NULL)
32. 0.000 0.000 ↓ 0.0 0

Nested Loop (cost=1.12..587.59 rows=1 width=8) (never executed)

33. 0.000 0.000 ↓ 0.0 0

Nested Loop (cost=0.84..579.29 rows=1 width=16) (never executed)

34. 0.000 0.000 ↓ 0.0 0

Nested Loop (cost=0.84..16.88 rows=1 width=16) (never executed)

35. 0.000 0.000 ↓ 0.0 0

Index Scan using kx_order_detail_pkey on kx_order_detail kap (cost=0.42..8.45 rows=1 width=24) (never executed)

  • Index Cond: (id = od.id)
  • Filter: ((costid = p.id) AND (platstatus = 1))
36. 0.000 0.000 ↓ 0.0 0

Index Only Scan using kx_order_pkey on kx_order ko_2 (cost=0.42..8.44 rows=1 width=8) (never executed)

  • Index Cond: (id = kap.orderid)
  • Heap Fetches: 0
37. 0.000 0.000 ↓ 0.0 0

Seq Scan on kx_cost_verification_order kco (cost=0.00..562.39 rows=1 width=16) (never executed)

  • Filter: ((detailid = od.id) AND (verificationid = kv.id))
38. 0.000 0.000 ↓ 0.0 0

Index Scan using pk_kx_cost_verification_1 on kx_cost_verification kv1 (cost=0.28..8.29 rows=1 width=8) (never executed)

  • Index Cond: (id = kv.id)
  • Filter: (applystatus = 3)
39. 86.376 44,944.312 ↑ 1.0 1 14,396

Aggregate (cost=579.16..579.17 rows=1 width=32) (actual time=3.122..3.122 rows=1 loops=14,396)

40. 14.896 44,857.936 ↑ 1.0 1 14,396

Result (cost=0.70..579.15 rows=1 width=8) (actual time=1.623..3.116 rows=1 loops=14,396)

  • One-Time Filter: (kv.id IS NOT NULL)
41. 42.445 44,843.040 ↑ 1.0 1 14,155

Nested Loop (cost=0.70..579.15 rows=1 width=8) (actual time=1.650..3.168 rows=1 loops=14,155)

42. 56.620 44,729.800 ↑ 1.0 1 14,155

Nested Loop (cost=0.42..570.85 rows=1 width=16) (actual time=1.643..3.160 rows=1 loops=14,155)

43. 127.395 127.395 ↑ 1.0 1 14,155

Index Scan using kx_order_detail_pkey on kx_order_detail kap_1 (cost=0.42..8.45 rows=1 width=16) (actual time=0.008..0.009 rows=1 loops=14,155)

  • Index Cond: (id = od.id)
  • Filter: ((costid = p.id) AND (platstatus = 1))
44. 44,545.785 44,545.785 ↑ 1.0 1 14,155

Seq Scan on kx_cost_verification_order kco_1 (cost=0.00..562.39 rows=1 width=16) (actual time=1.632..3.147 rows=1 loops=14,155)

  • Filter: ((detailid = od.id) AND (verificationid = kv.id))
  • Rows Removed by Filter: 19,514
45. 70.795 70.795 ↑ 1.0 1 14,159

Index Scan using pk_kx_cost_verification_1 on kx_cost_verification kv1_1 (cost=0.28..8.29 rows=1 width=8) (actual time=0.005..0.005 rows=1 loops=14,159)

  • Index Cond: (id = kv.id)
  • Filter: (applystatus = 3)
  • Rows Removed by Filter: 0
Planning time : 34.673 ms
Execution time : 46,876.368 ms