explain.depesz.com

PostgreSQL's explain analyze made readable

Result: YkOJ

Settings
# exclusive inclusive rows x rows loops node
1. 3.957 55.936 ↑ 13.4 108 1

GroupAggregate (cost=1,083,681.03..1,083,717.08 rows=1,442 width=78) (actual time=51.920..55.936 rows=108 loops=1)

  • Group Key: apps.bundle_id, apps.store_id, apps.platform
2.          

CTE steps_conversions

3. 3.260 13.661 ↑ 10,133.5 1,423 1

Group (cost=26,498.69..100,866.69 rows=14,420,000 width=72) (actual time=9.758..13.661 rows=1,423 loops=1)

  • Group Key: offer_hourly_statistics.offer_id, steps.value
4. 3.962 10.401 ↑ 61.6 2,568 1

Sort (cost=26,498.69..26,894.19 rows=158,200 width=36) (actual time=9.742..10.401 rows=2,568 loops=1)

  • Sort Key: offer_hourly_statistics.offer_id, steps.value
  • Sort Method: quicksort Memory: 297kB
5. 2.831 6.439 ↑ 61.6 2,568 1

Nested Loop (cost=0.43..8,511.02 rows=158,200 width=36) (actual time=0.031..6.439 rows=2,568 loops=1)

6. 1.667 1.667 ↓ 1.2 1,941 1

Index Scan using index_offer_hourly_statistics_on_start_time_and_offer_id on offer_hourly_statistics (cost=0.42..5,347.02 rows=1,582 width=50) (actual time=0.010..1.667 rows=1,941 loops=1)

  • Index Cond: ((start_time >= '2019-02-03 21:00:00'::timestamp without time zone) AND (start_time <= '2019-02-04 20:59:59'::timestamp without time zone))
7. 1.941 1.941 ↑ 100.0 1 1,941

Function Scan on jsonb_array_elements steps (cost=0.00..1.00 rows=100 width=32) (actual time=0.001..0.001 rows=1 loops=1,941)

8.          

CTE summed_finances

9. 1.435 3.273 ↑ 5.7 253 1

HashAggregate (cost=5,366.79..5,388.42 rows=1,442 width=84) (actual time=3.155..3.273 rows=253 loops=1)

  • Group Key: offer_hourly_statistics_1.offer_id
10. 1.838 1.838 ↓ 1.2 1,941 1

Index Scan using index_offer_hourly_statistics_on_start_time_and_offer_id on offer_hourly_statistics offer_hourly_statistics_1 (cost=0.42..5,347.02 rows=1,582 width=22) (actual time=0.023..1.838 rows=1,941 loops=1)

  • Index Cond: ((start_time >= '2019-02-03 21:00:00'::timestamp without time zone) AND (start_time <= '2019-02-04 20:59:59'::timestamp without time zone))
11.          

CTE summed_conversions

12. 0.245 1.901 ↑ 114.0 351 1

Sort (cost=399,866.79..399,966.79 rows=40,000 width=72) (actual time=1.795..1.901 rows=351 loops=1)

  • Sort Key: ((split_part(steps_conversions.step, '_'::text, 2))::integer)
  • Sort Method: quicksort Memory: 52kB
13. 1.194 1.656 ↑ 114.0 351 1

HashAggregate (cost=396,009.25..396,809.25 rows=40,000 width=72) (actual time=1.063..1.656 rows=351 loops=1)

  • Group Key: steps_conversions.offer_id, steps_conversions.step
14. 0.462 0.462 ↑ 10,082.9 1,423 1

CTE Scan on steps_conversions (cost=0.00..288,400.00 rows=14,347,900 width=40) (actual time=0.002..0.462 rows=1,423 loops=1)

  • Filter: (step IS NOT NULL)
15.          

CTE offer_steps_details

16. 0.451 26.508 ↑ 14.5 519 1

Sort (cost=326,060.08..326,078.87 rows=7,516 width=54) (actual time=26.322..26.508 rows=519 loops=1)

  • Sort Key: ((split_part((offer_steps.order_index)::text, '_'::text, 2))::integer)
  • Sort Method: quicksort Memory: 97kB
17. 1.917 26.057 ↑ 14.5 519 1

Group (cost=325,407.10..325,576.21 rows=7,516 width=54) (actual time=24.033..26.057 rows=519 loops=1)

  • Group Key: offer_steps.offer_id, offer_steps.reward, offer_steps.our_price, offer_steps.order_index
18. 0.609 24.140 ↑ 14.5 519 1

Sort (cost=325,407.10..325,425.89 rows=7,516 width=18) (actual time=24.004..24.140 rows=519 loops=1)

  • Sort Key: offer_steps.offer_id, offer_steps.reward, offer_steps.our_price, offer_steps.order_index
  • Sort Method: quicksort Memory: 65kB
19. 4.332 23.531 ↑ 14.5 519 1

Hash Join (cost=324,454.50..324,923.23 rows=7,516 width=18) (actual time=15.299..23.531 rows=519 loops=1)

  • Hash Cond: (offer_steps.offer_id = steps_conversions_1.offer_id)
20. 3.958 3.958 ↓ 1.0 15,047 1

Seq Scan on offer_steps (cost=0.00..409.31 rows=15,031 width=18) (actual time=0.010..3.958 rows=15,047 loops=1)

21. 0.058 15.241 ↓ 1.1 214 1

Hash (cost=324,452.00..324,452.00 rows=200 width=4) (actual time=15.241..15.241 rows=214 loops=1)

  • Buckets: 1024 Batches: 1 Memory Usage: 16kB
22. 0.540 15.183 ↓ 1.1 214 1

HashAggregate (cost=324,450.00..324,452.00 rows=200 width=4) (actual time=15.122..15.183 rows=214 loops=1)

  • Group Key: steps_conversions_1.offer_id
23. 14.643 14.643 ↑ 10,133.5 1,423 1

CTE Scan on steps_conversions steps_conversions_1 (cost=0.00..288,400.00 rows=14,420,000 width=4) (actual time=9.760..14.643 rows=1,423 loops=1)

24.          

CTE aggregated_offer_details

25. 5.141 39.557 ↓ 1.3 253 1

GroupAggregate (cost=4,596.23..249,409.77 rows=200 width=68) (actual time=32.742..39.557 rows=253 loops=1)

  • Group Key: summed_finances.offer_id
26. 0.758 34.416 ↑ 9,255.3 1,171 1

Merge Left Join (cost=4,596.23..168,121.77 rows=10,838,000 width=68) (actual time=32.724..34.416 rows=1,171 loops=1)

  • Merge Cond: (summed_finances.offer_id = summed_conversions.offer_id)
27. 0.384 31.123 ↑ 97.1 558 1

Merge Left Join (cost=738.69..1,558.75 rows=54,190 width=36) (actual time=30.500..31.123 rows=558 loops=1)

  • Merge Cond: (summed_finances.offer_id = offer_steps_details.offer_id)
28. 0.166 3.602 ↑ 5.7 253 1

Sort (cost=104.50..108.11 rows=1,442 width=4) (actual time=3.532..3.602 rows=253 loops=1)

  • Sort Key: summed_finances.offer_id
  • Sort Method: quicksort Memory: 36kB
29. 3.436 3.436 ↑ 5.7 253 1

CTE Scan on summed_finances (cost=0.00..28.84 rows=1,442 width=4) (actual time=3.158..3.436 rows=253 loops=1)

30. 0.398 27.137 ↑ 14.5 519 1

Sort (cost=634.19..652.98 rows=7,516 width=36) (actual time=26.963..27.137 rows=519 loops=1)

  • Sort Key: offer_steps_details.offer_id
  • Sort Method: quicksort Memory: 97kB
31. 26.739 26.739 ↑ 14.5 519 1

CTE Scan on offer_steps_details (cost=0.00..150.32 rows=7,516 width=36) (actual time=26.323..26.739 rows=519 loops=1)

32. 0.444 2.535 ↑ 35.4 1,131 1

Sort (cost=3,857.54..3,957.54 rows=40,000 width=36) (actual time=2.222..2.535 rows=1,131 loops=1)

  • Sort Key: summed_conversions.offer_id
  • Sort Method: quicksort Memory: 52kB
33. 2.091 2.091 ↑ 114.0 351 1

CTE Scan on summed_conversions (cost=0.00..800.00 rows=40,000 width=36) (actual time=1.797..2.091 rows=351 loops=1)

34.          

CTE agregated_with_apps

35. 5.835 47.842 ↑ 5.7 253 1

Hash Left Join (cost=47.15..1,474.81 rows=1,442 width=40) (actual time=32.994..47.842 rows=253 loops=1)

  • Hash Cond: (aggregated_offer_details.offer_id = summed_finances_1.offer_id)
36. 0.488 41.836 ↓ 1.3 253 1

Nested Loop Left Join (cost=0.29..1,359.00 rows=200 width=222) (actual time=32.774..41.836 rows=253 loops=1)

37. 39.830 39.830 ↓ 1.3 253 1

CTE Scan on aggregated_offer_details (cost=0.00..4.00 rows=200 width=68) (actual time=32.745..39.830 rows=253 loops=1)

38. 1.518 1.518 ↑ 1.0 1 253

Index Scan using offers_pkey on offers (cost=0.29..6.76 rows=1 width=154) (actual time=0.006..0.006 rows=1 loops=253)

  • Index Cond: (aggregated_offer_details.offer_id = id)
39. 0.096 0.171 ↑ 5.7 253 1

Hash (cost=28.84..28.84 rows=1,442 width=84) (actual time=0.171..0.171 rows=253 loops=1)

  • Buckets: 2048 Batches: 1 Memory Usage: 33kB
40. 0.075 0.075 ↑ 5.7 253 1

CTE Scan on summed_finances summed_finances_1 (cost=0.00..28.84 rows=1,442 width=84) (actual time=0.001..0.075 rows=253 loops=1)

41. 0.717 51.979 ↑ 5.7 253 1

Sort (cost=495.68..499.28 rows=1,442 width=78) (actual time=51.882..51.979 rows=253 loops=1)

  • Sort Key: apps.bundle_id, apps.store_id, apps.platform
  • Sort Method: quicksort Memory: 291kB
42. 0.296 51.262 ↑ 5.7 253 1

Hash Left Join (cost=371.35..420.01 rows=1,442 width=78) (actual time=35.793..51.262 rows=253 loops=1)

  • Hash Cond: (agregated_with_apps.app_id = apps.id)
43. 48.194 48.194 ↑ 5.7 253 1

CTE Scan on agregated_with_apps (cost=0.00..28.84 rows=1,442 width=36) (actual time=32.996..48.194 rows=253 loops=1)

44. 1.090 2.772 ↑ 1.0 2,771 1

Hash (cost=336.71..336.71 rows=2,771 width=50) (actual time=2.771..2.772 rows=2,771 loops=1)

  • Buckets: 4096 Batches: 1 Memory Usage: 260kB
45. 1.682 1.682 ↑ 1.0 2,771 1

Seq Scan on apps (cost=0.00..336.71 rows=2,771 width=50) (actual time=0.009..1.682 rows=2,771 loops=1)