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

On 4 February 2016 at 15:07, Peter Geoghegan <pg@heroku.com> wrote:
> On Tue, Feb 2, 2016 at 3:59 AM, Thom Brown <thom@linux.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