19 读计划，不看时钟

begin;
create temp table p_del as
  select lab.plan($$ delete from events where created_at < '2025-10-11' $$) as p;
select lab.prove('explain analyze of a delete really deletes (about 27,000 rows here)',
  (select count(*) from events) < 2000000
  and (select lab.field(p, 'Seq Scan', 'Actual Rows')::numeric between 20000 and 60000 from p_del));
rollback;
select lab.prove('and rollback puts them back',
  (select count(*) from events) = 2000000);

select lab.prove('PostgreSQL 18 includes Buffers without asking',
  lab.plan_text($$ select * from events where id = 1 $$, 'analyze, costs off') like '%Buffers: shared%');

select lab.prove('buffers are cumulative: the top node reports about 35,367, the events scan 35,173 of them',
  (select pg_temp.bufs(p) between 35364 and 35400 from p_tree)
  and (select (n ->> 'Shared Hit Blocks')::bigint + (n ->> 'Shared Read Blocks')::bigint = 35173
       from (select pg_temp.node(p, '{"Node Type": "Seq Scan", "Relation Name": "events"}') n from p_tree) x));

select lab.prove('the plan''s total cost equals relpages x 1.0 + reltuples x 0.01 + reltuples x 0.0025, to the cent',
  (select round((relpages * 1.0 + reltuples * 0.01 + reltuples * 0.0025)::numeric, 2)
   from pg_class where relname = 'events')
  = (select (p -> 'Plan' ->> 'Total Cost')::numeric
     from (select lab.plan($$ select * from events where kind = 'alert' $$) as p) x));

select lab.prove('the inner Limit ran 20 times, each a full Seq Scan with Filter (user_id = u.id)',
  (select lab.field(p, 'Limit', 'Actual Loops')::int = 20
      and pg_temp.node(p, '{"Node Type": "Seq Scan", "Relation Name": "events"}') ->> 'Filter' = '(user_id = u.id)'
      and (lab.field(p, 'Limit', 'Shared Hit Blocks')::bigint
         + lab.field(p, 'Limit', 'Shared Read Blocks')::bigint) = 20 * (pg_relation_size('events') / 8192)
   from p_ex2));

select lab.prove('before: about 703,714 pages for 20 rows',
  (select pg_temp.bufs(p) between 703500 and 704500
      and (p -> 'Plan' ->> 'Actual Rows')::numeric = 20 from p_ex2));

create index events_user_created_idx on events (user_id, created_at);

-- (lab.plan of the same query, after the index)
select lab.prove('each loop now costs 4 pages: 80 for 20 loops',
  (select (lab.field(p, 'Limit', 'Shared Hit Blocks')::int
         + coalesce(lab.field(p, 'Limit', 'Shared Read Blocks')::int, 0)) = 80
      and lab.field(p, 'Limit', 'Actual Loops')::int = 20
   from p_ex2_after));

select lab.prove('the default selectivity for an expression without statistics is 0.5%; the real share of failed is 25%',
  (select avg((payload->>'status' = 'failed')::int) between 0.24 and 0.26 from events));

select lab.prove('about 975 pages instead of 7,990',
  lab.warm_buffers($$ select p.id, p.name, count(*) as failures
                  from events e
                  join projects p on p.id = e.project_id
                  where e.kind = 'deploy'
                    and e.payload->>'status' = 'failed'
                    and e.created_at >= '2026-09-29'
                  group by p.id, p.name
                  order by failures desc
                  limit 10 $$) between 900 and 1050);

select lab.prove('the estimate (about 245,000) is within a few percent of the actual rows (5% allows for sampling)',
  (select abs((p -> 'Plan' ->> 'Plan Rows')::numeric - 249714) / 249714 < 0.05 from p_alert));

select lab.prove('hash join: 100,000 users hashed in 1 batch at the default work_mem (about 7,274 kB)',
  (select lab.field(p, 'Hash', 'Actual Rows')::numeric = 100000
      and lab.field(p, 'Hash', 'Hash Batches')::int = 1
      and lab.field(p, 'Hash', 'Peak Memory Usage')::int between 7000 and 7500
   from p_hash));

begin;
set local work_mem = '1MB';
create temp table p_hash1 on commit drop as
  select lab.plan($$ select e.id, u.email
                     from events e join users u on u.id = e.user_id
                     where e.created_at >= '2026-09-06' $$) as p;
select lab.prove('at work_mem = 1MB it splits into 4 batches and writes temp files',
  (select lab.field(p, 'Hash', 'Hash Batches')::int = 4
      and lab.field(p, 'Hash', 'Temp Written Blocks')::int > 0
   from p_hash1));
commit;

select lab.prove('sorting 30 days of events (about 164,000 rows) spills: external merge, about 5,480 kB on disk',
  (select lab.field(p, 'Sort', 'Sort Method') = 'external merge'
      and lab.field(p, 'Sort', 'Sort Space Type') = 'Disk'
      and lab.field(p, 'Sort', 'Sort Space Used')::int between 4800 and 6000
      and lab.field(p, 'Sort', 'Actual Rows')::numeric between 150000 and 170000
      and lab.field(p, 'Sort', 'Temp Written Blocks')::int > 0
   from p_sort));

select lab.prove('at work_mem = 32MB it is a quicksort in memory, about 12,566 kB',
  (select lab.field(p, 'Sort', 'Sort Method') = 'quicksort'
      and lab.field(p, 'Sort', 'Sort Space Type') = 'Memory'
      and lab.field(p, 'Sort', 'Sort Space Used')::int between 11000 and 13500
   from p_sort32));

select lab.prove('the Limit is costed near 20,860; the full nested loop over four billion',
  (select (p -> 'Plan' ->> 'Total Cost')::numeric between 19000 and 23000
      and lab.field(p, 'Nested Loop', 'Total Cost')::numeric > 4e9
   from p_limit));

select lab.prove('users 1 and 2 have no events: the third user found the ten rows (loops=3)',
  (select min(user_id) = 3 from events)
  and (select lab.field(p, 'Materialize', 'Actual Loops')::int = 3 from p_limit));

create index events_user_id_idx on events (user_id);

-- The two plans cost within a few percent of each other, so which one wins
-- depends on the kit's sampled statistics. Both read 16 pages.
select lab.prove('with the index, it reads 16 pages: the events index feeds a merge join, or a nested loop with a Memoize (9 hits, 1 miss)',
  lab.warm_buffers($$ select e.id, e.kind, u.email
                  from events e join users u on u.id = e.user_id
                  order by e.user_id limit 10 $$) = 16
  and (select pg_temp.node(p, '{"Index Name": "events_user_id_idx"}') is not null
          and lab.field(p, 'Materialize', 'Node Type') is null
          and lab.field(p, 'Sort', 'Node Type') is null
          and (lab.field(p, 'Merge Join', 'Node Type') is not null
               or (lab.field(p, 'Memoize', 'Cache Hits')::int = 9
                   and lab.field(p, 'Memoize', 'Cache Misses')::int = 1))
       from p_limit2));

select lab.prove('count of deploys: Finalize Aggregate over Gather, 2 workers, loops=3',
  (select (p -> 'Plan' ->> 'Node Type') = 'Aggregate'
      and (p -> 'Plan' ->> 'Partial Mode') = 'Finalize'
      and lab.field(p, 'Gather', 'Workers Planned')::int = 2
      and lab.field(p, 'Seq Scan', 'Actual Loops')::int = 3
   from p_par));

select lab.prove('there are 499,992 deploys',
  (select count(*) from events where kind = 'deploy') = 499992);