Postgres 10

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')
;
2 rows affected

-- CREATE EXTENSION pg_trgm; -- not necessary, module is installed:
CREATE INDEX names_idx
    ON public.names USING gin (name COLLATE pg_catalog."default" gin_trgm_ops);
SELECT
    names.name,
    similarity (names.name, 'Schoenstraße') AS similarity,
    show_trgm (names.name)
FROM
    "names"
WHERE
    "name" % 'Schoenstraße'
ORDER BY
    similarity DESC;


name
similarity
show_trgm


Schyrenstraße
0.588235
{0x9a07c3,0xde3801,"  s"," sc",chy,ens,hyr,nst,ren,sch,str,tra,0x76a40e,yre}

Schönstraße
0.5625
{0x9a07c3,0xde3801,0xf00320,0x095f29,"  s"," sc",0x6deea5,nst,sch,str,tra,0x76a40e}

name	similarity	show_trgm
Schyrenstraße	0.588235	{0x9a07c3,0xde3801," s"," sc",chy,ens,hyr,nst,ren,sch,str,tra,0x76a40e,yre}
Schönstraße	0.5625	{0x9a07c3,0xde3801,0xf00320,0x095f29," s"," sc",0x6deea5,nst,sch,str,tra,0x76a40e}