explain.depesz.com

PostgreSQL's explain analyze made readable

Result: dctr

Settings
# exclusive inclusive rows x rows loops node
1. 0.011 53.402 ↑ 55.2 91 1

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

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

Sort (cost=8.17..8.17 rows=1 width=8) (actual time=0.025..0.025 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.001 0.018 ↓ 0.0 0 1

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

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

Sort (cost=8.17..8.17 rows=1 width=8) (actual time=0.017..0.017 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.004..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.001 0.020 ↓ 0.0 0 1

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

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

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

Sort (cost=12.71..12.72 rows=1 width=18) (actual time=0.036..0.036 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-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.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_1.offer_id, sum(financial_transactions_1.amount)
  • Group Key: financial_transactions_1.offer_id
  • Buffers: shared hit=1
21. 0.010 0.013 ↓ 0.0 0 1

Sort (cost=12.71..12.72 rows=1 width=18) (actual time=0.013..0.013 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.003 ↓ 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.003..0.003 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.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_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.951 25.643 ↑ 7.1 141 1

Sort (cost=368.62..371.12 rows=999 width=96) (actual time=25.607..25.643 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. 4.156 23.692 ↑ 7.1 141 1

HashAggregate (cost=298.87..318.85 rows=999 width=96) (actual time=23.086..23.692 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. 19.536 19.536 ↓ 1.0 4,973 1

Index Scan using index_events_on_created_at on public.events (cost=0.28..261.58 rows=4,972 width=114) (actual time=0.047..19.536 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. 4.825 53.391 ↑ 55.2 91 1

GroupAggregate (cost=1,550.96..1,739.17 rows=5,019 width=216) (actual time=49.778..53.391 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.711 48.566 ↓ 1.0 5,065 1

Sort (cost=1,550.96..1,563.50 rows=5,019 width=128) (actual time=47.657..48.566 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.823 44.855 ↓ 1.0 5,065 1

Hash Left Join (cost=1,112.09..1,242.46 rows=5,019 width=128) (actual time=32.278..44.855 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. 3.680 43.016 ↓ 1.0 5,065 1

Hash Left Join (cost=1,112.06..1,223.60 rows=5,019 width=96) (actual time=32.242..43.016 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.874 39.297 ↓ 1.0 5,065 1

Hash Left Join (cost=1,112.03..1,204.73 rows=5,019 width=64) (actual time=32.181..39.297 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.746 37.401 ↓ 1.0 5,065 1

Hash Left Join (cost=1,111.99..1,185.87 rows=5,019 width=56) (actual time=32.137..37.401 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.695 35.635 ↓ 1.0 5,065 1

Hash Left Join (cost=1,111.96..1,167.00 rows=5,019 width=48) (actual time=32.093..35.635 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.890 33.911 ↓ 1.0 5,065 1

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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