btree-graphviz2.sql

create or replace function parsetid(tid) returns text as $$
  select (regexp_matches($1::text, '\((\d+),\d+\)'::text))[1];
$$ language sql immutable strict;

create or replace function getgraph(idxname text) returns text as $$
declare
  nblocks bigint;
  blkno bigint;
  i bigint;
  t text;
  downlink text;
  label text;
  statsr record;
  r record;
  hitext text;
  hiint int4;
  firstdatakey int4;
  rightlink int4;
begin
  nblocks = pg_relation_size(idxname) / 8192;

  t := E'digraph g {\n';
  t := t || E' node [shape = record,height=.1];\n  ratio="auto"\n';
  for blkno in 1..(nblocks-1) loop
    select  * into statsr from bt_page_stats(idxname, blkno::int4);

    -- ignore deleted pages
    continue when statsr.type = 'd';

    -- extract high key (for non-rightmost page)
    if statsr.btpo_next <> 0 then
      select data into hitext from bt_page_items(idxname, blkno::int4) where itemoffset = 1;
      --hiint := parsekey(hitext);
      firstdatakey := 2;
    else
      -- rightmost
      hitext := '';
      firstdatakey := 1;
    end if;

    hitext = left(hitext, 20);

    -- print right-link
    if statsr.btpo_next <> 0 then
      t := t || format(E'  \"node%s\" -> \"node%s\" [constraint=false, color=red];\n', blkno, statsr.btpo_next);
    end if;

    label := '';
    if statsr.type = 'l' then
      -- Leaf - hightext indicates what contexts of page should logically be less
      -- than
      label := 'L|&le; ' || hitext;
    end if;
    if statsr.type = 'i' OR statsr.type = 'r' OR statsr.type = 'e' then
      i := 0;
      -- print downlinks
      for r in select * from bt_page_items(idxname, blkno) where itemoffset >= firstdatakey loop

        label := label || left(r.data, 20) || '|';
        downlink := parsetid(r.ctid);

        t := t || format(E'  \"node%s\":f%s -> \"node%s\":f0 [color=blue];\n', blkno, i, downlink);
        i := i + 1;
      end loop;
      label := label || '&le; ' || hiint;
    end if;

    if statsr.type = 'r' then
      label := label || ' (ROOT)';
    end if;
    if (statsr.btpo_flags & 16) <> 0 then
      label := label || ' (HALF_DEAD)';

      -- "uplink" to the top of the deleted branch
      downlink = parsetid((select ctid from bt_page_items(idxname, blkno::int4) where itemoffset = 1));
      if downlink <> '4294967295' then
        t := t || format(E'  \"node%s\" -> \"node%s\" [style=dotted, color=orange];\n', blkno, downlink);
      end if;

    end if;

    t := t || format(E' node%s[label = "%s|%s"];\n', blkno, blkno, label);

  end loop;

  t := t || E'}\n';
  return t;
end;
$$ language plpgsql;