18 B-树之外

select lab.prove(
  'the default jsonb_ops GIN index is about 20 MB',
  pg_relation_size('events_payload_gin') between 18e6 and 23e6);

select lab.prove(
  'with jsonb_ops, the index alone reads about 987 pages for 81 rows',
  lab.node_pages($$ select id from events
                where payload @> '{"duration_ms": 4242, "region": "syd"}' $$,
             'Bitmap Index Scan') between 850 and 1150);

select lab.prove(
  'the jsonb_path_ops index is about 14 MB',
  pg_relation_size('events_payload_pathops') between 12.5e6 and 16e6);

select lab.prove(
  'with a GIN index on tags, the index reads 3 pages and the heap one page per match: 53 in all',
  lab.node_pages($$ select id from projects where tags @> '{gdpr}' $$, 'Bitmap Index Scan') <= 4
  and lab.buffers($$ select id from projects where tags @> '{gdpr}' $$) between 45 and 56);

select lab.prove(
  '''gdpr'' = any (tags) cannot use the GIN index: a sequential scan',
  lab.nodes($$ select id from projects where 'gdpr' = any (tags) $$) = '{Seq Scan}');

select lab.prove(
  'without an index, a search reads every page of comments (about 6,700)',
  lab.buffers($$ select id, body from comments
                 where search @@ to_tsquery('english', 'deadlock') $$) between 6400 and 7000);

create index comments_search_idx on comments using gin (search);

select lab.prove(
  'with a GIN index, the search finds all 49 and the index reads under 5 pages',
  lab.node_sum($$ select id, body from comments where search @@ to_tsquery('english', 'deadlock') $$,
           'Bitmap Heap Scan', 'Actual Rows') = 49
  and lab.node_pages($$ select id, body from comments where search @@ to_tsquery('english', 'deadlock') $$,
                 'Bitmap Index Scan') < 5);

select lab.prove(
  'the full-text index is about 2.4 MB for a quarter-million comments',
  pg_relation_size('comments_search_idx') between 2.0e6 and 3.0e6);

select lab.prove(
  'after 5,000 inserts the pending list holds 5,000 tuples on a few dozen pages',
  (select pending_tuples = 5000 and pending_pages between 25 and 60
   from pgstatginindex('events_payload_pathops')));

select lab.prove(
  'every search now scans the pending pages too (121 index pages became about 158)',
  lab.node_pages($$ select id from events
                where payload @> '{"duration_ms": 4242, "region": "syd"}' $$,
             'Bitmap Index Scan')
    - :pages_before >= (select pending_pages - 2 from pgstatginindex('events_payload_pathops')));

select lab.prove(
  'trigram GiST on email is about 11 MB, over 2.5 times the GIN',
  pg_relation_size('users_email_trgm_gist') between 10e6 and 13.5e6
  and pg_relation_size('users_email_trgm_gist')::numeric
      / pg_relation_size('users_email_trgm_gin') > 2.5);

select lab.prove(
  'with GiST, the similarity search is an index scan ordered by distance',
  lab.nodes($$ select email from users order by email <-> 'usr4242@exmaple.com' limit 5 $$)
    = '{Limit,Index Scan}');

do $$
begin
  insert into deploy_freezes (project_id, during, reason)
  values (4242, tstzrange('2026-12-31', '2027-01-02'), 'new year');
  raise exception 'NOT PROVED: the overlapping freeze was accepted';
exception when exclusion_violation then
  perform set_config('lab.freeze_rejected', 'yes', false);
end $$;
select lab.prove(
  'an overlapping freeze for the same project is rejected (exclusion_violation)',
  current_setting('lab.freeze_rejected', true) = 'yes');

select lab.prove(
  'the constraint''s index, led by project_id, reads more pages than the sequential scan',
  lab.buffers($$ select project_id, reason from deploy_freezes
                 where during @> now() - interval '100 days' $$) > :seq_pages);

create index deploy_freezes_during_idx on deploy_freezes using gist (during);

select lab.prove(
  'a GiST index on the range alone answers in about 7 index pages',
  lab.node_pages($$ select project_id, reason from deploy_freezes
                where during @> now() - interval '100 days' $$, 'Bitmap Index Scan') < 20);

select lab.prove(
  'with BRIN (128 pages per range), the one-day summary reads about 261 pages',
  lab.buffers(:'day_query') between 200 and 320);

select lab.prove(
  'BRIN at 32 pages per range gets within 20 pages of the B-tree',
  :brin32_pages - :btree_pages <= 20);

select lab.prove(
  'the B-tree is over 1,000 times larger than the BRIN (about 1,800)',
  pg_relation_size('events_created_btree')::numeric / pg_relation_size('events_created_brin') > 1000);

select lab.prove(
  'the BRIN node''s "rows" is ten per matching page, not a row count',
  lab.node_sum(:'day_query', 'Bitmap Index Scan', 'Actual Rows')
    = 10 * lab.node_sum(:'day_query', 'Bitmap Heap Scan', 'Lossy Heap Blocks'));

select lab.prove(
  'on the long key, the hash index is about a quarter of the B-tree',
  pg_relation_size('users_api_key_hash')::numeric / pg_relation_size('users_api_key_btree')
    between 0.2 and 0.3);

select lab.prove(
  'the hash index size does not depend on key width: same size on email',
  pg_relation_size('users_email_hash') = pg_relation_size('users_api_key_hash'));

select lab.prove(
  'a hash lookup on api_key touches 3 pages (metapage, bucket, heap)',
  lab.buffers($$ select id from users where api_key = encode(sha512('key4242'::bytea), 'hex') $$) = 3);