explain.depesz.com

PostgreSQL's explain analyze made readable

Result: O4LH

Settings
# exclusive inclusive rows x rows loops node
1. 0.026 5,930.472 ↑ 1.0 1 1

Aggregate (cost=145,234.28..145,234.29 rows=1 width=128) (actual time=5,930.472..5,930.472 rows=1 loops=1)

2.          

CTE orders_total_order

3. 10.931 550.754 ↓ 246.8 13,821 1

Sort (cost=690.75..690.89 rows=56 width=73) (actual time=547.147..550.754 rows=13,821 loops=1)

  • Sort Key: (((COALESCE((((sum(oml.amount) + COALESCE(o.shipping, '0'::numeric)) + COALESCE(sum(oml.shipping), '0'::numeric)) + COALESCE(o.processing_fee, '0'::numeric)), '0'::numeric) * common.get_exchange_rate(o.currency, ca.common_currency_iso_a3, o.ordered_at)))::numeric(20,2))
  • Sort Method: quicksort Memory: 2328kB
4. 30.706 539.823 ↓ 246.8 13,821 1

GroupAggregate (cost=685.34..689.12 rows=56 width=73) (actual time=508.262..539.823 rows=13,821 loops=1)

  • Group Key: ca.common_currency_iso_a3, omsm.lengow_status, o.id
5. 20.672 509.117 ↓ 275.2 15,409 1

Sort (cost=685.34..685.48 rows=56 width=63) (actual time=507.374..509.117 rows=15,409 loops=1)

  • Sort Key: ca.common_currency_iso_a3, omsm.lengow_status, o.id
  • Sort Method: quicksort Memory: 2551kB
6. 11.389 488.445 ↓ 275.2 15,409 1

Nested Loop (cost=2.12..683.72 rows=56 width=63) (actual time=42.283..488.445 rows=15,409 loops=1)

7. 13.525 338.846 ↓ 276.4 13,821 1

Nested Loop (cost=1.68..656.13 rows=50 width=53) (actual time=42.233..338.846 rows=13,821 loops=1)

  • Join Filter: (omsm.marketplace_status = o.marketplace_status)
  • Rows Removed by Join Filter: 187321
8. 0.036 0.655 ↓ 25.5 51 1

Nested Loop (cost=1.12..3.21 rows=2 width=22) (actual time=0.211..0.655 rows=51 loops=1)

9. 0.054 0.054 ↑ 1.0 1 1

Index Scan using common_account_pkey on account ca (cost=0.28..0.50 rows=1 width=8) (actual time=0.053..0.054 rows=1 loops=1)

  • Index Cond: (id = 1247)
10. 0.048 0.565 ↓ 25.5 51 1

Nested Loop (cost=0.84..2.69 rows=2 width=22) (actual time=0.156..0.565 rows=51 loops=1)

11. 0.011 0.307 ↓ 3.5 7 1

Nested Loop (cost=0.56..1.62 rows=2 width=21) (actual time=0.075..0.307 rows=7 loops=1)

12. 0.114 0.114 ↓ 3.5 7 1

Index Scan using channel_account_config_ui on account_config (cost=0.28..0.60 rows=2 width=12) (actual time=0.049..0.114 rows=7 loops=1)

  • Index Cond: (common_account_id = 1247)
13. 0.182 0.182 ↑ 1.0 1 7

Index Scan using holder_pkey on holder (cost=0.28..0.50 rows=1 width=17) (actual time=0.025..0.026 rows=1 loops=7)

  • Index Cond: (id = account_config.channel_holder_id)
14. 0.210 0.210 ↑ 1.3 7 7

Index Scan using channel_order_marketplace_status_mapping_pkey on order_marketplace_status_mapping omsm (cost=0.28..0.45 rows=9 width=24) (actual time=0.014..0.030 rows=7 loops=7)

  • Index Cond: (channel_holder_slug = (holder.slug)::text)
15. 324.666 324.666 ↓ 1.5 3,944 51

Index Scan using channel_account_config_id_index on order_marketplace o (cost=0.56..292.73 rows=2,698 width=60) (actual time=0.012..6.366 rows=3,944 loops=51)

  • Index Cond: (channel_account_config_id = account_config.id)
  • Filter: (ordered_at >= '2018-05-18 00:19:53+02'::timestamp with time zone)
16. 138.210 138.210 ↑ 4.0 1 13,821

Index Scan using channel_order_marketplace_id_index on order_marketplace_line oml (cost=0.43..0.51 rows=4 width=18) (actual time=0.010..0.010 rows=1 loops=13,821)

  • Index Cond: (channel_order_marketplace_id = o.id)
17.          

CTE orders_with_types

18. 562.391 766.878 ↓ 1.2 558,680 1

HashAggregate (cost=25,434.51..30,283.93 rows=484,942 width=8) (actual time=500.577..766.878 rows=558,680 loops=1)

  • Group Key: otm.channel_order_marketplace_id
19. 52.876 204.487 ↑ 1.0 817,999 1

Nested Loop (cost=0.55..23,361.73 rows=829,112 width=8) (actual time=0.146..204.487 rows=817,999 loops=1)

20. 0.059 0.059 ↑ 1.0 4 1

Index Only Scan using order_type_pkey on order_type ot (cost=0.13..0.39 rows=4 width=4) (actual time=0.039..0.059 rows=4 loops=1)

  • Heap Fetches: 0
21. 151.552 151.552 ↑ 1.0 204,500 4

Index Only Scan using order_type_mapping_order_type_id_channel_order_marketplace__key on order_type_mapping otm (cost=0.42..3,767.56 rows=207,278 width=16) (actual time=0.048..37.888 rows=204,500 loops=4)

  • Index Cond: (order_type_id = ot.id)
  • Heap Fetches: 47601
22.          

CTE orders_with_multiple_types

23. 2,947.684 4,672.841 ↓ 1.2 558,680 1

HashAggregate (cost=96,631.71..102,693.48 rows=484,942 width=40) (actual time=4,415.777..4,672.841 rows=558,680 loops=1)

  • Group Key: otm_1.channel_order_marketplace_id
24. 458.136 1,725.157 ↑ 1.0 817,999 1

Hash Join (cost=33,725.73..86,267.81 rows=829,112 width=104) (actual time=900.508..1,725.157 rows=817,999 loops=1)

  • Hash Cond: (owt.channel_order_marketplace_id = otm_1.channel_order_marketplace_id)
25. 871.379 871.379 ↓ 1.2 558,680 1

CTE Scan on orders_with_types owt (cost=0.00..9,698.84 rows=484,942 width=8) (actual time=500.606..871.379 rows=558,680 loops=1)

26. 223.834 395.642 ↑ 1.0 817,999 1

Hash (cost=23,361.83..23,361.83 rows=829,112 width=104) (actual time=395.642..395.642 rows=817,999 loops=1)

  • Buckets: 1048576 Batches: 1 Memory Usage: 71430kB
27. 61.357 171.808 ↑ 1.0 817,999 1

Nested Loop (cost=0.55..23,361.83 rows=829,112 width=104) (actual time=0.061..171.808 rows=817,999 loops=1)

28. 0.051 0.051 ↑ 1.0 4 1

Index Scan using order_type_pkey on order_type ot_1 (cost=0.13..0.49 rows=4 width=100) (actual time=0.026..0.051 rows=4 loops=1)

29. 110.400 110.400 ↑ 1.0 204,500 4

Index Only Scan using order_type_mapping_order_type_id_channel_order_marketplace__key on order_type_mapping otm_1 (cost=0.42..3,767.56 rows=207,278 width=16) (actual time=0.015..27.600 rows=204,500 loops=4)

  • Index Cond: (order_type_id = ot_1.id)
  • Heap Fetches: 47601
30.          

CTE all_orders

31. 3.924 5,921.397 ↓ 13,821.0 13,821 1

WindowAgg (cost=11,565.74..11,565.76 rows=1 width=127) (actual time=5,916.536..5,921.397 rows=13,821 loops=1)

32. 7.423 5,917.473 ↓ 13,821.0 13,821 1

Sort (cost=11,565.74..11,565.74 rows=1 width=89) (actual time=5,916.525..5,917.473 rows=13,821 loops=1)

  • Sort Key: omsm_1.lengow_status, o_1.ordered_at DESC
  • Sort Method: quicksort Memory: 2328kB
33. 3.567 5,910.050 ↓ 13,821.0 13,821 1

Group (cost=11,565.71..11,565.73 rows=1 width=89) (actual time=5,903.046..5,910.050 rows=13,821 loops=1)

  • Group Key: holder_1.slug, omsm_1.lengow_status, o_1.id, orders_total_order.total_order, oa.common_country_iso_a2
34. 27.674 5,906.483 ↓ 15,409.0 15,409 1

Sort (cost=11,565.71..11,565.72 rows=1 width=85) (actual time=5,903.035..5,906.483 rows=15,409 loops=1)

  • Sort Key: holder_1.slug, omsm_1.lengow_status, o_1.id, orders_total_order.total_order, oa.common_country_iso_a2
  • Sort Method: quicksort Memory: 2551kB
35. 5.616 5,878.809 ↓ 15,409.0 15,409 1

Nested Loop (cost=48.01..11,565.70 rows=1 width=85) (actual time=5,532.510..5,878.809 rows=15,409 loops=1)

36. 4.592 5,857.784 ↓ 15,409.0 15,409 1

Nested Loop (cost=47.73..11,565.29 rows=1 width=89) (actual time=5,532.472..5,857.784 rows=15,409 loops=1)

37. 8.912 5,784.087 ↓ 13,821.0 13,821 1

Nested Loop (cost=47.30..11,564.75 rows=1 width=97) (actual time=5,532.420..5,784.087 rows=13,821 loops=1)

38. 7.864 5,747.533 ↓ 13,821.0 13,821 1

Nested Loop Left Join (cost=47.01..11,564.43 rows=1 width=100) (actual time=5,532.391..5,747.533 rows=13,821 loops=1)

39. 77.370 5,642.922 ↓ 13,821.0 13,821 1

Hash Right Join (cost=46.58..11,563.96 rows=1 width=101) (actual time=5,532.315..5,642.922 rows=13,821 loops=1)

  • Hash Cond: (owmt.channel_order_marketplace_id = o_1.id)
40. 4,835.079 4,835.079 ↓ 1.2 558,680 1

CTE Scan on orders_with_multiple_types owmt (cost=0.00..9,698.84 rows=484,942 width=8) (actual time=4,415.782..4,835.079 rows=558,680 loops=1)

41. 4.951 730.473 ↓ 13,821.0 13,821 1

Hash (cost=46.57..46.57 rows=1 width=101) (actual time=730.473..730.473 rows=13,821 loops=1)

  • Buckets: 16384 (originally 1024) Batches: 1 (originally 1) Memory Usage: 1606kB
42. 7.362 725.522 ↓ 13,821.0 13,821 1

Nested Loop (cost=1.47..46.57 rows=1 width=101) (actual time=547.313..725.522 rows=13,821 loops=1)

43. 3.760 704.339 ↓ 13,821.0 13,821 1

Hash Join (cost=1.19..46.06 rows=1 width=92) (actual time=547.292..704.339 rows=13,821 loops=1)

  • Hash Cond: (o_1.channel_account_config_id = account_config_1.id)
44. 7.566 700.550 ↓ 260.8 13,821 1

Nested Loop (cost=0.56..45.22 rows=53 width=88) (actual time=547.218..700.550 rows=13,821 loops=1)

45. 554.774 554.774 ↓ 246.8 13,821 1

CTE Scan on orders_total_order (cost=0.00..1.12 rows=56 width=40) (actual time=547.150..554.774 rows=13,821 loops=1)

46. 138.210 138.210 ↑ 1.0 1 13,821

Index Scan using order_marketplace_pkey on order_marketplace o_1 (cost=0.56..0.78 rows=1 width=48) (actual time=0.010..0.010 rows=1 loops=13,821)

  • Index Cond: (id = orders_total_order.id)
  • Filter: (ordered_at >= '2018-05-18 00:19:53+02'::timestamp with time zone)
47. 0.004 0.029 ↓ 3.5 7 1

Hash (cost=0.60..0.60 rows=2 width=12) (actual time=0.029..0.029 rows=7 loops=1)

  • Buckets: 1024 Batches: 1 Memory Usage: 9kB
48. 0.025 0.025 ↓ 3.5 7 1

Index Scan using channel_account_config_ui on account_config account_config_1 (cost=0.28..0.60 rows=2 width=12) (actual time=0.012..0.025 rows=7 loops=1)

  • Index Cond: (common_account_id = 1247)
49. 13.821 13.821 ↑ 1.0 1 13,821

Index Scan using holder_pkey on holder holder_1 (cost=0.28..0.50 rows=1 width=17) (actual time=0.001..0.001 rows=1 loops=13,821)

  • Index Cond: (id = account_config_1.channel_holder_id)
50. 96.747 96.747 ↑ 1.0 1 13,821

Index Scan using order_address_pkey on order_address oa (cost=0.43..0.46 rows=1 width=11) (actual time=0.007..0.007 rows=1 loops=13,821)

  • Index Cond: (id = o_1.billing_address_id)
51. 27.642 27.642 ↑ 1.0 1 13,821

Index Scan using channel_order_marketplace_status_mapping_pkey on order_marketplace_status_mapping omsm_1 (cost=0.28..0.31 rows=1 width=24) (actual time=0.002..0.002 rows=1 loops=13,821)

  • Index Cond: ((channel_holder_slug = (holder_1.slug)::text) AND (marketplace_status = o_1.marketplace_status))
52. 69.105 69.105 ↑ 4.0 1 13,821

Index Only Scan using channel_order_marketplace_id_index on order_marketplace_line oml_1 (cost=0.43..0.50 rows=4 width=8) (actual time=0.005..0.005 rows=1 loops=13,821)

  • Index Cond: (channel_order_marketplace_id = o_1.id)
  • Heap Fetches: 956
53. 15.409 15.409 ↑ 1.0 1 15,409

Index Only Scan using common_account_pkey on account ca_1 (cost=0.28..0.40 rows=1 width=4) (actual time=0.001..0.001 rows=1 loops=15,409)

  • Index Cond: (id = 1247)
  • Heap Fetches: 15409
54.          

CTE surrounding_orders

55. 0.004 5,930.443 ↑ 1.0 2 1

Append (cost=0.03..0.12 rows=2 width=182) (actual time=5,928.356..5,930.443 rows=2 loops=1)

56. 0.000 5,928.354 ↑ 1.0 1 1

Limit (cost=0.03..0.05 rows=1 width=182) (actual time=5,928.353..5,928.354 rows=1 loops=1)

57.          

Initplan (for Limit)

58. 10.622 10.622 ↑ 1.0 1 1

CTE Scan on all_orders (cost=0.00..0.03 rows=1 width=8) (actual time=10.383..10.622 rows=1 loops=1)

  • Filter: ((marketplace_order_id = '312479230-A'::text) AND ((slug)::text = 'carrefour_fr'::text))
  • Rows Removed by Filter: 13820
59. 5,928.352 5,928.352 ↑ 1.0 1 1

CTE Scan on all_orders all_orders_2 (cost=0.00..0.03 rows=1 width=182) (actual time=5,928.352..5,928.352 rows=1 loops=1)

  • Filter: (number = ($16 - 1))
  • Rows Removed by Filter: 13535
60. 0.000 2.085 ↑ 1.0 1 1

Limit (cost=0.03..0.05 rows=1 width=182) (actual time=2.084..2.085 rows=1 loops=1)

61.          

Initplan (for Limit)

62. 0.918 0.918 ↑ 1.0 1 1

CTE Scan on all_orders all_orders_1 (cost=0.00..0.03 rows=1 width=8) (actual time=0.897..0.918 rows=1 loops=1)

  • Filter: ((marketplace_order_id = '312479230-A'::text) AND ((slug)::text = 'carrefour_fr'::text))
  • Rows Removed by Filter: 13820
63. 2.080 2.080 ↑ 1.0 1 1

CTE Scan on all_orders all_orders_3 (cost=0.00..0.03 rows=1 width=182) (actual time=2.080..2.080 rows=1 loops=1)

  • Filter: (number = ($17 + 1))
  • Rows Removed by Filter: 13537
64. 5,930.446 5,930.446 ↑ 1.0 2 1

CTE Scan on surrounding_orders (cost=0.00..0.04 rows=2 width=182) (actual time=5,928.358..5,930.446 rows=2 loops=1)

Planning time : 14.287 ms
Execution time : 6,035.697 ms