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.oidfrom (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