explain.depesz.com

PostgreSQL's explain analyze made readable

Result: 3A5k

Settings
# exclusive inclusive rows x rows loops node
1. 0.113 326,349.958 ↑ 56.1 14 1

Nested Loop Left Join (cost=34,459,093.28..26,907,812,759.42 rows=785 width=95) (actual time=27,196.320..326,349.958 rows=14 loops=1)

2. 0.080 2,233.745 ↑ 56.1 14 1

Nested Loop Left Join (cost=181,934.12..242,789.01 rows=785 width=65) (actual time=2,231.044..2,233.745 rows=14 loops=1)

3. 0.125 2,232.293 ↑ 50.9 14 1

Hash Left Join (cost=181,933.69..235,794.49 rows=712 width=52) (actual time=2,230.671..2,232.293 rows=14 loops=1)

  • Hash Cond: (o.source_id = s.store_id)
4. 0.111 2,231.616 ↑ 50.9 14 1

Nested Loop Left Join (cost=181,902.84..235,754.65 rows=712 width=52) (actual time=2,230.086..2,231.616 rows=14 loops=1)

5. 0.081 2,231.155 ↑ 50.9 14 1

Nested Loop Left Join (cost=181,902.41..231,794.75 rows=712 width=48) (actual time=2,230.005..2,231.155 rows=14 loops=1)

6. 0.080 2,230.822 ↑ 50.9 14 1

Nested Loop Left Join (cost=181,901.84..196,273.87 rows=712 width=35) (actual time=2,229.940..2,230.822 rows=14 loops=1)

7. 0.091 2,230.266 ↑ 50.9 14 1

Nested Loop (cost=181,901.27..187,678.60 rows=712 width=22) (actual time=2,229.871..2,230.266 rows=14 loops=1)

8. 0.130 2,229.923 ↑ 50.9 14 1

HashAggregate (cost=181,900.84..181,907.96 rows=712 width=12) (actual time=2,229.825..2,229.923 rows=14 loops=1)

  • Group Key: p_1.id
9. 6.272 2,229.793 ↑ 50.9 14 1

Gather (cost=1,001.43..181,899.06 rows=712 width=12) (actual time=2,138.191..2,229.793 rows=14 loops=1)

  • Workers Planned: 2
  • Workers Launched: 2
10. 0.008 2,223.521 ↑ 59.4 5 3 / 3

Nested Loop (cost=1.43..180,827.86 rows=297 width=12) (actual time=2,132.204..2,223.521 rows=5 loops=3)

11. 2,221.185 2,223.438 ↑ 59.4 5 3 / 3

Nested Loop (cost=1.00..178,951.36 rows=297 width=8) (actual time=2,132.158..2,223.438 rows=5 loops=3)

  • -> Parallel Index Scan using stb_product_state_when_start_expecting_production on stb_product_state ps (cost=0.43..142615.62 rows=6985 width=4) (actual time=2131.180..2220.981 rows=751 loops=3 (...)
  • Filter: (((when_start)::date >= '2020-04-18'::date) AND ((when_start)::date <= '2020-04-18'::date))
  • Rows Removed by Filter: 1225851
12. 2.253 2.253 ↓ 0.0 0 2,253 / 3

Index Only Scan using stb_data_int_property_id_value_target_id_idx on stb_data_int b (cost=0.57..5.19 rows=1 width=4) (actual time=0.003..0.003 rows=0 loops=2,253)

  • Index Cond: ((property_id = 706) AND (value = 1) AND (target_id = ps.product_id))
  • Heap Fetches: 7
13. 0.075 0.075 ↑ 1.0 1 14 / 3

Index Only Scan using stb_product_id_order_id_idx on stb_product p_1 (cost=0.43..6.32 rows=1 width=4) (actual time=0.016..0.016 rows=1 loops=14)

  • Index Cond: (id = b.target_id)
  • Heap Fetches: 7
14. 0.252 0.252 ↑ 1.0 1 14

Index Scan using stb_product_id_order_id_idx on stb_product p (cost=0.43..8.10 rows=1 width=22) (actual time=0.018..0.018 rows=1 loops=14)

  • Index Cond: (id = b.target_id)
15. 0.476 0.476 ↑ 2.0 1 14

Index Scan using "stb_data_text_IDX_prop_id_target_id" on stb_data_text bh (cost=0.57..12.05 rows=2 width=17) (actual time=0.031..0.034 rows=1 loops=14)

  • Index Cond: ((property_id = 710) AND (target_id = p.id))
16. 0.252 0.252 ↑ 12.0 1 14

Index Scan using "stb_data_text_IDX_prop_id_target_id" on stb_data_text bsku (cost=0.57..49.77 rows=12 width=17) (actual time=0.013..0.018 rows=1 loops=14)

  • Index Cond: ((property_id = 508) AND (target_id = p.id))
17. 0.350 0.350 ↑ 1.0 1 14

Index Only Scan using stb_order_id_source_id_idx on stb_order o (cost=0.43..5.56 rows=1 width=8) (actual time=0.025..0.025 rows=1 loops=14)

  • Index Cond: (id = p.order_id)
  • Heap Fetches: 2
18. 0.216 0.552 ↑ 1.0 749 1

Hash (cost=21.49..21.49 rows=749 width=8) (actual time=0.552..0.552 rows=749 loops=1)

  • Buckets: 1024 Batches: 1 Memory Usage: 38kB
19. 0.336 0.336 ↑ 1.0 749 1

Seq Scan on am_stores s (cost=0.00..21.49 rows=749 width=8) (actual time=0.019..0.336 rows=749 loops=1)

20. 1.372 1.372 ↑ 18.0 1 14

Index Only Scan using stb_data_text_target_id_p_170_value_r on stb_data_text soid (cost=0.43..9.64 rows=18 width=17) (actual time=0.094..0.098 rows=1 loops=14)

  • Index Cond: (target_id = o.id)
  • Heap Fetches: 14
21. 4.018 324,116.100 ↑ 1.0 1 14

Aggregate (cost=34,277,159.16..34,277,159.17 rows=1 width=32) (actual time=23,151.150..23,151.150 rows=1 loops=14)

22. 3,609.102 324,112.082 ↑ 162,181,723.2 5 14

Merge Right Join (cost=967.99..16,031,715.30 rows=810,908,616 width=300) (actual time=23,116.334..23,150.863 rows=5 loops=14)

  • Merge Cond: (sw.target_id = h.target_id)
23. 40,544.546 40,544.546 ↑ 1.0 3,816,649 14

Index Only Scan using stb_data_text_value_target_id_property_id_p_187 on stb_data_text sw (cost=0.43..1,193,204.53 rows=3,854,026 width=17) (actual time=0.037..2,896.039 rows=3,816,649 loops=14)

  • Heap Fetches: 5612326
24. 0.182 279,958.434 ↑ 6,186,836.2 5 14

Materialize (cost=967.56..2,742,581.91 rows=30,934,181 width=287) (actual time=19,977.417..19,997.031 rows=5 loops=14)

25. 8,647.002 279,958.252 ↑ 6,186,836.2 5 14

Merge Left Join (cost=967.56..2,665,246.46 rows=30,934,181 width=287) (actual time=19,977.409..19,997.018 rows=5 loops=14)

  • Merge Cond: (h.target_id = sh.target_id)
26. 1.568 102.550 ↑ 230,932.2 5 14

Merge Left Join (cost=967.13..757,914.64 rows=1,154,661 width=274) (actual time=5.487..7.325 rows=5 loops=14)

  • Merge Cond: (h.target_id = pp.target_id)
27. 6.720 91.112 ↑ 230,932.2 5 14

Merge Left Join (cost=491.19..754,538.02 rows=1,154,661 width=173) (actual time=4.674..6.508 rows=5 loops=14)

  • Merge Cond: (h.target_id = r.target_id)
28. 0.159 33.278 ↑ 230,932.2 5 14

Nested Loop Left Join (cost=15.24..751,161.40 rows=1,154,661 width=72) (actual time=0.556..2.377 rows=5 loops=14)

29. 0.112 32.382 ↑ 8,295.2 5 14

Nested Loop Left Join (cost=14.81..60,581.83 rows=41,476 width=59) (actual time=0.542..2.313 rows=5 loops=14)

30. 0.254 31.332 ↑ 338.8 5 14

Nested Loop Left Join (cost=14.38..34,186.76 rows=1,694 width=46) (actual time=0.512..2.238 rows=5 loops=14)

31. 0.159 30.408 ↑ 338.8 5 14

Nested Loop Left Join (cost=13.95..19,876.70 rows=1,694 width=32) (actual time=0.501..2.172 rows=5 loops=14)

32. 0.186 29.512 ↑ 24.2 5 14

Nested Loop (cost=13.38..13,599.15 rows=121 width=19) (actual time=0.492..2.108 rows=5 loops=14)

33. 0.427 6.412 ↑ 3.8 5 14

Nested Loop Left Join (cost=12.95..60.86 rows=19 width=16) (actual time=0.277..0.458 rows=5 loops=14)

34. 3.640 3.640 ↑ 2.2 5 14

Index Only Scan using stb_data_text_value_target_id_p_710_value on stb_data_text h (cost=0.41..12.62 rows=11 width=4) (actual time=0.244..0.260 rows=5 loops=14)

  • Index Cond: (value = bh.value)
  • Filter: (target_id <> p.id)
  • Rows Removed by Filter: 1
  • Heap Fetches: 75
35. 1.057 2.345 ↑ 2.0 1 67

Hash Right Join (cost=12.54..15.76 rows=2 width=16) (actual time=0.031..0.035 rows=1 loops=67)

  • Hash Cond: (cf.ct_name = f.value)
36. 0.350 0.350 ↑ 1.0 81 35

Seq Scan on ct_frame cf (cost=0.00..2.81 rows=81 width=12) (actual time=0.002..0.010 rows=81 loops=35)

37. 0.134 0.938 ↑ 2.0 1 67

Hash (cost=12.51..12.51 rows=2 width=17) (actual time=0.014..0.014 rows=1 loops=67)

  • Buckets: 1024 Batches: 1 Memory Usage: 8kB
38. 0.804 0.804 ↑ 2.0 1 67

Index Scan using "stb_data_text_IDX_prop_id_target_id" on stb_data_text f (cost=0.57..12.51 rows=2 width=17) (actual time=0.012..0.012 rows=1 loops=67)

  • Index Cond: ((property_id = 181) AND (target_id = h.target_id))
39. 1.541 22.914 ↑ 136.0 1 67

Nested Loop (cost=0.43..711.18 rows=136 width=7) (actual time=0.201..0.342 rows=1 loops=67)

40. 3.149 3.149 ↑ 1.0 136 67

Seq Scan on ct_technology ct (cost=0.00..5.71 rows=136 width=7) (actual time=0.002..0.047 rows=136 loops=67)

  • Filter: (suffix <> 'set'::text)
  • Rows Removed by Filter: 1
41. 18.224 18.224 ↓ 0.0 0 9,112

Index Only Scan using stb_data_int_property_id_value_target_id_idx_p_186_tv on stb_data_int t (cost=0.43..5.18 rows=1 width=12) (actual time=0.002..0.002 rows=0 loops=9,112)

  • Index Cond: ((target_id = h.target_id) AND (value = ct.id))
  • Heap Fetches: 67
42. 0.737 0.737 ↑ 12.0 1 67

Index Scan using "stb_data_text_IDX_prop_id_target_id" on stb_data_text sku (cost=0.57..51.76 rows=12 width=17) (actual time=0.010..0.011 rows=1 loops=67)

  • Index Cond: ((property_id = 508) AND (target_id = h.target_id))
43. 0.670 0.670 ↑ 1.0 1 67

Index Scan using stb_product_id_order_id_idx on stb_product hp (cost=0.43..8.45 rows=1 width=18) (actual time=0.010..0.010 rows=1 loops=67)

  • Index Cond: (id = h.target_id)
44. 0.938 0.938 ↑ 21.0 1 67

Index Scan using stb_data_text_target_id_p_184 on stb_data_text pat (cost=0.43..15.37 rows=21 width=17) (actual time=0.013..0.014 rows=1 loops=67)

  • Index Cond: (target_id = h.target_id)
45. 0.737 0.737 ↑ 24.0 1 67

Index Scan using stb_data_text_target_id_p_192 on stb_data_text parts (cost=0.43..16.41 rows=24 width=17) (actual time=0.010..0.011 rows=1 loops=67)

  • Index Cond: (target_id = h.target_id)
46. 9.296 51.114 ↓ 46.1 5,481 14

Sort (cost=475.94..476.24 rows=119 width=105) (actual time=3.182..3.651 rows=5,481 loops=14)

  • Sort Key: r.target_id
  • Sort Method: quicksort Memory: 1057kB
47. 41.818 41.818 ↓ 51.7 6,148 1

Index Scan using "stb_data_text_IDX_prop_id_value" on stb_data_text r (cost=0.70..471.84 rows=119 width=105) (actual time=0.100..41.818 rows=6,148 loops=1)

  • Index Cond: ((property_id = 179) AND (value = 'rand'::text))
48. 2.153 9.870 ↓ 10.3 1,231 14

Sort (cost=475.94..476.24 rows=119 width=105) (actual time=0.592..0.705 rows=1,231 loops=14)

  • Sort Key: pp.target_id
  • Sort Method: quicksort Memory: 231kB
49. 7.717 7.717 ↓ 10.9 1,297 1

Index Scan using "stb_data_text_IDX_prop_id_value" on stb_data_text pp (cost=0.70..471.84 rows=119 width=105) (actual time=0.059..7.717 rows=1,297 loops=1)

  • Index Cond: ((property_id = 179) AND (value = 'passe_partout'::text))
50. 8,260.882 271,208.700 ↑ 1.0 3,816,649 14

Materialize (cost=0.43..1,450,279.49 rows=3,938,814 width=17) (actual time=0.040..19,372.050 rows=3,816,649 loops=14)

51. 262,947.818 262,947.818 ↑ 1.0 3,816,649 14

Index Scan using stb_data_text_target_id_p_182 on stb_data_text sh (cost=0.43..1,440,432.45 rows=3,938,814 width=17) (actual time=0.038..18,781.987 rows=3,816,649 loops=14)

Planning time : 71.490 ms
Execution time : 326,350.442 ms