Re: [WIP] Effective storage of duplicates in B-tree index.

On 4 February 2016 at 15:07, Peter Geoghegan <pg(at)heroku(dot)com> wrote:
> On Tue, Feb 2, 2016 at 3:59 AM, Thom Brown <thom(at)linux(dot)com> wrote:
>> public | pgbench_accounts_pkey | index | thom | pgbench_accounts | 214 MB |
>> public | pgbench_branches_pkey | index | thom | pgbench_branches | 24 kB |
>> public | pgbench_tellers_pkey | index | thom | pgbench_tellers | 48 kB |
>
> I see the same.
>
> I use my regular SQL query to see the breakdown of leaf/internal/root pages:
>
> postgres=# with tots as (
> SELECT count(*) c,
> avg(live_items) avg_live_items,
> avg(dead_items) avg_dead_items,
> u.type,
> r.oid
> from (select c.oid,
> c.relpages,
> generate_series(1, c.relpages - 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,
> (select relpages - 1 from pg_class c where c.oid = tots.oid) non_meta_pages,
> upper(type) page_type,
> c npages,
> to_char(avg_live_items, '990.999'),
> to_char(avg_dead_items, '990.999'),
> 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
> where tots.oid = 'pgbench_accounts_pkey'::regclass
> order by ct.relnamespace, table_name, index_name, npages, type;
> table_name │ index_name │ non_meta_pages │ page_type
> │ npages │ to_char │ to_char │ prop_of_index
> ──────────────────┼───────────────────────┼────────────────┼───────────┼────────┼──────────┼──────────┼───────────────
> pgbench_accounts │ pgbench_accounts_pkey │ 27,421 │ R
> │ 1 │ 97.000 │ 0.000 │ 0.004 %
> pgbench_accounts │ pgbench_accounts_pkey │ 27,421 │ I
> │ 97 │ 282.670 │ 0.000 │ 0.354 %
> pgbench_accounts │ pgbench_accounts_pkey │ 27,421 │ L
> │ 27,323 │ 366.992 │ 0.000 │ 99.643 %
> (3 rows)
>
> But this looks healthy -- I see the same with master. And since the
> accounts table is listed as 1281 MB, this looks like a plausible ratio
> in the size of the table to its primary index (which I would not say
> is true of an 87MB primary key index).
>
> Are you sure you have the details right, Thom?

*facepalm*

No, I'm not. I've just realised that all I've been checking is the
primary key expecting it to change in size, which is, of course,
nonsense. I should have been creating an index on the bid field of
pgbench_accounts and reviewing the size of that.

Now I've checked it with the latest patch, and can see it working
fine. Apologies for the confusion.

Thom