explain.depesz.com

PostgreSQL's explain analyze made readable

Result: i2m6

Settings
# exclusive inclusive rows x rows loops node
1. 0.223 179,953.865 ↓ 1,000.0 1,000 1

Limit (cost=741,719.04..741,719.04 rows=1 width=179) (actual time=179,953.436..179,953.865 rows=1,000 loops=1)

2. 12.466 179,953.642 ↓ 1,000.0 1,000 1

Sort (cost=741,719.04..741,719.04 rows=1 width=179) (actual time=179,953.435..179,953.642 rows=1,000 loops=1)

  • Sort Key: rh.created
  • Sort Method: top-N heapsort Memory: 314kB
3. 1,100.496 179,941.176 ↓ 5,055.0 5,055 1

Nested Loop Left Join (cost=723,264.53..741,719.03 rows=1 width=179) (actual time=16,357.433..179,941.176 rows=5,055 loops=1)

  • Join Filter: ((o.id = payu_returns.order_id) AND (ro.id = payu_returns.returning_order_id))
  • Rows Removed by Join Filter: 19820655
4. 33,586.913 175,934.055 ↓ 5,055.0 5,055 1

Nested Loop Left Join (cost=719,164.53..736,700.29 rows=1 width=144) (actual time=16,336.861..175,934.055 rows=5,055 loops=1)

  • Join Filter: (returning_orders.order_id = o.id)
  • Rows Removed by Join Filter: 615729330
5. 4,897.058 39,755.917 ↓ 5,055.0 5,055 1

Nested Loop Left Join (cost=709,310.01..722,542.97 rows=1 width=136) (actual time=16,187.616..39,755.917 rows=5,055 loops=1)

  • Join Filter: (imoje_payments.order_id = o.id)
  • Rows Removed by Join Filter: 89903174
6. 36.761 16,559.759 ↓ 5,055.0 5,055 1

Nested Loop (cost=698,313.89..711,025.03 rows=1 width=130) (actual time=16,138.865..16,559.759 rows=5,055 loops=1)

  • Join Filter: (o.client_id = c.id)
  • Rows Removed by Join Filter: 181980
7. 76.977 16,472.448 ↓ 5,055.0 5,055 1

Hash Right Join (cost=698,313.89..711,020.22 rows=1 width=119) (actual time=16,138.848..16,472.448 rows=5,055 loops=1)

  • Hash Cond: (o_1.id = o.id)
8. 532.326 3,462.511 ↓ 1.1 591,520 1

HashAggregate (cost=416,399.18..421,749.21 rows=535,003 width=16) (actual time=3,205.783..3,462.511 rows=591,520 loops=1)

  • Group Key: o_1.id
9. 1,861.646 2,930.185 ↓ 1.2 1,106,176 1

Hash Right Join (cost=55,599.14..411,729.62 rows=933,911 width=12) (actual time=392.566..2,930.185 rows=1,106,176 loops=1)

  • Hash Cond: (i.order_id = o_1.id)
10. 678.618 678.618 ↓ 1.0 10,080,288 1

Seq Scan on orders_item i (cost=0.00..309,245.18 rows=10,012,318 width=8) (actual time=0.009..678.618 rows=10,080,288 loops=1)

11. 106.928 389.921 ↓ 1.1 591,520 1

Hash (cost=48,911.60..48,911.60 rows=535,003 width=8) (actual time=389.921..389.921 rows=591,520 loops=1)

  • Buckets: 1048576 Batches: 1 Memory Usage: 31299kB
12. 282.993 282.993 ↓ 1.1 591,520 1

Index Scan using orders_created_index on orders o_1 (cost=0.44..48,911.60 rows=535,003 width=8) (actual time=0.018..282.993 rows=591,520 loops=1)

  • Index Cond: (created > (now() - '4 mons'::interval))
13. 2.769 12,932.960 ↓ 5,055.0 5,055 1

Hash (cost=281,914.70..281,914.70 rows=1 width=111) (actual time=12,932.960..12,932.960 rows=5,055 loops=1)

  • Buckets: 8192 (originally 1024) Batches: 1 (originally 1) Memory Usage: 802kB
14. 4.613 12,930.191 ↓ 5,055.0 5,055 1

Nested Loop (cost=269,208.13..281,914.70 rows=1 width=111) (actual time=1,896.496..12,930.191 rows=5,055 loops=1)

15. 1,955.675 12,910.413 ↓ 5,055.0 5,055 1

Nested Loop Left Join (cost=269,207.70..281,914.07 rows=1 width=117) (actual time=1,896.484..12,910.413 rows=5,055 loops=1)

  • Join Filter: (o_3.id = o.id)
  • Rows Removed by Join Filter: 34894661
16. 64.418 1,790.023 ↓ 5,055.0 5,055 1

Hash Right Join (cost=207,062.76..219,769.09 rows=1 width=109) (actual time=1,632.597..1,790.023 rows=5,055 loops=1)

  • Hash Cond: (o_2.id = o.id)
17. 225.004 1,116.386 ↓ 1.1 591,520 1

HashAggregate (cost=120,174.45..125,524.48 rows=535,003 width=16) (actual time=1,023.311..1,116.386 rows=591,520 loops=1)

  • Group Key: o_2.id
18. 385.367 891.382 ↓ 1.2 654,901 1

Hash Right Join (cost=55,599.14..117,499.43 rows=535,003 width=12) (actual time=377.115..891.382 rows=654,901 loops=1)

  • Hash Cond: (i_1.order_id = o_2.id)
19. 131.478 131.478 ↓ 1.0 1,496,059 1

Seq Scan on returning_orders i_1 (cost=0.00..54,956.15 rows=1,482,915 width=8) (actual time=0.008..131.478 rows=1,496,059 loops=1)

20. 106.019 374.537 ↓ 1.1 591,520 1

Hash (cost=48,911.60..48,911.60 rows=535,003 width=8) (actual time=374.537..374.537 rows=591,520 loops=1)

  • Buckets: 1048576 Batches: 1 Memory Usage: 31299kB
21. 268.518 268.518 ↓ 1.1 591,520 1

Index Scan using orders_created_index on orders o_2 (cost=0.44..48,911.60 rows=535,003 width=8) (actual time=0.014..268.518 rows=591,520 loops=1)

  • Index Cond: (created > (now() - '4 mons'::interval))
22. 2.018 609.219 ↓ 5,055.0 5,055 1

Hash (cost=86,888.30..86,888.30 rows=1 width=101) (actual time=609.219..609.219 rows=5,055 loops=1)

  • Buckets: 8192 (originally 1024) Batches: 1 (originally 1) Memory Usage: 784kB
23. 0.000 607.201 ↓ 5,055.0 5,055 1

Nested Loop (cost=5,545.72..86,888.30 rows=1 width=101) (actual time=506.817..607.201 rows=5,055 loops=1)

24. 18.978 535.764 ↓ 23,893.0 23,893 1

Nested Loop (cost=5,545.28..86,887.67 rows=1 width=56) (actual time=56.120..535.764 rows=23,893 loops=1)

25. 436.468 466.430 ↓ 2.5 25,178 1

Seq Scan on returning_orders ro (cost=5,544.86..67,915.58 rows=10,109 width=36) (actual time=56.100..466.430 rows=25,178 loops=1)

  • Filter: ((NOT (hashed SubPlan 1)) AND (status = 0))
  • Rows Removed by Filter: 1470881
26.          

SubPlan (for Seq Scan)

27. 29.962 29.962 ↓ 1.0 147,860 1

Seq Scan on payu_returns payu_returns_1 (cost=0.00..5,177.55 rows=146,923 width=4) (actual time=0.009..29.962 rows=147,860 loops=1)

  • Filter: (status <> 9)
  • Rows Removed by Filter: 43238
28. 50.356 50.356 ↑ 1.0 1 25,178

Index Scan using idx_returning_history_order_item_id on returning_history rh (cost=0.43..1.87 rows=1 width=20) (actual time=0.002..0.002 rows=1 loops=25,178)

  • Index Cond: (order_item_id = ro.order_item_id)
  • Filter: ((ro.order_id = order_id) AND (created < (now() - '1 day'::interval)))
  • Rows Removed by Filter: 0
29. 71.679 71.679 ↓ 0.0 0 23,893

Index Scan using orders_pkey on orders o (cost=0.43..0.62 rows=1 width=53) (actual time=0.003..0.003 rows=0 loops=23,893)

  • Index Cond: (id = rh.order_id)
  • Filter: ((master_client_id = 5) AND ((payment_method)::text = 'online'::text) AND (created > (now() - '4 mons'::interval)) AND (client_id = ANY ('{6,8,9,12,31,39,36,40,44}'::integer[])))
  • Rows Removed by Filter: 1
30. 7,087.110 9,164.715 ↓ 6,903.0 6,903 5,055

GroupAggregate (cost=62,144.94..62,144.96 rows=1 width=16) (actual time=0.052..1.813 rows=6,903 loops=5,055)

  • Group Key: o_3.id
31. 1,817.798 2,077.605 ↓ 7,204.0 7,204 5,055

Sort (cost=62,144.94..62,144.95 rows=1 width=12) (actual time=0.052..0.411 rows=7,204 loops=5,055)

  • Sort Key: o_3.id
  • Sort Method: quicksort Memory: 530kB
32. 0.000 259.807 ↓ 7,204.0 7,204 1

Nested Loop (cost=1,000.86..62,144.93 rows=1 width=12) (actual time=0.560..259.807 rows=7,204 loops=1)

33. 0.000 35.765 ↓ 75,861.0 75,861 1

Gather (cost=1,000.43..62,144.29 rows=1 width=12) (actual time=0.545..35.765 rows=75,861 loops=1)

  • Workers Planned: 3
  • Workers Launched: 3
34. 3.239 165.423 ↓ 18,965.0 18,965 4 / 4

Nested Loop (cost=0.43..61,144.19 rows=1 width=12) (actual time=0.187..165.423 rows=18,965 loops=4)

35. 76.482 76.482 ↑ 1.2 21,426 4 / 4

Parallel Seq Scan on returning_history h (cost=0.00..33,228.45 rows=26,667 width=8) (actual time=0.042..76.482 rows=21,426 loops=4)

  • Filter: ((status = 2) OR (status = 3))
  • Rows Removed by Filter: 388017
36. 85.702 85.702 ↑ 1.0 1 85,702 / 4

Index Scan using returning_orders_order_item_id_uindex on returning_orders i_2 (cost=0.43..1.04 rows=1 width=16) (actual time=0.004..0.004 rows=1 loops=85,702)

  • Index Cond: (order_item_id = h.order_item_id)
  • Filter: ((date_export IS NOT NULL) AND (h.order_id = order_id))
  • Rows Removed by Filter: 0
37. 227.583 227.583 ↓ 0.0 0 75,861

Index Scan using orders_pkey on orders o_3 (cost=0.43..0.63 rows=1 width=8) (actual time=0.003..0.003 rows=0 loops=75,861)

  • Index Cond: (id = i_2.order_id)
  • Filter: (created > (now() - '4 mons'::interval))
  • Rows Removed by Filter: 1
38. 15.165 15.165 ↑ 1.0 1 5,055

Index Scan using orders_item_pkey on orders_item oi (cost=0.43..0.61 rows=1 width=10) (actual time=0.003..0.003 rows=1 loops=5,055)

  • Index Cond: (id = rh.order_item_id)
39. 50.550 50.550 ↓ 1.0 37 5,055

Seq Scan on clients c (cost=0.00..4.36 rows=36 width=15) (actual time=0.003..0.010 rows=37 loops=5,055)

40. 18,259.355 18,299.100 ↓ 1.1 17,785 5,055

HashAggregate (cost=10,996.12..11,156.68 rows=16,056 width=14) (actual time=0.010..3.620 rows=17,785 loops=5,055)

  • Group Key: imoje_payments.order_id, imoje_payments.i_payment_method_code
41. 39.745 39.745 ↓ 1.1 17,786 1

Seq Scan on imoje_payments (cost=0.00..10,915.36 rows=16,152 width=14) (actual time=5.580..39.745 rows=17,786 loops=1)

  • Filter: ((status = 2) AND (created > (now() - '4 mons'::interval)))
  • Rows Removed by Filter: 48900
42. 102,514.653 102,591.225 ↑ 1.1 121,807 5,055

HashAggregate (cost=9,854.52..11,178.46 rows=132,394 width=12) (actual time=0.030..20.295 rows=121,807 loops=5,055)

  • Group Key: returning_orders.order_id
43. 76.572 76.572 ↓ 1.4 200,092 1

Index Scan using returning_orders_date_start on returning_orders (cost=0.43..9,130.23 rows=144,857 width=8) (actual time=0.015..76.572 rows=200,092 loops=1)

  • Index Cond: (date_start > (now() - '4 mons'::interval))
44. 2,889.417 2,906.625 ↑ 6.7 3,921 5,055

HashAggregate (cost=4,100.01..4,362.50 rows=26,249 width=8) (actual time=0.005..0.575 rows=3,921 loops=5,055)

  • Group Key: payu_returns.order_id, payu_returns.returning_order_id
45. 17.208 17.208 ↑ 1.7 17,163 1

Index Scan using payu_returns_status_index on payu_returns (cost=0.42..3,955.35 rows=28,932 width=8) (actual time=0.015..17.208 rows=17,163 loops=1)

  • Index Cond: (status = 9)
  • Filter: (created > (now() - '4 mons'::interval))
  • Rows Removed by Filter: 26075
Planning time : 10.941 ms
Execution time : 179,960.052 ms