diff options
Diffstat (limited to 'contrib/pgstattuple/expected/pgstattuple.out')
| -rw-r--r-- | contrib/pgstattuple/expected/pgstattuple.out | 248 |
1 files changed, 248 insertions, 0 deletions
diff --git a/contrib/pgstattuple/expected/pgstattuple.out b/contrib/pgstattuple/expected/pgstattuple.out new file mode 100644 index 0000000..9920dbf --- /dev/null +++ b/contrib/pgstattuple/expected/pgstattuple.out @@ -0,0 +1,248 @@ +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'); + table_len | tuple_count | tuple_len | tuple_percent | dead_tuple_count | dead_tuple_len | dead_tuple_percent | free_space | free_percent +-----------+-------------+-----------+---------------+------------------+----------------+--------------------+------------+-------------- + 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 +(1 row) + +select * from pgstattuple('test'::text); + table_len | tuple_count | tuple_len | tuple_percent | dead_tuple_count | dead_tuple_len | dead_tuple_percent | free_space | free_percent +-----------+-------------+-----------+---------------+------------------+----------------+--------------------+------------+-------------- + 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 +(1 row) + +select * from pgstattuple('test'::name); + table_len | tuple_count | tuple_len | tuple_percent | dead_tuple_count | dead_tuple_len | dead_tuple_percent | free_space | free_percent +-----------+-------------+-----------+---------------+------------------+----------------+--------------------+------------+-------------- + 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 +(1 row) + +select * from pgstattuple('test'::regclass); + table_len | tuple_count | tuple_len | tuple_percent | dead_tuple_count | dead_tuple_len | dead_tuple_percent | free_space | free_percent +-----------+-------------+-----------+---------------+------------------+----------------+--------------------+------------+-------------- + 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 +(1 row) + +select pgstattuple(oid) from pg_class where relname = 'test'; + pgstattuple +--------------------- + (0,0,0,0,0,0,0,0,0) +(1 row) + +select pgstattuple(relname) from pg_class where relname = 'test'; + pgstattuple +--------------------- + (0,0,0,0,0,0,0,0,0) +(1 row) + +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'); + version | tree_level | index_size | root_block_no | internal_pages | leaf_pages | empty_pages | deleted_pages | avg_leaf_density | leaf_fragmentation +---------+------------+------------+---------------+----------------+------------+-------------+---------------+------------------+-------------------- + 4 | 0 | 1 | 0 | 0 | 0 | 0 | 0 | NaN | NaN +(1 row) + +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); + version | tree_level | index_size | root_block_no | internal_pages | leaf_pages | empty_pages | deleted_pages | avg_leaf_density | leaf_fragmentation +---------+------------+------------+---------------+----------------+------------+-------------+---------------+------------------+-------------------- + 4 | 0 | 1 | 0 | 0 | 0 | 0 | 0 | NaN | NaN +(1 row) + +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); + version | tree_level | index_size | root_block_no | internal_pages | leaf_pages | empty_pages | deleted_pages | avg_leaf_density | leaf_fragmentation +---------+------------+------------+---------------+----------------+------------+-------------+---------------+------------------+-------------------- + 4 | 0 | 1 | 0 | 0 | 0 | 0 | 0 | NaN | NaN +(1 row) + +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); + version | tree_level | index_size | root_block_no | internal_pages | leaf_pages | empty_pages | deleted_pages | avg_leaf_density | leaf_fragmentation +---------+------------+------------+---------------+----------------+------------+-------------+---------------+------------------+-------------------- + 4 | 0 | 1 | 0 | 0 | 0 | 0 | 0 | NaN | NaN +(1 row) + +select pg_relpages('test'); + pg_relpages +------------- + 0 +(1 row) + +select pg_relpages('test_pkey'); + pg_relpages +------------- + 1 +(1 row) + +select pg_relpages('test_pkey'::text); + pg_relpages +------------- + 1 +(1 row) + +select pg_relpages('test_pkey'::name); + pg_relpages +------------- + 1 +(1 row) + +select pg_relpages('test_pkey'::regclass); + pg_relpages +------------- + 1 +(1 row) + +select pg_relpages(oid) from pg_class where relname = 'test_pkey'; + pg_relpages +------------- + 1 +(1 row) + +select pg_relpages(relname) from pg_class where relname = 'test_pkey'; + pg_relpages +------------- + 1 +(1 row) + +create index test_ginidx on test using gin (b); +select * from pgstatginindex('test_ginidx'); + version | pending_pages | pending_tuples +---------+---------------+---------------- + 2 | 0 | 0 +(1 row) + +create index test_hashidx on test using hash (b); +select * from pgstathashindex('test_hashidx'); + version | bucket_pages | overflow_pages | bitmap_pages | unused_pages | live_items | dead_items | free_percent +---------+--------------+----------------+--------------+--------------+------------+------------+-------------- + 4 | 4 | 0 | 1 | 0 | 0 | 0 | 100 +(1 row) + +-- these should error with the wrong type +select pgstatginindex('test_pkey'); +ERROR: relation "test_pkey" is not a GIN index +select pgstathashindex('test_pkey'); +ERROR: relation "test_pkey" is not a hash index +select pgstatindex('test_ginidx'); +ERROR: relation "test_ginidx" is not a btree index +select pgstathashindex('test_ginidx'); +ERROR: relation "test_ginidx" is not a hash index +select pgstatindex('test_hashidx'); +ERROR: relation "test_hashidx" is not a btree index +select pgstatginindex('test_hashidx'); +ERROR: relation "test_hashidx" is not a GIN index +-- check that using any of these functions with unsupported relations will fail +create table test_partitioned (a int) partition by range (a); +create index test_partitioned_index on test_partitioned(a); +-- these should all fail +select pgstattuple('test_partitioned'); +ERROR: "test_partitioned" (partitioned table) is not supported +select pgstattuple('test_partitioned_index'); +ERROR: "test_partitioned_index" (partitioned index) is not supported +select pgstattuple_approx('test_partitioned'); +ERROR: "test_partitioned" is not a table or materialized view +select pg_relpages('test_partitioned'); +ERROR: "test_partitioned" is not a table, index, materialized view, sequence, or TOAST table +select pgstatindex('test_partitioned'); +ERROR: relation "test_partitioned" is not a btree index +select pgstatginindex('test_partitioned'); +ERROR: relation "test_partitioned" is not a GIN index +select pgstathashindex('test_partitioned'); +ERROR: "test_partitioned" is not an index +create view test_view as select 1; +-- these should all fail +select pgstattuple('test_view'); +ERROR: "test_view" (view) is not supported +select pgstattuple_approx('test_view'); +ERROR: "test_view" is not a table or materialized view +select pg_relpages('test_view'); +ERROR: "test_view" is not a table, index, materialized view, sequence, or TOAST table +select pgstatindex('test_view'); +ERROR: relation "test_view" is not a btree index +select pgstatginindex('test_view'); +ERROR: relation "test_view" is not a GIN index +select pgstathashindex('test_view'); +ERROR: "test_view" is not an index +create foreign data wrapper dummy; +create server dummy_server foreign data wrapper dummy; +create foreign table test_foreign_table () server dummy_server; +-- these should all fail +select pgstattuple('test_foreign_table'); +ERROR: "test_foreign_table" (foreign table) is not supported +select pgstattuple_approx('test_foreign_table'); +ERROR: "test_foreign_table" is not a table or materialized view +select pg_relpages('test_foreign_table'); +ERROR: "test_foreign_table" is not a table, index, materialized view, sequence, or TOAST table +select pgstatindex('test_foreign_table'); +ERROR: relation "test_foreign_table" is not a btree index +select pgstatginindex('test_foreign_table'); +ERROR: relation "test_foreign_table" is not a GIN index +select pgstathashindex('test_foreign_table'); +ERROR: "test_foreign_table" is not an index +-- a partition of a partitioned table should work though +create table test_partition partition of test_partitioned for values from (1) to (100); +select pgstattuple('test_partition'); + pgstattuple +--------------------- + (0,0,0,0,0,0,0,0,0) +(1 row) + +select pgstattuple_approx('test_partition'); + pgstattuple_approx +----------------------- + (0,0,0,0,0,0,0,0,0,0) +(1 row) + +select pg_relpages('test_partition'); + pg_relpages +------------- + 0 +(1 row) + +-- not for the index calls though, of course +select pgstatindex('test_partition'); +ERROR: relation "test_partition" is not a btree index +select pgstatginindex('test_partition'); +ERROR: relation "test_partition" is not a GIN index +select pgstathashindex('test_partition'); +ERROR: "test_partition" is not an index +-- an actual index of a partitioned table should work though +create index test_partition_idx on test_partition(a); +create index test_partition_hash_idx on test_partition using hash (a); +-- these should work +select pgstatindex('test_partition_idx'); + pgstatindex +------------------------------ + (4,0,8192,0,0,0,0,0,NaN,NaN) +(1 row) + +select pgstathashindex('test_partition_hash_idx'); + pgstathashindex +--------------------- + (4,8,0,1,0,0,0,100) +(1 row) + +drop table test_partitioned; +drop view test_view; +drop foreign table test_foreign_table; +drop server dummy_server; +drop foreign data wrapper dummy; |