Postgres 12

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 users(user_id INT NOT NULL PRIMARY KEY);
CREATE TABLE profiles1 (
    profile_id SERIAL PRIMARY KEY NOT NULL,
    bio TEXT,
    user_id INTEGER NOT NULL,
    FOREIGN KEY (user_id) REFERENCES users(user_id)
);
CREATE OR REPLACE FUNCTION show_create_table(table_name text, join_char text = E'\n' ) 
  RETURNS text AS 
$BODY$
SELECT 'CREATE TABLE ' || $1 || ' (' || $2 || '' || 
    string_agg(column_list.column_expr, ', ' || $2 || '') || 
    '' || $2 || ');'
FROM (
  SELECT '    ' || column_name || ' ' || data_type || 
       coalesce('(' || character_maximum_length || ')', '') || 
       case when is_nullable = 'YES' then '' else ' NOT NULL' end as column_expr
  FROM information_schema.columns
  WHERE /* table_schema = 'public' AND */ table_name = $1
  ORDER BY ordinal_position) column_list;
$BODY$
  LANGUAGE SQL STABLE;
SELECT * FROM INFORMATION_SCHEMA.KEY_COLUMN_USAGE WHERE TABLE_NAME = 'profiles1';


constraint_catalog
constraint_schema
constraint_name
table_catalog
table_schema
table_name
column_name
ordinal_position
position_in_unique_constraint


db_1803946168
public
profiles1_user_id_fkey
db_1803946168
public
profiles1
user_id
1
1

db_1803946168
public
profiles1_pkey
db_1803946168
public
profiles1
profile_id
1
null


SELECT show_create_table('profiles1');


show_create_table


CREATE TABLE profiles1 (
    profile_id integer NOT NULL, 
    bio text, 
    user_id integer NOT NULL
);


CREATE TABLE profiles2 (
    profile_id SERIAL PRIMARY KEY NOT NULL,
    bio TEXT,
    user_id INTEGER NOT NULL REFERENCES users(user_id) 
);
SELECT * FROM INFORMATION_SCHEMA.KEY_COLUMN_USAGE WHERE TABLE_NAME = 'profiles2';


constraint_catalog
constraint_schema
constraint_name
table_catalog
table_schema
table_name
column_name
ordinal_position
position_in_unique_constraint


db_1803946168
public
profiles2_user_id_fkey
db_1803946168
public
profiles2
user_id
1
1

db_1803946168
public
profiles2_pkey
db_1803946168
public
profiles2
profile_id
1
null


SELECT show_create_table('profiles2');


show_create_table


CREATE TABLE profiles2 (
    profile_id integer NOT NULL, 
    bio text, 
    user_id integer NOT NULL
);

constraint_catalog	constraint_schema	constraint_name	table_catalog	table_schema	table_name	column_name	ordinal_position	position_in_unique_constraint
db_1803946168	public	profiles1_user_id_fkey	db_1803946168	public	profiles1	user_id	1	1
db_1803946168	public	profiles1_pkey	db_1803946168	public	profiles1	profile_id	1	null

constraint_catalog	constraint_schema	constraint_name	table_catalog	table_schema	table_name	column_name	ordinal_position	position_in_unique_constraint
db_1803946168	public	profiles2_user_id_fkey	db_1803946168	public	profiles2	user_id	1	1
db_1803946168	public	profiles2_pkey	db_1803946168	public	profiles2	profile_id	1	null