Postgres 13

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 IF NOT EXISTS sql_test_1 (
    id serial PRIMARY KEY,
    query_me varchar(10) NOT NULL,
    val TEXT
);

CREATE TABLE


CREATE TABLE IF NOT EXISTS sql_test_2 (
    id serial PRIMARY KEY,
    query_me varchar(10) NOT NULL,
    val TEXT
);

CREATE TABLE


INSERT INTO sql_test_1 (query_me, val)
VALUES 
    ('a', 'hey'),
    ('b', 'ho'),
    ('c', 'silver'),
    ('a', 'let''s'),
    ('b', 'ride');

INSERT 0 5


INSERT INTO sql_test_2 (query_me, val)
VALUES 
    ('a', 'hey'),
    ('f', 'ho'),
    ('g', 'silver'),
    ('a', 'let''s'),
    ('f', 'ride');

INSERT 0 5


with queries as (    
  select format('select distinct %L as table_name, %I as col from %I.%I', c.table_name, c.column_name, c.table_schema, c.table_name) as sql
  from information_schema.columns c
  where c.column_name = 'query_me'
), results as (
  select query_to_xml(q.sql, true, false, '')  as data
  from queries q
)
select x.*
from results r
  cross join xmltable('/table/row' passing r.data
                       columns table_name text path 'table_name', 
                               value      text path 'col') as x
order by x.table_name


table_name
value


sql_test_1
c

sql_test_1
a

sql_test_1
b

sql_test_2
g

sql_test_2
f

sql_test_2
a



SELECT 6

table_name	value
sql_test_1	c
sql_test_1	a
sql_test_1	b
sql_test_2	g
sql_test_2	f
sql_test_2	a