explain.depesz.com

PostgreSQL's explain analyze made readable

Result: hWPt

Settings
# exclusive inclusive rows x rows loops node
1. 3.759 55.024 ↑ 14.6 100 1

GroupAggregate (cost=1,096,131.89..1,096,168.39 rows=1,460 width=78) (actual time=51.208..55.024 rows=100 loops=1)

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

CTE steps_conversions

3. 3.330 13.853 ↑ 10,451.0 1,397 1

Group (cost=26,886.66..102,184.66 rows=14,600,000 width=72) (actual time=9.923..13.853 rows=1,397 loops=1)

  • Group Key: offer_hourly_statistics.offer_id, steps.value
4. 3.824 10.523 ↑ 61.6 2,602 1

Sort (cost=26,886.66..27,287.66 rows=160,400 width=36) (actual time=9.908..10.523 rows=2,602 loops=1)

  • Sort Key: offer_hourly_statistics.offer_id, steps.value
  • Sort Method: quicksort Memory: 300kB
5. 2.869 6.699 ↑ 61.6 2,602 1

Nested Loop (cost=0.43..8,630.02 rows=160,400 width=36) (actual time=0.032..6.699 rows=2,602 loops=1)

6. 1.838 1.838 ↓ 1.2 1,992 1

Index Scan using index_offer_hourly_statistics_on_start_time_and_offer_id on offer_hourly_statistics (cost=0.42..5,422.02 rows=1,604 width=50) (actual time=0.010..1.838 rows=1,992 loops=1)

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

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,992)

8.          

CTE summed_finances

9. 1.415 3.323 ↑ 5.8 251 1

HashAggregate (cost=5,442.07..5,463.97 rows=1,460 width=84) (actual time=3.195..3.323 rows=251 loops=1)

  • Group Key: offer_hourly_statistics_1.offer_id
10. 1.908 1.908 ↓ 1.2 1,992 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,422.02 rows=1,604 width=22) (actual time=0.021..1.908 rows=1,992 loops=1)

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

CTE summed_conversions

12. 0.200 1.831 ↑ 114.6 349 1

Sort (cost=404,810.04..404,910.04 rows=40,000 width=72) (actual time=1.755..1.831 rows=349 loops=1)

  • Sort Key: ((split_part(steps_conversions.step, '_'::text, 2))::integer)
  • Sort Method: quicksort Memory: 52kB
13. 1.195 1.631 ↑ 114.6 349 1

HashAggregate (cost=400,952.50..401,752.50 rows=40,000 width=72) (actual time=1.041..1.631 rows=349 loops=1)

  • Group Key: steps_conversions.offer_id, steps_conversions.step
14. 0.436 0.436 ↑ 10,398.7 1,397 1

CTE Scan on steps_conversions (cost=0.00..292,000.00 rows=14,527,000 width=40) (actual time=0.002..0.436 rows=1,397 loops=1)

  • Filter: (step IS NOT NULL)
15.          

CTE offer_steps_details

16. 0.342 26.739 ↑ 15.0 500 1

Sort (cost=330,103.31..330,122.03 rows=7,486 width=54) (actual time=26.618..26.739 rows=500 loops=1)

  • Sort Key: ((split_part((offer_steps.order_index)::text, '_'::text, 2))::integer)
  • Sort Method: quicksort Memory: 95kB
17. 1.877 26.397 ↑ 15.0 500 1

Group (cost=329,453.15..329,621.59 rows=7,486 width=54) (actual time=24.425..26.397 rows=500 loops=1)

  • Group Key: offer_steps.offer_id, offer_steps.reward, offer_steps.our_price, offer_steps.order_index
18. 0.581 24.520 ↑ 15.0 500 1

Sort (cost=329,453.15..329,471.87 rows=7,486 width=18) (actual time=24.392..24.520 rows=500 loops=1)

  • Sort Key: offer_steps.offer_id, offer_steps.reward, offer_steps.our_price, offer_steps.order_index
  • Sort Method: quicksort Memory: 64kB
19. 4.619 23.939 ↑ 15.0 500 1

Hash Join (cost=328,504.50..328,971.43 rows=7,486 width=18) (actual time=15.438..23.939 rows=500 loops=1)

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

Seq Scan on offer_steps (cost=0.00..407.73 rows=14,973 width=18) (actual time=0.011..3.931 rows=15,036 loops=1)

21. 0.051 15.389 ↓ 1.0 206 1

Hash (cost=328,502.00..328,502.00 rows=200 width=4) (actual time=15.388..15.389 rows=206 loops=1)

  • Buckets: 1024 Batches: 1 Memory Usage: 16kB
22. 0.535 15.338 ↓ 1.0 206 1

HashAggregate (cost=328,500.00..328,502.00 rows=200 width=4) (actual time=15.278..15.338 rows=206 loops=1)

  • Group Key: steps_conversions_1.offer_id
23. 14.803 14.803 ↑ 10,451.0 1,397 1

CTE Scan on steps_conversions steps_conversions_1 (cost=0.00..292,000.00 rows=14,600,000 width=4) (actual time=9.925..14.803 rows=1,397 loops=1)

24.          

CTE aggregated_offer_details

25. 5.140 39.799 ↓ 1.3 251 1

GroupAggregate (cost=4,594.92..251,477.56 rows=200 width=68) (actual time=33.014..39.799 rows=251 loops=1)

  • Group Key: summed_finances.offer_id
26. 0.780 34.659 ↑ 9,169.1 1,192 1

Merge Left Join (cost=4,594.92..169,502.56 rows=10,929,600 width=68) (actual time=32.994..34.659 rows=1,192 loops=1)

  • Merge Cond: (summed_finances.offer_id = summed_conversions.offer_id)
27. 0.360 31.456 ↑ 100.3 545 1

Merge Left Join (cost=737.38..1,564.40 rows=54,648 width=36) (actual time=30.853..31.456 rows=545 loops=1)

  • Merge Cond: (summed_finances.offer_id = offer_steps_details.offer_id)
28. 0.174 3.757 ↑ 5.8 251 1

Sort (cost=105.94..109.59 rows=1,460 width=4) (actual time=3.680..3.757 rows=251 loops=1)

  • Sort Key: summed_finances.offer_id
  • Sort Method: quicksort Memory: 36kB
29. 3.583 3.583 ↑ 5.8 251 1

CTE Scan on summed_finances (cost=0.00..29.20 rows=1,460 width=4) (actual time=3.198..3.583 rows=251 loops=1)

30. 0.366 27.339 ↑ 15.0 500 1

Sort (cost=631.44..650.16 rows=7,486 width=36) (actual time=27.168..27.339 rows=500 loops=1)

  • Sort Key: offer_steps_details.offer_id
  • Sort Method: quicksort Memory: 95kB
31. 26.973 26.973 ↑ 15.0 500 1

CTE Scan on offer_steps_details (cost=0.00..149.72 rows=7,486 width=36) (actual time=26.620..26.973 rows=500 loops=1)

32. 0.396 2.423 ↑ 34.9 1,146 1

Sort (cost=3,857.54..3,957.54 rows=40,000 width=36) (actual time=2.139..2.423 rows=1,146 loops=1)

  • Sort Key: summed_conversions.offer_id
  • Sort Method: quicksort Memory: 52kB
33. 2.027 2.027 ↑ 114.6 349 1

CTE Scan on summed_conversions (cost=0.00..800.00 rows=40,000 width=36) (actual time=1.756..2.027 rows=349 loops=1)

34.          

CTE agregated_with_apps

35. 5.442 47.316 ↑ 5.8 251 1

Hash Left Join (cost=47.74..1,476.30 rows=1,460 width=40) (actual time=33.244..47.316 rows=251 loops=1)

  • Hash Cond: (aggregated_offer_details.offer_id = summed_finances_1.offer_id)
36. 0.435 41.716 ↓ 1.3 251 1

Nested Loop Left Join (cost=0.29..1,359.00 rows=200 width=222) (actual time=33.046..41.716 rows=251 loops=1)

37. 40.026 40.026 ↓ 1.3 251 1

CTE Scan on aggregated_offer_details (cost=0.00..4.00 rows=200 width=68) (actual time=33.016..40.026 rows=251 loops=1)

38. 1.255 1.255 ↑ 1.0 1 251

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

  • Index Cond: (aggregated_offer_details.offer_id = id)
39. 0.079 0.158 ↑ 5.8 251 1

Hash (cost=29.20..29.20 rows=1,460 width=84) (actual time=0.158..0.158 rows=251 loops=1)

  • Buckets: 2048 Batches: 1 Memory Usage: 33kB
40. 0.079 0.079 ↑ 5.8 251 1

CTE Scan on summed_finances summed_finances_1 (cost=0.00..29.20 rows=1,460 width=84) (actual time=0.002..0.079 rows=251 loops=1)

41. 0.596 51.265 ↑ 5.8 251 1

Sort (cost=497.34..500.99 rows=1,460 width=78) (actual time=51.173..51.265 rows=251 loops=1)

  • Sort Key: apps.bundle_id, apps.store_id, apps.platform
  • Sort Method: quicksort Memory: 274kB
42. 0.252 50.669 ↑ 5.8 251 1

Hash Left Join (cost=371.32..420.60 rows=1,460 width=78) (actual time=36.067..50.669 rows=251 loops=1)

  • Hash Cond: (agregated_with_apps.app_id = apps.id)
43. 47.605 47.605 ↑ 5.8 251 1

CTE Scan on agregated_with_apps (cost=0.00..29.20 rows=1,460 width=36) (actual time=33.246..47.605 rows=251 loops=1)

44. 1.112 2.812 ↑ 1.0 2,770 1

Hash (cost=336.70..336.70 rows=2,770 width=50) (actual time=2.811..2.812 rows=2,770 loops=1)

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

Seq Scan on apps (cost=0.00..336.70 rows=2,770 width=50) (actual time=0.010..1.700 rows=2,770 loops=1)