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 COLLATION ignore_accent (provider = icu, locale = 'und-u-ks-level1-kc-true', deterministic = false);
SELECT 'fóo' = 'foö';
SELECT 'fóo' = 'foö' COLLATE ignore_accent;

?column?

f

?column?

t

CREATE TABLE users (
  id   serial PRIMARY KEY
, name text NOT NULL
);

INSERT INTO users (name) VALUES
  ('João')
, ('Jóao')
, ('foo')
, ('Föò')
, ('Joao')
, ('Jóãò')
, ('FÖò')
, ('Foo')
;

-- accent-insensitive index using above ICU collation
CREATE INDEX users_name_ignore_accent_idx ON users(name COLLATE ignore_accent);
8 rows affected

-- only exact matches
SELECT * FROM users WHERE name = 'João';

id
name

1
João

-- accent-insensitive
SELECT * FROM users WHERE name = 'João' COLLATE ignore_accent;

id
name

1
João

2
Jóao

5
Joao

6
Jóãò

-- to prove index is usable
set enable_seqscan = off;

EXPLAIN
SELECT * FROM users WHERE name = 'João' COLLATE ignore_accent;

QUERY PLAN

Index Scan using users_name_ignore_accent_idx on users  (cost=0.13..8.15 rows=1 width=36)

  Index Cond: (name = 'João'::text COLLATE ignore_accent)