22 经得住数据增长的查询

-- the last row of the page before OFFSET 1000000 is the cursor
select created_at as cur_ts, id as cur_id
from events order by created_at desc, id desc offset 999999 limit 1 \gset

select lab.prove(
  'keyset returns exactly the page OFFSET 1,000,000 returns',
  (select array_agg(id order by created_at desc, id desc) from (
     select id, created_at from events
     where (created_at, id) < (:'cur_ts'::timestamptz, :'cur_id'::bigint)
     order by created_at desc, id desc limit 20) k)
  = (select array_agg(id order by created_at desc, id desc) from (
     select id, created_at from events
     order by created_at desc, id desc limit 20 offset 1000000) o));

select lab.prove(
  'keyset reads under 10 pages at the same depth',
  lab.buffers(format($$ select id, kind, created_at from events
                        where (created_at, id) < (%L::timestamptz, %s)
                        order by created_at desc, id desc limit 20 $$,
                     :'cur_ts', :'cur_id')) < 10);

select lab.prove(
  'NOT IN over 2 million events is a plain subplan costed in the billions',
  (select (p ->> 'Total Cost')::numeric > 1e9
      and lab.find_node(p, 'Seq Scan') ->> 'Filter' not like '%hashed%'
   from lab.estimate($$ select count(*) from users u
                        where u.id not in (select user_id from events) $$) p));

select lab.prove(
  'NOT EXISTS over the same tables is an anti-join costed under 100,000',
  (select (p ->> 'Total Cost')::numeric < 100000
      and (lab.join_types($$ select count(*) from users u
                    where not exists (select 1 from events e where e.user_id = u.id) $$))[1] like '%Anti'
   from lab.estimate($$ select count(*) from users u
                        where not exists (select 1 from events e where e.user_id = u.id) $$) p));

select lab.prove(
  'where id = 42 is an index scan that reads under 10 pages',
  'Index Scan' = any(lab.nodes('select * from events where id = 42'))
  and lab.buffers('select * from events where id = 42') < 10);
select lab.prove(
  'where id = 42.0 casts the column to numeric and reads the whole table (over 35,000 pages)',
  lab.estimate('select * from events where id = 42.0') ->> 'Filter' = '((id)::numeric = 42.0)'
  and lab.buffers('select * from events where id = 42.0') > 35000);

select lab.prove(
  'an OR across the join scans both tables: over 1,000 pages for one row',
  (select (p -> 'Plan' ->> 'Actual Rows')::numeric = 1
      and (p -> 'Plan' ->> 'Shared Hit Blocks')::int + (p -> 'Plan' ->> 'Shared Read Blocks')::int > 1000
   from lab.plan($$ select p.id, p.name from projects p
                    join users u on u.id = p.owner_id
                    where p.name = 'Project 4242' or u.email = 'user29695@example.com' $$) p));

select lab.prove(
  'the UNION of two indexed queries reads under 30 pages for the same row',
  (select (p -> 'Plan' ->> 'Actual Rows')::numeric = 1
      and (p -> 'Plan' ->> 'Shared Hit Blocks')::int + (p -> 'Plan' ->> 'Shared Read Blocks')::int < 30
   from lab.plan($$ select p.id, p.name from projects p where p.name = 'Project 4242'
                    union
                    select p.id, p.name from projects p
                    join users u on u.id = p.owner_id
                    where u.email = 'user29695@example.com' $$) p));

-- :'last_week' is '2026-09-29' (set at the top of the lab)
select lab.prove(
  'a CTE referenced once is inlined: one index scan, under 20 pages',
  (select p -> 'Plan' ->> 'Node Type' = 'Index Scan'
      and (p -> 'Plan' ->> 'Shared Hit Blocks')::int + (p -> 'Plan' ->> 'Shared Read Blocks')::int < 20
   from lab.plan(format($$ with recent as (
                             select * from events where created_at > %L)
                           select id, kind from recent where project_id = 4242 $$, :'last_week')) p));

select lab.prove(
  'MATERIALIZED fetches the whole week first: a CTE Scan, hundreds of pages, and a temp file',
  (select p -> 'Plan' ->> 'Node Type' = 'CTE Scan'
      and (p -> 'Plan' ->> 'Shared Hit Blocks')::int + (p -> 'Plan' ->> 'Shared Read Blocks')::int > 500
      and (p -> 'Plan' ->> 'Temp Written Blocks')::int > 0
   from lab.plan(format($$ with recent as materialized (
                             select * from events where created_at > %L)
                           select id, kind from recent where project_id = 4242 $$, :'last_week')) p));

select lab.prove(
  'the retry with the same key inserts nothing and returns no rows',
  (select count(*) from ids) = 0 and (select count(*) from payments) = 1);

select dblink_exec('a', 'begin') \g /dev/null
select dblink_exec('a', $$insert into project_daily_counts values (1, '2026-01-01', 'x', 1)
  on conflict (project_id, day, kind) do update set n = project_daily_counts.n + excluded.n$$) \g /dev/null
select dblink_send_query('b', $$insert into project_daily_counts values (1, '2026-01-01', 'x', 1)
  on conflict (project_id, day, kind) do update set n = project_daily_counts.n + excluded.n$$) \g /dev/null
select pg_sleep(0.3) \g /dev/null

select lab.prove(
  'a concurrent ON CONFLICT on the same key waits for the first transaction',
  (select wait_event_type = 'Lock' from pg_stat_activity where application_name = 'upsert-b'));

select dblink_exec('a', 'commit') \g /dev/null
select lab.finish('b') as b_result \gset

select lab.prove(
  'then it updates instead of failing: no error, n = 2',
  :'b_result' = 'ok'
  and (select n from project_daily_counts where project_id = 1 and kind = 'x') = 2);

-- session a has run this MERGE in an open transaction; session b runs it too
select dblink_send_query('b', $$merge into project_daily_counts t
  using (values (2::bigint, date '2026-01-01', 'x', 1::bigint)) s(p, d, k, n)
  on t.project_id = s.p and t.day = s.d and t.kind = s.k
  when matched then update set n = t.n + s.n
  when not matched then insert values (s.p, s.d, s.k, s.n)$$) \g /dev/null
select pg_sleep(0.3) \g /dev/null
select dblink_exec('a', 'commit') \g /dev/null
select lab.finish('b') as b_result \gset

select lab.prove(
  'two concurrent MERGEs both decide the key is missing, and the second fails with a unique violation',
  :'b_result' like '%duplicate key value violates unique constraint%');

select lab.prove(
  'row_number() fetches every event of every project: about 2,084 rows and over 1,500 pages for 60 results',
  (select (p -> 'Plan' ->> 'Actual Rows')::numeric = 60
      and (lab.find_node(p -> 'Plan', 'Sort') ->> 'Actual Rows')::numeric between 2000 and 2200
      and (p -> 'Plan' ->> 'Shared Hit Blocks')::int + (p -> 'Plan' ->> 'Shared Read Blocks')::int > 1500
   from lab.plan($$ select * from (
                      select p.id as pid, p.name, e.id, e.kind, e.created_at,
                             row_number() over (partition by e.project_id order by e.created_at desc) as rn
                      from projects p
                      join events e on e.project_id = p.id
                      where p.account_id = 42) t
                    where rn <= 3 $$) p));

select lab.prove(
  'LATERAL with LIMIT 3 runs 20 short index scans: the same 60 rows in about 311 pages',
  (select (p -> 'Plan' ->> 'Actual Rows')::numeric = 60
      and (lab.find_node(p -> 'Plan', 'Limit') ->> 'Actual Loops')::int = 20
      and (p -> 'Plan' ->> 'Shared Hit Blocks')::int + (p -> 'Plan' ->> 'Shared Read Blocks')::int < 400
   from lab.plan($$ select p.id, p.name, e.id, e.kind, e.created_at
                    from projects p
                    cross join lateral (
                      select id, kind, created_at from events
                      where project_id = p.id
                      order by created_at desc
                      limit 3) e
                    where p.account_id = 42 $$) p));