21 代价最低的计划胜出

select lab.prove('a filter on a grouped subquery is pushed below the aggregate',
  lab.est_nodes($$ select * from (select project_id, count(*) from events
                              group by project_id) s
               where project_id = 42 $$) = '{Aggregate,Seq Scan}'
  and lab.est_rows($$ select * from (select project_id, count(*) from events
                                 group by project_id) s
                  where project_id = 42 $$) = 1);

select lab.prove('an unused LEFT JOIN to a unique key is removed entirely',
  lab.est_nodes($$ select p.* from projects p left join users u on u.id = p.owner_id $$)
    = '{Seq Scan}');

select lab.prove('PostgreSQL 18 removes a self-join on the primary key',
  lab.est_nodes($$ select e1.* from events e1 join events e2 on e2.id = e1.id
               where e2.kind = 'deploy' and e1.id < 100 $$) = '{Index Scan}');

select lab.prove('a redundant tenant clause divides the join estimate by about 1,000',
  lab.est_rows($$ select * from events e join projects p
              on p.id = e.project_id and p.account_id = e.account_id $$)
  between 1500 and 2500
  and lab.est_rows($$ select * from events e join projects p on p.id = e.project_id $$)
      > 1900000);

select lab.prove('the compounded misestimate costs over 100x the pages of the plain join',
  lab.buffers($$ select count(*) from events e
                 join projects p on p.id = e.project_id and p.account_id = e.account_id
                 join users u on u.id = p.owner_id and u.account_id = p.account_id $$)
  > 100 * lab.buffers($$ select count(*) from events e
                         join projects p on p.id = e.project_id
                         join users u on u.id = p.owner_id $$));

begin;
alter table projects add constraint projects_id_account_key unique (id, account_id);
alter table events add constraint events_project_account_fkey
  foreign key (project_id, account_id) references projects (id, account_id);
analyze projects;
select lab.prove('with a composite foreign key, the two-condition join is estimated at about 2 million rows',
  lab.est_rows($$ select * from events e join projects p
              on p.id = e.project_id and p.account_id = e.account_id $$) > 1900000);
rollback;

select lab.prove('correlated: the index scan holds until 50 to 80 percent of the table, then a seq scan',
  path_at('events', 60) = 'Index Scan'
  and path_at('events', current_setting('plans.flip_corr')::int + 1) = 'Seq Scan'
  and rows_at('events', current_setting('plans.flip_corr')::int)
      / (select reltuples from pg_class where relname = 'events') between 0.5 and 0.8);

select lab.prove('shuffled: index scan only for a handful of rows (under 100), then bitmap',
  path_at('events_shuffled', 1) = 'Index Scan'
  and path_at('events_shuffled', current_setting('plans.flip_ib')::int + 1) = 'Bitmap Heap Scan'
  and rows_at('events_shuffled', current_setting('plans.flip_ib')::int) < 100);

select lab.prove('shuffled: bitmap until 20 to 60 percent of the table, then a seq scan',
  path_at('events_shuffled', current_setting('plans.flip_bs')::int + 1) = 'Seq Scan'
  and rows_at('events_shuffled', current_setting('plans.flip_bs')::int)
      / (select reltuples from pg_class where relname = 'events_shuffled') between 0.2 and 0.6);

select lab.prove('ORDER BY created_at DESC LIMIT 10 walks the index backward: no Sort, under 20 pages',
  lab.est_nodes($$ select id, kind, created_at from events
               order by created_at desc limit 10 $$) = '{Limit,Index Scan}'
  and lab.buffers($$ select id, kind, created_at from events
                     order by created_at desc limit 10 $$) < 20);

select lab.prove('ORDER BY created_at DESC, kind LIMIT 10 uses an incremental sort on the index order',
  lab.est_nodes($$ select id, kind, created_at from events
               order by created_at desc, kind limit 10 $$)
    = '{Limit,Incremental Sort,Index Scan}'
  and lab.buffers($$ select id, kind, created_at from events
                     order by created_at desc, kind limit 10 $$) < 20);

select lab.prove('exhaustive planning time grows steeply: 11 tables take over 10x as long as 7',
  current_setting('plans.ms11')::numeric > 10 * current_setting('plans.ms7')::numeric);

select lab.prove('at 12 tables GEQO takes over, planning at least 3x faster than exhaustive search',
  current_setting('plans.ms12')::numeric > 3 * current_setting('plans.ms12g')::numeric);

select lab.prove('explicit JOINs past join_collapse_limit (8) are planned in pieces, far faster',
  current_setting('plans.ms12')::numeric > 5 * current_setting('plans.ms12j')::numeric);

select lab.prove('more outer rows flip it to a hash join, somewhere between 2 and 30 accounts',
  current_setting('plans.nl_flip')::int between 2 and 30
  and join_node($$ select count(e.payload) from projects p
                   join events e on e.project_id = p.id where p.account_id <= 50 $$) = 'Hash Join');

set work_mem = '4MB';
select lab.prove('at work_mem 4MB, events x users ORDER BY user_id is a merge join',
  join_node($$ select e.kind, u.email from events e join users u on u.id = e.user_id
              order by e.user_id $$) = 'Merge Join');
select set_config('plans.merge_cost', est_cost($$ select e.kind, u.email from events e
       join users u on u.id = e.user_id order by e.user_id $$)::text, false) \g /dev/null
set enable_mergejoin = off;
select set_config('plans.hj4', hj_cost($$ select e.kind, u.email from events e
       join users u on u.id = e.user_id order by e.user_id $$)::text, false),
       set_config('plans.hashsort4', est_cost($$ select e.kind, u.email from events e
       join users u on u.id = e.user_id order by e.user_id $$)::text, false) \g /dev/null
select lab.prove('at 4MB the rejected hash join plus sort costs more than the merge join',
  join_node($$ select e.kind, u.email from events e join users u on u.id = e.user_id
              order by e.user_id $$) = 'Hash Join'
  and current_setting('plans.hashsort4')::numeric
      > current_setting('plans.merge_cost')::numeric);
reset enable_mergejoin;
set work_mem = '8MB';
select lab.prove('at work_mem 8MB the same query becomes a hash join plus a sort',
  join_node($$ select e.kind, u.email from events e join users u on u.id = e.user_id
              order by e.user_id $$) = 'Hash Join');
select lab.prove('the hash join costs the same at 4MB and 8MB; only the sort above it got cheaper',
  hj_cost($$ select e.kind, u.email from events e join users u on u.id = e.user_id
             order by e.user_id $$) = current_setting('plans.hj4')::numeric
  and est_cost($$ select e.kind, u.email from events e join users u on u.id = e.user_id
                  order by e.user_id $$) < current_setting('plans.hashsort4')::numeric);

select lab.prove('workers grow with table size: one more each time the table triples past 8 MB',
  (select relpages from pg_class where relname = 'ev_70k') between 1024 and 3071
  and (select relpages from pg_class where relname = 'ev_200k') between 3072 and 9215
  and (select relpages from pg_class where relname = 'ev_600k') between 9216 and 27647
  and (select relpages from pg_class where relname = 'events') between 27648 and 82943
  and workers($$ select count(*) from ev_70k  where kind = 'alert' $$) = 1
  and workers($$ select count(*) from ev_200k where kind = 'alert' $$) = 2
  and workers($$ select count(*) from ev_600k where kind = 'alert' $$) = 3
  and workers($$ select count(*) from events  where kind = 'alert' $$) = 4);

select lab.prove('one unsafe function in WHERE and the query gets no parallel plan',
  workers($$ select count(*) from events where is_slow(payload) $$) is null);

select lab.prove('even an inlined SQL function blocks parallelism while marked unsafe',
  workers($$ select count(*) from events where is_slow_sql(payload) $$) is null
  and workers($$ select count(*) from events
                 where (payload ->> 'duration_ms')::int > 4000 $$) = 2);

select lab.prove('the kind report costs just over jit_above_cost (100,000) and gets JIT',
  est_cost($$ select kind, count(*), avg((payload ->> 'duration_ms')::int),
                     count(*) filter (where payload ->> 'status' = 'failed')
              from events group by kind $$) between 100000 and 105000
  and est($$ select kind, count(*), avg((payload ->> 'duration_ms')::int),
                    count(*) filter (where payload ->> 'status' = 'failed')
             from events group by kind $$) ? 'JIT');

select lab.prove('with inlining and optimization on, compiling takes over 10x longer',
  current_setting('plans.jit_full')::numeric > 10 * current_setting('plans.jit_basic')::numeric);