Postgres 13

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 names (name text);
INSERT INTO names VALUES
  ('Schyrenstraße')
, ('Schönstraße')
, ('Schonstraße')
, ('Schoenstraße')
;
4 rows affected

-- CREATE EXTENSION unaccent; -- not necessary, module is installed:

SELECT extnamespace::regnamespace -- we need the schema (normally just "public")
FROM   pg_extension
WHERE  extname = 'unaccent';


extnamespace


x_unaccent


/* -- We lack privileges for the optimal implementation ...

CREATE OR REPLACE FUNCTION public.immutable_unaccent(regdictionary, text)
  RETURNS text LANGUAGE c IMMUTABLE PARALLEL SAFE STRICT AS
'$libdir/unaccent', 'unaccent_dict';

CREATE OR REPLACE FUNCTION public.f_unaccent(text)
  RETURNS text LANGUAGE sql IMMUTABLE PARALLEL SAFE STRICT AS
$func$
SELECT public.immutable_unaccent(regdictionary 'x_unaccent.unaccent', $1)
$func$;
*/
-- ... so we use the next-best thing:
-- IMMUTABLE wrapper function with actual schema of extension:
CREATE OR REPLACE FUNCTION f_unaccent(text)  -- optionally schema-qualify ...
  RETURNS text
  LANGUAGE sql IMMUTABLE PARALLEL SAFE STRICT AS
$func$
SELECT x_unaccent.unaccent('x_unaccent.unaccent', $1)  -- schema-qualify function and dictionary
$func$;

-- index
CREATE INDEX names_trgm_idx ON names USING gin (f_unaccent(name) gin_trgm_ops);
-- trigram similarity demo with filter & sort:
SELECT name
     , similarity(f_unaccent(name), f_unaccent('Schönstraße')) AS sim_unaccent
     , similarity(name, 'Schönstraße') AS sim
FROM   names
WHERE  f_unaccent(name) % f_unaccent('Schönstraße')
ORDER  BY f_unaccent(name) <-> f_unaccent('Schönstraße')
        , name <-> 'Schönstraße';  -- best match first


name
sim_unaccent
sim


Schönstraße
1
1

Schonstraße
1
0.6

Schoenstraße
0.6875
0.5625

Schyrenstraße
0.5555556
0.5294118


-- ALTERNATIVE: full text search

CREATE TEXT SEARCH CONFIGURATION de (COPY = german);
ALTER TEXT SEARCH CONFIGURATION de
ALTER MAPPING FOR hword, hword_part, word WITH unaccent, german_stem;

-- index
CREATE INDEX names_fts_idx ON names USING GIN (to_tsvector('de', name));
-- fts demo all:
SELECT name
     , ts_lexize('unaccent', name)
     , to_tsvector('de', name)
     , to_tsquery('de', 'Schönstraße')
     , to_tsvector('de', name) @@ to_tsquery('de', 'Schönstraße') AS match
FROM   names;


name
ts_lexize
to_tsvector
to_tsquery
match


Schyrenstraße
{Schyrenstrasse}
'schyrenstrass':1
'schonstrass'
f

Schönstraße
{Schonstrasse}
'schonstrass':1
'schonstrass'
t

Schonstraße
{Schonstrasse}
'schonstrass':1
'schonstrass'
t

Schoenstraße
{Schoenstrasse}
'schoenstrass':1
'schonstrass'
f


-- fts demo with filter & sort:
SELECT name
     , to_tsvector('de', name)
     , to_tsquery('de', 'Schönstraße')
     , to_tsvector('de', name) @@ to_tsquery('de', 'Schönstraße') AS match
     , similarity(name, 'Schönstraße')
FROM   names
WHERE  to_tsvector('de', name) @@ to_tsquery('de', 'Schönstraße')
ORDER  BY name <-> 'Schönstraße';  -- best match first


name
to_tsvector
to_tsquery
match
similarity


Schönstraße
'schonstrass':1
'schonstrass'
t
1

Schonstraße
'schonstrass':1
'schonstrass'
t
0.6


-- to make Postgres use index if possible
SET enable_seqscan = off;
-- query plan: trigram index *can* be used
EXPLAIN
SELECT name
     , similarity(f_unaccent(name), f_unaccent('Schönstraße')) AS sim_unaccent
     , similarity(name, 'Schönstraße') AS sim
FROM   names
WHERE  f_unaccent(name) % f_unaccent('Schönstraße')
ORDER  BY f_unaccent(name) <-> f_unaccent('Schönstraße')
        , name <-> 'Schönstraße';  -- best match first


QUERY PLAN


Sort  (cost=60.78..60.79 rows=1 width=48)

  Sort Key: ((f_unaccent(name) <-> 'Schonstrasse'::text)), ((name <-> 'Schönstraße'::text))

  ->  Bitmap Heap Scan on names  (cost=56.00..60.77 rows=1 width=48)

        Recheck Cond: (f_unaccent(name) % 'Schonstrasse'::text)

        ->  Bitmap Index Scan on names_trgm_idx  (cost=0.00..56.00 rows=1 width=0)

              Index Cond: (f_unaccent(name) % 'Schonstrasse'::text)


-- query plan: fts index *can* be used
EXPLAIN
SELECT name
     , ts_lexize('unaccent', name)
     , to_tsvector('de', name)
     , to_tsquery('de', 'Schönstraße')
     , to_tsvector('de', name) @@ to_tsquery('de', 'Schönstraße')
     , similarity(name, 'Schönstraße')
FROM   names
WHERE  to_tsvector('de', name) @@ to_tsquery('de', 'Schönstraße')
ORDER  BY name <-> 'Schönstraße';  -- best match first


QUERY PLAN


Sort  (cost=12.78..12.79 rows=1 width=137)

  Sort Key: ((name <-> 'Schönstraße'::text))

  ->  Bitmap Heap Scan on names  (cost=8.00..12.77 rows=1 width=137)

        Recheck Cond: (to_tsvector('de'::regconfig, name) @@ '''schonstrass'''::tsquery)

        ->  Bitmap Index Scan on names_fts_idx  (cost=0.00..8.00 rows=1 width=0)

              Index Cond: (to_tsvector('de'::regconfig, name) @@ '''schonstrass'''::tsquery)

name	sim_unaccent	sim
Schönstraße	1	1
Schonstraße	1	0.6
Schoenstraße	0.6875	0.5625
Schyrenstraße	0.5555556	0.5294118

name	ts_lexize	to_tsvector	to_tsquery	match
Schyrenstraße	{Schyrenstrasse}	'schyrenstrass':1	'schonstrass'	f
Schönstraße	{Schonstrasse}	'schonstrass':1	'schonstrass'	t
Schonstraße	{Schonstrasse}	'schonstrass':1	'schonstrass'	t
Schoenstraße	{Schoenstrasse}	'schoenstrass':1	'schonstrass'	f