explain.depesz.com

PostgreSQL's explain analyze made readable

Result: HEDe

Settings
# exclusive inclusive rows x rows loops node
1. 0.026 70.798 ↑ 55.2 91 1

Subquery Scan on stats (cost=1,998.14..2,236.54 rows=5,019 width=128) (actual time=64.813..70.798 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=1153
2.          

CTE clicks

3. 0.001 0.037 ↓ 0.0 0 1

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

Sort (cost=8.17..8.17 rows=1 width=8) (actual time=0.036..0.036 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.015 0.015 ↓ 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.015..0.015 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.019 ↓ 0.0 0 1

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

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

Sort (cost=8.17..8.17 rows=1 width=8) (actual time=0.018..0.018 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.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: completed_offers.offer_id, count(completed_offers.id)
  • Group Key: completed_offers.offer_id
  • Buffers: shared hit=1
12. 0.013 0.017 ↓ 0.0 0 1

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

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

Sort (cost=12.71..12.72 rows=1 width=18) (actual time=0.016..0.016 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.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.004 0.004 ↓ 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.004..0.004 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.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.010 0.012 ↓ 0.0 0 1

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

HashAggregate (cost=377.16..397.14 rows=999 width=96) (actual time=31.085..31.797 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=140
26. 26.759 26.759 ↑ 1.0 4,973 1

Seq Scan on public.events (cost=0.00..339.87 rows=4,973 width=114) (actual time=0.115..26.759 rows=4,973 loops=1)

  • Output: (events.data ->> 'offer_id'::text), (events.data ->> 'order_index'::text), events.data
  • Filter: ((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) AND ((events.type)::text = 'app_opened'::text))
  • Rows Removed by Filter: 5027
  • Buffers: shared hit=140
27. 6.926 70.772 ↑ 55.2 91 1

GroupAggregate (cost=1,550.96..1,739.17 rows=5,019 width=216) (actual time=64.812..70.772 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=1153
28. 5.057 63.846 ↓ 1.0 5,065 1

Sort (cost=1,550.96..1,563.50 rows=5,019 width=128) (actual time=62.599..63.846 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=1153
29. 1.900 58.789 ↓ 1.0 5,065 1

Hash Left Join (cost=1,112.09..1,242.46 rows=5,019 width=128) (actual time=46.589..58.789 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=1144
30. 1.844 56.876 ↓ 1.0 5,065 1

Hash Left Join (cost=1,112.06..1,223.60 rows=5,019 width=96) (actual time=46.557..56.876 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=1143
31. 1.934 55.014 ↓ 1.0 5,065 1

Hash Left Join (cost=1,112.03..1,204.73 rows=5,019 width=64) (actual time=46.520..55.014 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=1142
32. 1.781 53.060 ↓ 1.0 5,065 1

Hash Left Join (cost=1,111.99..1,185.87 rows=5,019 width=56) (actual time=46.467..53.060 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=1141
33. 1.800 51.258 ↓ 1.0 5,065 1

Hash Left Join (cost=1,111.96..1,167.00 rows=5,019 width=48) (actual time=46.426..51.258 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=1140
34. 2.290 49.419 ↓ 1.0 5,065 1

Hash Right Join (cost=1,111.93..1,148.14 rows=5,019 width=40) (actual time=46.359..49.419 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=1139
35. 31.944 31.944 ↑ 7.1 141 1

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

  • Output: steps_stat.offer_id, steps_stat.steps_stat_json
  • Buffers: shared hit=140
36. 2.840 15.185 ↑ 1.0 5,019 1

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

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

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

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

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

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

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

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

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

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

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

  • Output: installs.installs_count, installs.offer_id
  • Buffers: shared hit=1
42. 0.001 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: completions.completions_count, completions.offer_id
  • Buckets: 1024 Batches: 1 Memory Usage: 8kB
  • Buffers: shared hit=1
43. 0.019 0.019 ↓ 0.0 0 1

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

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

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

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

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

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

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

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

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

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