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')
;

CREATE TABLE



INSERT 0 4


-- CREATE EXTENSION pg_trgm;  -- not necessary, module is installed:
-- 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



SELECT 1


/* -- 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);

CREATE FUNCTION



CREATE INDEX


-- trigram similarity demo with filter & sort:
SELECT name
     , similarity(f_unaccent(name), f_unaccent('Schoenstraße')) AS sim_unaccent
     , similarity(name, 'Schoenstraße') AS sim
FROM   names
WHERE  f_unaccent(name) % f_unaccent('Schoenstraße')
ORDER  BY f_unaccent(name) <-> f_unaccent('Schoenstraße')
        , name <-> 'Schoenstraße';  -- best match first


name
sim_unaccent
sim


Schoenstraße
1
1

Schonstraße
0.6875
0.6666667

Schönstraße
0.6875
0.5625

Schyrenstraße
0.6111111
0.5882353



SELECT 4


-- 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));

CREATE TEXT SEARCH CONFIGURATION



ALTER TEXT SEARCH CONFIGURATION



CREATE INDEX


-- fts demo all:
SELECT name
     , ts_lexize('unaccent', name)
     , to_tsvector('de', name)
     , to_tsquery('de', 'Schoenstraße')
     , to_tsvector('de', name) @@ to_tsquery('de', 'Schoenstraße') AS match
FROM   names;


name
ts_lexize
to_tsvector
to_tsquery
match


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

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

Schonstraße
{Schonstrasse}
'schonstrass':1
'schoenstrass'
f

Schoenstraße
{Schoenstrasse}
'schoenstrass':1
'schoenstrass'
t



SELECT 4


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


name
to_tsvector
to_tsquery
match
similarity


Schoenstraße
'schoenstrass':1
'schoenstrass'
t
1



SELECT 1


-- to make Postgres use index if possible
SET enable_seqscan = off;

SET


-- query plan: trigram index *can* be used
EXPLAIN
SELECT name
     , similarity(f_unaccent(name), f_unaccent('Schoenstraße')) AS sim_unaccent
     , similarity(name, 'Schoenstraße') AS sim
FROM   names
WHERE  f_unaccent(name) % f_unaccent('Schoenstraße')
ORDER  BY f_unaccent(name) <-> f_unaccent('Schoenstraße')
        , name <-> 'Schoenstraße';  -- best match first


QUERY PLAN


Sort  (cost=64.78..64.79 rows=1 width=48)

  Sort Key: ((f_unaccent(name) <-> 'Schoenstrasse'::text)), ((name <-> 'Schoenstraße'::text))

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

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

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

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



EXPLAIN


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


QUERY PLAN


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

  Sort Key: ((name <-> 'Schoenstraße'::text))

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

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

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

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



EXPLAIN

name	sim_unaccent	sim
Schoenstraße	1	1
Schonstraße	0.6875	0.6666667
Schönstraße	0.6875	0.5625
Schyrenstraße	0.6111111	0.5882353

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