explain.depesz.com

PostgreSQL's explain analyze made readable

Result: todS

Settings
# exclusive inclusive rows x rows loops node
1. 0.096 616.539 ↑ 76.1 253 1

Subquery Scan on stats (cost=60,564.07..60,949.05 rows=19,249 width=128) (actual time=616.283..616.539 rows=253 loops=1)

2.          

CTE clicks

3. 11.040 79.099 ↑ 25.7 193 1

HashAggregate (cost=1,656.28..1,705.81 rows=4,953 width=12) (actual time=79.017..79.099 rows=193 loops=1)

  • Group Key: offer_clicks.offer_id
4. 68.059 68.059 ↓ 3.2 30,169 1

Index Scan using index_offer_clicks_on_created_at on offer_clicks (cost=0.44..1,608.68 rows=9,520 width=8) (actual time=0.409..68.059 rows=30,169 loops=1)

  • Index Cond: ((created_at >= '2019-02-03 21:00:00'::timestamp without time zone) AND (created_at <= '2019-02-04 20:59:59'::timestamp without time zone))
5.          

CTE installs

6. 5.546 19.687 ↑ 22.4 174 1

HashAggregate (cost=826.63..865.54 rows=3,891 width=12) (actual time=19.647..19.687 rows=174 loops=1)

  • Group Key: postbacks.offer_id
7. 14.141 14.141 ↓ 2.9 16,122 1

Index Scan using index_postbacks_on_created_at on postbacks (cost=0.43..799.28 rows=5,469 width=8) (actual time=0.462..14.141 rows=16,122 loops=1)

  • Index Cond: ((created_at >= '2019-02-03 21:00:00'::timestamp without time zone) AND (created_at <= '2019-02-04 20:59:59'::timestamp without time zone))
8.          

CTE completions

9. 3.995 17.158 ↑ 13.4 190 1

HashAggregate (cost=935.60..961.14 rows=2,554 width=12) (actual time=17.096..17.158 rows=190 loops=1)

  • Group Key: completed_offers.offer_id
10. 13.163 13.163 ↓ 3.7 11,500 1

Index Scan using index_completed_offers_on_created_at on completed_offers (cost=0.43..920.00 rows=3,120 width=8) (actual time=0.414..13.163 rows=11,500 loops=1)

  • Index Cond: ((created_at >= '2019-02-03 21:00:00'::timestamp without time zone) AND (created_at <= '2019-02-04 20:59:59'::timestamp without time zone))
  • Filter: rewarded
  • Rows Removed by Filter: 801
11.          

CTE expenses

12. 9.497 36.814 ↑ 26.2 243 1

HashAggregate (cost=17,126.92..17,206.47 rows=6,364 width=36) (actual time=36.704..36.814 rows=243 loops=1)

  • Group Key: financial_transactions.offer_id
13. 27.317 27.317 ↓ 1.6 20,410 1

Index Scan using index_financial_transactions_on_created_at on financial_transactions (cost=0.44..17,063.02 rows=12,780 width=9) (actual time=0.039..27.317 rows=20,410 loops=1)

  • Index Cond: ((created_at >= '2019-02-03 21:00:00'::timestamp without time zone) AND (created_at <= '2019-02-04 20:59:59'::timestamp without time zone))
  • Filter: ((NOT archived) AND (transaction_type = 0))
  • Rows Removed by Filter: 36563
14.          

CTE revenue

15. 16.798 50.622 ↑ 29.3 244 1

HashAggregate (cost=17,153.68..17,243.01 rows=7,147 width=36) (actual time=50.478..50.622 rows=244 loops=1)

  • Group Key: financial_transactions_1.offer_id
16. 33.824 33.824 ↓ 2.0 36,521 1

Index Scan using index_financial_transactions_on_created_at on financial_transactions financial_transactions_1 (cost=0.44..17,063.02 rows=18,132 width=9) (actual time=0.027..33.824 rows=36,521 loops=1)

  • Index Cond: ((created_at >= '2019-02-03 21:00:00'::timestamp without time zone) AND (created_at <= '2019-02-04 20:59:59'::timestamp without time zone))
  • Filter: ((NOT archived) AND (transaction_type = 1))
  • Rows Removed by Filter: 20452
17.          

CTE offers_steps_stat

18. 9.947 60.147 ↑ 58.3 351 1

HashAggregate (cost=7,167.70..7,577.18 rows=20,474 width=96) (actual time=59.339..60.147 rows=351 loops=1)

  • Group Key: (events.data ->> 'offer_id'::text), (events.data ->> 'order_index'::text)
19. 50.200 50.200 ↑ 1.0 20,302 1

Index Scan using index_events_on_created_at_app_opened on events (cost=0.43..7,013.04 rows=20,622 width=103) (actual time=1.281..50.200 rows=20,302 loops=1)

  • Index Cond: ((created_at >= '2019-02-03 21:00:00'::timestamp without time zone) AND (created_at <= '2019-02-04 20:59:59'::timestamp without time zone))
20.          

CTE steps_stat

21. 0.854 61.275 ↓ 1.1 214 1

HashAggregate (cost=511.85..514.35 rows=200 width=64) (actual time=61.140..61.275 rows=214 loops=1)

  • Group Key: offers_steps_stat.offer_id
22. 60.421 60.421 ↑ 58.3 351 1

CTE Scan on offers_steps_stat (cost=0.00..409.48 rows=20,474 width=64) (actual time=59.342..60.421 rows=351 loops=1)

23. 0.442 616.443 ↑ 76.1 253 1

HashAggregate (cost=14,490.57..14,683.06 rows=19,249 width=248) (actual time=616.283..616.443 rows=253 loops=1)

  • Group Key: offers.id, clicks.clicks_count, installs.installs_count, completions.completions_count, expenses.expenses_amount, revenue.revenue_amount, steps_stat.steps_stat_json
24. 6.268 616.001 ↑ 76.1 253 1

Hash Right Join (cost=14,022.82..14,153.71 rows=19,249 width=128) (actual time=609.683..616.001 rows=253 loops=1)

  • Hash Cond: (completions.offer_id = offers.id)
  • 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(steps_stat.steps_stat_json, '[]'::jsonb)) <> 0))
  • Rows Removed by Filter: 18996
25. 17.273 17.273 ↑ 13.4 190 1

CTE Scan on completions (cost=0.00..51.08 rows=2,554 width=12) (actual time=17.100..17.273 rows=190 loops=1)

26. 6.508 592.460 ↑ 1.0 19,249 1

Hash (cost=13,782.21..13,782.21 rows=19,249 width=120) (actual time=592.460..592.460 rows=19,249 loops=1)

  • Buckets: 32768 Batches: 1 Memory Usage: 1030kB
27. 10.818 585.952 ↑ 1.0 19,249 1

Hash Left Join (cost=11,856.71..13,782.21 rows=19,249 width=120) (actual time=569.309..585.952 rows=19,249 loops=1)

  • Hash Cond: ((offers.id)::text = steps_stat.offer_id)
28. 5.683 513.620 ↑ 1.0 19,249 1

Hash Right Join (cost=11,850.21..12,091.42 rows=19,249 width=88) (actual time=507.773..513.620 rows=19,249 loops=1)

  • Hash Cond: (revenue.offer_id = offers.id)
29. 50.767 50.767 ↑ 29.3 244 1

CTE Scan on revenue (cost=0.00..142.94 rows=7,147 width=36) (actual time=50.483..50.767 rows=244 loops=1)

30. 5.850 457.170 ↑ 1.0 19,249 1

Hash (cost=11,609.60..11,609.60 rows=19,249 width=56) (actual time=457.170..457.170 rows=19,249 loops=1)

  • Buckets: 32768 Batches: 1 Memory Usage: 1008kB
31. 5.480 451.320 ↑ 1.0 19,249 1

Hash Right Join (cost=11,394.81..11,609.60 rows=19,249 width=56) (actual time=445.699..451.320 rows=19,249 loops=1)

  • Hash Cond: (expenses.offer_id = offers.id)
32. 36.965 36.965 ↑ 26.2 243 1

CTE Scan on expenses (cost=0.00..127.28 rows=6,364 width=36) (actual time=36.708..36.965 rows=243 loops=1)

33. 5.858 408.875 ↑ 1.0 19,249 1

Hash (cost=11,154.20..11,154.20 rows=19,249 width=24) (actual time=408.875..408.875 rows=19,249 loops=1)

  • Buckets: 32768 Batches: 1 Memory Usage: 1007kB
34. 5.383 403.017 ↑ 1.0 19,249 1

Hash Right Join (cost=10,987.04..11,154.20 rows=19,249 width=24) (actual time=397.566..403.017 rows=19,249 loops=1)

  • Hash Cond: (clicks.offer_id = offers.id)
35. 79.208 79.208 ↑ 25.7 193 1

CTE Scan on clicks (cost=0.00..99.06 rows=4,953 width=12) (actual time=79.021..79.208 rows=193 loops=1)

36. 5.905 318.426 ↑ 1.0 19,249 1

Hash (cost=10,746.42..10,746.42 rows=19,249 width=16) (actual time=318.425..318.426 rows=19,249 loops=1)

  • Buckets: 32768 Batches: 1 Memory Usage: 1005kB
37. 6.080 312.521 ↑ 1.0 19,249 1

Hash Right Join (cost=10,615.10..10,746.42 rows=19,249 width=16) (actual time=306.420..312.521 rows=19,249 loops=1)

  • Hash Cond: (installs.offer_id = offers.id)
38. 19.807 19.807 ↑ 22.4 174 1

CTE Scan on installs (cost=0.00..77.82 rows=3,891 width=12) (actual time=19.651..19.807 rows=174 loops=1)

39. 7.895 286.634 ↑ 1.0 19,249 1

Hash (cost=10,374.49..10,374.49 rows=19,249 width=8) (actual time=286.634..286.634 rows=19,249 loops=1)

  • Buckets: 32768 Batches: 1 Memory Usage: 1004kB
40. 278.739 278.739 ↑ 1.0 19,249 1

Seq Scan on offers (cost=0.00..10,374.49 rows=19,249 width=8) (actual time=0.371..278.739 rows=19,249 loops=1)

41. 0.086 61.514 ↓ 1.1 214 1

Hash (cost=4.00..4.00 rows=200 width=64) (actual time=61.513..61.514 rows=214 loops=1)

  • Buckets: 1024 Batches: 1 Memory Usage: 37kB
42. 61.428 61.428 ↓ 1.1 214 1

CTE Scan on steps_stat (cost=0.00..4.00 rows=200 width=64) (actual time=61.143..61.428 rows=214 loops=1)