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