Postgres 15

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

select version();

version

PostgreSQL 15.0 on x86_64-pc-linux-gnu, compiled by gcc (GCC) 8.5.0 20210514 (Red Hat 8.5.0-10), 64-bit

SELECT 1

CREATE TABLE m (
   metadata JSON);

CREATE TABLE

INSERT INTO m VALUES 
  ('{"video-url":"xyz.com/video/xy4jnj?pubtool=oembed","provider":"some-video","video-id":"x8cse6q"}');

INSERT 0 1

INSERT INTO m VALUES 
  ('{"video-url":"xyz.com/video/x8cse6q?pubtool=oembed","provider":"some-video","video-id":"x8cse6q"}');

INSERT 0 1

INSERT INTO m VALUES 
  ('{"video-url":"xyz.com/video/x8cse6q","provider":"some-video","video-id":"x8cse6q"}');

INSERT 0 1

select * from m;

metadata

{"video-url":"xyz.com/video/xy4jnj?pubtool=oembed","provider":"some-video","video-id":"x8cse6q"}

{"video-url":"xyz.com/video/x8cse6q?pubtool=oembed","provider":"some-video","video-id":"x8cse6q"}

{"video-url":"xyz.com/video/x8cse6q","provider":"some-video","video-id":"x8cse6q"}

SELECT 3

SELECT
  metadata->>'video-url' as "video-url"
FROM m;

video-url

xyz.com/video/xy4jnj?pubtool=oembed

xyz.com/video/x8cse6q?pubtool=oembed

xyz.com/video/x8cse6q

SELECT 3