explain.depesz.com

PostgreSQL's explain analyze made readable

Result: v0HN

Settings
# exclusive inclusive rows x rows loops node
1. 3.967 58.353 ↑ 12.9 108 1

GroupAggregate (cost=1,044,899.19..1,044,933.94 rows=1,390 width=78) (actual time=54.342..58.353 rows=108 loops=1)

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

CTE steps_conversions

3. 3.339 13.827 ↑ 9,768.1 1,423 1

Group (cost=25,426.20..97,108.70 rows=13,900,000 width=72) (actual time=9.777..13.827 rows=1,423 loops=1)

  • Group Key: offer_hourly_statistics.offer_id, steps.value
4. 4.012 10.488 ↑ 59.2 2,568 1

Sort (cost=25,426.20..25,806.20 rows=152,000 width=36) (actual time=9.765..10.488 rows=2,568 loops=1)

  • Sort Key: offer_hourly_statistics.offer_id, steps.value
  • Sort Method: quicksort Memory: 297kB
5. 2.884 6.476 ↑ 59.2 2,568 1

Nested Loop (cost=0.43..8,185.78 rows=152,000 width=36) (actual time=0.031..6.476 rows=2,568 loops=1)

6. 1.651 1.651 ↓ 1.3 1,941 1

Index Scan using index_offer_hourly_statistics_on_start_time_and_offer_id on offer_hourly_statistics (cost=0.42..5,145.78 rows=1,520 width=50) (actual time=0.009..1.651 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.464 3.355 ↑ 5.5 253 1

HashAggregate (cost=5,164.78..5,185.63 rows=1,390 width=84) (actual time=3.236..3.355 rows=253 loops=1)

  • Group Key: offer_hourly_statistics_1.offer_id
10. 1.891 1.891 ↓ 1.3 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,145.78 rows=1,520 width=22) (actual time=0.021..1.891 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.308 1.948 ↑ 114.0 351 1

Sort (cost=385,586.29..385,686.29 rows=40,000 width=72) (actual time=1.802..1.948 rows=351 loops=1)

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

HashAggregate (cost=381,728.75..382,528.75 rows=40,000 width=72) (actual time=1.047..1.640 rows=351 loops=1)

  • Group Key: steps_conversions.offer_id, steps_conversions.step
14. 0.438 0.438 ↑ 9,719.3 1,423 1

CTE Scan on steps_conversions (cost=0.00..278,000.00 rows=13,830,500 width=40) (actual time=0.001..0.438 rows=1,423 loops=1)

  • Filter: (step IS NOT NULL)
15.          

CTE offer_steps_details

16. 0.386 26.647 ↑ 14.5 519 1

Sort (cost=314,360.08..314,378.87 rows=7,516 width=54) (actual time=26.528..26.647 rows=519 loops=1)

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

Group (cost=313,707.10..313,876.21 rows=7,516 width=54) (actual time=24.200..26.261 rows=519 loops=1)

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

Sort (cost=313,707.10..313,725.89 rows=7,516 width=18) (actual time=24.175..24.310 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.252 23.718 ↑ 14.5 519 1

Hash Join (cost=312,754.50..313,223.23 rows=7,516 width=18) (actual time=15.439..23.718 rows=519 loops=1)

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

Seq Scan on offer_steps (cost=0.00..409.31 rows=15,031 width=18) (actual time=0.016..4.086 rows=15,048 loops=1)

21. 0.076 15.380 ↓ 1.1 214 1

Hash (cost=312,752.00..312,752.00 rows=200 width=4) (actual time=15.379..15.380 rows=214 loops=1)

  • Buckets: 1024 Batches: 1 Memory Usage: 16kB
22. 0.565 15.304 ↓ 1.1 214 1

HashAggregate (cost=312,750.00..312,752.00 rows=200 width=4) (actual time=15.263..15.304 rows=214 loops=1)

  • Group Key: steps_conversions_1.offer_id
23. 14.739 14.739 ↑ 9,768.1 1,423 1

CTE Scan on steps_conversions steps_conversions_1 (cost=0.00..278,000.00 rows=13,900,000 width=4) (actual time=9.780..14.739 rows=1,423 loops=1)

24.          

CTE aggregated_offer_details

25. 5.169 39.846 ↓ 1.3 253 1

GroupAggregate (cost=4,592.10..240,578.18 rows=200 width=68) (actual time=33.045..39.846 rows=253 loops=1)

  • Group Key: summed_finances.offer_id
26. 0.766 34.677 ↑ 8,921.6 1,171 1

Merge Left Join (cost=4,592.10..162,221.18 rows=10,447,200 width=68) (actual time=33.027..34.677 rows=1,171 loops=1)

  • Merge Cond: (summed_finances.offer_id = summed_conversions.offer_id)
27. 0.372 31.413 ↑ 93.6 558 1

Merge Left Join (cost=734.55..1,525.04 rows=52,236 width=36) (actual time=30.797..31.413 rows=558 loops=1)

  • Merge Cond: (summed_finances.offer_id = offer_steps_details.offer_id)
28. 0.193 3.721 ↑ 5.5 253 1

Sort (cost=100.36..103.84 rows=1,390 width=4) (actual time=3.627..3.721 rows=253 loops=1)

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

CTE Scan on summed_finances (cost=0.00..27.80 rows=1,390 width=4) (actual time=3.238..3.528 rows=253 loops=1)

30. 0.421 27.320 ↑ 14.5 519 1

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

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

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

32. 0.376 2.498 ↑ 35.4 1,131 1

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

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

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

34.          

CTE agregated_with_apps

35. 7.762 50.053 ↑ 5.5 253 1

Hash Left Join (cost=45.46..1,470.70 rows=1,390 width=40) (actual time=33.285..50.053 rows=253 loops=1)

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

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

37. 40.103 40.103 ↓ 1.3 253 1

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

38. 1.771 1.771 ↑ 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.007 rows=1 loops=253)

  • Index Cond: (aggregated_offer_details.offer_id = id)
39. 0.088 0.166 ↑ 5.5 253 1

Hash (cost=27.80..27.80 rows=1,390 width=84) (actual time=0.166..0.166 rows=253 loops=1)

  • Buckets: 2048 Batches: 1 Memory Usage: 33kB
40. 0.078 0.078 ↑ 5.5 253 1

CTE Scan on summed_finances summed_finances_1 (cost=0.00..27.80 rows=1,390 width=84) (actual time=0.001..0.078 rows=253 loops=1)

41. 0.882 54.386 ↑ 5.5 253 1

Sort (cost=490.82..494.30 rows=1,390 width=78) (actual time=54.298..54.386 rows=253 loops=1)

  • Sort Key: apps.bundle_id, apps.store_id, apps.platform
  • Sort Method: quicksort Memory: 291kB
42. 0.243 53.504 ↑ 5.5 253 1

Hash Left Join (cost=371.35..418.26 rows=1,390 width=78) (actual time=36.127..53.504 rows=253 loops=1)

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

CTE Scan on agregated_with_apps (cost=0.00..27.80 rows=1,390 width=36) (actual time=33.288..50.445 rows=253 loops=1)

44. 1.107 2.816 ↑ 1.0 2,771 1

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

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

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