explain.depesz.com

PostgreSQL's explain analyze made readable

Result: kA5y

Settings
# exclusive inclusive rows x rows loops node
1. 0.055 58,028.134 ↑ 1.0 10 1

HashAggregate (cost=678,008.52..1,137,873.04 rows=10 width=35) (actual time=58,028.132..58,028.134 rows=10 loops=1)

2. 0.161 58,028.079 ↑ 1.0 10 1

Subquery Scan on o (cost=218,143.69..678,008.37 rows=10 width=35) (actual time=21,579.206..58,028.079 rows=10 loops=1)

3. 0.006 17,924.088 ↑ 1.0 10 1

Limit (cost=218,143.69..218,143.84 rows=10 width=35) (actual time=17,924.050..17,924.088 rows=10 loops=1)

4. 0.024 17,924.082 ↑ 4.8 10 1

Unique (cost=218,143.69..218,144.40 rows=48 width=35) (actual time=17,924.049..17,924.082 rows=10 loops=1)

5. 20.477 17,924.058 ↑ 4.1 23 1

Sort (cost=218,143.69..218,143.93 rows=94 width=35) (actual time=17,924.047..17,924.058 rows=23 loops=1)

  • Sort Key: o.order_date, o.order_id
  • Sort Method: quicksort Memory: 2214kB
6. 2.890 17,903.581 ↓ 196.8 18,499 1

Nested Loop Left Join (cost=53,668.17..218,140.61 rows=94 width=35) (actual time=13,864.270..17,903.581 rows=18,499 loops=1)

  • Join Filter: ((ov_purchase.order_id)::text = (o.order_id)::text)
7. 2.692 13,275.941 ↓ 196.8 18,499 1

Nested Loop Left Join (cost=53,668.17..150,938.19 rows=94 width=35) (actual time=9,244.647..13,275.941 rows=18,499 loops=1)

  • Join Filter: ((ov.order_id)::text = (o.order_id)::text)
8. 17.910 8,759.493 ↓ 196.8 18,499 1

Nested Loop Left Join (cost=53,668.17..83,735.77 rows=94 width=35) (actual time=4,736.356..8,759.493 rows=18,499 loops=1)

  • Join Filter: (((o.member_id)::text = (m.member_id)::text) AND (o.shop_no = m.shop_no))
9. 62.537 8,741.583 ↓ 196.8 18,499 1

Nested Loop Left Join (cost=53,668.17..83,705.24 rows=94 width=41) (actual time=4,736.336..8,741.583 rows=18,499 loops=1)

  • Join Filter: ((os.os_order_id)::text = (o.order_id)::text)
  • Rows Removed by Join Filter: 314483
10. 805.216 8,642.048 ↓ 196.8 18,499 1

Hash Join (cost=53,668.17..83,678.71 rows=94 width=39) (actual time=4,736.294..8,642.048 rows=18,499 loops=1)

  • Hash Cond: ((op.ord_item_code)::text = (om.ord_item_code)::text)
11. 3,100.595 3,100.595 ↑ 1.0 346,628 1

Seq Scan on order_product op (cost=0.00..28,709.34 rows=346,734 width=19) (actual time=0.002..3,100.595 rows=346,628 loops=1)

12. 11.688 4,736.237 ↓ 196.8 18,499 1

Hash (cost=53,667.00..53,667.00 rows=94 width=59) (actual time=4,736.237..4,736.237 rows=18,499 loops=1)

  • Buckets: 1024 Batches: 1 Memory Usage: 1807kB
13. 229.866 4,724.549 ↓ 196.8 18,499 1

Hash Join (cost=33,801.02..53,667.00 rows=94 width=59) (actual time=3,011.372..4,724.549 rows=18,499 loops=1)

  • Hash Cond: ((om.order_id)::text = (o.order_id)::text)
14. 1,483.343 1,483.343 ↑ 1.0 345,552 1

Seq Scan on order_manage om (cost=0.00..18,568.75 rows=345,677 width=37) (actual time=0.004..1,483.343 rows=345,552 loops=1)

  • Filter: ((action)::text = ANY ('{N1,N2,C1,C2,C3,E1,E2,F,H1,H2}'::text[]))
  • Rows Removed by Filter: 726
15. 9.109 3,011.340 ↓ 198.5 9,527 1

Hash (cost=33,800.42..33,800.42 rows=48 width=39) (actual time=3,011.340..3,011.340 rows=9,527 loops=1)

  • Buckets: 1024 Batches: 1 Memory Usage: 745kB
16. 3,002.231 3,002.231 ↓ 198.5 9,527 1

Seq Scan on orders o (cost=0.00..33,800.42 rows=48 width=39) (actual time=0.422..3,002.231 rows=9,527 loops=1)

  • Filter: (((shop_no = 1) OR (shop_no IS NULL)) AND ((member_id)::text = 'h043'::text) AND (is_display = 'T'::bpchar) AND (COALESCE(is_temporary_order, 'F'::bpchar) = 'F'::bpchar))
  • Rows Removed by Filter: 168018
17. 36.969 36.998 ↑ 1.0 17 18,499

Materialize (cost=0.00..2.61 rows=17 width=19) (actual time=0.000..0.002 rows=17 loops=18,499)

18. 0.029 0.029 ↑ 1.0 17 1

Seq Scan on te_ord_settle os (cost=0.00..2.52 rows=17 width=19) (actual time=0.010..0.029 rows=17 loops=1)

  • Filter: ((os_settle_type = 'O'::bpchar) AND (os_is_deleted = 'F'::bpchar))
  • Rows Removed by Filter: 18
19. 0.000 0.000 ↑ 1.0 1 18,499

Materialize (cost=0.00..28.89 rows=1 width=8) (actual time=0.000..0.000 rows=1 loops=18,499)

20. 0.027 0.027 ↑ 1.0 1 1

Index Only Scan using ixnn_member__concatenated4 on member m (cost=0.00..28.88 rows=1 width=8) (actual time=0.016..0.027 rows=1 loops=1)

  • Index Cond: (member_id = 'h043'::text)
  • Heap Fetches: 3
21. 5.468 4,513.756 ↓ 0.0 0 18,499

Materialize (cost=0.00..67,201.01 rows=1 width=17) (actual time=0.244..0.244 rows=0 loops=18,499)

22. 4,508.288 4,508.288 ↓ 0.0 0 1

Seq Scan on t_order_value ov (cost=0.00..67,201.01 rows=1 width=17) (actual time=4,508.288..4,508.288 rows=0 loops=1)

  • Filter: (((entity_type)::text = 'order'::text) AND ((attribute_code)::text = 'easypay_name'::text) AND (value_idx = 0))
  • Rows Removed by Filter: 1155391
23. 5.132 4,624.750 ↓ 0.0 0 18,499

Materialize (cost=0.00..67,201.01 rows=1 width=17) (actual time=0.250..0.250 rows=0 loops=18,499)

24. 4,619.618 4,619.618 ↓ 0.0 0 1

Seq Scan on t_order_value ov_purchase (cost=0.00..67,201.01 rows=1 width=17) (actual time=4,619.618..4,619.618 rows=0 loops=1)

  • Filter: (((entity_type)::text = 'order'::text) AND ((attribute_code)::text = 'is_purchased'::text) AND (value_idx = 0))
  • Rows Removed by Filter: 1155391
25.          

SubPlan (forSubquery Scan)

26. 0.190 40,103.410 ↑ 2.0 1 10

GroupAggregate (cost=15,971.52..45,981.18 rows=2 width=30) (actual time=4,010.341..4,010.341 rows=1 loops=10)

27. 1,924.620 40,103.220 ↑ 1.0 2 10

Hash Join (cost=15,971.52..45,981.14 rows=2 width=30) (actual time=1,907.908..4,010.322 rows=2 loops=10)

  • Hash Cond: ((op.ord_item_code)::text = (om.ord_item_code)::text)
28. 25,579.460 25,579.460 ↑ 1.0 346,628 10

Seq Scan on order_product op (cost=0.00..28,709.34 rows=346,734 width=28) (actual time=0.080..2,557.946 rows=346,628 loops=10)

29. 0.070 12,599.140 ↑ 1.0 2 10

Hash (cost=15,971.50..15,971.50 rows=2 width=41) (actual time=1,259.914..1,259.914 rows=2 loops=10)

  • Buckets: 1024 Batches: 1 Memory Usage: 1kB
30. 12,599.070 12,599.070 ↑ 1.0 2 10

Seq Scan on order_manage om (cost=0.00..15,971.50 rows=2 width=41) (actual time=842.664..1,259.907 rows=2 loops=10)

  • Filter: (((action)::text = ANY ('{N1,N2}'::text[])) AND ((order_id)::text = (o.order_id)::text))
  • Rows Removed by Filter: 346276
31. 0.030 0.330 ↑ 1.0 1 10

Aggregate (cost=2.62..2.63 rows=1 width=5) (actual time=0.033..0.033 rows=1 loops=10)

32. 0.300 0.300 ↓ 0.0 0 10

Seq Scan on te_ord_settle os (cost=0.00..2.61 rows=2 width=5) (actual time=0.030..0.030 rows=0 loops=10)

  • Filter: (((os_order_id)::text = (o.order_id)::text) AND (os_is_payed = 'T'::bpchar) AND (os_is_deleted = 'F'::bpchar))
  • Rows Removed by Filter: 35
33. 0.020 0.090 ↑ 1.0 1 10

Aggregate (cost=2.62..2.63 rows=1 width=5) (actual time=0.009..0.009 rows=1 loops=10)

34. 0.070 0.070 ↓ 0.0 0 10

Seq Scan on te_ord_settle os (cost=0.00..2.61 rows=2 width=5) (actual time=0.007..0.007 rows=0 loops=10)

  • Filter: (((os_order_id)::text = (o.order_id)::text) AND (os_is_payed = 'T'::bpchar) AND (os_is_deleted = 'F'::bpchar))
  • Rows Removed by Filter: 35