explain.depesz.com

PostgreSQL's explain analyze made readable

Result: 1pES

Settings
# exclusive inclusive rows x rows loops node
1. 0.037 45.552 ↑ 55.2 91 1
→

Subquery Scan on stats (cost=1,972.12..2,210.52 rows=5,019 width=128) (actual time=39.420..45.552 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.035 ↓ 0.0 0 1
→

GroupAggregate (cost=8.17..8.19 rows=1 width=12) (actual time=0.035..0.035 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.023 0.032 ↓ 0.0 0 1
→

Sort (cost=8.17..8.17 rows=1 width=8) (actual time=0.032..0.032 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.009 0.009 ↓ 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.009..0.009 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-15 20:59:59'::timestamp without time zone))
  • Buffers: shared hit=1
6.          

CTE installs
7. 0.002 0.016 ↓ 0.0 0 1
→

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

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

Sort (cost=8.17..8.17 rows=1 width=8) (actual time=0.014..0.014 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.003 0.003 ↓ 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.003..0.003 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-15 20:59:59'::timestamp without time zone))
  • Buffers: shared hit=1
10.          

CTE completions
11. 0.001 0.014 ↓ 0.0 0 1
→

GroupAggregate (cost=8.17..8.19 rows=1 width=12) (actual time=0.014..0.014 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.010 0.013 ↓ 0.0 0 1
→

Sort (cost=8.17..8.18 rows=1 width=8) (actual time=0.013..0.013 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.003 0.003 ↓ 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.003..0.003 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-15 20:59:59'::timestamp without time zone))
  • Filter: completed_offers.rewarded
  • Buffers: shared hit=1
14.          

CTE expenses
15. 0.001 0.015 ↓ 0.0 0 1
→

GroupAggregate (cost=12.71..12.73 rows=1 width=36) (actual time=0.015..0.015 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.010 0.014 ↓ 0.0 0 1
→

Sort (cost=12.71..12.72 rows=1 width=18) (actual time=0.014..0.014 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.001 0.004 ↓ 0.0 0 1
→

Bitmap Heap Scan on public.financial_transactions (cost=4.20..12.70 rows=1 width=18) (actual time=0.004..0.004 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-15 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-15 20:59:59'::timestamp without time zone))
  • Buffers: shared hit=1
19.          

CTE revenue
20. 0.002 0.013 ↓ 0.0 0 1
→

GroupAggregate (cost=12.71..12.73 rows=1 width=36) (actual time=0.013..0.013 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.009 0.011 ↓ 0.0 0 1
→

Sort (cost=12.71..12.72 rows=1 width=18) (actual time=0.011..0.011 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.000 0.002 ↓ 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.002..0.002 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-15 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-15 20:59:59'::timestamp without time zone))
  • Buffers: shared hit=1
24.          

CTE steps_stat
25. 1.839 17.736 ↑ 7.1 141 1
→

Sort (cost=368.62..371.12 rows=999 width=96) (actual time=17.708..17.736 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))
  • Sort Key: ((events.data ->> 'offer_id'::text)), ((events.data ->> 'order_index'::text))
  • Sort Method: quicksort Memory: 44kB
  • Buffers: shared hit=201
26. 3.175 15.897 ↑ 7.1 141 1
→

HashAggregate (cost=298.87..318.85 rows=999 width=96) (actual time=15.245..15.897 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
27. 12.722 12.722 ↓ 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.048..12.722 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-15 20:59:59'::timestamp without time zone))
  • Buffers: shared hit=201
28. 7.274 45.515 ↑ 55.2 91 1
→

GroupAggregate (cost=1,550.96..1,739.17 rows=5,019 width=216) (actual time=39.419..45.515 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
29. 5.070 38.241 ↓ 1.0 5,065 1
→

Sort (cost=1,550.96..1,563.50 rows=5,019 width=128) (actual time=36.794..38.241 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
30. 1.819 33.171 ↓ 1.0 5,065 1
→

Hash Left Join (cost=1,112.09..1,242.46 rows=5,019 width=128) (actual time=22.450..33.171 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
31. 1.809 31.338 ↓ 1.0 5,065 1
→

Hash Left Join (cost=1,112.06..1,223.60 rows=5,019 width=96) (actual time=22.417..31.338 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
32. 1.921 29.514 ↓ 1.0 5,065 1
→

Hash Left Join (cost=1,112.03..1,204.73 rows=5,019 width=64) (actual time=22.384..29.514 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
33. 1.743 27.578 ↓ 1.0 5,065 1
→

Hash Left Join (cost=1,111.99..1,185.87 rows=5,019 width=56) (actual time=22.349..27.578 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
34. 1.721 25.818 ↓ 1.0 5,065 1
→

Hash Left Join (cost=1,111.96..1,167.00 rows=5,019 width=48) (actual time=22.293..25.818 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
35. 1.892 24.061 ↓ 1.0 5,065 1
→

Hash Right Join (cost=1,111.93..1,148.14 rows=5,019 width=40) (actual time=22.222..24.061 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
36. 17.814 17.814 ↑ 7.1 141 1
→

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

  • Output: steps_stat.offer_id, steps_stat.steps_stat_json
  • Buffers: shared hit=201
37. 1.370 4.355 ↑ 1.0 5,019 1
→

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

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

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

  • Output: offers.id, offers.app_id
  • Buffers: shared hit=999
39. 0.000 0.036 ↓ 0.0 0 1
→

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

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

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

  • Output: clicks.clicks_count, clicks.offer_id
  • Buffers: shared hit=1
41. 0.000 0.017 ↓ 0.0 0 1
→

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

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

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

  • Output: installs.installs_count, installs.offer_id
  • Buffers: shared hit=1
43. 0.000 0.015 ↓ 0.0 0 1
→

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

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

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

  • Output: completions.completions_count, completions.offer_id
  • Buffers: shared hit=1
45. 0.000 0.015 ↓ 0.0 0 1
→

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

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

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

  • Output: expenses.expenses_amount, expenses.offer_id
  • Buffers: shared hit=1
47. 0.000 0.014 ↓ 0.0 0 1
→

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

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

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

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