Horton

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

Peter Geoghegan <pg@heroku.com>

View thread View on postgr.es
From: Peter Geoghegan <pg@heroku.com>
To: Thom Brown <thom@linux.com>
Cc: Anastasia Lubennikova <a.lubennikova@postgrespro.ru>, PostgreSQL-development <pgsql-hackers@postgresql.org>
Date: 2016-02-04T15:07:36Z
Lists: pgsql-hackers 
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?
-- 
Peter Geoghegan

Commits

  1. Teach pageinspect about nbtree deduplication.

  2. Doc: Fix deduplicate_items index term.

  3. Revise BTP_HAS_GARBAGE nbtree VACUUM comments.

  4. Remove unneeded "pin scan" nbtree VACUUM code.

  5. Cleanup code in reloptions.h regarding reloption handling

  6. Catch invalid typlens in a couple of places

  7. Compute XID horizon for page level index vacuum on primary.

  8. Make heap TID a tiebreaker nbtree index column.

  9. Avoid pin scan for replay of XLOG_BTREE_VACUUM in all cases

  10. Revert buggy optimization of index scans

  11. Restructure index access method API to hide most of it at the C level.

  12. Reduce pinning and buffer content locking for btree scans.

  13. Avoid scanning nulls at the beginning of a btree index scan.