--
-- Among B-Tree indexes, what proportion are leaf pages, internal pages and
-- root pages?  Note that a single non-meta page B-Tree, which has only a root
-- page yet to undergo a root page split counts as only having a single leaf
-- page.
with tots as (
  SELECT count(*) c,
  avg(live_items) avg_live_items,
  avg(dead_items) avg_dead_items,
  avg(avg_item_size) avg_item_size,
  u.type,
  r.oid
  from (select c.oid,
          -- Don't count meta-page:
          generate_series(1, (select (pg_relation_size(c.oid) / (2^13))::int4) - 1) i
          from pg_index i
          join pg_opclass op on i.indclass[0] = op.oid
          join pg_am am on op.opcmethod = am.oid
          join pg_class c on i.indexrelid = c.oid
          where am.amname = 'btree') r,
        lateral (select * from bt_page_stats(r.oid::regclass::text, i)) u
  group by r.oid, type)
select ct.relname table_name,
  tots.oid::regclass::text index_name,
  upper(type) page_type,
  c npages,
  to_char(avg_live_items, '990.999') as avg_live_items,
  to_char(avg_dead_items, '990.999') as avg_dead_items,
  to_char(avg_item_size, '990.999') as avg_item_size,
  to_char(c/sum(c) over(partition by tots.oid) * 100, '990.999') || ' %' as prop_of_index
  from tots
  join pg_index i on i.indexrelid = tots.oid
  join pg_class ct on ct.oid = i.indrelid
  order by ct.relnamespace, table_name, index_name, npages, type;

--
-- Stats on root page:
--
with index_details as (
  select 'ff_compcity'::text idx
),

meta_stats as (
select *
from index_details s,
lateral (select * from bt_metap(s.idx)) meta),

root_stats as (
  select idx, root, level, fastroot, fastlevel, stats.*
  from meta_stats s,
  lateral (select * from bt_page_stats(idx, root)) stats)
select
root_stats.*
from root_stats;

--
-- Internal page stats (with page high key)
--
-- Shows internal pages (including root page) in logical order, along with high
-- key data that determines logically ordering. (This is an upper bound on page
-- data).
--
-- Should work with text data, provided small varlena header used, and single
-- attribute key (multiple attributes look a bit funny, but are basically fine).
with recursive index_details as (
  select 'sort_test_skew_sortkey_idx'::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.*
  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),

internal_page_stats as (
  select *
  from page_stats where type != 'l'),

meta_stats as (
  select *
  from index_details s,
  lateral (select * from bt_metap(s.idx)) meta),

internal_items as (
  select *
  from internal_page_stats
  order by btpo desc),

-- XXX: Ordering dependency within this CTE, on internal_items?
ordered_internal_items(item, blk, level) as (
  select 1, blkno, btpo
  from internal_items
  where btpo_prev = 0 and btpo = (select level from meta_stats)
  union
  select case when level = btpo then o.item + 1 else 1 end, blkno, btpo
  from internal_items i,
  ordered_internal_items o
  where i.btpo_prev = o.blk or (btpo_prev = 0 and btpo = o.level - 1)
)
select
idx,
btpo as level,
item as l_item,
blkno,
btpo_prev,
btpo_next,
btpo_flags,
type,
live_items,
dead_items,
avg_item_size,
page_size,
free_size,

-- Only non-rightmost pages have high key.
--
-- Data is formed starting after varlena header bytes. NUL bytes often appear
-- due to alignment considerations, and so are stripped here.
case when btpo_next != 0 then (select convert_from(decode(regexp_replace(substring(data from 4), ' 0{2}', '', 'g'), 'hex'), 'utf-8')
                               from bt_page_items(idx, blkno) where itemoffset = 1) end as highkey
from ordered_internal_items o
join internal_items i on o.blk = i.blkno
order by btpo desc, item;
