explain.depesz.com

PostgreSQL's explain analyze made readable

Result: wxKr

Settings
# exclusive inclusive rows x rows loops node
1. 0.000 5.273 ↓ 0.0 2,997 1

GroupAggregate (cost=15171520.96..15221424.50 rows=201 width=2350) (actual time=80721.222..80926.988 rows=27 loops=1)97 width=43) (cost=0..0 rows=0 width=0) (actual time=5.273..5.273 rows=2,997 loops=1)

  • Group Key: t.day Buckets: 4,096 Batches: 1 Memory Usage: 263kB
  • Filter: (t.day IS NOT NULL) Hash Cond: (l.offer_id = f.id)
2. 4.157 4.157 ↑ 1.0 2,997 1

Group Key: () -> Hash Join (cost=83.53..205.71 rows=2,997 width=43) (actual time=1.794..4.157 rows=2,997 loops=1)

3. 0.571 0.571 ↑ 1.0 2,997 1

Rows Removed by Filter: 2 -> Seq Scan on landings l (cost=0.00..80.97 rows=2,997 width=16) (actual time=0.008..0.571 rows=2,997 loops=1)

4. 0.732 1.745 ↓ 0.0 1,757 1

Sort (cost=15171520.96..15172834.15 rows=525274 width=2350) (actual time=80717.286..80718.068 rows=5307 loops=1)7 rows=1757 width=45) (cost=0..0 rows=0 width=0) (actual time=1.745..1.745 rows=1,757 loops=1)

  • Sort Key: t.day Buckets: 2,048 Batches: 1 Memory Usage: 154kB
  • -> Subquery Scan on t (cost=3,343,737.32..14809295.31 rows=525,274 width=2,350) (actual time=75,441.829..80712.189 rows=5,307 loops=1).0.031 rows=24 loops=1)
5. 0.000 1.013 ↑ 1.0 1,757 1

Sort Method: quicksort Memory: 4392kB -> Seq Scan on offers f (cost=0.00..61.57 rows=1,757 width=45) (actual time=0.010..1.013 rows=1,757 loops=1)

6. 80,664.494 80,707.102 ↑ 99.0 5,307 1

GroupAggregate (cost=3,343,737.32..14,804,042.57 rows=525,274 width=256) (actual time=75,441.791..80,707.102 rows=5,307 loops=1)

  • Group Key: () Buckets: 16,384 Batches: 1 Memory Usage: 683kB
  • -> Sort (cost=3,343,737.32..3344735.45 rows=399,251 width=256) (actual time=75,441.465..75629.135 rows=447,519 loops=1)1 loops=447,519)
7. 0.017 0.017 ↑ 1.0 24 1

Group Key: f.name, (date_trunc('day'::text, timezone('Europe/Moscow'::text, o.created_at)))sons cr (cost=0.00..2.24 rows=24 width=77) (actual time=0.005..0.017 rows=24 loops=1)

8. 8.459 8.459 ↓ 1.0 14,201 1

Group Key: f.name -> Hash (cost=524.75..524.75 rows=13,775 width=8) (actual time=8.459..8.459 rows=14,201 loops=1)

9. 0.000 4.274 ↓ 1.0 14,201 1

Sort Key: (date_trunc('day'::text, timezone('Europe/Moscow'::text, o.created_at))) (cost=0.00..524.75 rows=13,775 width=8) (actual time=0.009..4.274 rows=14,201 loops=1)

  • Group Key: (date_trunc('day'::text, timezone('Europe/Moscow'::text, o.created_at)))l time=0.127..0.131 rows=1 loops=447,519)
  • Sort Method: external merge Disk: 133,824kB(id = o.id) AND (id = o.id))
  • -> Nested Loop Left Join (cost=727,484.79..3306593.13 rows=399,251 width=256) (actual time=7,572.803..73753.501 rows=447,519 loops=1).000 rows=0 loops=447,519)
10. 0.000 3,580.152 ↓ 0.0 1 447,519

Sort Key: f.name, (date_trunc('day'::text, timezone('Europe/Moscow'::text, o.created_at)))ws=1 width=67) (cost=0..0 rows=0 width=0) (actual time=0.003..0.008 rows=1 loops=447,519)

  • Join Filter: (r.id = c.region_id)d: (o1.region = name)
  • Rows Removed by Join Filter: 59,072,508h_at IS NOT NULL)
11. 0.000 67,753.559 ↓ 1.1 447,519 1

Nested Loop Left Join (cost=727,484.79..2,514,076.25 rows=399,251 width=272) (actual time=7,572.727..67,753.559 rows=447,519 loops=1)

  • Rows Removed by Join Filter: 443,922city)
12. 54,597.318 54,597.318 ↓ 0.0 0 447,519

Join Filter: ((r.trash_at < o1.created_at) OR (c.trash_at < o.created_at))id on delivery_cities c (cost=0.42..0.47 rows=3 width=42) (actual time=0.122..0.122 rows=0 loops=447,519)

13. 688.888 8,980.314 ↓ 1.1 447,519 1

Hash Right Join (cost=727,483.66..1,441,493.55 rows=399,251 width=248) (actual time=7,572.620..8,980.314 rows=447,519 loops=1)

  • Hash Cond: (tl.order_id = o.id)
14. 2,538.452 2,538.452 ↓ 1.1 537,636 1

Index Scan using order_attributes_order_id_name_idx on order_attributes tl (cost=0.56..712,086.20 rows=485,832 width=42) (actual time=681.528..2,538.452 rows=537,636 loops=1)

  • Index Cond: (name = 'transfered_to_logistics'::text)(actual time=0.008..0.016 rows=132 loops=1)
15. 237.300 5,752.974 ↓ 1.1 447,519 1

Hash (cost=722,492.46..722,492.46 rows=399,251 width=214) (actual time=5,752.974..5,752.974 rows=447,519 loops=1)

16. 0.000 0.000 ↓ 0.0 0

Buckets: 524288 Batches: 1 Memory Usage: 138779kB rows=1 width=0) (cost=0..0 rows=0 width=0) (never executed)

17. 1,149.710 5,515.674 ↓ 1.1 447,519 1

Hash Right Join (cost=208,588.07..722,492.46 rows=399,251 width=214) (actual time=3,475.086..5,515.674 rows=447,519 loops=1)

  • Hash Cond: (oatl.order_id = o.id)
18. 1,542.353 2,485.728 ↓ 1.0 4,622,692 1

Bitmap Heap Scan on order_attributes oatl (cost=6,843.89..502,889.34 rows=4,508,996 width=42) (actual time=1,033.775..2,485.728 rows=4,622,692 loops=1)

  • Heap Blocks: exact=375,695
19. 0.355 0.355 ↓ 0.0 145 1

Recheck Cond: (name = 'lead'::text)6445.84 rows=7822 width=8) (cost=0..0 rows=0 width=0) (actual time=0.013..0.355 rows=145 loops=1)

20. 943.020 943.020 ↓ 1.0 4,653,298 1

Bitmap Index Scan on order_attributes_lead_support_id (cost=0.00..5,716.64 rows=4,508,996 width=0) (actual time=943.020..943.020 rows=4,653,298 loops=1)

21. 234.384 1,880.236 ↓ 1.1 447,519 1

Hash (cost=196,753.54..196,753.54 rows=399,251 width=180) (actual time=1,880.236..1,880.236 rows=447,519 loops=1)

  • Buckets: 524,288 Batches: 1 Memory Usage: 83,837kB
22. 113.820 1,645.852 ↓ 1.1 447,519 1

Hash Left Join (cost=943.09..196,753.54 rows=399,251 width=180) (actual time=15.648..1,645.852 rows=447,519 loops=1)

  • Hash Cond: (o.id = ccr.cold_order_id)
23. 118.351 1,523.573 ↓ 1.1 447,519 1

Hash Left Join (cost=246.15..194,556.51 rows=399,251 width=180) (actual time=7.075..1,523.573 rows=447,519 loops=1)

  • Hash Cond: (o.cancelation_reason_id = cr.id)
24. 389.813 1,405.191 ↓ 1.1 447,519 1

Hash Join (cost=243.61..190,519.81 rows=399,251 width=111) (actual time=7.028..1,405.191 rows=447,519 loops=1)

25. 0.000 0.000 ↓ 0.0 0

Hash Cond: (o.landing_id = l.id)idth=0) (cost=0..0 rows=0 width=0) (never executed)

26. 1,010.105 1,010.105 ↓ 1.1 447,519 1

Index Scan using orders_created_at1 on orders o (cost=0.44..184,786.93 rows=399,251 width=126) (actual time=1.708..1,010.105 rows=447,519 loops=1)

  • Index Cond: ((created_at >= '2020-06-30 21:00:00+00'::timestamp with time zone) AND (created_at <= '2020-07-31 20:59:59+00'::timestamp with time zone))
  • Filter: (is_cold = 0)
  • Rows Removed by Filter: 4,290
27. 1.116 5.273 ↑ 1.0 2,997 1

Hash (cost=205.71..205.71 rows=2,997 width=43) (actual time=5.273..5.273 rows=2,997 loops=1)

  • Buckets: 4,096 Batches: 1 Memory Usage: 263kB
28. 1.841 4.157 ↑ 1.0 2,997 1

Hash Join (cost=83.53..205.71 rows=2,997 width=43) (actual time=1.794..4.157 rows=2,997 loops=1)

  • Hash Cond: (l.offer_id = f.id)
29. 0.571 0.571 ↑ 1.0 2,997 1

Seq Scan on landings l (cost=0.00..80.97 rows=2,997 width=16) (actual time=0.008..0.571 rows=2,997 loops=1)

30. 0.732 1.745 ↑ 1.0 1,757 1

Hash (cost=61.57..61.57 rows=1,757 width=45) (actual time=1.745..1.745 rows=1,757 loops=1)

  • Buckets: 2,048 Batches: 1 Memory Usage: 154kB
31. 1.013 1.013 ↑ 1.0 1,757 1

Seq Scan on offers f (cost=0.00..61.57 rows=1,757 width=45) (actual time=0.010..1.013 rows=1,757 loops=1)

32. 0.014 0.031 ↑ 1.0 24 1

Hash (cost=2.24..2.24 rows=24 width=77) (actual time=0.031..0.031 rows=24 loops=1)

  • Buckets: 1,024 Batches: 1 Memory Usage: 11kB
33. 0.017 0.017 ↑ 1.0 24 1

Seq Scan on cancelation_reasons cr (cost=0.00..2.24 rows=24 width=77) (actual time=0.005..0.017 rows=24 loops=1)

34. 4.185 8.459 ↓ 1.0 14,201 1

Hash (cost=524.75..524.75 rows=13,775 width=8) (actual time=8.459..8.459 rows=14,201 loops=1)

  • Buckets: 16,384 Batches: 1 Memory Usage: 683kB
35. 4.274 4.274 ↓ 1.0 14,201 1

Seq Scan on resales ccr (cost=0.00..524.75 rows=13,775 width=8) (actual time=0.009..4.274 rows=14,201 loops=1)

36. 0.000 58,624.989 ↑ 1.0 1 447,519

Nested Loop Left Join (cost=1.13..2.67 rows=1 width=48) (actual time=0.127..0.131 rows=1 loops=447,519)

37. 447.519 4,027.671 ↑ 1.0 1 447,519

Nested Loop Left Join (cost=0.71..2.17 rows=1 width=51) (actual time=0.004..0.009 rows=1 loops=447,519)

38. 3,580.152 3,580.152 ↑ 1.0 1 447,519

Index Scan using orders_pkey on orders o1 (cost=0.56..2.00 rows=1 width=67) (actual time=0.003..0.008 rows=1 loops=447,519)

  • Index Cond: ((id = o.id) AND (id = o.id))
39. 0.000 0.000 ↓ 0.0 0 447,519

Index Scan using regions_name_key on regions r (cost=0.14..0.16 rows=1 width=49) (actual time=0.000..0.000 rows=0 loops=447,519)

  • Index Cond: (o1.region = name)
  • Filter: (trash_at IS NOT NULL)
  • Rows Removed by Filter: 0
40. 54,597.318 54,597.318 ↓ 0.0 0 447,519

Index Scan using delivery_cities_unique_name_region_id_district_id on delivery_cities c (cost=0.42..0.47 rows=3 width=42) (actual time=0.122..0.122 rows=0 loops=447,519)

  • Index Cond: (name = o1.city)
  • Filter: (trash_at IS NOT NULL)
  • Rows Removed by Filter: 1
41. 2,237.579 2,237.595 ↑ 1.0 132 447,519

Materialize (cost=0.00..3.98 rows=132 width=0) (actual time=0.000..0.005 rows=132 loops=447,519)

42. 0.016 0.016 ↑ 1.0 132 1

Seq Scan on regions d_r (cost=0.00..3.32 rows=132 width=0) (actual time=0.008..0.016 rows=132 loops=1)

43.          

SubPlan (for GroupAggregate)

44. 0.000 0.000 ↓ 0.0 0

Index Scan using idx_order_tags_order_id on order_tags ot (cost=0.43..3.34 rows=1 width=0) (never executed)

  • Index Cond: (order_id = o.id)
  • Filter: (tag_id = 296)
45. 0.355 0.355 ↑ 53.9 145 1

Index Scan using idx_order_tags_tag_id on order_tags ot_1 (cost=0.43..6,445.84 rows=7,822 width=8) (actual time=0.013..0.355 rows=145 loops=1)

  • Index Cond: (tag_id = 296)
46. 0.000 0.000 ↓ 0.0 0

Index Scan using idx_order_tags_order_id on order_tags ot_2 (cost=0.43..3.34 rows=1 width=0) (never executed)

  • Index Cond: (order_id = o.id)
  • Filter: (tag_id = 296)
47. 0.366 0.366 ↑ 53.9 145 1

Index Scan using idx_order_tags_tag_id on order_tags ot_3 (cost=0.43..6,445.84 rows=7,822 width=8) (actual time=0.016..0.366 rows=145 loops=1)

  • Index Cond: (tag_id = 296)
48. 0.000 0.000 ↓ 0.0 0

Index Scan using idx_order_tags_order_id on order_tags ot_4 (cost=0.43..3.34 rows=1 width=0) (never executed)

  • Index Cond: (order_id = o.id)
  • Filter: (tag_id = 296)
49. 0.389 0.389 ↑ 53.9 145 1

Index Scan using idx_order_tags_tag_id on order_tags ot_5 (cost=0.43..6,445.84 rows=7,822 width=8) (actual time=0.021..0.389 rows=145 loops=1)

  • Index Cond: (tag_id = 296)
50. 0.000 0.000 ↓ 0.0 0

Index Scan using idx_order_tags_order_id on order_tags ot_6 (cost=0.43..3.34 rows=1 width=0) (never executed)

  • Index Cond: (order_id = o.id)
  • Filter: (tag_id = 296)
51. 28.748 28.748 ↑ 53.9 145 1

Index Scan using idx_order_tags_tag_id on order_tags ot_7 (cost=0.43..6,445.84 rows=7,822 width=8) (actual time=1.749..28.748 rows=145 loops=1)

  • Index Cond: (tag_id = 296)
Planning time : 15.009 ms
Execution time : 80,975.043 ms