explain.depesz.com

PostgreSQL's explain analyze made readable

Result: 25BZ

Settings
# exclusive inclusive rows x rows loops node
1. 0.000 0.000 ↓ 0.0

CTE Scan on raw_agg_data (cost=443,497.72..443,951.19 rows=18,139 width=318) (actual rows= loops=)

2.          

CTE raw_agg_data

3. 0.000 0.000 ↓ 0.0

Unique (cost=442,500.07..443,497.72 rows=18,139 width=149) (actual rows= loops=)

4.          

CTE mediator_clients

5. 0.000 0.000 ↓ 0.0

Bitmap Heap Scan on clients (cost=182.43..1,468.05 rows=3,410 width=136) (actual rows= loops=)

  • Recheck Cond: (type = 'mediator'::text)
6. 0.000 0.000 ↓ 0.0

Bitmap Index Scan on idx_client_type (cost=0.00..181.57 rows=3,410 width=0) (actual rows= loops=)

  • Index Cond: (type = 'mediator'::text)
7. 0.000 0.000 ↓ 0.0

Sort (cost=441,032.02..441,077.37 rows=18,139 width=149) (actual rows= loops=)

  • Sort Key: p.payment_id, p.paid_at, p.user_id, p.amount, p.currency, p.gateway, ((p.gateway_amount - p.amount)), (CASE WHEN (((ac.created_ts IS NOT NULL) AND (CASE WHEN (ac.data_before = u.user_id) THEN true ELSE false END)) OR ((ac.created_ts IS NULL) AND (a.owner_id IS NOT NULL))) THEN true ELSE false END), (CASE WHEN (m.agency_owner_id IS NOT NULL) THEN true ELSE false END), (CASE WHEN (c.is_io IS NOT NULL) THEN c.is_io ELSE false END), (CASE WHEN ((c.user_id IS NOT NULL) AND (c.type = 'agency'::text) AND (NOT c.is_io)) THEN true ELSE false END), (CASE WHEN ((mc.user_id IS NOT NULL) AND (mc.type = 'mediator'::text)) THEN true ELSE false END), p.contract_type, p.contract_id, tr.plan_title, tr.reason, tr.start_date, u.registration_date, u.country, u.locale, u.active
8. 0.000 0.000 ↓ 0.0

Nested Loop Left Join (cost=72,681.23..439,748.98 rows=18,139 width=149) (actual rows= loops=)

9. 0.000 0.000 ↓ 0.0

Nested Loop Left Join (cost=72,669.26..222,021.60 rows=18,139 width=175) (actual rows= loops=)

10. 0.000 0.000 ↓ 0.0

Hash Right Join (cost=72,661.25..76,365.43 rows=18,139 width=166) (actual rows= loops=)

  • Hash Cond: (c.user_id = u.user_id)
  • Join Filter: ((c.created_at <= p.paid_at) AND (COALESCE(c.deleted_at, p.paid_at) >= p.paid_at))
11. 0.000 0.000 ↓ 0.0

Seq Scan on clients c (cost=0.00..1,881.99 rows=63,899 width=28) (actual rows= loops=)

12. 0.000 0.000 ↓ 0.0

Hash (cost=72,434.52..72,434.52 rows=18,139 width=154) (actual rows= loops=)

13. 0.000 0.000 ↓ 0.0

Hash Right Join (cost=72,268.50..72,434.52 rows=18,139 width=154) (actual rows= loops=)

  • Hash Cond: (m.agency_owner_id = u.user_id)
  • Join Filter: ((m.created_at <= p.paid_at) AND (COALESCE(m.deleted_at, p.paid_at) >= p.paid_at) AND (COALESCE(m.valid_till_date, p.paid_at) >= p.paid_at))
14. 0.000 0.000 ↓ 0.0

CTE Scan on mediator_clients m (cost=0.00..68.20 rows=3,410 width=28) (actual rows= loops=)

15. 0.000 0.000 ↓ 0.0

Hash (cost=72,041.76..72,041.76 rows=18,139 width=150) (actual rows= loops=)

16. 0.000 0.000 ↓ 0.0

Hash Right Join (cost=71,875.74..72,041.76 rows=18,139 width=150) (actual rows= loops=)

  • Hash Cond: (mc.user_id = u.user_id)
  • Join Filter: ((mc.created_at <= p.paid_at) AND (COALESCE(mc.deleted_at, p.paid_at) >= p.paid_at) AND (COALESCE(mc.valid_till_date, p.paid_at) >= p.paid_at))
17. 0.000 0.000 ↓ 0.0

CTE Scan on mediator_clients mc (cost=0.00..68.20 rows=3,410 width=60) (actual rows= loops=)

18. 0.000 0.000 ↓ 0.0

Hash (cost=71,649.01..71,649.01 rows=18,139 width=114) (actual rows= loops=)

19. 0.000 0.000 ↓ 0.0

Gather (cost=1,385.74..71,649.01 rows=18,139 width=114) (actual rows= loops=)

  • Workers Planned: 2
20. 0.000 0.000 ↓ 0.0

Hash Left Join (cost=385.74..68,835.11 rows=7,558 width=114) (actual rows= loops=)

  • Hash Cond: (u.user_id = a.owner_id)
  • Join Filter: (a.created_at <= p.paid_at)
21. 0.000 0.000 ↓ 0.0

Nested Loop Left Join (cost=0.42..68,418.54 rows=7,558 width=110) (actual rows= loops=)

22. 0.000 0.000 ↓ 0.0

Parallel Seq Scan on payments p (cost=0.00..44,055.32 rows=3,431 width=89) (actual rows= loops=)

  • Filter: ((paid_at)::date = '2019-03-23'::date)
23. 0.000 0.000 ↓ 0.0

Index Scan using idx_users_user_id on users u (cost=0.42..7.08 rows=2 width=21) (actual rows= loops=)

  • Index Cond: (p.user_id = user_id)
24. 0.000 0.000 ↓ 0.0

Hash (cost=282.36..282.36 rows=8,236 width=12) (actual rows= loops=)

25. 0.000 0.000 ↓ 0.0

Seq Scan on agencies a (cost=0.00..282.36 rows=8,236 width=12) (actual rows= loops=)

26. 0.000 0.000 ↓ 0.0

Limit (cost=8.01..8.01 rows=1 width=9) (actual rows= loops=)

27. 0.000 0.000 ↓ 0.0

Sort (cost=8.01..8.01 rows=1 width=9) (actual rows= loops=)

  • Sort Key: ac.created_ts
28. 0.000 0.000 ↓ 0.0

Seq Scan on agency_changelog ac (cost=0.00..8.00 rows=1 width=9) (actual rows= loops=)

  • Filter: ((created_ts > p.paid_at) AND ((data_before = u.user_id) OR (data_after = u.user_id)))
29. 0.000 0.000 ↓ 0.0

Limit (cost=11.97..11.98 rows=1 width=22) (actual rows= loops=)

30. 0.000 0.000 ↓ 0.0

Sort (cost=11.97..11.98 rows=1 width=22) (actual rows= loops=)

  • Sort Key: tr.start_date DESC
31. 0.000 0.000 ↓ 0.0

Index Scan using idx_tariff_user_id on tariffs tr (cost=0.42..11.96 rows=1 width=22) (actual rows= loops=)

  • Index Cond: (user_id = u.user_id)
  • Filter: (start_date <= p.paid_at)