explain.depesz.com

PostgreSQL's explain analyze made readable

Result: GcSU

Settings
# exclusive inclusive rows x rows loops node
1. 0.162 49.205 ↑ 55.2 91 1

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

GroupAggregate (cost=8.17..8.19 rows=1 width=12) (actual time=0.017..0.017 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.006 0.016 ↓ 0.0 0 1

Sort (cost=8.17..8.17 rows=1 width=8) (actual time=0.015..0.016 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.010 0.010 ↓ 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.010..0.010 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.008 ↓ 0.0 0 1

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

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

Sort (cost=8.17..8.17 rows=1 width=8) (actual time=0.006..0.006 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.004 0.004 ↓ 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.004 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.002 0.007 ↓ 0.0 0 1

GroupAggregate (cost=8.17..8.19 rows=1 width=12) (actual time=0.007..0.007 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.002 0.005 ↓ 0.0 0 1

Sort (cost=8.17..8.18 rows=1 width=8) (actual time=0.005..0.005 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.000 0.007 ↓ 0.0 0 1

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

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

GroupAggregate (cost=12.71..12.73 rows=1 width=36) (actual time=0.005..0.005 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.002 0.004 ↓ 0.0 0 1

Sort (cost=12.71..12.72 rows=1 width=18) (actual time=0.004..0.004 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.677 22.623 ↑ 7.1 141 1

Sort (cost=368.62..371.12 rows=999 width=96) (actual time=22.592..22.623 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. 5.347 20.946 ↑ 7.1 141 1

HashAggregate (cost=298.87..318.85 rows=999 width=96) (actual time=20.360..20.946 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. 15.599 15.599 ↓ 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.043..15.599 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. 5.365 49.043 ↑ 55.2 91 1

GroupAggregate (cost=1,550.96..1,739.17 rows=5,019 width=216) (actual time=43.780..49.043 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. 3.751 43.678 ↓ 1.0 5,065 1

Sort (cost=1,550.96..1,563.50 rows=5,019 width=128) (actual time=42.613..43.678 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.874 39.927 ↓ 1.0 5,065 1

Hash Left Join (cost=1,112.09..1,242.46 rows=5,019 width=128) (actual time=28.125..39.927 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.674 38.045 ↓ 1.0 5,065 1

Hash Left Join (cost=1,112.06..1,223.60 rows=5,019 width=96) (actual time=28.102..38.045 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. 2.141 36.363 ↓ 1.0 5,065 1

Hash Left Join (cost=1,112.03..1,204.73 rows=5,019 width=64) (actual time=28.088..36.363 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.871 34.215 ↓ 1.0 5,065 1

Hash Left Join (cost=1,111.99..1,185.87 rows=5,019 width=56) (actual time=28.075..34.215 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. 2.649 32.335 ↓ 1.0 5,065 1

Hash Left Join (cost=1,111.96..1,167.00 rows=5,019 width=48) (actual time=28.059..32.335 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.620 29.667 ↓ 1.0 5,065 1

Hash Right Join (cost=1,111.93..1,148.14 rows=5,019 width=40) (actual time=28.029..29.667 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. 22.698 22.698 ↑ 7.1 141 1

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

  • Output: steps_stat.offer_id, steps_stat.steps_stat_json
  • Buffers: shared hit=201
37. 1.474 5.349 ↑ 1.0 5,019 1

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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