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,5e4);

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,5e4);

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

SELECT 50000

ALTER TABLE

INSERT 0 1

INSERT 0 50000

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 first_name<->'Johny'
        ,last_name <->'Smit'
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=19.341..19.353 rows=1 loops=1)

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

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

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

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

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

Planning Time: 1.364 ms

Execution Time: 19.416 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 1 desc,2 desc
limit 2;

explain analyze verbose execute find_john_smith_using_pg_trgm;

execute find_john_smith_using_pg_trgm;

DEALLOCATE

PREPARE

QUERY PLAN

Limit  (cost=8.33..8.34 rows=1 width=151) (actual time=18.271..18.273 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))

  ->  Sort  (cost=8.33..8.34 rows=1 width=151) (actual time=18.269..18.270 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))

        Sort Key: my_table.first_name DESC, my_table.last_name DESC

        Sort Method: quicksort  Memory: 25kB

        ->  Index Scan using trgm_idx on public.my_table  (cost=0.28..8.32 rows=1 width=151) (actual time=13.132..18.259 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))

Planning Time: 1.136 ms

Execution Time: 18.356 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

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

first_name
last_name
email

John
Smith
john.smith@gmail.com

SELECT 1