Postgres 16

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?.

create table local_table as
select generate_series(1,(random()*3e5)::int,1), (gen_random_uuid())::text;

SELECT 175939

--this is all just to create a similar table on another db on db<>fiddle
create extension dblink;
select dblink_connect('neighbour_db_dblink', 
                      'dbname=template1');
select dblink_exec('neighbour_db_dblink','create schema neighbour_schema;');

select dblink_exec('neighbour_db_dblink',
$long_literal$ 
create table neighbour_schema.neighbour_table as
select generate_series(1,(random()*3e5)::int,1), (gen_random_uuid())::text;
$long_literal$);

select dblink_exec('neighbour_db_dblink',
$long_literal$ 
create view neighbour_schema.v_pg_total_relation_sizes as 
SELECT nspname || '.' || relname AS "relation",
    pg_size_pretty(pg_total_relation_size(C.oid)) AS "readable_size",
    pg_total_relation_size(C.oid) as size
  FROM pg_class C
  LEFT JOIN pg_namespace N ON (N.oid = C.relnamespace)
  WHERE nspname NOT IN ('pg_catalog', 'information_schema', 'pg_toast')
  ORDER BY pg_total_relation_size(C.oid) DESC;
$long_literal$);

select dblink_disconnect('neighbour_db_dblink');

CREATE EXTENSION

dblink_connect

OK

SELECT 1

dblink_exec

CREATE SCHEMA

SELECT 1

dblink_exec

SELECT 87372

SELECT 1

dblink_exec

CREATE VIEW

SELECT 1

dblink_disconnect

OK

SELECT 1

create database neighbour1_db;

CREATE DATABASE

create extension postgres_fdw;

CREATE SERVER foreign_server
        FOREIGN DATA WRAPPER postgres_fdw
        OPTIONS (dbname 'neighbour1_db');
CREATE USER MAPPING FOR current_user
        SERVER foreign_server
        OPTIONS (user 'postgres');
CREATE FOREIGN TABLE fdw_v_pg_total_relation_sizes (
        relation text,
        readable_size text,
        size bigint )
  SERVER foreign_server
  OPTIONS (schema_name 'neighbour_schema', 
           table_name 'v_pg_total_relation_sizes');

select * from fdw_v_pg_total_relation_sizes;

CREATE EXTENSION

CREATE SERVER

CREATE USER MAPPING

CREATE FOREIGN TABLE

relation
readable_size
size

neighbour_schema.neighbour_table
6664 kB
6823936

neighbour_schema.v_pg_total_relation_sizes
0 bytes
0

SELECT 2

create view v_pg_total_relation_sizes as 
SELECT nspname || '.' || relname AS "relation",
    pg_size_pretty(pg_total_relation_size(C.oid)) AS "readable_size",
    pg_total_relation_size(C.oid) as size
  FROM pg_class C
  LEFT JOIN pg_namespace N ON (N.oid = C.relnamespace)
  WHERE nspname NOT IN ('pg_catalog', 'information_schema', 'pg_toast')
  ORDER BY pg_total_relation_size(C.oid) DESC;

select * from v_pg_total_relation_sizes;

CREATE VIEW

relation
readable_size
size

public.local_table
13 MB
13639680

public.dblink_pkey_results
0 bytes
0

public.fdw_v_pg_total_relation_sizes
0 bytes
0

public.v_pg_total_relation_sizes
0 bytes
0

SELECT 4

create materialized view f_v_cluster_pg_total_relation_sizes as 
select current_database() as dbname,* from v_pg_total_relation_sizes
union all
select 'neighbour1_db',* from fdw_v_pg_total_relation_sizes
order by size desc;

refresh materialized view f_v_cluster_pg_total_relation_sizes;

select * from f_v_cluster_pg_total_relation_sizes;

SELECT 6

REFRESH MATERIALIZED VIEW

dbname
relation
readable_size
size

postgres
public.local_table
13 MB
13639680

neighbour1_db
neighbour_schema.neighbour_table
6664 kB
6823936

postgres
public.f_v_cluster_pg_total_relation_sizes
16 kB
16384

postgres
public.pg_temp_16464
8192 bytes
8192

postgres
public.dblink_pkey_results
0 bytes
0

postgres
public.fdw_v_pg_total_relation_sizes
0 bytes
0

postgres
public.v_pg_total_relation_sizes
0 bytes
0

neighbour1_db
neighbour_schema.v_pg_total_relation_sizes
0 bytes
0

SELECT 8

relation	readable_size	size
neighbour_schema.neighbour_table	6664 kB	6823936
neighbour_schema.v_pg_total_relation_sizes	0 bytes	0

relation	readable_size	size
public.local_table	13 MB	13639680
public.dblink_pkey_results	0 bytes	0
public.fdw_v_pg_total_relation_sizes	0 bytes	0
public.v_pg_total_relation_sizes	0 bytes	0

dbname	relation	readable_size	size
postgres	public.local_table	13 MB	13639680
neighbour1_db	neighbour_schema.neighbour_table	6664 kB	6823936
postgres	public.f_v_cluster_pg_total_relation_sizes	16 kB	16384
postgres	public.pg_temp_16464	8192 bytes	8192
postgres	public.dblink_pkey_results	0 bytes	0
postgres	public.fdw_v_pg_total_relation_sizes	0 bytes	0
postgres	public.v_pg_total_relation_sizes	0 bytes	0
neighbour1_db	neighbour_schema.v_pg_total_relation_sizes	0 bytes	0