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?.
drop table if exists t1; drop table if exists t2; drop table if exists t3; drop table if exists t4;
create table t1(a int); create table t2(a int); create table t3(a int); create table t4(a int);
create or replace function get_query_tables(p_query text) returns text[] language plpgsql as $$
declare
x xml;
begin
execute 'explain (format xml) ' || p_query into x;
return xpath('//explain:Relation-Name/text()', x, array[array['explain', 'http://www.postgresql.org/2009/explain']])::text[];
end $$;
select get_query_tables('with c as (select * from t1)select (select count(*) from t2) from c join t3 on true,t4 where exists (select * from t4);');
| get_query_tables |
|---|
| {t1,t2,t4,t3,t4} |