explain.depesz.com

PostgreSQL's explain analyze made readable

Result: y4c

Settings
# exclusive inclusive rows x rows loops node
1. 0.589 32,169.766 ↑ 213,607.8 1,138 1

Sort (cost=69,398,476.52..70,006,190.63 rows=243,085,644 width=13) (actual time=32,169.606..32,169.766 rows=1,138 loops=1)

  • Output: ur.id, (COALESCE(ft.fast_track, false)), (COALESCE(ur.delayed_to, (ur.created_at + CASE WHEN ft.fast_track THEN '01:00:00'::interval WHEN ('cpa'::order_label = ANY (ur.order_labels)) THEN '72:00:00'::interval WHEN (u.status_id = ANY ('{5,6,7,8,9,10,11,12}'::integer[])) THEN '03:00:00'::interval ELSE '24:00:00'::interval END)))
  • Sort Key: (COALESCE(ur.delayed_to, (ur.created_at + CASE WHEN ft.fast_track THEN '01:00:00'::interval WHEN ('cpa'::order_label = ANY (ur.order_labels)) THEN '72:00:00'::interval WHEN (u.status_id = ANY ('{5,6,7,8,9,10,11,12}'::integer[])) THEN '03:00:00'::interval ELSE '24:00:00'::interval END))) DESC
  • Sort Method: quicksort Memory: 102kB
  • Buffers: shared hit=25,067,771 read=3 dirtied=2,293, temp read=104,541 written=104,541
  • I/O Timings: read=0.670
2.          

CTE unprocessed_requests

3. 0.268 4.192 ↑ 1,070.6 1,138 1

Hash Left Join (cost=20.42..557,740.21 rows=1,218,337 width=85) (actual time=0.172..4.192 rows=1,138 loops=1)

  • Output: go.id, go.user_id, go.amount_usd, go.created_at, go.delayed_to, go.order_labels, ps.handler, go.status, go.provider_name
  • Inner Unique: true
  • Hash Cond: (go.payment_system_id = ps.id)
  • Buffers: shared hit=1,289 dirtied=9
4. 3.772 3.772 ↑ 1,070.6 1,138 1

Index Scan using index_gamepay_orders_on_id on public.gamepay_orders go (cost=0.28..554,473.27 rows=1,218,337 width=77) (actual time=0.011..3.772 rows=1,138 loops=1)

  • Output: go.id, go.user_id, go.amount_usd, go.created_at, go.delayed_to, go.order_labels, go.status, go.provider_name, go.payment_system_id
  • Buffers: shared hit=1,275 dirtied=9
5. 0.053 0.152 ↓ 1.0 281 1

Hash (cost=16.73..16.73 rows=273 width=16) (actual time=0.152..0.152 rows=281 loops=1)

  • Output: ps.handler, ps.id
  • Buckets: 1,024 Batches: 1 Memory Usage: 22kB
  • Buffers: shared hit=14
6. 0.099 0.099 ↓ 1.0 281 1

Seq Scan on public.payment_systems ps (cost=0.00..16.73 rows=273 width=16) (actual time=0.022..0.099 rows=281 loops=1)

  • Output: ps.handler, ps.id
  • Buffers: shared hit=14
7.          

CTE request_sums

8. 0.859 0.971 ↓ 4.5 902 1

HashAggregate (cost=30,458.42..30,460.92 rows=200 width=36) (actual time=0.730..0.971 rows=902 loops=1)

  • Output: ur_1.user_id, sum(ur_1.amount_usd)
  • Group Key: ur_1.user_id
9. 0.112 0.112 ↑ 1,070.6 1,138 1

CTE Scan on unprocessed_requests ur_1 (cost=0.00..24,366.74 rows=1,218,337 width=36) (actual time=0.000..0.112 rows=1,138 loops=1)

  • Output: ur_1.id, ur_1.user_id, ur_1.amount_usd, ur_1.created_at, ur_1.delayed_to, ur_1.order_labels, ur_1.handler, ur_1.status, ur_1.provider_name
10.          

CTE intersections

11. 0.333 6.836 ↓ 4.0 793 1

Nested Loop (cost=0.44..538.50 rows=200 width=4) (actual time=0.024..6.836 rows=793 loops=1)

  • Output: rs.user_id
  • Inner Unique: true
  • Buffers: shared hit=3,607 read=1
  • I/O Timings: read=0.246
12. 0.189 0.189 ↓ 4.5 902 1

CTE Scan on request_sums rs (cost=0.00..4.00 rows=200 width=4) (actual time=0.002..0.189 rows=902 loops=1)

  • Output: rs.user_id, rs.usd_amount
13. 6.314 6.314 ↑ 1.0 1 902

Index Scan using user_union_trees_pkey on public.user_union_trees uut (cost=0.44..2.66 rows=1 width=12) (actual time=0.007..0.007 rows=1 loops=902)

  • Output: uut.user_id, uut.ip_parent_id, uut.ip_tree_size, uut.purse_parent_id, uut.purse_tree_size
  • Index Cond: (uut.user_id = rs.user_id)
  • Filter: ((uut.purse_parent_id <> rs.user_id) OR (uut.purse_tree_size > 1))
  • Rows Removed by Filter: 0
  • Buffers: shared hit=3,607 read=1
  • I/O Timings: read=0.246
14.          

CTE success_withdraws

15. 6.722 126.782 ↑ 630.3 573 1

HashAggregate (cost=32,697.54..36,309.00 rows=361,146 width=5) (actual time=123.362..126.782 rows=573 loops=1)

  • Output: gamepay_orders.user_id, true
  • Group Key: gamepay_orders.user_id
  • Buffers: shared hit=57,267
16. 2.024 120.060 ↑ 20.4 18,580 1

Nested Loop (cost=27,413.02..31,749.14 rows=379,357 width=4) (actual time=0.452..120.060 rows=18,580 loops=1)

  • Output: gamepay_orders.user_id
  • Buffers: shared hit=57,267
17. 0.636 0.776 ↓ 4.5 902 1

HashAggregate (cost=27,412.58..27,414.58 rows=200 width=4) (actual time=0.411..0.776 rows=902 loops=1)

  • Output: unprocessed_requests_1.user_id
  • Group Key: unprocessed_requests_1.user_id
18. 0.140 0.140 ↑ 1,070.6 1,138 1

CTE Scan on unprocessed_requests unprocessed_requests_1 (cost=0.00..24,366.74 rows=1,218,337 width=4) (actual time=0.001..0.140 rows=1,138 loops=1)

  • Output: unprocessed_requests_1.id, unprocessed_requests_1.user_id, unprocessed_requests_1.amount_usd, unprocessed_requests_1.created_at, unprocessed_requests_1.delayed_to, unprocessed_requests_1.order_labels, unprocessed_requests_1.handler, unprocessed_requests_1.status, unprocessed_requests_1.provider_name
19. 117.260 117.260 ↓ 10.5 21 902

Index Scan using idx_gamepay_orders_on_updated_at_202002_temp4 on public.gamepay_orders (cost=0.43..21.65 rows=2 width=4) (actual time=0.016..0.130 rows=21 loops=902)

  • Output: gamepay_orders.user_id
  • Index Cond: (gamepay_orders.user_id = unprocessed_requests_1.user_id)
  • Filter: (gamepay_orders.amount < 0)
  • Rows Removed by Filter: 40
  • Buffers: shared hit=57,267
20.          

CTE balances_before_ftd

21. 2.401 130.566 ↑ 21.5 902 1

Subquery Scan on bl (cost=457,892.18..583,925.45 rows=19,382 width=12) (actual time=99.540..130.566 rows=902 loops=1)

  • Output: bl.user_id, (bl.balance - bl.amount)
  • Filter: (bl.pos = 1)
  • Rows Removed by Filter: 34,928
  • Buffers: shared hit=38,458 read=2
  • I/O Timings: read=0.424
22. 24.655 128.165 ↑ 108.2 35,830 1

WindowAgg (cost=457,892.18..535,421.30 rows=3,876,456 width=32) (actual time=99.536..128.165 rows=35,830 loops=1)

  • Output: balance_logs.id, balance_logs.user_id, balance_logs.balance, balance_logs.amount, rank() OVER (?)
  • Buffers: shared hit=38,458 read=2
  • I/O Timings: read=0.424
23. 28.362 103.510 ↑ 108.2 35,830 1

Sort (cost=457,892.18..467,583.32 rows=3,876,456 width=24) (actual time=99.510..103.510 rows=35,830 loops=1)

  • Output: balance_logs.id, balance_logs.user_id, balance_logs.balance, balance_logs.amount
  • Sort Key: balance_logs.user_id, balance_logs.id
  • Sort Method: quicksort Memory: 4,336kB
  • Buffers: shared hit=38,458 read=2
  • I/O Timings: read=0.424
24. 4.901 75.148 ↑ 108.2 35,830 1

Nested Loop (cost=27,413.01..33,685.65 rows=3,876,456 width=24) (actual time=0.453..75.148 rows=35,830 loops=1)

  • Output: balance_logs.id, balance_logs.user_id, balance_logs.balance, balance_logs.amount
  • Buffers: shared hit=38,458 read=2
  • I/O Timings: read=0.424
25. 0.679 0.793 ↓ 4.5 902 1

HashAggregate (cost=27,412.58..27,414.58 rows=200 width=4) (actual time=0.436..0.793 rows=902 loops=1)

  • Output: unprocessed_requests_2.user_id
  • Group Key: unprocessed_requests_2.user_id
26. 0.114 0.114 ↑ 1,070.6 1,138 1

CTE Scan on unprocessed_requests unprocessed_requests_2 (cost=0.00..24,366.74 rows=1,218,337 width=4) (actual time=0.001..0.114 rows=1,138 loops=1)

  • Output: unprocessed_requests_2.id, unprocessed_requests_2.user_id, unprocessed_requests_2.amount_usd, unprocessed_requests_2.created_at, unprocessed_requests_2.delayed_to, unprocessed_requests_2.order_labels, unprocessed_requests_2.handler, unprocessed_requests_2.status, unprocessed_requests_2.provider_name
27. 69.454 69.454 ↑ 1.8 40 902

Index Scan using index_balance_logs_on_user_id on public.balance_logs (cost=0.43..30.66 rows=70 width=24) (actual time=0.008..0.077 rows=40 loops=902)

  • Output: balance_logs.id, balance_logs.user_id, balance_logs.balance, balance_logs.amount
  • Index Cond: (balance_logs.user_id = unprocessed_requests_2.user_id)
  • Buffers: shared hit=38,458 read=2
  • I/O Timings: read=0.424
28.          

CTE fast_track

29. 13.865 284.348 ↑ 2,574.5 31 1

HashAggregate (cost=1,175,280.73..1,192,679.78 rows=79,809 width=45) (actual time=270.897..284.348 rows=31 loops=1)

  • Output: ur_2.id, true, u_1.id, rs_1.usd_amount, c.id
  • Group Key: ur_2.id, u_1.id, rs_1.usd_amount, c.id
  • Filter: (c.auto_fast_track OR (c.auto_fast_track_for_verified AND (u_1.docs_verified_at IS NOT NULL)) OR (count(gp.id) = 1))
  • Rows Removed by Filter: 1
  • Buffers: shared hit=99,988 read=3
  • I/O Timings: read=0.670
30. 0.059 270.483 ↑ 12,427.9 112 1

Hash Join (cost=149,311.11..1,157,881.68 rows=1,391,924 width=58) (actual time=262.722..270.483 rows=112 loops=1)

  • Output: ur_2.id, u_1.id, rs_1.usd_amount, c.id, c.auto_fast_track, c.auto_fast_track_for_verified, u_1.docs_verified_at, gp.id
  • Inner Unique: true
  • Hash Cond: (ur_2.user_id = unprocessed_requests_3.user_id)
  • Buffers: shared hit=99,988 read=3
  • I/O Timings: read=0.670
31. 0.043 269.721 ↑ 24,855.8 112 1

Hash Left Join (cost=121,894.03..1,107,671.84 rows=2,783,848 width=62) (actual time=262.000..269.721 rows=112 loops=1)

  • Output: ur_2.id, ur_2.user_id, u_1.id, u_1.docs_verified_at, gp.id, rs_1.usd_amount, c.id, c.auto_fast_track, c.auto_fast_track_for_verified
  • Hash Cond: (c.id = cbs.country_id)
  • Filter: ((cbs.withdraw IS NULL) OR (NOT cbs.withdraw))
  • Buffers: shared hit=99,988 read=3
  • I/O Timings: read=0.670
32. 0.377 269.629 ↑ 61,131.8 112 1

Hash Left Join (cost=121,889.15..1,046,653.48 rows=6,846,761 width=62) (actual time=261.943..269.629 rows=112 loops=1)

  • Output: ur_2.id, ur_2.user_id, u_1.id, u_1.docs_verified_at, gp.id, rs_1.usd_amount, c.id, c.auto_fast_track, c.auto_fast_track_for_verified
  • Inner Unique: true
  • Hash Cond: (u_1.country_id = c.id)
  • Filter: (c.auto_fast_track OR (c.auto_fast_track_for_verified AND (u_1.docs_verified_at IS NOT NULL)) OR (((bbf.balance_before_ftd = 0) OR sw.has_success_withdraws) AND (NOT (hashed SubPlan 6)) AND (((u_1.deposits_sum_usd <= '50000'::numeric) AND (abs(rs_1.usd_amount) <= ((u_1.deposits_sum_usd + (u_1.withdraws_sum_usd)::numeric) + '50000'::numeric))) OR ((u_1.deposits_sum_usd > '50000'::numeric) AND (abs(rs_1.usd_amount) <= ((u_1.deposits_sum_usd * '2'::numeric) + (u_1.withdraws_sum_usd)::numeric))))))
  • Rows Removed by Filter: 102
  • Buffers: shared hit=99,986 read=3
  • I/O Timings: read=0.670
33. 0.146 261.916 ↑ 242,583.8 214 1

Merge Left Join (cost=121,848.07..900,685.80 rows=51,912,932 width=80) (actual time=261.690..261.916 rows=214 loops=1)

  • Output: ur_2.id, ur_2.user_id, u_1.id, u_1.docs_verified_at, u_1.country_id, u_1.deposits_sum_usd, u_1.withdraws_sum_usd, gp.id, rs_1.usd_amount, bbf.balance_before_ftd, sw.has_success_withdraws
  • Merge Cond: (ur_2.user_id = sw.user_id)
  • Buffers: shared hit=96,348 read=2
  • I/O Timings: read=0.424
34. 0.127 134.600 ↑ 134.3 214 1

Sort (cost=81,287.36..81,359.23 rows=28,749 width=79) (actual time=134.581..134.600 rows=214 loops=1)

  • Output: ur_2.id, ur_2.user_id, u_1.id, u_1.docs_verified_at, u_1.country_id, u_1.deposits_sum_usd, u_1.withdraws_sum_usd, gp.id, rs_1.usd_amount, bbf.balance_before_ftd
  • Sort Key: ur_2.user_id
  • Sort Method: quicksort Memory: 55kB
  • Buffers: shared hit=39,081 read=2
  • I/O Timings: read=0.424
35. 0.682 134.473 ↑ 134.3 214 1

Hash Right Join (cost=78,697.84..79,158.32 rows=28,749 width=79) (actual time=103.564..134.473 rows=214 loops=1)

  • Output: ur_2.id, ur_2.user_id, u_1.id, u_1.docs_verified_at, u_1.country_id, u_1.deposits_sum_usd, u_1.withdraws_sum_usd, gp.id, rs_1.usd_amount, bbf.balance_before_ftd
  • Hash Cond: (bbf.user_id = u_1.id)
  • Buffers: shared hit=39,081 read=2
  • I/O Timings: read=0.424
36. 130.846 130.846 ↑ 21.5 902 1

CTE Scan on balances_before_ftd bbf (cost=0.00..387.64 rows=19,382 width=12) (actual time=99.543..130.846 rows=902 loops=1)

  • Output: bbf.user_id, bbf.balance_before_ftd
  • Buffers: shared hit=38,458 read=2
  • I/O Timings: read=0.424
37. 0.075 2.945 ↑ 134.3 214 1

Hash (cost=78,338.48..78,338.48 rows=28,749 width=71) (actual time=2.945..2.945 rows=214 loops=1)

  • Output: ur_2.id, ur_2.user_id, u_1.id, u_1.docs_verified_at, u_1.country_id, u_1.deposits_sum_usd, u_1.withdraws_sum_usd, gp.id, rs_1.usd_amount
  • Buckets: 32,768 Batches: 1 Memory Usage: 274kB
  • Buffers: shared hit=623
38. 0.082 2.870 ↑ 134.3 214 1

Hash Left Join (cost=7.50..78,338.48 rows=28,749 width=71) (actual time=1.625..2.870 rows=214 loops=1)

  • Output: ur_2.id, ur_2.user_id, u_1.id, u_1.docs_verified_at, u_1.country_id, u_1.deposits_sum_usd, u_1.withdraws_sum_usd, gp.id, rs_1.usd_amount
  • Hash Cond: (u_1.id = rs_1.user_id)
  • Buffers: shared hit=623
39. 0.074 1.384 ↑ 134.3 214 1

Nested Loop Left Join (cost=0.99..77,541.37 rows=28,749 width=39) (actual time=0.198..1.384 rows=214 loops=1)

  • Output: ur_2.id, ur_2.user_id, u_1.id, u_1.docs_verified_at, u_1.country_id, u_1.deposits_sum_usd, u_1.withdraws_sum_usd, gp.id
  • Buffers: shared hit=623
40. 0.017 0.750 ↑ 108.6 56 1

Nested Loop (cost=0.56..44,201.87 rows=6,081 width=35) (actual time=0.177..0.750 rows=56 loops=1)

  • Output: ur_2.id, ur_2.user_id, u_1.id, u_1.docs_verified_at, u_1.country_id, u_1.deposits_sum_usd, u_1.withdraws_sum_usd
  • Inner Unique: true
  • Buffers: shared hit=285
41. 0.163 0.163 ↑ 106.9 57 1

CTE Scan on unprocessed_requests ur_2 (cost=0.00..27,412.58 rows=6,092 width=8) (actual time=0.147..0.163 rows=57 loops=1)

  • Output: ur_2.id, ur_2.user_id, ur_2.amount_usd, ur_2.created_at, ur_2.delayed_to, ur_2.order_labels, ur_2.handler, ur_2.status, ur_2.provider_name
  • Filter: ((ur_2.status)::text = 'pending'::text)
  • Rows Removed by Filter: 1,081
42. 0.570 0.570 ↑ 1.0 1 57

Index Scan using idx_users_temp on public.users u_1 (cost=0.56..2.76 rows=1 width=27) (actual time=0.010..0.010 rows=1 loops=57)

  • Output: u_1.id, u_1.docs_verified_at, u_1.country_id, u_1.deposits_sum_usd, u_1.withdraws_sum_usd
  • Index Cond: (u_1.id = ur_2.user_id)
  • Filter: ((NOT u_1.test) AND (NOT u_1.auto_process_banned))
  • Rows Removed by Filter: 0
  • Buffers: shared hit=285
43. 0.560 0.560 ↑ 1.2 4 56

Index Scan using gamepay_purses_user_id_idx_1909 on public.gamepay_purses gp (cost=0.43..5.43 rows=5 width=8) (actual time=0.007..0.010 rows=4 loops=56)

  • Output: gp.id, gp.handler, gp.user_id, gp.purse, gp.data, gp.display_purse, gp.encrypted_card_number, gp.encrypted_card_holder, gp.encrypted_card_expire, gp.payment_system_id, gp.recurring_valid_thru, gp.created_at, gp.updated_at, gp.provider_name, gp.verified_at, gp.requested_at, gp.received_at, gp.status, gp.pgw
  • Index Cond: (ur_2.user_id = gp.user_id)
  • Buffers: shared hit=338
44. 0.171 1.404 ↓ 4.5 902 1

Hash (cost=4.00..4.00 rows=200 width=36) (actual time=1.404..1.404 rows=902 loops=1)

  • Output: rs_1.usd_amount, rs_1.user_id
  • Buckets: 1,024 Batches: 1 Memory Usage: 47kB
45. 1.233 1.233 ↓ 4.5 902 1

CTE Scan on request_sums rs_1 (cost=0.00..4.00 rows=200 width=36) (actual time=0.733..1.233 rows=902 loops=1)

  • Output: rs_1.usd_amount, rs_1.user_id
46. 0.221 127.170 ↑ 537.4 672 1

Sort (cost=40,560.71..41,463.57 rows=361,146 width=5) (actual time=127.092..127.170 rows=672 loops=1)

  • Output: sw.user_id, sw.has_success_withdraws
  • Sort Key: sw.user_id
  • Sort Method: quicksort Memory: 51kB
  • Buffers: shared hit=57,267
47. 126.949 126.949 ↑ 630.3 573 1

CTE Scan on success_withdraws sw (cost=0.00..7,222.92 rows=361,146 width=5) (actual time=123.365..126.949 rows=573 loops=1)

  • Output: sw.user_id, sw.has_success_withdraws
  • Buffers: shared hit=57,267
48. 0.039 0.231 ↑ 1.0 248 1

Hash (cost=33.48..33.48 rows=248 width=6) (actual time=0.231..0.231 rows=248 loops=1)

  • Output: c.id, c.auto_fast_track, c.auto_fast_track_for_verified
  • Buckets: 1,024 Batches: 1 Memory Usage: 18kB
  • Buffers: shared hit=31
49. 0.192 0.192 ↑ 1.0 248 1

Seq Scan on public.countries c (cost=0.00..33.48 rows=248 width=6) (actual time=0.011..0.192 rows=248 loops=1)

  • Output: c.id, c.auto_fast_track, c.auto_fast_track_for_verified
  • Buffers: shared hit=31
50.          

SubPlan (for Hash Left Join)

51. 7.105 7.105 ↓ 4.0 793 1

CTE Scan on intersections (cost=0.00..4.00 rows=200 width=4) (actual time=0.025..7.105 rows=793 loops=1)

  • Output: intersections.user_id
  • Buffers: shared hit=3,607 read=1
  • I/O Timings: read=0.246
52. 0.019 0.049 ↓ 1.0 129 1

Hash (cost=3.28..3.28 rows=128 width=5) (actual time=0.048..0.049 rows=129 loops=1)

  • Output: cbs.country_id, cbs.withdraw
  • Buckets: 1,024 Batches: 1 Memory Usage: 13kB
  • Buffers: shared hit=2
53. 0.030 0.030 ↓ 1.0 129 1

Seq Scan on public.country_blocking_settings cbs (cost=0.00..3.28 rows=128 width=5) (actual time=0.011..0.030 rows=129 loops=1)

  • Output: cbs.country_id, cbs.withdraw
  • Buffers: shared hit=2
54. 0.146 0.703 ↓ 4.5 902 1

Hash (cost=27,414.58..27,414.58 rows=200 width=4) (actual time=0.703..0.703 rows=902 loops=1)

  • Output: unprocessed_requests_3.user_id
  • Buckets: 1,024 Batches: 1 Memory Usage: 40kB
55. 0.400 0.557 ↓ 4.5 902 1

HashAggregate (cost=27,412.58..27,414.58 rows=200 width=4) (actual time=0.463..0.557 rows=902 loops=1)

  • Output: unprocessed_requests_3.user_id
  • Group Key: unprocessed_requests_3.user_id
56. 0.157 0.157 ↑ 1,070.6 1,138 1

CTE Scan on unprocessed_requests unprocessed_requests_3 (cost=0.00..24,366.74 rows=1,218,337 width=4) (actual time=0.001..0.157 rows=1,138 loops=1)

  • Output: unprocessed_requests_3.user_id
57. 0.838 32,169.177 ↑ 213,607.8 1,138 1

Hash Join (cost=1,497,043.33..30,705,542.00 rows=243,085,644 width=13) (actual time=23,425.444..32,169.177 rows=1,138 loops=1)

  • Output: ur.id, COALESCE(ft.fast_track, false), COALESCE(ur.delayed_to, (ur.created_at + CASE WHEN ft.fast_track THEN '01:00:00'::interval WHEN ('cpa'::order_label = ANY (ur.order_labels)) THEN '72:00:00'::interval WHEN (u.status_id = ANY ('{5,6,7,8,9,10,11,12}'::integer[])) THEN '03:00:00'::interval ELSE '24:00:00'::interval END))
  • Inner Unique: true
  • Hash Cond: (ur.user_id = unprocessed_requests.user_id)
  • Buffers: shared hit=25,067,771 read=3 dirtied=2,293, temp read=104,541 written=104,541
  • I/O Timings: read=0.670
58. 8,796.783 32,162.726 ↑ 427,215.5 1,138 1

Hash Join (cost=1,469,626.24..20,620,456.40 rows=486,171,288 width=65) (actual time=23,419.814..32,162.726 rows=1,138 loops=1)

  • Output: ur.id, ur.delayed_to, ur.created_at, ur.order_labels, ur.user_id, ft.fast_track, u.status_id, u.id
  • Inner Unique: true
  • Hash Cond: (ur.user_id = u.id)
  • Buffers: shared hit=25,066,482 read=3 dirtied=2,284, temp read=104,541 written=104,541
  • I/O Timings: read=0.670
59. 0.309 285.264 ↑ 427,215.5 1,138 1

Merge Right Join (cost=155,613.51..7,448,581.88 rows=486,171,288 width=57) (actual time=284.845..285.264 rows=1,138 loops=1)

  • Output: ur.id, ur.delayed_to, ur.created_at, ur.order_labels, ur.user_id, ft.fast_track
  • Merge Cond: (ft.id = ur.id)
  • Buffers: shared hit=99,988 read=3
  • I/O Timings: read=0.670
60. 0.034 284.424 ↑ 2,574.5 31 1

Sort (cost=8,094.33..8,293.86 rows=79,809 width=5) (actual time=284.418..284.424 rows=31 loops=1)

  • Output: ft.fast_track, ft.id
  • Sort Key: ft.id
  • Sort Method: quicksort Memory: 26kB
  • Buffers: shared hit=99,988 read=3
  • I/O Timings: read=0.670
61. 284.390 284.390 ↑ 2,574.5 31 1

CTE Scan on fast_track ft (cost=0.00..1,596.18 rows=79,809 width=5) (actual time=270.901..284.390 rows=31 loops=1)

  • Output: ft.fast_track, ft.id
  • Buffers: shared hit=99,988 read=3
  • I/O Timings: read=0.670
62. 0.315 0.531 ↑ 1,070.6 1,138 1

Sort (cost=147,519.18..150,565.02 rows=1,218,337 width=56) (actual time=0.420..0.531 rows=1,138 loops=1)

  • Output: ur.id, ur.delayed_to, ur.created_at, ur.order_labels, ur.user_id
  • Sort Key: ur.id
  • Sort Method: quicksort Memory: 154kB
63. 0.216 0.216 ↑ 1,070.6 1,138 1

CTE Scan on unprocessed_requests ur (cost=0.00..24,366.74 rows=1,218,337 width=56) (actual time=0.003..0.216 rows=1,138 loops=1)

  • Output: ur.id, ur.delayed_to, ur.created_at, ur.order_labels, ur.user_id
64. 9,368.654 23,080.679 ↑ 1.0 34,945,099 1

Hash (cost=740,404.33..740,404.33 rows=34,962,752 width=8) (actual time=23,080.679..23,080.679 rows=34,945,099 loops=1)

  • Output: u.status_id, u.id
  • Buckets: 8,388,608 Batches: 8 Memory Usage: 236,024kB
  • Buffers: shared hit=24,966,494 dirtied=2,284, temp written=104,520
65. 13,712.025 13,712.025 ↑ 1.0 34,945,099 1

Index Only Scan using idx_users_temp on public.users u (cost=0.56..740,404.33 rows=34,962,752 width=8) (actual time=0.023..13,712.025 rows=34,945,099 loops=1)

  • Output: u.status_id, u.id
  • Heap Fetches: 600,308
  • Buffers: shared hit=24,966,494 dirtied=2,284
66. 0.125 5.613 ↓ 4.5 902 1

Hash (cost=27,414.58..27,414.58 rows=200 width=4) (actual time=5.613..5.613 rows=902 loops=1)

  • Output: unprocessed_requests.user_id
  • Buckets: 1,024 Batches: 1 Memory Usage: 40kB
  • Buffers: shared hit=1,289 dirtied=9
67. 0.464 5.488 ↓ 4.5 902 1

HashAggregate (cost=27,412.58..27,414.58 rows=200 width=4) (actual time=5.362..5.488 rows=902 loops=1)

  • Output: unprocessed_requests.user_id
  • Group Key: unprocessed_requests.user_id
  • Buffers: shared hit=1,289 dirtied=9
68. 5.024 5.024 ↑ 1,070.6 1,138 1

CTE Scan on unprocessed_requests (cost=0.00..24,366.74 rows=1,218,337 width=4) (actual time=0.175..5.024 rows=1,138 loops=1)

  • Output: unprocessed_requests.user_id
  • Buffers: shared hit=1,289 dirtied=9