explain.depesz.com

PostgreSQL's explain analyze made readable

Result: ckE5 : WEEK PLAN

Settings
# exclusive inclusive rows x rows loops node
1. 0.253 32,213.260 ↑ 184.4 540 1

Subquery Scan on stats (cost=871,828.81..876,559.05 rows=99,584 width=128) (actual time=32,193.741..32,213.260 rows=540 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=1020012 read=242761, temp read=1425 written=1425
2.          

CTE clicks

3. 83.940 221.467 ↑ 16.5 388 1

HashAggregate (cost=11,411.87..11,475.83 rows=6,396 width=12) (actual time=221.351..221.467 rows=388 loops=1)

  • Output: offer_clicks.offer_id, count(offer_clicks.id)
  • Group Key: offer_clicks.offer_id
  • Buffers: shared hit=229342
4. 137.527 137.527 ↓ 3.4 225,087 1

Index Scan using index_offer_clicks_on_created_at on public.offer_clicks (cost=0.44..11,077.88 rows=66,797 width=8) (actual time=0.020..137.527 rows=225,087 loops=1)

  • Output: offer_clicks.id, offer_clicks.user_id, offer_clicks.offer_id, offer_clicks.ip, offer_clicks.browser, offer_clicks.created_at, offer_clicks.updated_at, offer_clicks.referer, offer_clicks.md5_hash, offer_clicks.state, offer_clicks.aff_sub, offer_clicks.aff_sub2, offer_clicks.idfa, offer_clicks.device_id
  • Index Cond: ((offer_clicks.created_at >= '2019-01-28 21:00:00'::timestamp without time zone) AND (offer_clicks.created_at <= '2019-02-04 20:59:59'::timestamp without time zone))
  • Buffers: shared hit=229342
5.          

CTE installs

6. 41.296 116.522 ↑ 19.2 391 1

HashAggregate (cost=5,581.40..5,656.44 rows=7,504 width=12) (actual time=116.374..116.522 rows=391 loops=1)

  • Output: postbacks.offer_id, count(postbacks.id)
  • Group Key: postbacks.offer_id
  • Buffers: shared hit=120176
7. 75.226 75.226 ↓ 3.1 121,113 1

Index Scan using index_postbacks_on_created_at on public.postbacks (cost=0.43..5,386.87 rows=38,906 width=8) (actual time=0.026..75.226 rows=121,113 loops=1)

  • Output: postbacks.offer_id, postbacks.id
  • Index Cond: ((postbacks.created_at >= '2019-01-28 21:00:00'::timestamp without time zone) AND (postbacks.created_at <= '2019-02-04 20:59:59'::timestamp without time zone))
  • Buffers: shared hit=120176
8.          

CTE completions

9. 32.101 93.872 ↑ 15.8 448 1

HashAggregate (cost=6,152.24..6,223.21 rows=7,097 width=12) (actual time=93.726..93.872 rows=448 loops=1)

  • Output: completed_offers.offer_id, count(completed_offers.id)
  • Group Key: completed_offers.offer_id
  • Buffers: shared hit=97120
10. 61.771 61.771 ↓ 4.3 92,025 1

Index Scan using index_completed_offers_on_created_at on public.completed_offers (cost=0.43..6,043.99 rows=21,650 width=8) (actual time=0.026..61.771 rows=92,025 loops=1)

  • Output: completed_offers.id, completed_offers.user_id, completed_offers.offer_id, completed_offers.created_at, completed_offers.updated_at, completed_offers.reason_id, completed_offers.reason_type, completed_offers.rewarded, completed_offers.device_id
  • Index Cond: ((completed_offers.created_at >= '2019-01-28 21:00:00'::timestamp without time zone) AND (completed_offers.created_at <= '2019-02-04 20:59:59'::timestamp without time zone))
  • Filter: completed_offers.rewarded
  • Rows Removed by Filter: 6158
  • Buffers: shared hit=97120
11.          

CTE expenses

12. 93.249 300.716 ↑ 15.7 508 1

HashAggregate (cost=113,151.06..113,250.59 rows=7,963 width=36) (actual time=300.471..300.716 rows=508 loops=1)

  • Output: financial_transactions.offer_id, sum(financial_transactions.amount)
  • Group Key: financial_transactions.offer_id
  • Buffers: shared hit=281363
13. 207.467 207.467 ↓ 1.5 156,567 1

Index Scan using index_financial_transactions_on_created_at on public.financial_transactions (cost=0.44..112,638.63 rows=102,485 width=9) (actual time=0.030..207.467 rows=156,567 loops=1)

  • Output: financial_transactions.id, financial_transactions.user_id, financial_transactions.offer_id, financial_transactions.transaction_type, financial_transactions.amount, financial_transactions.archived, financial_transactions.created_at, financial_transactions.updated_at, financial_transactions.offer_step_id
  • Index Cond: ((financial_transactions.created_at >= '2019-01-28 21:00:00'::timestamp without time zone) AND (financial_transactions.created_at <= '2019-02-04 20:59:59'::timestamp without time zone))
  • Filter: ((NOT financial_transactions.archived) AND (financial_transactions.transaction_type = 0))
  • Rows Removed by Filter: 278294
  • Buffers: shared hit=281363
14.          

CTE revenue

15. 128.858 362.102 ↑ 15.3 521 1

HashAggregate (cost=113,365.64..113,465.18 rows=7,963 width=36) (actual time=361.848..362.102 rows=521 loops=1)

  • Output: financial_transactions_1.offer_id, sum(financial_transactions_1.amount)
  • Group Key: financial_transactions_1.offer_id
  • Buffers: shared hit=281363
16. 233.244 233.244 ↓ 1.9 278,243 1

Index Scan using index_financial_transactions_on_created_at on public.financial_transactions financial_transactions_1 (cost=0.44..112,638.63 rows=145,402 width=9) (actual time=0.025..233.244 rows=278,243 loops=1)

  • Output: financial_transactions_1.id, financial_transactions_1.user_id, financial_transactions_1.offer_id, financial_transactions_1.transaction_type, financial_transactions_1.amount, financial_transactions_1.archived, financial_transactions_1.created_at, financial_transactions_1.updated_at, financial_transactions_1.offer_step_id
  • Index Cond: ((financial_transactions_1.created_at >= '2019-01-28 21:00:00'::timestamp without time zone) AND (financial_transactions_1.created_at <= '2019-02-04 20:59:59'::timestamp without time zone))
  • Filter: ((NOT financial_transactions_1.archived) AND (financial_transactions_1.transaction_type = 1))
  • Rows Removed by Filter: 156618
  • Buffers: shared hit=281363
17.          

CTE steps_stat

18. 53.067 30,990.423 ↑ 149.3 667 1

GroupAggregate (cost=589,455.85..592,479.98 rows=99,584 width=96) (actual time=30,831.731..30,990.423 rows=667 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=2584 read=242761, temp read=1425 written=1425
19. 521.227 30,937.356 ↓ 1.5 155,255 1

Sort (cost=589,455.85..589,713.97 rows=103,245 width=103) (actual time=30,831.098..30,937.356 rows=155,255 loops=1)

  • Output: ((events.data ->> 'offer_id'::text)), ((events.data ->> 'order_index'::text)), events.data
  • Sort Key: ((events.data ->> 'offer_id'::text)), ((events.data ->> 'order_index'::text))
  • Sort Method: external merge Disk: 11360kB
  • Buffers: shared hit=2584 read=242761, temp read=1425 written=1425
20. 30,416.129 30,416.129 ↓ 1.5 155,255 1

Seq Scan on public.events (cost=0.00..575,208.76 rows=103,245 width=103) (actual time=170.473..30,416.129 rows=155,255 loops=1)

  • Output: (events.data ->> 'offer_id'::text), (events.data ->> 'order_index'::text), events.data
  • Filter: ((events.created_at >= '2019-01-28 21:00:00'::timestamp without time zone) AND (events.created_at <= '2019-02-04 20:59:59'::timestamp without time zone) AND ((events.type)::text = 'app_opened'::text))
  • Rows Removed by Filter: 18637277
  • Buffers: shared hit=2584 read=242761
21. 14.818 32,213.007 ↑ 184.4 540 1

GroupAggregate (cost=29,277.58..33,011.98 rows=99,584 width=216) (actual time=32,193.739..32,213.007 rows=540 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: 18651
  • Buffers: shared hit=1020012 read=242761, temp read=1425 written=1425
22. 14.389 32,198.189 ↑ 5.1 19,424 1

Sort (cost=29,277.58..29,526.54 rows=99,584 width=128) (actual time=32,193.206..32,198.189 rows=19,424 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: 1745kB
  • Buffers: shared hit=1020012 read=242761, temp read=1425 written=1425
23. 6.243 32,183.800 ↑ 5.1 19,424 1

Hash Right Join (cost=10,932.39..14,542.31 rows=99,584 width=128) (actual time=32,018.303..32,183.800 rows=19,424 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: ((steps_stat.offer_id)::integer = offers.id)
  • Buffers: shared hit=1020003 read=242761, temp read=1425 written=1425
24. 30,991.107 30,991.107 ↑ 149.3 667 1

CTE Scan on steps_stat (cost=0.00..1,991.68 rows=99,584 width=64) (actual time=30,831.737..30,991.107 rows=667 loops=1)

  • Output: steps_stat.offer_id, steps_stat.steps_stat_json
  • Buffers: shared hit=2584 read=242761, temp read=1425 written=1425
25. 6.248 1,186.450 ↑ 1.0 19,191 1

Hash (cost=10,692.50..10,692.50 rows=19,191 width=96) (actual time=1,186.449..1,186.450 rows=19,191 loops=1)

  • Output: offers.id, offers.app_id, clicks.clicks_count, installs.installs_count, completions.completions_count, expenses.expenses_amount, revenue.revenue_amount
  • Buckets: 32768 Batches: 1 Memory Usage: 1017kB
  • Buffers: shared hit=1017419
26. 5.866 1,180.202 ↑ 1.0 19,191 1

Hash Right Join (cost=10,423.75..10,692.50 rows=19,191 width=96) (actual time=1,173.894..1,180.202 rows=19,191 loops=1)

  • Output: offers.id, offers.app_id, clicks.clicks_count, installs.installs_count, completions.completions_count, expenses.expenses_amount, revenue.revenue_amount
  • Hash Cond: (revenue.offer_id = offers.id)
  • Buffers: shared hit=1017419
27. 362.409 362.409 ↑ 15.3 521 1

CTE Scan on revenue (cost=0.00..159.26 rows=7,963 width=36) (actual time=361.851..362.409 rows=521 loops=1)

  • Output: revenue.offer_id, revenue.revenue_amount
  • Buffers: shared hit=281363
28. 6.105 811.927 ↑ 1.0 19,191 1

Hash (cost=10,183.86..10,183.86 rows=19,191 width=64) (actual time=811.927..811.927 rows=19,191 loops=1)

  • Output: offers.id, offers.app_id, clicks.clicks_count, installs.installs_count, completions.completions_count, expenses.expenses_amount
  • Buckets: 32768 Batches: 1 Memory Usage: 1015kB
  • Buffers: shared hit=736056
29. 5.739 805.822 ↑ 1.0 19,191 1

Hash Right Join (cost=9,915.11..10,183.86 rows=19,191 width=64) (actual time=799.666..805.822 rows=19,191 loops=1)

  • Output: offers.id, offers.app_id, clicks.clicks_count, installs.installs_count, completions.completions_count, expenses.expenses_amount
  • Hash Cond: (expenses.offer_id = offers.id)
  • Buffers: shared hit=736056
30. 301.009 301.009 ↑ 15.7 508 1

CTE Scan on expenses (cost=0.00..159.26 rows=7,963 width=36) (actual time=300.475..301.009 rows=508 loops=1)

  • Output: expenses.offer_id, expenses.expenses_amount
  • Buffers: shared hit=281363
31. 6.098 499.074 ↑ 1.0 19,191 1

Hash (cost=9,675.22..9,675.22 rows=19,191 width=32) (actual time=499.074..499.074 rows=19,191 loops=1)

  • Output: offers.id, offers.app_id, clicks.clicks_count, installs.installs_count, completions.completions_count
  • Buckets: 32768 Batches: 1 Memory Usage: 1011kB
  • Buffers: shared hit=454693
32. 6.206 492.976 ↑ 1.0 19,191 1

Hash Right Join (cost=9,421.96..9,675.22 rows=19,191 width=32) (actual time=486.510..492.976 rows=19,191 loops=1)

  • Output: offers.id, offers.app_id, clicks.clicks_count, installs.installs_count, completions.completions_count
  • Hash Cond: (installs.offer_id = offers.id)
  • Buffers: shared hit=454693
33. 116.757 116.757 ↑ 19.2 391 1

CTE Scan on installs (cost=0.00..150.08 rows=7,504 width=12) (actual time=116.378..116.757 rows=391 loops=1)

  • Output: installs.offer_id, installs.installs_count
  • Buffers: shared hit=120176
34. 6.001 370.013 ↑ 1.0 19,191 1

Hash (cost=9,182.07..9,182.07 rows=19,191 width=24) (actual time=370.013..370.013 rows=19,191 loops=1)

  • Output: offers.id, offers.app_id, clicks.clicks_count, completions.completions_count
  • Buckets: 32768 Batches: 1 Memory Usage: 1008kB
  • Buffers: shared hit=334517
35. 5.532 364.012 ↑ 1.0 19,191 1

Hash Right Join (cost=8,942.55..9,182.07 rows=19,191 width=24) (actual time=358.207..364.012 rows=19,191 loops=1)

  • Output: offers.id, offers.app_id, clicks.clicks_count, completions.completions_count
  • Hash Cond: (completions.offer_id = offers.id)
  • Buffers: shared hit=334517
36. 94.121 94.121 ↑ 15.8 448 1

CTE Scan on completions (cost=0.00..141.94 rows=7,097 width=12) (actual time=93.729..94.121 rows=448 loops=1)

  • Output: completions.offer_id, completions.completions_count
  • Buffers: shared hit=97120
37. 5.983 264.359 ↑ 1.0 19,191 1

Hash (cost=8,702.66..8,702.66 rows=19,191 width=16) (actual time=264.359..264.359 rows=19,191 loops=1)

  • Output: offers.id, offers.app_id, clicks.clicks_count
  • Buckets: 32768 Batches: 1 Memory Usage: 1005kB
  • Buffers: shared hit=237397
38. 6.055 258.376 ↑ 1.0 19,191 1

Hash Right Join (cost=8,486.80..8,702.66 rows=19,191 width=16) (actual time=252.090..258.376 rows=19,191 loops=1)

  • Output: offers.id, offers.app_id, clicks.clicks_count
  • Hash Cond: (clicks.offer_id = offers.id)
  • Buffers: shared hit=237397
39. 221.712 221.712 ↑ 16.5 388 1

CTE Scan on clicks (cost=0.00..127.92 rows=6,396 width=12) (actual time=221.356..221.712 rows=388 loops=1)

  • Output: clicks.offer_id, clicks.clicks_count
  • Buffers: shared hit=229342
40. 6.280 30.609 ↑ 1.0 19,191 1

Hash (cost=8,246.91..8,246.91 rows=19,191 width=8) (actual time=30.609..30.609 rows=19,191 loops=1)

  • Output: offers.id, offers.app_id
  • Buckets: 32768 Batches: 1 Memory Usage: 1002kB
  • Buffers: shared hit=8055
41. 24.329 24.329 ↑ 1.0 19,191 1

Seq Scan on public.offers (cost=0.00..8,246.91 rows=19,191 width=8) (actual time=0.009..24.329 rows=19,191 loops=1)

  • Output: offers.id, offers.app_id
  • Buffers: shared hit=8055