explain.depesz.com

PostgreSQL's explain analyze made readable

Result: ExG3

Settings
# exclusive inclusive rows x rows loops node
1. 0.103 58.759 ↑ 1.9 108 1

Hash Left Join (cost=820,172.04..820,178.79 rows=200 width=78) (actual time=57.929..58.759 rows=108 loops=1)

  • Hash Cond: (active_apps.app_id = apps.id)
2.          

CTE active_offers_ids

3. 0.750 2.529 ↑ 5.5 253 1

HashAggregate (cost=184.86..198.76 rows=1,390 width=4) (actual time=2.450..2.529 rows=253 loops=1)

  • Group Key: offer_hourly_statistics.offer_id
4. 1.779 1.779 ↓ 1.3 1,941 1

Index Only Scan using index_offer_hourly_statistics_on_start_time_and_offer_id on offer_hourly_statistics (cost=0.42..181.06 rows=1,520 width=4) (actual time=0.033..1.779 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))
  • Heap Fetches: 3882
5.          

CTE active_offers

6. 0.295 4.234 ↑ 5.5 253 1

Nested Loop Left Join (cost=0.29..6,313.65 rows=1,390 width=1,619) (actual time=2.474..4.234 rows=253 loops=1)

7. 2.674 2.674 ↑ 5.5 253 1

CTE Scan on active_offers_ids (cost=0.00..27.80 rows=1,390 width=4) (actual time=2.451..2.674 rows=253 loops=1)

8. 1.265 1.265 ↑ 1.0 1 253

Index Scan using offers_pkey on offers (cost=0.29..4.51 rows=1 width=1,615) (actual time=0.005..0.005 rows=1 loops=253)

  • Index Cond: (active_offers_ids.offer_id = id)
9.          

CTE steps_conversions

10. 3.267 13.749 ↑ 9,768.1 1,423 1

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

  • Group Key: offer_hourly_statistics_1.offer_id, steps.value
11. 3.918 10.482 ↑ 59.2 2,568 1

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

  • Sort Key: offer_hourly_statistics_1.offer_id, steps.value
  • Sort Method: quicksort Memory: 297kB
12. 2.901 6.564 ↑ 59.2 2,568 1

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

13. 1.722 1.722 ↓ 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=50) (actual time=0.011..1.722 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))
14. 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)

15.          

CTE summed_finances

16. 1.410 3.090 ↑ 5.5 253 1

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

  • Group Key: offer_hourly_statistics_2.offer_id
17. 1.680 1.680 ↓ 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_2 (cost=0.42..5,145.78 rows=1,520 width=22) (actual time=0.023..1.680 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))
18.          

CTE summed_conversions

19. 0.212 16.336 ↑ 114.0 351 1

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

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

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

  • Group Key: steps_conversions.offer_id, steps_conversions.step
21. 14.802 14.802 ↑ 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=9.836..14.802 rows=1,423 loops=1)

  • Filter: (step IS NOT NULL)
22.          

CTE offer_steps_details

23. 0.424 12.306 ↑ 14.5 519 1

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

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

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

  • Group Key: offer_steps.offer_id, offer_steps.reward, offer_steps.our_price, offer_steps.order_index
25. 0.611 9.949 ↑ 14.5 519 1

Sort (cost=313,707.10..313,725.89 rows=7,516 width=18) (actual time=9.810..9.949 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
26. 4.468 9.338 ↑ 14.5 519 1

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

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

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

28. 0.054 0.947 ↓ 1.1 214 1

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

  • Buckets: 1024 Batches: 1 Memory Usage: 16kB
29. 0.511 0.893 ↓ 1.1 214 1

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

  • Group Key: steps_conversions_1.offer_id
30. 0.382 0.382 ↑ 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=0.001..0.382 rows=1,423 loops=1)

31.          

CTE aggregated_steps_by_ofers

32. 4.461 39.002 ↓ 31.1 218 1

GroupAggregate (cost=1,803.08..10,207.03 rows=7 width=68) (actual time=33.200..39.002 rows=218 loops=1)

  • Group Key: active_offers.offer_id
33. 0.588 34.541 ↑ 321.8 1,136 1

Merge Left Join (cost=1,803.08..7,465.15 rows=365,570 width=68) (actual time=33.167..34.541 rows=1,136 loops=1)

  • Merge Cond: (active_offers.offer_id = offer_steps_details.offer_id)
34. 0.330 20.940 ↑ 27.4 355 1

Merge Left Join (cost=1,168.89..1,321.84 rows=9,730 width=36) (actual time=20.425..20.940 rows=355 loops=1)

  • Merge Cond: (active_offers.offer_id = summed_finances.offer_id)
35. 0.215 17.175 ↑ 3.9 355 1

Sort (cost=1,068.52..1,072.02 rows=1,400 width=36) (actual time=17.080..17.175 rows=355 loops=1)

  • Sort Key: active_offers.offer_id
  • Sort Method: quicksort Memory: 52kB
36. 0.218 16.960 ↑ 3.9 355 1

Hash Right Join (cost=31.36..995.36 rows=1,400 width=36) (actual time=16.483..16.960 rows=355 loops=1)

  • Hash Cond: (summed_conversions.offer_id = active_offers.offer_id)
37. 16.514 16.514 ↑ 114.0 351 1

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

38. 0.057 0.228 ↓ 31.1 218 1

Hash (cost=31.27..31.27 rows=7 width=4) (actual time=0.227..0.228 rows=218 loops=1)

  • Buckets: 1024 Batches: 1 Memory Usage: 16kB
39. 0.171 0.171 ↓ 31.1 218 1

CTE Scan on active_offers (cost=0.00..31.27 rows=7 width=4) (actual time=0.006..0.171 rows=218 loops=1)

  • Filter: ((reward_after)::text = 'retention'::text)
  • Rows Removed by Filter: 35
40. 0.191 3.435 ↑ 3.6 390 1

Sort (cost=100.36..103.84 rows=1,390 width=4) (actual time=3.340..3.435 rows=390 loops=1)

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

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

42. 0.433 13.013 ↑ 6.6 1,132 1

Sort (cost=634.19..652.98 rows=7,516 width=36) (actual time=12.739..13.013 rows=1,132 loops=1)

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

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

44.          

CTE aggregated_json_offers

45. 5.263 50.541 ↑ 38.2 253 1

Merge Left Join (cost=200.97..477.78 rows=9,660 width=36) (actual time=44.774..50.541 rows=253 loops=1)

  • Merge Cond: (active_offers_1.offer_id = summed_finances_1.offer_id)
46. 0.237 44.992 ↑ 5.5 253 1

Merge Left Join (cost=100.60..108.29 rows=1,390 width=684) (actual time=44.542..44.992 rows=253 loops=1)

  • Merge Cond: (active_offers_1.offer_id = aggregated_steps_by_ofers.offer_id)
47. 0.296 5.335 ↑ 5.5 253 1

Sort (cost=100.36..103.84 rows=1,390 width=620) (actual time=5.218..5.335 rows=253 loops=1)

  • Sort Key: active_offers_1.offer_id
  • Sort Method: quicksort Memory: 86kB
48. 5.039 5.039 ↑ 5.5 253 1

CTE Scan on active_offers active_offers_1 (cost=0.00..27.80 rows=1,390 width=620) (actual time=2.480..5.039 rows=253 loops=1)

49. 0.211 39.420 ↓ 31.1 218 1

Sort (cost=0.24..0.26 rows=7 width=68) (actual time=39.321..39.420 rows=218 loops=1)

  • Sort Key: aggregated_steps_by_ofers.offer_id
  • Sort Method: quicksort Memory: 118kB
50. 39.209 39.209 ↓ 31.1 218 1

CTE Scan on aggregated_steps_by_ofers (cost=0.00..0.14 rows=7 width=68) (actual time=33.201..39.209 rows=218 loops=1)

51. 0.206 0.286 ↑ 5.5 253 1

Sort (cost=100.36..103.84 rows=1,390 width=84) (actual time=0.192..0.286 rows=253 loops=1)

  • Sort Key: summed_finances_1.offer_id
  • Sort Method: quicksort Memory: 44kB
52. 0.080 0.080 ↑ 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.002..0.080 rows=253 loops=1)

53.          

CTE active_apps

54. 4.865 55.684 ↑ 1.9 108 1

HashAggregate (cost=241.50..244.00 rows=200 width=36) (actual time=55.055..55.684 rows=108 loops=1)

  • Group Key: aggregated_json_offers.app_id
55. 50.819 50.819 ↑ 38.2 253 1

CTE Scan on aggregated_json_offers (cost=0.00..193.20 rows=9,660 width=36) (actual time=44.775..50.819 rows=253 loops=1)

56. 55.818 55.818 ↑ 1.9 108 1

CTE Scan on active_apps (cost=0.00..4.00 rows=200 width=36) (actual time=55.059..55.818 rows=108 loops=1)

57. 1.103 2.838 ↑ 1.0 2,771 1

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

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

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