mirror of
https://github.com/postgres/postgres.git
synced 2025-04-21 12:05:57 +03:00
b7547166fe
Dead or half-dead index leaf pages were incorrectly reported as live, as a consequence of a code rearrangement I made (during a moment of severe brain fade, evidently) in commit d287818eb514d431. The index metapage was not counted in index_size, causing that result to not agree with the actual index size on-disk. Index root pages were not counted in internal_pages, which is inconsistent compared to the case of a root that's also a leaf (one-page index), where the root would be counted in leaf_pages. Aside from that inconsistency, this could lead to additional transient discrepancies between the reported page counts and index_size, since it's possible for pgstatindex's scan to see zero or multiple pages marked as BTP_ROOT, if the root moves due to a split during the scan. With these fixes, index_size will always be exactly one page more than the sum of the displayed page counts. Also, the index_size result was incorrectly documented as being measured in pages; it's always been measured in bytes. (While fixing that, I couldn't resist doing some small additional wordsmithing on the pgstattuple docs.) Including the metapage causes the reported index_size to not be zero for an empty index. To preserve the desired property that the pgstattuple regression test results are platform-independent (ie, BLCKSZ configuration independent), scale the index_size result in the regression tests. The documentation issue was reported by Otsuka Kenji, and the inconsistent root page counting by Peter Geoghegan; the other problems noted by me. Back-patch to all supported branches, because this has been broken for a long time.
50 lines
1.9 KiB
SQL
50 lines
1.9 KiB
SQL
CREATE EXTENSION pgstattuple;
|
|
|
|
--
|
|
-- It's difficult to come up with platform-independent test cases for
|
|
-- the pgstattuple functions, but the results for empty tables and
|
|
-- indexes should be that.
|
|
--
|
|
|
|
create table test (a int primary key, b int[]);
|
|
|
|
select * from pgstattuple('test');
|
|
select * from pgstattuple('test'::text);
|
|
select * from pgstattuple('test'::name);
|
|
select * from pgstattuple('test'::regclass);
|
|
select pgstattuple(oid) from pg_class where relname = 'test';
|
|
select pgstattuple(relname) from pg_class where relname = 'test';
|
|
|
|
select version, tree_level,
|
|
index_size / current_setting('block_size')::int as index_size,
|
|
root_block_no, internal_pages, leaf_pages, empty_pages, deleted_pages,
|
|
avg_leaf_density, leaf_fragmentation
|
|
from pgstatindex('test_pkey');
|
|
select version, tree_level,
|
|
index_size / current_setting('block_size')::int as index_size,
|
|
root_block_no, internal_pages, leaf_pages, empty_pages, deleted_pages,
|
|
avg_leaf_density, leaf_fragmentation
|
|
from pgstatindex('test_pkey'::text);
|
|
select version, tree_level,
|
|
index_size / current_setting('block_size')::int as index_size,
|
|
root_block_no, internal_pages, leaf_pages, empty_pages, deleted_pages,
|
|
avg_leaf_density, leaf_fragmentation
|
|
from pgstatindex('test_pkey'::name);
|
|
select version, tree_level,
|
|
index_size / current_setting('block_size')::int as index_size,
|
|
root_block_no, internal_pages, leaf_pages, empty_pages, deleted_pages,
|
|
avg_leaf_density, leaf_fragmentation
|
|
from pgstatindex('test_pkey'::regclass);
|
|
|
|
select pg_relpages('test');
|
|
select pg_relpages('test_pkey');
|
|
select pg_relpages('test_pkey'::text);
|
|
select pg_relpages('test_pkey'::name);
|
|
select pg_relpages('test_pkey'::regclass);
|
|
select pg_relpages(oid) from pg_class where relname = 'test_pkey';
|
|
select pg_relpages(relname) from pg_class where relname = 'test_pkey';
|
|
|
|
create index test_ginidx on test using gin (b);
|
|
|
|
select * from pgstatginindex('test_ginidx');
|