explain.depesz.com

PostgreSQL's explain analyze made readable

Result: pEPo

Settings
# exclusive inclusive rows x rows loops node
1. 3.969 72.335 ↑ 15.7 108 1

GroupAggregate (cost=1,265,958.36..1,265,996.45 rows=1,693 width=60) (actual time=68.326..72.335 rows=108 loops=1)

  • Output: apps.bundle_id, apps.platform, jsonb_agg(agregated_with_apps.offers_detail)
  • Group Key: apps.bundle_id, apps.platform
  • Buffers: shared hit=9153 read=3
2.          

CTE steps_conversions

3. 3.538 14.385 ↑ 11,897.4 1,423 1

Group (cost=31,896.14..119,234.14 rows=16,930,000 width=72) (actual time=10.214..14.385 rows=1,423 loops=1)

  • Output: offer_hourly_statistics.offer_id, ((steps.value ->> jsonb_object_keys(steps.value)))::integer, jsonb_object_keys(steps.value), steps.value
  • Group Key: offer_hourly_statistics.offer_id, steps.value
  • Buffers: shared hit=3911
4. 4.056 10.847 ↑ 73.6 2,568 1

Sort (cost=31,896.14..32,368.89 rows=189,100 width=36) (actual time=10.199..10.847 rows=2,568 loops=1)

  • Output: offer_hourly_statistics.offer_id, steps.value
  • Sort Key: offer_hourly_statistics.offer_id, steps.value
  • Sort Method: quicksort Memory: 297kB
  • Buffers: shared hit=3911
5. 3.137 6.791 ↑ 73.6 2,568 1

Nested Loop (cost=0.43..10,149.67 rows=189,100 width=36) (actual time=0.041..6.791 rows=2,568 loops=1)

  • Output: offer_hourly_statistics.offer_id, steps.value
  • Buffers: shared hit=3906
6. 1.713 1.713 ↓ 1.0 1,941 1

Index Scan using index_offer_hourly_statistics_on_start_time_and_offer_id on public.offer_hourly_statistics (cost=0.42..6,367.67 rows=1,891 width=50) (actual time=0.009..1.713 rows=1,941 loops=1)

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

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

  • Output: steps.value
  • Function Call: jsonb_array_elements(offer_hourly_statistics.steps_stat)
8.          

CTE summed_finances

9. 1.500 3.368 ↑ 6.7 253 1

HashAggregate (cost=6,391.31..6,416.70 rows=1,693 width=84) (actual time=3.208..3.368 rows=253 loops=1)

  • Output: offer_hourly_statistics_1.offer_id, sum(offer_hourly_statistics_1.clicks), sum(offer_hourly_statistics_1.installs), sum(offer_hourly_statistics_1.expenses), sum(offer_hourly_statistics_1.revenue)
  • Group Key: offer_hourly_statistics_1.offer_id
  • Buffers: shared hit=3906
10. 1.868 1.868 ↓ 1.0 1,941 1

Index Scan using index_offer_hourly_statistics_on_start_time_and_offer_id on public.offer_hourly_statistics offer_hourly_statistics_1 (cost=0.42..6,367.67 rows=1,891 width=22) (actual time=0.017..1.868 rows=1,941 loops=1)

  • Output: offer_hourly_statistics_1.offer_id, offer_hourly_statistics_1.clicks, offer_hourly_statistics_1.installs, offer_hourly_statistics_1.expenses, offer_hourly_statistics_1.revenue
  • Index Cond: ((offer_hourly_statistics_1.start_time >= '2019-02-03 21:00:00'::timestamp without time zone) AND (offer_hourly_statistics_1.start_time <= '2019-02-04 20:59:59'::timestamp without time zone))
  • Buffers: shared hit=3906
11.          

CTE summed_conversions

12. 1.380 1.814 ↑ 114.0 351 1

HashAggregate (cost=464,940.12..465,440.12 rows=40,000 width=68) (actual time=1.105..1.814 rows=351 loops=1)

  • Output: steps_conversions.offer_id, jsonb_build_object(steps_conversions.step, sum(steps_conversions.step_conversions)), steps_conversions.step
  • Group Key: steps_conversions.offer_id, steps_conversions.step
13. 0.434 0.434 ↑ 11,837.9 1,423 1

CTE Scan on steps_conversions (cost=0.00..338,600.00 rows=16,845,350 width=40) (actual time=0.001..0.434 rows=1,423 loops=1)

  • Output: steps_conversions.offer_id, steps_conversions.step_conversions, steps_conversions.step
  • Filter: (steps_conversions.step IS NOT NULL)
14.          

CTE offer_steps_details

15. 1.907 27.257 ↑ 14.4 519 1

Group (cost=381,878.15..381,990.44 rows=7,486 width=50) (actual time=25.232..27.257 rows=519 loops=1)

  • Output: offer_steps.offer_id, jsonb_build_object('reward', offer_steps.reward, 'our_price', offer_steps.our_price, 'order_index', offer_steps.order_index), offer_steps.reward, offer_steps.our_price, offer_steps.order_index
  • Group Key: offer_steps.offer_id, offer_steps.reward, offer_steps.our_price, offer_steps.order_index
  • Buffers: shared hit=4172
16. 0.595 25.350 ↑ 14.4 519 1

Sort (cost=381,878.15..381,896.87 rows=7,486 width=18) (actual time=25.214..25.350 rows=519 loops=1)

  • Output: offer_steps.offer_id, offer_steps.reward, offer_steps.our_price, offer_steps.order_index
  • Sort Key: offer_steps.offer_id, offer_steps.reward, offer_steps.our_price, offer_steps.order_index
  • Sort Method: quicksort Memory: 65kB
  • Buffers: shared hit=4172
17. 4.550 24.755 ↑ 14.4 519 1

Hash Join (cost=380,929.50..381,396.43 rows=7,486 width=18) (actual time=16.075..24.755 rows=519 loops=1)

  • Output: offer_steps.offer_id, offer_steps.reward, offer_steps.our_price, offer_steps.order_index
  • Hash Cond: (offer_steps.offer_id = steps_conversions_1.offer_id)
  • Buffers: shared hit=4169
18. 4.178 4.178 ↓ 1.0 14,983 1

Seq Scan on public.offer_steps (cost=0.00..407.73 rows=14,973 width=18) (actual time=0.008..4.178 rows=14,983 loops=1)

  • Output: offer_steps.id, offer_steps.event_type, offer_steps.reward, offer_steps.min_time, offer_steps.max_time, offer_steps.offer_id, offer_steps.created_at, offer_steps.updated_at, offer_steps.our_price, offer_steps.order_index, offer_steps.description
  • Buffers: shared hit=258
19. 0.067 16.027 ↓ 1.1 214 1

Hash (cost=380,927.00..380,927.00 rows=200 width=4) (actual time=16.027..16.027 rows=214 loops=1)

  • Output: steps_conversions_1.offer_id
  • Buckets: 1024 Batches: 1 Memory Usage: 16kB
  • Buffers: shared hit=3911
20. 0.570 15.960 ↓ 1.1 214 1

HashAggregate (cost=380,925.00..380,927.00 rows=200 width=4) (actual time=15.906..15.960 rows=214 loops=1)

  • Output: steps_conversions_1.offer_id
  • Group Key: steps_conversions_1.offer_id
  • Buffers: shared hit=3911
21. 15.390 15.390 ↑ 11,897.4 1,423 1

CTE Scan on steps_conversions steps_conversions_1 (cost=0.00..338,600.00 rows=16,930,000 width=4) (actual time=10.216..15.390 rows=1,423 loops=1)

  • Output: steps_conversions_1.offer_id
  • Buffers: shared hit=3911
22.          

CTE aggregated_offer_details

23. 4.484 39.912 ↓ 1.3 253 1

GroupAggregate (cost=4,613.64..290,894.56 rows=200 width=68) (actual time=33.810..39.912 rows=253 loops=1)

  • Output: summed_finances.offer_id, COALESCE(jsonb_agg(DISTINCT summed_conversions.steps_stat) FILTER (WHERE (summed_conversions.steps_stat IS NOT NULL)), '[]'::jsonb), COALESCE(jsonb_agg(DISTINCT offer_steps_details.steps_details) FILTER (WHERE (summed_conversions.steps_stat IS NOT NULL)), '[]'::jsonb)
  • Group Key: summed_finances.offer_id
  • Buffers: shared hit=8078
24. 0.774 35.428 ↑ 10,823.1 1,171 1

Merge Left Join (cost=4,613.64..195,838.06 rows=12,673,800 width=68) (actual time=33.789..35.428 rows=1,171 loops=1)

  • Output: summed_finances.offer_id, summed_conversions.steps_stat, offer_steps_details.steps_details
  • Merge Cond: (summed_finances.offer_id = summed_conversions.offer_id)
  • Buffers: shared hit=8078
25. 0.359 32.121 ↑ 113.6 558 1

Merge Left Join (cost=756.09..1,715.09 rows=63,369 width=36) (actual time=31.548..32.121 rows=558 loops=1)

  • Output: summed_finances.offer_id, offer_steps_details.steps_details
  • Merge Cond: (summed_finances.offer_id = offer_steps_details.offer_id)
  • Buffers: shared hit=8078
26. 0.193 3.729 ↑ 6.7 253 1

Sort (cost=124.65..128.88 rows=1,693 width=4) (actual time=3.647..3.729 rows=253 loops=1)

  • Output: summed_finances.offer_id
  • Sort Key: summed_finances.offer_id
  • Sort Method: quicksort Memory: 36kB
  • Buffers: shared hit=3906
27. 3.536 3.536 ↑ 6.7 253 1

CTE Scan on summed_finances (cost=0.00..33.86 rows=1,693 width=4) (actual time=3.211..3.536 rows=253 loops=1)

  • Output: summed_finances.offer_id
  • Buffers: shared hit=3906
28. 0.360 28.033 ↑ 14.4 519 1

Sort (cost=631.44..650.16 rows=7,486 width=36) (actual time=27.898..28.033 rows=519 loops=1)

  • Output: offer_steps_details.steps_details, offer_steps_details.offer_id
  • Sort Key: offer_steps_details.offer_id
  • Sort Method: quicksort Memory: 97kB
  • Buffers: shared hit=4172
29. 27.673 27.673 ↑ 14.4 519 1

CTE Scan on offer_steps_details (cost=0.00..149.72 rows=7,486 width=36) (actual time=25.235..27.673 rows=519 loops=1)

  • Output: offer_steps_details.steps_details, offer_steps_details.offer_id
  • Buffers: shared hit=4172
30. 0.454 2.533 ↑ 35.4 1,131 1

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

  • Output: summed_conversions.steps_stat, summed_conversions.offer_id
  • Sort Key: summed_conversions.offer_id
  • Sort Method: quicksort Memory: 52kB
31. 2.079 2.079 ↑ 114.0 351 1

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

  • Output: summed_conversions.steps_stat, summed_conversions.offer_id
32.          

CTE agregated_with_apps

33. 21.857 64.277 ↑ 6.7 253 1

Hash Right Join (cost=1,357.50..1,463.31 rows=1,693 width=40) (actual time=42.398..64.277 rows=253 loops=1)

  • Output: offers.app_id, aggregated_offer_details.offer_id, jsonb_build_object(offers.id, jsonb_build_object('expenses', summed_finances_1.expenses, 'revenue', summed_finances_1.revenue, 'installs', summed_finances_1.installs, 'clicks', summed_finances_1.clicks, 'steps', aggregated_offer_details.steps_details, 'steps_stat', aggregated_offer_details.steps_stat, 'source', offers.source, 'reward_after', offers.reward_after, 'reward', offers.reward, 'our_price', offers.our_price, 'price_history', offers.price_history, 'manager_email', offers.manager_email))
  • Hash Cond: (summed_finances_1.offer_id = aggregated_offer_details.offer_id)
  • Buffers: shared hit=8844 read=3
34. 0.073 0.073 ↑ 6.7 253 1

CTE Scan on summed_finances summed_finances_1 (cost=0.00..33.86 rows=1,693 width=84) (actual time=0.001..0.073 rows=253 loops=1)

  • Output: summed_finances_1.offer_id, summed_finances_1.clicks, summed_finances_1.installs, summed_finances_1.expenses, summed_finances_1.revenue
35. 0.226 42.347 ↓ 1.3 253 1

Hash (cost=1,355.00..1,355.00 rows=200 width=222) (actual time=42.347..42.347 rows=253 loops=1)

  • Output: aggregated_offer_details.offer_id, aggregated_offer_details.steps_details, aggregated_offer_details.steps_stat, offers.app_id, offers.id, offers.source, offers.reward_after, offers.reward, offers.our_price, offers.price_history, offers.manager_email
  • Buckets: 1024 Batches: 1 Memory Usage: 109kB
  • Buffers: shared hit=8839
36. 0.416 42.121 ↓ 1.3 253 1

Nested Loop Left Join (cost=0.29..1,355.00 rows=200 width=222) (actual time=33.834..42.121 rows=253 loops=1)

  • Output: aggregated_offer_details.offer_id, aggregated_offer_details.steps_details, aggregated_offer_details.steps_stat, offers.app_id, offers.id, offers.source, offers.reward_after, offers.reward, offers.our_price, offers.price_history, offers.manager_email
  • Buffers: shared hit=8839
37. 40.187 40.187 ↓ 1.3 253 1

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

  • Output: aggregated_offer_details.offer_id, aggregated_offer_details.steps_stat, aggregated_offer_details.steps_details
  • Buffers: shared hit=8078
38. 1.518 1.518 ↑ 1.0 1 253

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

  • Output: offers.app_id, offers.id, offers.source, offers.reward_after, offers.reward, offers.our_price, offers.price_history, offers.manager_email
  • Index Cond: (aggregated_offer_details.offer_id = offers.id)
  • Buffers: shared hit=761
39. 0.660 68.366 ↑ 6.7 253 1

Sort (cost=519.07..523.31 rows=1,693 width=60) (actual time=68.284..68.366 rows=253 loops=1)

  • Output: apps.bundle_id, apps.platform, agregated_with_apps.offers_detail
  • Sort Key: apps.bundle_id, apps.platform
  • Sort Method: quicksort Memory: 271kB
  • Buffers: shared hit=9153 read=3
40. 0.231 67.706 ↑ 6.7 253 1

Hash Left Join (cost=371.14..428.28 rows=1,693 width=60) (actual time=45.283..67.706 rows=253 loops=1)

  • Output: apps.bundle_id, apps.platform, agregated_with_apps.offers_detail
  • Hash Cond: (agregated_with_apps.app_id = apps.id)
  • Buffers: shared hit=9153 read=3
41. 64.611 64.611 ↑ 6.7 253 1

CTE Scan on agregated_with_apps (cost=0.00..33.86 rows=1,693 width=36) (actual time=42.401..64.611 rows=253 loops=1)

  • Output: agregated_with_apps.app_id, agregated_with_apps.offer_id, agregated_with_apps.offers_detail
  • Buffers: shared hit=8844 read=3
42. 1.081 2.864 ↓ 1.0 2,764 1

Hash (cost=336.62..336.62 rows=2,762 width=32) (actual time=2.864..2.864 rows=2,764 loops=1)

  • Output: apps.bundle_id, apps.platform, apps.id
  • Buckets: 4096 Batches: 1 Memory Usage: 212kB
  • Buffers: shared hit=309
43. 1.783 1.783 ↓ 1.0 2,764 1

Seq Scan on public.apps (cost=0.00..336.62 rows=2,762 width=32) (actual time=0.006..1.783 rows=2,764 loops=1)

  • Output: apps.bundle_id, apps.platform, apps.id
  • Buffers: shared hit=309