leafkeyspace.sql

with recursive index_details as (
  select
    'pgbench_accounts_pkey'::text idx
),
size_in_pages_index as (
  select
    (pg_relation_size(idx::regclass) / (2^13))::int4 size_pages
  from
    index_details
),
page_stats as (
  select
    index_details.*,
    stats.*,
    item.*,
    nheapblocks.*,
    nhtids.*,
    ndeadheapblocks.*,
    nhtidschecksimple.*
  from
    index_details,
    size_in_pages_index,
    lateral (select i from generate_series(1, size_pages - 1) i) series,
    lateral (select * from bt_page_stats(idx, i)) stats,
    lateral (select count(*) nhtids from (select unnest(coalesce(tids, ARRAY[htid])) from bt_page_items(idx, i) where dead is not null) aaa ) nhtids,
    lateral (select count(*) nheapblocks from (select distinct ((unnest(coalesce(tids, ARRAY[htid]))::text::point)[0]::bigint) from bt_page_items(idx, i) where dead is not null) aaa ) nheapblocks,
    lateral (select count(*) ndeadheapblocks from (select distinct ((unnest(coalesce(tids, ARRAY[htid]))::text::point)[0]::bigint) from bt_page_items(idx, i) where dead) aaa ) ndeadheapblocks,
    lateral (select count(*) nhtidschecksimple from (select unnest(coalesce(tids, ARRAY[htid])) mtid from bt_page_items(idx, i) where dead is not null) aaa where (mtid::text::point)[0]::bigint in ( select ((unnest(coalesce(tids, ARRAY[htid]))::text::point)[0]::bigint) from bt_page_items(idx, i) where dead)) nhtidschecksimple,
    lateral (select * from bt_page_items(idx, i) where itemoffset = 1) item
),
all_page_stats as (
  select
    *
  from
    page_stats),
internal_items as (
  select
    *
  from
    all_page_stats
  order by
    btpo_level desc),
ordered_internal_items(item, blk, level) as (
  select
    1,
    blkno,
    btpo_level,
    data,
    htid,
    nhtids,
    nheapblocks,
    ndeadheapblocks,
    nhtidschecksimple
  from
    internal_items
  where
    btpo_prev = 0
    and btpo_level = 0
  union
  select
    case when level = btpo_level then o.item + 1 else 1 end,
    blkno,
    btpo_level,
    i.data,
    i.htid,
    i.nhtids,
    i.nheapblocks,
    i.ndeadheapblocks,
    i.nhtidschecksimple
  from
    internal_items i,
    ordered_internal_items o
  where
    i.btpo_prev = o.blk or (btpo_prev = 0 and btpo_level = o.level - 1)
)
select
  -- btpo as level,
  item as i,
  blkno,
  btpo_flags flags,
  -- type,
  o.nhtids nhtids,
  o.nheapblocks nhblks,
  o.ndeadheapblocks ndeadhblks,
  live_items nlive,
  dead_items ndead,
  -- nhtidschecksimple is the number of TIDs that simple deletion will
  -- check, which is hopefully much higher than ndead (though definitely no
  -- lower barring posting list vs TID distinction).  It may even occasionally be
  -- as high as nhtids
  o.nhtidschecksimple nhtidschecksimple,
  avg_item_size avgsize,
  free_size freespace,
  case when btpo_next != 0 then (o.data || coalesce(', (htid)=(''' || o.htid || ''')', '')) end as highkey
from
  ordered_internal_items o
  join internal_items i on o.blk = i.blkno
order by item;