explain.depesz.com

PostgreSQL's explain analyze made readable

Result: hVJg

Settings
# exclusive inclusive rows x rows loops node
1. 55.284 57,670.379 ↓ 14,396.0 14,396 1

Nested Loop Left Join (cost=16,791.44..17,981.67 rows=1 width=32) (actual time=80.461..57,670.379 rows=14,396 loops=1)

2. 19.354 1,758.615 ↓ 14,396.0 14,396 1

Nested Loop (cost=16,791.16..16,814.59 rows=1 width=32) (actual time=77.805..1,758.615 rows=14,396 loops=1)

3. 51.394 1,681.677 ↓ 14,396.0 14,396 1

Nested Loop (cost=16,791.02..16,814.37 rows=1 width=56) (actual time=77.794..1,681.677 rows=14,396 loops=1)

4. 23.039 1,168.913 ↓ 15,379.0 15,379 1

Nested Loop (cost=16,790.60..16,808.97 rows=1 width=66) (actual time=77.766..1,168.913 rows=15,379 loops=1)

5. 23.674 1,084.358 ↓ 15,379.0 15,379 1

Nested Loop Left Join (cost=16,790.45..16,808.77 rows=1 width=58) (actual time=77.759..1,084.358 rows=15,379 loops=1)

  • Join Filter: ((kvor.detailid = od.id) AND (kvor.detailid = od.id))
  • Rows Removed by Join Filter: 40
6. 23.715 661.376 ↓ 15,358.0 15,358 1

Nested Loop (cost=16,789.47..16,804.86 rows=1 width=58) (actual time=77.728..661.376 rows=15,358 loops=1)

  • Join Filter: (r.costid = od.costid)
  • Rows Removed by Join Filter: 16
7. 34.084 438.033 ↓ 15,356.0 15,356 1

Nested Loop (cost=16,789.04..16,801.68 rows=1 width=34) (actual time=77.705..438.033 rows=15,356 loops=1)

8. 20.998 170.222 ↓ 17,979.0 17,979 1

Subquery Scan on r (cost=16,788.62..16,793.23 rows=1 width=16) (actual time=76.072..170.222 rows=17,979 loops=1)

  • Filter: ((r.rownumber = 1) AND (r.auditstatus = ANY ('{2,7,8}'::integer[])))
  • Rows Removed by Filter: 506
9. 62.827 149.224 ↓ 155.3 18,485 1

WindowAgg (cost=16,788.62..16,791.30 rows=119 width=192) (actual time=76.061..149.224 rows=18,485 loops=1)

10. 21.027 86.397 ↓ 155.3 18,485 1

Sort (cost=16,788.62..16,788.92 rows=119 width=28) (actual time=76.047..86.397 rows=18,485 loops=1)

  • Sort Key: kx_cost_execrecord.costid, kx_cost_execrecord.execid, kx_cost_execrecord.examine_time DESC
  • Sort Method: quicksort Memory: 2,213kB
11. 9.396 65.370 ↓ 155.3 18,485 1

Hash Join (cost=5,079.83..16,784.52 rows=119 width=28) (actual time=24.268..65.370 rows=18,485 loops=1)

  • Hash Cond: (ko_1.orderid = kx_cost_execrecord.execid)
12. 33.317 39.518 ↓ 28.6 28,371 1

Bitmap Heap Scan on kx_order_detail ko_1 (cost=2,704.77..14,361.20 rows=991 width=8) (actual time=7.641..39.518 rows=28,371 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,173
13. 6.201 6.201 ↓ 4.5 28,373 1

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

  • Index Cond: ((costid IS NOT NULL) AND (isgift = 1))
14. 2.888 16.456 ↑ 1.0 18,456 1

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

  • Buckets: 32,768 Batches: 1 Memory Usage: 1,410kB
15. 13.568 13.568 ↑ 1.0 18,456 1

Seq Scan on kx_cost_execrecord (cost=0.00..2,135.87 rows=19,135 width=28) (actual time=0.037..13.568 rows=18,456 loops=1)

  • Filter: ((area = 2) AND (type = 1) AND (platstatus = 1))
  • Rows Removed by Filter: 27,851
16. 233.727 233.727 ↑ 1.0 1 17,979

Index Scan using kx_order_pkey on kx_order o (cost=0.42..8.45 rows=1 width=18) (actual time=0.013..0.013 rows=1 loops=17,979)

  • Index Cond: (id = r.execid)
  • Filter: ((platstatus = 1) AND (status = 50) AND (to_char(createtime, 'mm'::text) ~~ '%06%'::text))
  • Rows Removed by Filter: 0
17. 199.628 199.628 ↑ 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=40) (actual time=0.012..0.013 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
18. 16.696 399.308 ↑ 1.0 1 15,358

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

19. 81.258 291.802 ↑ 1.0 1 15,358

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

20. 61.432 61.432 ↑ 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.004 rows=1 loops=15,358)

  • Index Cond: (orderid = o.id)
21. 149.112 149.112 ↑ 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.008..0.008 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
22. 90.810 90.810 ↑ 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.006 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
23. 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=8) (actual time=0.004..0.004 rows=1 loops=15,379)

  • Index Cond: (id = od.costid)
  • Filter: ((platstatus = 1) AND (costmodel = '1027741035423469568'::bigint))
24. 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)
25. 57.584 57.584 ↑ 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.003..0.004 rows=1 loops=14,396)

  • Index Cond: (costid = od.costid)
  • Filter: ((goodstype = 2) AND (od.gpruleid = giftid))
  • Rows Removed by Filter: 1
26. 71.980 71.980 ↑ 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.005..0.005 rows=1 loops=14,396)

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

SubPlan (for Nested Loop Left Join)

28. 0.000 0.000 ↓ 0.0 0

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

29. 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)
30. 0.000 0.000 ↓ 0.0 0

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

31. 0.000 0.000 ↓ 0.0 0

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

32. 0.000 0.000 ↓ 0.0 0

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

33. 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))
34. 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
35. 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))
36. 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)
37. 57.584 55,784.500 ↑ 1.0 1 14,396

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

38. 12.836 55,726.916 ↑ 1.0 1 14,396

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

  • One-Time Filter: (kv.id IS NOT NULL)
39. 28.290 55,714.080 ↑ 1.0 1 14,155

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

40. 84.930 55,614.995 ↑ 1.0 1 14,155

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

41. 113.240 113.240 ↑ 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.007..0.008 rows=1 loops=14,155)

  • Index Cond: (id = od.id)
  • Filter: ((costid = p.id) AND (platstatus = 1))
42. 55,416.825 55,416.825 ↑ 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=2.017..3.915 rows=1 loops=14,155)

  • Filter: ((detailid = od.id) AND (verificationid = kv.id))
  • Rows Removed by Filter: 19,514
43. 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 : 5.962 ms
Execution time : 57,681.138 ms