Postgres 16

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?.
/*
Fast string search in PostgreSQL table with slightly erroneous input

Assume I have a user table in PostgreSQL with columns

first_name (PK)
last_name (PK)
email
There are now millions of users in it. One user has the record

(John, Smith, john.smith@gmail.com)

Now I search for him and enter erroneously Johny Smit.
*/
create table my_table(first_name,last_name,email) as 
select gen_random_uuid()::text,gen_random_uuid()::text,gen_random_uuid()::text
from generate_series(1,4e4);

alter table my_table add primary key (first_name,last_name);

insert into my_table values ('John', 'Smith', 'john.smith@gmail.com');
  
insert into my_table 
select gen_random_uuid()::text,gen_random_uuid()::text,gen_random_uuid()::text
from generate_series(1,4e4);

create extension pg_trgm;
CREATE INDEX trgm_idx ON my_table USING GIST (first_name gist_trgm_ops, 
                                              last_name gist_trgm_ops);

SELECT 40000



ALTER TABLE



INSERT 0 1



INSERT 0 40000



CREATE EXTENSION



CREATE INDEX


vacuum analyze my_table;

VACUUM


select show_limit();


show_limit


0.3



SELECT 1


prepare find_john_smith_using_pg_trgm as 
select * from my_table 
where    first_name % 'Johny' 
  and    last_name  % 'Smit' 
order by last_name <->'Smit'
        ,first_name<->'Johny'
limit 5;

explain analyze verbose execute find_john_smith_using_pg_trgm;

execute find_john_smith_using_pg_trgm;

PREPARE




QUERY PLAN


Limit  (cost=0.28..8.31 rows=1 width=119) (actual time=14.087..14.090 rows=1 loops=1)

  Output: first_name, last_name, email, ((last_name <-> 'Smit'::text)), ((first_name <-> 'Johny'::text))

  ->  Index Scan using trgm_idx on public.my_table  (cost=0.28..8.31 rows=1 width=119) (actual time=14.083..14.085 rows=1 loops=1)

        Output: first_name, last_name, email, (last_name <-> 'Smit'::text), (first_name <-> 'Johny'::text)

        Index Cond: ((my_table.first_name % 'Johny'::text) AND (my_table.last_name % 'Smit'::text))

        Order By: ((my_table.last_name <-> 'Smit'::text) AND (my_table.first_name <-> 'Johny'::text))

Planning Time: 1.315 ms

Execution Time: 14.149 ms



EXPLAIN




first_name
last_name
email


John
Smith
john.smith@gmail.com



SELECT 1


deallocate find_john_smith_using_pg_trgm;
prepare find_john_smith_using_pg_trgm as 
select *, first_name<->'Johny' as "first_name<->"
        , first_name<<->'Johny' as "first_name<<->"
        , first_name<<<->'Johny' as "first_name<<<->"
        , last_name<->'Smit'  as "last_name<->"
        , last_name<<->'Smit'  as "last_name<<->"
        , last_name<<<->'Smit'  as "last_name<<<->"
        , word_similarity(first_name,'Johny') as "word_similarity(first_name"
        , word_similarity(last_name,'Smit')  as "word_similarity(last_name"
        , strict_word_similarity(first_name,'Johny') as "strict_word_similarity(first_name"
        , strict_word_similarity(last_name,'Smit') as "strict_word_similarity(last_name"
from my_table 
where    first_name % 'Johny' 
  and    last_name  % 'Smit' 
order by last_name <->'Smit'
        ,first_name<->'Johny'
limit 5;

explain analyze verbose execute find_john_smith_using_pg_trgm;

execute find_john_smith_using_pg_trgm;

DEALLOCATE



PREPARE




QUERY PLAN


Limit  (cost=0.28..8.33 rows=1 width=151) (actual time=14.058..14.061 rows=1 loops=1)

  Output: first_name, last_name, email, ((first_name <-> 'Johny'::text)), ((first_name <<-> 'Johny'::text)), ((first_name <<<-> 'Johny'::text)), ((last_name <-> 'Smit'::text)), ((last_name <<-> 'Smit'::text)), ((last_name <<<-> 'Smit'::text)), (word_similarity(first_name, 'Johny'::text)), (word_similarity(last_name, 'Smit'::text)), (strict_word_similarity(first_name, 'Johny'::text)), (strict_word_similarity(last_name, 'Smit'::text))

  ->  Index Scan using trgm_idx on public.my_table  (cost=0.28..8.33 rows=1 width=151) (actual time=14.056..14.058 rows=1 loops=1)

        Output: first_name, last_name, email, (first_name <-> 'Johny'::text), (first_name <<-> 'Johny'::text), (first_name <<<-> 'Johny'::text), (last_name <-> 'Smit'::text), (last_name <<-> 'Smit'::text), (last_name <<<-> 'Smit'::text), word_similarity(first_name, 'Johny'::text), word_similarity(last_name, 'Smit'::text), strict_word_similarity(first_name, 'Johny'::text), strict_word_similarity(last_name, 'Smit'::text)

        Index Cond: ((my_table.first_name % 'Johny'::text) AND (my_table.last_name % 'Smit'::text))

        Order By: ((my_table.last_name <-> 'Smit'::text) AND (my_table.first_name <-> 'Johny'::text))

Planning Time: 1.106 ms

Execution Time: 14.133 ms



EXPLAIN




first_name
last_name
email
first_name<->
first_name<<->
first_name<<<->
last_name<->
last_name<<->
last_name<<<->
word_similarity(first_name
word_similarity(last_name
strict_word_similarity(first_name
strict_word_similarity(last_name


John
Smith
john.smith@gmail.com
0.4285714
0.19999999
0.4285714
0.4285714
0.3333333
0.4285714
0.8
0.6666667
0.5714286
0.5714286



SELECT 1


insert into my_table values ('Johan','Smittson','johan.smittson@gmail.com');
execute find_john_smith_using_pg_trgm;

INSERT 0 1




first_name
last_name
email
first_name<->
first_name<<->
first_name<<<->
last_name<->
last_name<<->
last_name<<<->
word_similarity(first_name
word_similarity(last_name
strict_word_similarity(first_name
strict_word_similarity(last_name


John
Smith
john.smith@gmail.com
0.4285714
0.19999999
0.4285714
0.4285714
0.3333333
0.4285714
0.8
0.6666667
0.5714286
0.5714286

Johan
Smittson
johan.smittson@gmail.com
0.6666666
0.5
0.6666666
0.6
0.5555556
0.6
0.5
0.44444445
0.33333334
0.4



SELECT 2


drop index trgm_idx;

DROP INDEX


vacuum analyze my_table;

VACUUM


--CREATE INDEX trgm_idx1 ON my_table USING GIST (first_name gist_trgm_ops,last_name gist_trgm_ops);
CREATE INDEX trgm_idx2 ON my_table USING GIST ((first_name||' '||last_name) gist_trgm_ops);

CREATE INDEX


vacuum analyze my_table;

VACUUM


deallocate find_john_smith_using_pg_trgm;
prepare find_john_smith_using_pg_trgm(text) as 
select *,$1 as search_phrase
        , (first_name||' '||last_name)<->  $1 as "<->"
        , (first_name||' '||last_name)<<-> $1 as "<<->"
        , (first_name||' '||last_name)<<<->$1 as "<<<->"
        , word_similarity(first_name||' '||last_name,$1)
        , strict_word_similarity(first_name||' '||last_name,$1)
from my_table 
where    (first_name||' '||last_name) % $1
order by (first_name||' '||last_name)<->$1
limit 5;

explain analyze verbose execute find_john_smith_using_pg_trgm('Johny Smit');

execute find_john_smith_using_pg_trgm('Johny Smit');

DEALLOCATE



PREPARE




QUERY PLAN


Limit  (cost=0.28..23.07 rows=5 width=163) (actual time=22.448..22.473 rows=2 loops=1)

  Output: first_name, last_name, email, 'Johny Smit'::text, ((((first_name || ' '::text) || last_name) <-> 'Johny Smit'::text)), ((((first_name || ' '::text) || last_name) <<-> 'Johny Smit'::text)), ((((first_name || ' '::text) || last_name) <<<-> 'Johny Smit'::text)), (word_similarity(((first_name || ' '::text) || last_name), 'Johny Smit'::text)), (strict_word_similarity(((first_name || ' '::text) || last_name), 'Johny Smit'::text))

  ->  Index Scan using trgm_idx2 on public.my_table  (cost=0.28..36.74 rows=8 width=163) (actual time=22.446..22.469 rows=2 loops=1)

        Output: first_name, last_name, email, 'Johny Smit'::text, (((first_name || ' '::text) || last_name) <-> 'Johny Smit'::text), (((first_name || ' '::text) || last_name) <<-> 'Johny Smit'::text), (((first_name || ' '::text) || last_name) <<<-> 'Johny Smit'::text), word_similarity(((first_name || ' '::text) || last_name), 'Johny Smit'::text), strict_word_similarity(((first_name || ' '::text) || last_name), 'Johny Smit'::text)

        Index Cond: (((my_table.first_name || ' '::text) || my_table.last_name) % 'Johny Smit'::text)

        Order By: (((my_table.first_name || ' '::text) || my_table.last_name) <-> 'Johny Smit'::text)

Planning Time: 1.412 ms

Execution Time: 22.504 ms



EXPLAIN




first_name
last_name
email
search_phrase
<->
<<->
<<<->
word_similarity
strict_word_similarity


John
Smith
john.smith@gmail.com
Johny Smit
0.4285714
0.38461536
0.4285714
0.61538464
0.5714286

Johan
Smittson
johan.smittson@gmail.com
Johny Smit
0.6315789
0.6111111
0.6315789
0.3888889
0.36842105



SELECT 2


execute find_john_smith_using_pg_trgm('Johny');


first_name
last_name
email
search_phrase
<->
<<->
<<<->
word_similarity
strict_word_similarity


John
Smith
john.smith@gmail.com
Johny
0.6923077
0.6363636
0.6923077
0.36363637
0.30769232



SELECT 1


execute find_john_smith_using_pg_trgm('Smitt');


first_name
last_name
email
search_phrase
<->
<<->
<<<->
word_similarity
strict_word_similarity


Johan
Smittson
johan.smittson@gmail.com
Smitt
0.6875
0.6666666
0.6875
0.33333334
0.3125

John
Smith
john.smith@gmail.com
Smitt
0.6923077
0.6363636
0.6923077
0.36363637
0.30769232



SELECT 2
first_name	last_name	email	first_name<->	first_name<<->	first_name<<<->	last_name<->	last_name<<->	last_name<<<->	word_similarity(first_name	word_similarity(last_name	strict_word_similarity(first_name	strict_word_similarity(last_name
John	Smith	john.smith@gmail.com	0.4285714	0.19999999	0.4285714	0.4285714	0.3333333	0.4285714	0.8	0.6666667	0.5714286	0.5714286
Johan	Smittson	johan.smittson@gmail.com	0.6666666	0.5	0.6666666	0.6	0.5555556	0.6	0.5	0.44444445	0.33333334	0.4