explain.depesz.com

PostgreSQL's explain analyze made readable

Result: wJNA

Settings
# exclusive inclusive rows x rows loops node
1. 0.024 40.066 ↑ 55.2 91 1

Subquery Scan on stats (cost=1,919.85..2,158.25 rows=5,019 width=128) (actual time=35.924..40.066 rows=91 loops=1)

  • Output: stats.offer_id, stats.app_id, stats.clicks, stats.installs, stats.completions, stats.expenses, stats.revenue, stats.steps_stats
  • Buffers: shared hit=1205
2.          

CTE clicks

3. 0.003 0.016 ↓ 0.0 0 1

GroupAggregate (cost=8.17..8.19 rows=1 width=12) (actual time=0.015..0.016 rows=0 loops=1)

  • Output: offer_clicks.offer_id, count(offer_clicks.id)
  • Group Key: offer_clicks.offer_id
  • Buffers: shared hit=1
4. 0.005 0.013 ↓ 0.0 0 1

Sort (cost=8.17..8.17 rows=1 width=8) (actual time=0.013..0.013 rows=0 loops=1)

  • Output: offer_clicks.offer_id, offer_clicks.id
  • Sort Key: offer_clicks.offer_id
  • Sort Method: quicksort Memory: 25kB
  • Buffers: shared hit=1
5. 0.008 0.008 ↓ 0.0 0 1

Index Scan using index_offer_clicks_on_created_at on public.offer_clicks (cost=0.14..8.16 rows=1 width=8) (actual time=0.008..0.008 rows=0 loops=1)

  • Output: offer_clicks.offer_id, offer_clicks.id
  • Index Cond: ((offer_clicks.created_at >= '2019-01-13 21:00:00'::timestamp without time zone) AND (offer_clicks.created_at <= '2019-01-14 20:59:59'::timestamp without time zone))
  • Buffers: shared hit=1
6.          

CTE installs

7. 0.001 0.021 ↓ 0.0 0 1

GroupAggregate (cost=8.17..8.19 rows=1 width=12) (actual time=0.021..0.021 rows=0 loops=1)

  • Output: postbacks.offer_id, count(postbacks.id)
  • Group Key: postbacks.offer_id
  • Buffers: shared hit=1
8. 0.015 0.020 ↓ 0.0 0 1

Sort (cost=8.17..8.17 rows=1 width=8) (actual time=0.020..0.020 rows=0 loops=1)

  • Output: postbacks.offer_id, postbacks.id
  • Sort Key: postbacks.offer_id
  • Sort Method: quicksort Memory: 25kB
  • Buffers: shared hit=1
9. 0.005 0.005 ↓ 0.0 0 1

Index Scan using index_postbacks_on_created_at on public.postbacks (cost=0.14..8.16 rows=1 width=8) (actual time=0.005..0.005 rows=0 loops=1)

  • Output: postbacks.offer_id, postbacks.id
  • Index Cond: ((postbacks.created_at >= '2019-01-13 21:00:00'::timestamp without time zone) AND (postbacks.created_at <= '2019-01-14 20:59:59'::timestamp without time zone))
  • Buffers: shared hit=1
10.          

CTE completions

11. 0.002 0.043 ↓ 0.0 0 1

GroupAggregate (cost=8.17..8.19 rows=1 width=12) (actual time=0.043..0.043 rows=0 loops=1)

  • Output: completed_offers.offer_id, count(completed_offers.id)
  • Group Key: completed_offers.offer_id
  • Buffers: shared hit=1
12. 0.033 0.041 ↓ 0.0 0 1

Sort (cost=8.17..8.18 rows=1 width=8) (actual time=0.041..0.041 rows=0 loops=1)

  • Output: completed_offers.offer_id, completed_offers.id
  • Sort Key: completed_offers.offer_id
  • Sort Method: quicksort Memory: 25kB
  • Buffers: shared hit=1
13. 0.008 0.008 ↓ 0.0 0 1

Index Scan using index_completed_offers_on_created_at on public.completed_offers (cost=0.14..8.16 rows=1 width=8) (actual time=0.008..0.008 rows=0 loops=1)

  • Output: completed_offers.offer_id, completed_offers.id
  • Index Cond: ((completed_offers.created_at >= '2019-01-13 21:00:00'::timestamp without time zone) AND (completed_offers.created_at <= '2019-01-14 20:59:59'::timestamp without time zone))
  • Filter: completed_offers.rewarded
  • Buffers: shared hit=1
14.          

CTE expenses

15. 0.001 0.048 ↓ 0.0 0 1

GroupAggregate (cost=12.71..12.73 rows=1 width=36) (actual time=0.048..0.048 rows=0 loops=1)

  • Output: financial_transactions.offer_id, sum(financial_transactions.amount)
  • Group Key: financial_transactions.offer_id
  • Buffers: shared hit=1
16. 0.042 0.047 ↓ 0.0 0 1

Sort (cost=12.71..12.72 rows=1 width=18) (actual time=0.047..0.047 rows=0 loops=1)

  • Output: financial_transactions.offer_id, financial_transactions.amount
  • Sort Key: financial_transactions.offer_id
  • Sort Method: quicksort Memory: 25kB
  • Buffers: shared hit=1
17. 0.002 0.005 ↓ 0.0 0 1

Bitmap Heap Scan on public.financial_transactions (cost=4.20..12.70 rows=1 width=18) (actual time=0.005..0.005 rows=0 loops=1)

  • Output: financial_transactions.offer_id, financial_transactions.amount
  • Recheck Cond: ((financial_transactions.created_at >= '2019-01-13 21:00:00'::timestamp without time zone) AND (financial_transactions.created_at <= '2019-01-14 20:59:59'::timestamp without time zone))
  • Filter: ((NOT financial_transactions.archived) AND (financial_transactions.transaction_type = 0))
  • Buffers: shared hit=1
18. 0.003 0.003 ↓ 0.0 0 1

Bitmap Index Scan on index_financial_transactions_on_created_at (cost=0.00..4.20 rows=5 width=0) (actual time=0.003..0.003 rows=0 loops=1)

  • Index Cond: ((financial_transactions.created_at >= '2019-01-13 21:00:00'::timestamp without time zone) AND (financial_transactions.created_at <= '2019-01-14 20:59:59'::timestamp without time zone))
  • Buffers: shared hit=1
19.          

CTE revenue

20. 0.000 0.031 ↓ 0.0 0 1

GroupAggregate (cost=12.71..12.73 rows=1 width=36) (actual time=0.031..0.031 rows=0 loops=1)

  • Output: financial_transactions_1.offer_id, sum(financial_transactions_1.amount)
  • Group Key: financial_transactions_1.offer_id
  • Buffers: shared hit=1
21. 0.027 0.031 ↓ 0.0 0 1

Sort (cost=12.71..12.72 rows=1 width=18) (actual time=0.031..0.031 rows=0 loops=1)

  • Output: financial_transactions_1.offer_id, financial_transactions_1.amount
  • Sort Key: financial_transactions_1.offer_id
  • Sort Method: quicksort Memory: 25kB
  • Buffers: shared hit=1
22. 0.002 0.004 ↓ 0.0 0 1

Bitmap Heap Scan on public.financial_transactions financial_transactions_1 (cost=4.20..12.70 rows=1 width=18) (actual time=0.004..0.004 rows=0 loops=1)

  • Output: financial_transactions_1.offer_id, financial_transactions_1.amount
  • Recheck Cond: ((financial_transactions_1.created_at >= '2019-01-13 21:00:00'::timestamp without time zone) AND (financial_transactions_1.created_at <= '2019-01-14 20:59:59'::timestamp without time zone))
  • Filter: ((NOT financial_transactions_1.archived) AND (financial_transactions_1.transaction_type = 1))
  • Buffers: shared hit=1
23. 0.002 0.002 ↓ 0.0 0 1

Bitmap Index Scan on index_financial_transactions_on_created_at (cost=0.00..4.20 rows=5 width=0) (actual time=0.002..0.002 rows=0 loops=1)

  • Index Cond: ((financial_transactions_1.created_at >= '2019-01-13 21:00:00'::timestamp without time zone) AND (financial_transactions_1.created_at <= '2019-01-14 20:59:59'::timestamp without time zone))
  • Buffers: shared hit=1
24.          

CTE steps_stat

25. 3.122 16.971 ↑ 7.1 141 1

HashAggregate (cost=298.87..318.85 rows=999 width=96) (actual time=16.326..16.971 rows=141 loops=1)

  • Output: ((events.data ->> 'offer_id'::text)), jsonb_build_object('step', ((events.data ->> 'order_index'::text)), 'steps_count', count(*)), ((events.data ->> 'order_index'::text))
  • Group Key: (events.data ->> 'offer_id'::text), (events.data ->> 'order_index'::text)
  • Buffers: shared hit=201
26. 13.849 13.849 ↓ 1.0 4,973 1

Index Scan using index_events_on_created_at_app_opened on public.events (cost=0.28..261.58 rows=4,972 width=114) (actual time=0.056..13.849 rows=4,973 loops=1)

  • Output: (events.data ->> 'offer_id'::text), (events.data ->> 'order_index'::text), events.data
  • Index Cond: ((events.created_at >= '2019-01-13 21:00:00'::timestamp without time zone) AND (events.created_at <= '2019-01-14 20:59:59'::timestamp without time zone))
  • Buffers: shared hit=201
27. 4.516 40.042 ↑ 55.2 91 1

GroupAggregate (cost=1,550.96..1,739.17 rows=5,019 width=216) (actual time=35.923..40.042 rows=91 loops=1)

  • Output: offers.id, offers.app_id, COALESCE(clicks.clicks_count, '0'::bigint), COALESCE(installs.installs_count, '0'::bigint), COALESCE(completions.completions_count, '0'::bigint), COALESCE(expenses.expenses_amount, '0'::numeric), COALESCE(revenue.revenue_amount, '0'::numeric), COALESCE(jsonb_agg(steps_stat.steps_stat_json) FILTER (WHERE (steps_stat.steps_stat_json IS NOT NULL)), '[]'::jsonb), clicks.clicks_count, installs.installs_count, completions.completions_count, expenses.expenses_amount, revenue.revenue_amount
  • Group Key: offers.id, clicks.clicks_count, installs.installs_count, completions.completions_count, expenses.expenses_amount, revenue.revenue_amount
  • Filter: ((COALESCE(clicks.clicks_count, '0'::bigint) <> 0) OR (COALESCE(installs.installs_count, '0'::bigint) <> 0) OR (COALESCE(completions.completions_count, '0'::bigint) <> 0) OR (COALESCE(expenses.expenses_amount, '0'::numeric) <> '0'::numeric) OR (COALESCE(revenue.revenue_amount, '0'::numeric) <> '0'::numeric) OR (jsonb_array_length(COALESCE(jsonb_agg(steps_stat.steps_stat_json) FILTER (WHERE (steps_stat.steps_stat_json IS NOT NULL)), '[]'::jsonb)) <> 0))
  • Rows Removed by Filter: 4928
  • Buffers: shared hit=1205
28. 3.473 35.526 ↓ 1.0 5,065 1

Sort (cost=1,550.96..1,563.50 rows=5,019 width=128) (actual time=34.709..35.526 rows=5,065 loops=1)

  • Output: offers.id, clicks.clicks_count, installs.installs_count, completions.completions_count, expenses.expenses_amount, revenue.revenue_amount, offers.app_id, steps_stat.steps_stat_json
  • Sort Key: offers.id, clicks.clicks_count, installs.installs_count, completions.completions_count, expenses.expenses_amount, revenue.revenue_amount
  • Sort Method: quicksort Memory: 443kB
  • Buffers: shared hit=1205
29. 1.410 32.053 ↓ 1.0 5,065 1

Hash Left Join (cost=1,112.09..1,242.46 rows=5,019 width=128) (actual time=23.373..32.053 rows=5,065 loops=1)

  • Output: offers.id, clicks.clicks_count, installs.installs_count, completions.completions_count, expenses.expenses_amount, revenue.revenue_amount, offers.app_id, steps_stat.steps_stat_json
  • Hash Cond: (offers.id = revenue.offer_id)
  • Buffers: shared hit=1205
30. 1.436 30.610 ↓ 1.0 5,065 1

Hash Left Join (cost=1,112.06..1,223.60 rows=5,019 width=96) (actual time=23.279..30.610 rows=5,065 loops=1)

  • Output: offers.id, offers.app_id, clicks.clicks_count, installs.installs_count, completions.completions_count, expenses.expenses_amount, steps_stat.steps_stat_json
  • Hash Cond: (offers.id = expenses.offer_id)
  • Buffers: shared hit=1204
31. 2.324 29.124 ↓ 1.0 5,065 1

Hash Left Join (cost=1,112.03..1,204.73 rows=5,019 width=64) (actual time=23.165..29.124 rows=5,065 loops=1)

  • Output: offers.id, offers.app_id, clicks.clicks_count, installs.installs_count, completions.completions_count, steps_stat.steps_stat_json
  • Hash Cond: (offers.id = completions.offer_id)
  • Buffers: shared hit=1203
32. 1.300 26.755 ↓ 1.0 5,065 1

Hash Left Join (cost=1,111.99..1,185.87 rows=5,019 width=56) (actual time=22.111..26.755 rows=5,065 loops=1)

  • Output: offers.id, offers.app_id, clicks.clicks_count, installs.installs_count, steps_stat.steps_stat_json
  • Hash Cond: (offers.id = installs.offer_id)
  • Buffers: shared hit=1202
33. 1.243 25.432 ↓ 1.0 5,065 1

Hash Left Join (cost=1,111.96..1,167.00 rows=5,019 width=48) (actual time=22.068..25.432 rows=5,065 loops=1)

  • Output: offers.id, offers.app_id, clicks.clicks_count, steps_stat.steps_stat_json
  • Hash Cond: (offers.id = clicks.offer_id)
  • Buffers: shared hit=1201
34. 1.488 24.173 ↓ 1.0 5,065 1

Hash Right Join (cost=1,111.93..1,148.14 rows=5,019 width=40) (actual time=22.041..24.173 rows=5,065 loops=1)

  • Output: offers.id, offers.app_id, steps_stat.steps_stat_json
  • Hash Cond: ((steps_stat.offer_id)::integer = offers.id)
  • Buffers: shared hit=1200
35. 17.101 17.101 ↑ 7.1 141 1

CTE Scan on steps_stat (cost=0.00..19.98 rows=999 width=64) (actual time=16.330..17.101 rows=141 loops=1)

  • Output: steps_stat.offer_id, steps_stat.steps_stat_json
  • Buffers: shared hit=201
36. 2.465 5.584 ↑ 1.0 5,019 1

Hash (cost=1,049.19..1,049.19 rows=5,019 width=8) (actual time=5.584..5.584 rows=5,019 loops=1)

  • Output: offers.id, offers.app_id
  • Buckets: 8192 Batches: 1 Memory Usage: 261kB
  • Buffers: shared hit=999
37. 3.119 3.119 ↑ 1.0 5,019 1

Seq Scan on public.offers (cost=0.00..1,049.19 rows=5,019 width=8) (actual time=0.022..3.119 rows=5,019 loops=1)

  • Output: offers.id, offers.app_id
  • Buffers: shared hit=999
38. 0.001 0.016 ↓ 0.0 0 1

Hash (cost=0.02..0.02 rows=1 width=12) (actual time=0.016..0.016 rows=0 loops=1)

  • Output: clicks.clicks_count, clicks.offer_id
  • Buckets: 1024 Batches: 1 Memory Usage: 8kB
  • Buffers: shared hit=1
39. 0.015 0.015 ↓ 0.0 0 1

CTE Scan on clicks (cost=0.00..0.02 rows=1 width=12) (actual time=0.015..0.015 rows=0 loops=1)

  • Output: clicks.clicks_count, clicks.offer_id
  • Buffers: shared hit=1
40. 0.000 0.023 ↓ 0.0 0 1

Hash (cost=0.02..0.02 rows=1 width=12) (actual time=0.023..0.023 rows=0 loops=1)

  • Output: installs.installs_count, installs.offer_id
  • Buckets: 1024 Batches: 1 Memory Usage: 8kB
  • Buffers: shared hit=1
41. 0.023 0.023 ↓ 0.0 0 1

CTE Scan on installs (cost=0.00..0.02 rows=1 width=12) (actual time=0.023..0.023 rows=0 loops=1)

  • Output: installs.installs_count, installs.offer_id
  • Buffers: shared hit=1
42. 0.000 0.045 ↓ 0.0 0 1

Hash (cost=0.02..0.02 rows=1 width=12) (actual time=0.045..0.045 rows=0 loops=1)

  • Output: completions.completions_count, completions.offer_id
  • Buckets: 1024 Batches: 1 Memory Usage: 8kB
  • Buffers: shared hit=1
43. 0.045 0.045 ↓ 0.0 0 1

CTE Scan on completions (cost=0.00..0.02 rows=1 width=12) (actual time=0.045..0.045 rows=0 loops=1)

  • Output: completions.completions_count, completions.offer_id
  • Buffers: shared hit=1
44. 0.000 0.050 ↓ 0.0 0 1

Hash (cost=0.02..0.02 rows=1 width=36) (actual time=0.050..0.050 rows=0 loops=1)

  • Output: expenses.expenses_amount, expenses.offer_id
  • Buckets: 1024 Batches: 1 Memory Usage: 8kB
  • Buffers: shared hit=1
45. 0.050 0.050 ↓ 0.0 0 1

CTE Scan on expenses (cost=0.00..0.02 rows=1 width=36) (actual time=0.050..0.050 rows=0 loops=1)

  • Output: expenses.expenses_amount, expenses.offer_id
  • Buffers: shared hit=1
46. 0.001 0.033 ↓ 0.0 0 1

Hash (cost=0.02..0.02 rows=1 width=36) (actual time=0.033..0.033 rows=0 loops=1)

  • Output: revenue.revenue_amount, revenue.offer_id
  • Buckets: 1024 Batches: 1 Memory Usage: 8kB
  • Buffers: shared hit=1
47. 0.032 0.032 ↓ 0.0 0 1

CTE Scan on revenue (cost=0.00..0.02 rows=1 width=36) (actual time=0.032..0.032 rows=0 loops=1)

  • Output: revenue.revenue_amount, revenue.offer_id
  • Buffers: shared hit=1