explain.depesz.com

PostgreSQL's explain analyze made readable

Result: xQcn

Settings
# exclusive inclusive rows x rows loops node
1. 9,109.376 9,109.376 ↑ 20.2 898 1

CTE Scan on raw_agg_data (cost=443,497.72..443,951.19 rows=18,139 width=318) (actual time=9,095.844..9,109.376 rows=898 loops=1)

2.          

CTE raw_agg_data

3. 3.788 9,100.571 ↑ 20.2 898 1

Unique (cost=442,500.07..443,497.72 rows=18,139 width=149) (actual time=9,095.835..9,100.571 rows=898 loops=1)

4.          

CTE mediator_clients

5. 2.883 30.063 ↓ 1.0 3,448 1

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

  • Recheck Cond: (type = 'mediator'::text)
  • Heap Blocks: exact=1009
6. 27.180 27.180 ↓ 1.0 3,448 1

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

  • Index Cond: (type = 'mediator'::text)
7. 29.465 9,096.783 ↑ 4.3 4,245 1

Sort (cost=441,032.02..441,077.37 rows=18,139 width=149) (actual time=9,095.834..9,096.783 rows=4,245 loops=1)

  • 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
  • Sort Method: quicksort Memory: 1256kB
8. 14.670 9,067.318 ↑ 4.3 4,245 1

Nested Loop Left Join (cost=72,681.23..439,748.98 rows=18,139 width=149) (actual time=6,237.123..9,067.318 rows=4,245 loops=1)

9. 3.798 6,798.553 ↑ 4.3 4,245 1

Nested Loop Left Join (cost=72,669.26..222,021.60 rows=18,139 width=175) (actual time=6,237.090..6,798.553 rows=4,245 loops=1)

10. 30.269 6,709.855 ↑ 4.3 4,245 1

Hash Right Join (cost=72,661.25..76,365.43 rows=18,139 width=166) (actual time=6,237.055..6,709.855 rows=4,245 loops=1)

  • 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))
  • Rows Removed by Join Filter: 448
11. 442.837 442.837 ↑ 1.0 63,899 1

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

12. 6.307 6,236.749 ↑ 4.3 4,238 1

Hash (cost=72,434.52..72,434.52 rows=18,139 width=154) (actual time=6,236.749..6,236.749 rows=4,238 loops=1)

  • Buckets: 32768 Batches: 1 Memory Usage: 800kB
13. 5.243 6,230.442 ↑ 4.3 4,238 1

Hash Right Join (cost=72,268.50..72,434.52 rows=18,139 width=154) (actual time=6,228.488..6,230.442 rows=4,238 loops=1)

  • 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))
  • Rows Removed by Join Filter: 65
14. 0.847 0.847 ↓ 1.0 3,448 1

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

15. 4.979 6,224.352 ↑ 4.3 4,226 1

Hash (cost=72,041.76..72,041.76 rows=18,139 width=150) (actual time=6,224.352..6,224.352 rows=4,226 loops=1)

  • Buckets: 32768 Batches: 1 Memory Usage: 798kB
16. 10.468 6,219.373 ↑ 4.3 4,226 1

Hash Right Join (cost=71,875.74..72,041.76 rows=18,139 width=150) (actual time=6,213.078..6,219.373 rows=4,226 loops=1)

  • 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))
  • Rows Removed by Join Filter: 44
17. 33.022 33.022 ↓ 1.0 3,448 1

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

18. 3.656 6,175.883 ↑ 4.3 4,226 1

Hash (cost=71,649.01..71,649.01 rows=18,139 width=114) (actual time=6,175.883..6,175.883 rows=4,226 loops=1)

  • Buckets: 32768 Batches: 1 Memory Usage: 794kB
19. 34.711 6,172.227 ↑ 4.3 4,226 1

Gather (cost=1,385.74..71,649.01 rows=18,139 width=114) (actual time=3,793.992..6,172.227 rows=4,226 loops=1)

  • Workers Planned: 2
  • Workers Launched: 2
20. 1.780 6,137.516 ↑ 5.4 1,409 3

Hash Left Join (cost=385.74..68,835.11 rows=7,558 width=114) (actual time=3,785.316..6,137.516 rows=1,409 loops=3)

  • Hash Cond: (u.user_id = a.owner_id)
  • Join Filter: (a.created_at <= p.paid_at)
21. 2,328.416 6,119.619 ↑ 5.4 1,409 3

Nested Loop Left Join (cost=0.42..68,418.54 rows=7,558 width=110) (actual time=3,769.068..6,119.619 rows=1,409 loops=3)

22. 3,783.388 3,783.388 ↑ 11.5 299 3

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

  • Filter: ((paid_at)::date = '2019-03-23'::date)
  • Rows Removed by Filter: 548678
23. 7.815 7.815 ↓ 2.5 5 896

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

  • Index Cond: (p.user_id = user_id)
24. 6.598 16.117 ↑ 1.0 8,236 3

Hash (cost=282.36..282.36 rows=8,236 width=12) (actual time=16.116..16.117 rows=8,236 loops=3)

  • Buckets: 16384 Batches: 1 Memory Usage: 515kB
25. 9.519 9.519 ↑ 1.0 8,236 3

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

26. 4.245 84.900 ↓ 0.0 0 4,245

Limit (cost=8.01..8.01 rows=1 width=9) (actual time=0.020..0.020 rows=0 loops=4,245)

27. 8.490 80.655 ↓ 0.0 0 4,245

Sort (cost=8.01..8.01 rows=1 width=9) (actual time=0.019..0.019 rows=0 loops=4,245)

  • Sort Key: ac.created_ts
  • Sort Method: quicksort Memory: 25kB
28. 72.165 72.165 ↓ 0.0 0 4,245

Seq Scan on agency_changelog ac (cost=0.00..8.00 rows=1 width=9) (actual time=0.017..0.017 rows=0 loops=4,245)

  • Filter: ((created_ts > p.paid_at) AND ((data_before = u.user_id) OR (data_after = u.user_id)))
  • Rows Removed by Filter: 171
29. 4.245 2,254.095 ↑ 1.0 1 4,245

Limit (cost=11.97..11.98 rows=1 width=22) (actual time=0.530..0.531 rows=1 loops=4,245)

30. 16.980 2,249.850 ↑ 1.0 1 4,245

Sort (cost=11.97..11.98 rows=1 width=22) (actual time=0.530..0.530 rows=1 loops=4,245)

  • Sort Key: tr.start_date DESC
  • Sort Method: quicksort Memory: 25kB
31. 2,232.870 2,232.870 ↓ 3.0 3 4,245

Index Scan using idx_tariff_user_id on tariffs tr (cost=0.42..11.96 rows=1 width=22) (actual time=0.401..0.526 rows=3 loops=4,245)

  • Index Cond: (user_id = u.user_id)
  • Filter: (start_date <= p.paid_at)
  • Rows Removed by Filter: 0