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 versions (
  id int primary key,
  version text
);

CREATE TABLE


insert into versions (id, version) values 
  (1, '1.0.0'),
  (2, '1.0.1'),
  (3, '1.1.0'),
  (4, '1.2.0'),
  (5, '2.0.0')
;

INSERT 0 5


select * from versions


id
version


1
1.0.0

2
1.0.1

3
1.1.0

4
1.2.0

5
2.0.0



SELECT 5


select * from information_schema.tables where table_name = 'versions'


table_catalog
table_schema
table_name
table_type
self_referencing_column_name
reference_generation
user_defined_type_catalog
user_defined_type_schema
user_defined_type_name
is_insertable_into
is_typed
commit_action


postgres
public
versions
BASE TABLE
null
null
null
null
null
YES
NO
null



SELECT 1


with x(d) as (
  select query_to_xml('select version from ' || (
           select case count(*) when 0 then '(select 0 as version)' else 'versions' end
           from information_schema.tables t
           where t.table_name = 'versions' -- try 'nonexistent' table_name instead
         ) || ' limit 1', false, false, '')::xml
)
select xmltable.*
from x
   , xmltable('/table/row'
       passing d
       columns
         version text path 'version'
     )


version


1.0.0



SELECT 1