By using db<>fiddle, you agree to license everything you submit by Creative Commons CC0.
Help with an interesting Postgres question: Why isn't an Index Only Scan used on a partition accessed via the parent table?.
select version();
| version |
|---|
| PostgreSQL 17.2 (Debian 17.2-1.pgdg120+1) on x86_64-pc-linux-gnu, compiled by gcc (Debian 12.2.0-14) 12.2.0, 64-bit |
SELECT 1
SELECT
schema_name,
relname,
pg_size_pretty(table_size) AS size,
estimate AS row_count_est -- << Note!
FROM (
SELECT
pg_catalog.pg_namespace.nspname AS schema_name,
pg_catalog.pg_class.reltuples::BIGINT AS estimate, -- << Note!
relname,
pg_relation_size(pg_catalog.pg_class.oid) AS table_size
FROM pg_catalog.pg_class
JOIN pg_catalog.pg_namespace ON relnamespace = pg_catalog.pg_namespace.oid
) t
WHERE schema_name NOT LIKE 'pg_%'
ORDER BY table_size DESC;
| schema_name | relname | size | row_count_est |
|---|---|---|---|
| information_schema | sql_features | 64 kB | 755 |
| information_schema | sql_sizing | 8192 bytes | 23 |
| information_schema | sql_implementation_info | 8192 bytes | 12 |
| information_schema | sql_parts | 8192 bytes | 11 |
| information_schema | administrable_role_authorizations | 0 bytes | -1 |
| information_schema | attributes | 0 bytes | -1 |
| information_schema | collations | 0 bytes | -1 |
| information_schema | character_sets | 0 bytes | -1 |
| information_schema | check_constraint_routine_usage | 0 bytes | -1 |
| information_schema | column_privileges | 0 bytes | -1 |
| information_schema | collation_character_set_applicability | 0 bytes | -1 |
| information_schema | column_domain_usage | 0 bytes | -1 |
| information_schema | column_udt_usage | 0 bytes | -1 |
| information_schema | columns | 0 bytes | -1 |
| information_schema | constraint_column_usage | 0 bytes | -1 |
| information_schema | constraint_table_usage | 0 bytes | -1 |
| information_schema | domain_constraints | 0 bytes | -1 |
| information_schema | routine_table_usage | 0 bytes | -1 |
| information_schema | domain_udt_usage | 0 bytes | -1 |
| information_schema | domains | 0 bytes | -1 |
| information_schema | enabled_roles | 0 bytes | -1 |
| information_schema | routines | 0 bytes | -1 |
| information_schema | key_column_usage | 0 bytes | -1 |
| information_schema | parameters | 0 bytes | -1 |
| information_schema | referential_constraints | 0 bytes | -1 |
| information_schema | schemata | 0 bytes | -1 |
| information_schema | role_column_grants | 0 bytes | -1 |
| information_schema | routine_column_usage | 0 bytes | -1 |
| information_schema | routine_privileges | 0 bytes | -1 |
| information_schema | sequences | 0 bytes | -1 |
| information_schema | role_routine_grants | 0 bytes | -1 |
| information_schema | routine_routine_usage | 0 bytes | -1 |
| information_schema | routine_sequence_usage | 0 bytes | -1 |
| information_schema | role_table_grants | 0 bytes | -1 |
| information_schema | table_privileges | 0 bytes | -1 |
| information_schema | table_constraints | 0 bytes | -1 |
| information_schema | transforms | 0 bytes | -1 |
| information_schema | tables | 0 bytes | -1 |
| information_schema | triggered_update_columns | 0 bytes | -1 |
| information_schema | triggers | 0 bytes | -1 |
| information_schema | udt_privileges | 0 bytes | -1 |
| information_schema | _pg_foreign_data_wrappers | 0 bytes | -1 |
| information_schema | role_udt_grants | 0 bytes | -1 |
| information_schema | usage_privileges | 0 bytes | -1 |
| information_schema | foreign_tables | 0 bytes | -1 |
| information_schema | role_usage_grants | 0 bytes | -1 |
| information_schema | foreign_data_wrapper_options | 0 bytes | -1 |
| information_schema | user_defined_types | 0 bytes | -1 |
| information_schema | view_column_usage | 0 bytes | -1 |
| information_schema | view_routine_usage | 0 bytes | -1 |
| information_schema | foreign_data_wrappers | 0 bytes | -1 |
| information_schema | view_table_usage | 0 bytes | -1 |
| information_schema | views | 0 bytes | -1 |
| information_schema | _pg_foreign_servers | 0 bytes | -1 |
| information_schema | data_type_privileges | 0 bytes | -1 |
| information_schema | element_types | 0 bytes | -1 |
| information_schema | _pg_foreign_table_columns | 0 bytes | -1 |
| information_schema | _pg_user_mappings | 0 bytes | -1 |
| information_schema | column_options | 0 bytes | -1 |
| information_schema | foreign_server_options | 0 bytes | -1 |
| information_schema | foreign_servers | 0 bytes | -1 |
| information_schema | _pg_foreign_tables | 0 bytes | -1 |
| information_schema | foreign_table_options | 0 bytes | -1 |
| information_schema | user_mapping_options | 0 bytes | -1 |
| information_schema | column_column_usage | 0 bytes | -1 |
| information_schema | user_mappings | 0 bytes | -1 |
| information_schema | information_schema_catalog_name | 0 bytes | -1 |
| information_schema | check_constraints | 0 bytes | -1 |
| information_schema | applicable_roles | 0 bytes | -1 |
SELECT 69