Postgres 17

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 demo
(
  id bigint generated always as identity primary key,
  str text
);

insert into demo (str)
select i::text from generate_series(1, 1000) as i;

CREATE TABLE

INSERT 0 1000

select str from demo order by str asc limit 10;

str

1

10

100

1000

101

102

103

104

105

106

SELECT 10

-- https://www.postgresql.org/docs/17/collation.html#ICU-CUSTOM-COLLATIONS
-- https://www.postgresql.eu/events/pgconfeu2022/sessions/session/4040/slides/337/Collations%20in%20PostgreSQL%20-%20The%20good,%20the%20bad%20and%20the%20ugly%20-%20Tobias%20Bussmann.pdf
create collation numeric (provider = icu, deterministic = true, locale = 'en-u-kn-true');

CREATE COLLATION

select str from demo order by str collate "numeric" asc limit 10;

str

1

2

3

4

5

6

7

8

9

10

SELECT 10

create index demo_str_numeric_collation on demo (str collate "numeric");

CREATE INDEX

create index demo_str on demo (str);

CREATE INDEX

explain select str from demo order by str asc limit 10;

QUERY PLAN

Limit  (cost=0.28..0.86 rows=10 width=32)

  ->  Index Only Scan using demo_str on demo  (cost=0.28..59.27 rows=1000 width=32)

EXPLAIN

explain select str from demo order by str collate "numeric" asc limit 10;

QUERY PLAN

Limit  (cost=0.28..0.86 rows=10 width=64)

  ->  Index Only Scan using demo_str_numeric_collation on demo  (cost=0.28..59.27 rows=1000 width=64)

EXPLAIN