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

CREATE TABLE messages_dev
    ("input_column" varchar(33))
;
    
INSERT INTO messages_dev
    ("input_column")
VALUES
    ('2023-08-04T09:00:32.822+00:00-1'),
    ('2023-08-04T09:00:32.822+00:00-1-1'),
    ('2023-08-04T09:00:32.822+00:00-2'),
    ('2023-08-04T09:00:32.822+00:00-2-1'),
    ('2023-08-04T09:00:32.822+00:00-3'),
    ('2023-08-04T09:00:32.822+00:00-4')
;

CREATE TABLE

INSERT 0 6

select count(*) AS result
FROM messages_dev 
  where input_column like '2023-08-04T09:00:32.822+00:00' || repeat('-%', 1)

result

6

SELECT 1

SELECT COUNT(*) AS result
FROM messages_dev
WHERE input_column LIKE '2023-08-04T09:00:32.822+00:00-%'  -- Begins with the given input
  AND LENGTH(input_column) - LENGTH(REPLACE(input_column, '-', '')) = 3; -- Contains 3 hyphens

result

4

SELECT 1