On 4 February 2016 at 15:07, Peter Geoghegan <p...@heroku.com> wrote: > On Tue, Feb 2, 2016 at 3:59 AM, Thom Brown <t...@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 -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
