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?.
create schema usr;
set search_path to usr;
CREATE EXTENSION IF NOT EXISTS pg_trgm;

-- Table definitions
CREATE TABLE usr (
    id bigint NOT NULL GENERATED ALWAYS AS IDENTITY,
    loc_id bigint,
    first_name text,
    last_name text
);
CREATE TABLE loc (
    id bigint NOT NULL GENERATED ALWAYS AS IDENTITY,
    country text,
    city text
);

-- Primary keys
ALTER TABLE ONLY usr
    ADD CONSTRAINT usr_pkey PRIMARY KEY (id);
ALTER TABLE ONLY loc
    ADD CONSTRAINT loc_pkey PRIMARY KEY (id);

-- Foreign key
ALTER TABLE ONLY usr--.loc_id
    ADD CONSTRAINT usr_loc_id_fkey FOREIGN KEY (loc_id) REFERENCES loc(id);

-- Indices
CREATE INDEX usr_loc_id_idx ON usr
    USING btree (loc_id);
    
CREATE INDEX usr_first_name_last_name_idx ON usr
    USING gist ((first_name || ' ' || last_name) gist_trgm_ops);
CREATE INDEX loc_country_city_idx ON loc
    USING gist ((country || ' ' || city) gist_trgm_ops);

-- Insert dummy data
select setseed(.42);
INSERT INTO loc (country, city)
SELECT substr(md5(random()::text), 0, 25), 
       substr(md5(random()::text), 0, 25)
FROM generate_series(1, 6e4) AS t ;--on conflict do nothing;

--select setseed(.42);
INSERT INTO usr (first_name, last_name, loc_id)
SELECT substr(md5(random()::text), 0, 25), 
       substr(md5(random()::text), 0, 25), 
       trunc(random() * 6e4 + 1)
FROM generate_series(1, 6e4) AS t ;--on conflict do nothing;

CREATE SCHEMA

SET

CREATE EXTENSION

CREATE TABLE

CREATE TABLE

ALTER TABLE

ALTER TABLE

ALTER TABLE

CREATE INDEX

CREATE INDEX

CREATE INDEX

setseed

SELECT 1

INSERT 0 60000

INSERT 0 60000

--this one is quick on its own
explain analyze verbose
SELECT
    usr.id AS usr_id, usr.first_name, usr.last_name,
    loc.id AS loc_id, loc.country, loc.city
FROM usr
LEFT JOIN loc ON usr.loc_id = loc.id
WHERE 'baker' <% (usr.first_name || ' ' || usr.last_name)
ORDER  BY 'baker' <<-> (usr.first_name || ' ' || usr.last_name)
--LIMIT 20;

QUERY PLAN

Sort  (cost=2793.39..2794.78 rows=556 width=148) (actual time=14.499..14.502 rows=0 loops=1)

  Output: usr.id, usr.first_name, usr.last_name, loc.id, loc.country, loc.city, (('baker'::text <<-> ((usr.first_name || ' '::text) || usr.last_name)))

  Sort Key: (('baker'::text <<-> ((usr.first_name || ' '::text) || usr.last_name)))

  Sort Method: quicksort  Memory: 25kB

  ->  Hash Left Join  (cost=2030.92..2768.04 rows=556 width=148) (actual time=14.484..14.487 rows=0 loops=1)

        Output: usr.id, usr.first_name, usr.last_name, loc.id, loc.country, loc.city, ('baker'::text <<-> ((usr.first_name || ' '::text) || usr.last_name))

        Inner Unique: true

        Hash Cond: (usr.loc_id = loc.id)

        ->  Bitmap Heap Scan on usr.usr  (cost=105.98..837.46 rows=556 width=80) (actual time=14.484..14.484 rows=0 loops=1)

              Output: usr.id, usr.loc_id, usr.first_name, usr.last_name

              Filter: ('baker'::text <% ((usr.first_name || ' '::text) || usr.last_name))

              ->  Bitmap Index Scan on usr_first_name_last_name_idx  (cost=0.00..105.84 rows=556 width=0) (actual time=14.475..14.475 rows=0 loops=1)

                    Index Cond: (((usr.first_name || ' '::text) || usr.last_name) %> 'baker'::text)

        ->  Hash  (cost=1234.42..1234.42 rows=55242 width=72) (never executed)

              Output: loc.id, loc.country, loc.city

              ->  Seq Scan on usr.loc  (cost=0.00..1234.42 rows=55242 width=72) (never executed)

                    Output: loc.id, loc.country, loc.city

Planning Time: 0.561 ms

Execution Time: 14.624 ms

EXPLAIN

--this one is also quick on its own
explain analyze verbose
SELECT
    usr.id AS usr_id, usr.first_name, usr.last_name,
    loc.id AS loc_id, loc.country, loc.city
FROM usr
LEFT JOIN loc ON usr.loc_id = loc.id
WHERE 'baker' <% (loc.country || ' ' || loc.city)
ORDER  BY ('baker' <<-> (loc.country || ' ' || loc.city))
--LIMIT 20;

QUERY PLAN

Sort  (cost=2271.61..2273.00 rows=555 width=148) (actual time=14.286..14.289 rows=0 loops=1)

  Output: usr.id, usr.first_name, usr.last_name, loc.id, loc.country, loc.city, (('baker'::text <<-> ((loc.country || ' '::text) || loc.city)))

  Sort Key: (('baker'::text <<-> ((loc.country || ' '::text) || loc.city)))

  Sort Method: quicksort  Memory: 25kB

  ->  Hash Join  (cost=799.51..2246.31 rows=555 width=148) (actual time=14.273..14.276 rows=0 loops=1)

        Output: usr.id, usr.first_name, usr.last_name, loc.id, loc.country, loc.city, ('baker'::text <<-> ((loc.country || ' '::text) || loc.city))

        Inner Unique: true

        Hash Cond: (usr.loc_id = loc.id)

        ->  Seq Scan on usr.usr  (cost=0.00..1296.75 rows=55575 width=80) (actual time=0.014..0.014 rows=1 loops=1)

              Output: usr.id, usr.loc_id, usr.first_name, usr.last_name

        ->  Hash  (cost=792.61..792.61 rows=552 width=72) (actual time=14.251..14.252 rows=0 loops=1)

              Output: loc.id, loc.country, loc.city

              Buckets: 1024  Batches: 1  Memory Usage: 8kB

              ->  Bitmap Heap Scan on usr.loc  (cost=105.94..792.61 rows=552 width=72) (actual time=14.250..14.251 rows=0 loops=1)

                    Output: loc.id, loc.country, loc.city

                    Filter: ('baker'::text <% ((loc.country || ' '::text) || loc.city))

                    ->  Bitmap Index Scan on loc_country_city_idx  (cost=0.00..105.80 rows=552 width=0) (actual time=14.246..14.247 rows=0 loops=1)

                          Index Cond: (((loc.country || ' '::text) || loc.city) %> 'baker'::text)

Planning Time: 0.191 ms

Execution Time: 14.334 ms

EXPLAIN

--this one's dog slow
explain analyze verbose
SELECT
    usr.id AS usr_id, usr.first_name, usr.last_name,
    loc.id AS loc_id, loc.country, loc.city
FROM usr
LEFT JOIN loc ON usr.loc_id = loc.id
WHERE
    'baker' <% (usr.first_name || ' ' || usr.last_name)
    OR
    'baker' <% (loc.country || ' ' || loc.city)
ORDER BY
    ('baker' <<-> (usr.first_name || ' ' || usr.last_name))
    +
    ('baker' <<-> (loc.country || ' ' || loc.city));

QUERY PLAN

Sort  (cost=3442.88..3445.64 rows=1106 width=148) (actual time=877.859..877.862 rows=0 loops=1)

  Output: usr.id, usr.first_name, usr.last_name, loc.id, loc.country, loc.city, ((('baker'::text <<-> ((usr.first_name || ' '::text) || usr.last_name)) + ('baker'::text <<-> ((loc.country || ' '::text) || loc.city))))

  Sort Key: ((('baker'::text <<-> ((usr.first_name || ' '::text) || usr.last_name)) + ('baker'::text <<-> ((loc.country || ' '::text) || loc.city))))

  Sort Method: quicksort  Memory: 25kB

  ->  Hash Left Join  (cost=1924.95..3386.96 rows=1106 width=148) (actual time=877.854..877.856 rows=0 loops=1)

        Output: usr.id, usr.first_name, usr.last_name, loc.id, loc.country, loc.city, (('baker'::text <<-> ((usr.first_name || ' '::text) || usr.last_name)) + ('baker'::text <<-> ((loc.country || ' '::text) || loc.city)))

        Inner Unique: true

        Hash Cond: (usr.loc_id = loc.id)

        Filter: (('baker'::text <% ((usr.first_name || ' '::text) || usr.last_name)) OR ('baker'::text <% ((loc.country || ' '::text) || loc.city)))

        Rows Removed by Filter: 60000

        ->  Seq Scan on usr.usr  (cost=0.00..1296.75 rows=55575 width=80) (actual time=0.008..9.546 rows=60000 loops=1)

              Output: usr.id, usr.loc_id, usr.first_name, usr.last_name

        ->  Hash  (cost=1234.42..1234.42 rows=55242 width=72) (actual time=22.177..22.178 rows=60000 loops=1)

              Output: loc.id, loc.country, loc.city

              Buckets: 65536  Batches: 1  Memory Usage: 5786kB

              ->  Seq Scan on usr.loc  (cost=0.00..1234.42 rows=55242 width=72) (actual time=0.009..7.300 rows=60000 loops=1)

                    Output: loc.id, loc.country, loc.city

Planning Time: 0.175 ms

Execution Time: 877.918 ms

EXPLAIN

--this one runs the two quick ones separately, deduplicates and orders them
EXPLAIN ANALYSE VERBOSE
SELECT * FROM
(   SELECT
        usr.id AS usr_id, usr.first_name, usr.last_name,
        loc.id AS loc_id, loc.country, loc.city
    FROM usr
    LEFT JOIN loc ON usr.loc_id = loc.id
    WHERE 'baker' <% (loc.country || ' ' || loc.city)
    UNION
    SELECT
        usr.id AS usr_id, usr.first_name, usr.last_name,
        loc.id AS loc_id, loc.country, loc.city
    FROM usr
    LEFT JOIN loc ON usr.loc_id = loc.id
    WHERE 'baker' <% (usr.first_name || ' ' || usr.last_name)
) ORDER BY 
    ('baker' <<-> (first_name || ' ' || last_name))
    +
    ('baker' <<-> (country || ' ' || city));

QUERY PLAN

Sort  (cost=5112.22..5115.00 rows=1111 width=148) (actual time=28.785..28.791 rows=0 loops=1)

  Output: unnamed_subquery.usr_id, unnamed_subquery.first_name, unnamed_subquery.last_name, unnamed_subquery.loc_id, unnamed_subquery.country, unnamed_subquery.city, ((('baker'::text <<-> ((unnamed_subquery.first_name || ' '::text) || unnamed_subquery.last_name)) + ('baker'::text <<-> ((unnamed_subquery.country || ' '::text) || unnamed_subquery.city))))

  Sort Key: ((('baker'::text <<-> ((unnamed_subquery.first_name || ' '::text) || unnamed_subquery.last_name)) + ('baker'::text <<-> ((unnamed_subquery.country || ' '::text) || unnamed_subquery.city))))

  Sort Method: quicksort  Memory: 25kB

  ->  Subquery Scan on unnamed_subquery  (cost=5025.47..5056.02 rows=1111 width=148) (actual time=28.779..28.785 rows=0 loops=1)

        Output: unnamed_subquery.usr_id, unnamed_subquery.first_name, unnamed_subquery.last_name, unnamed_subquery.loc_id, unnamed_subquery.country, unnamed_subquery.city, (('baker'::text <<-> ((unnamed_subquery.first_name || ' '::text) || unnamed_subquery.last_name)) + ('baker'::text <<-> ((unnamed_subquery.country || ' '::text) || unnamed_subquery.city)))

        ->  HashAggregate  (cost=5025.47..5036.58 rows=1111 width=144) (actual time=28.778..28.783 rows=0 loops=1)

              Output: usr.id, usr.first_name, usr.last_name, loc.id, loc.country, loc.city

              Group Key: usr.id, usr.first_name, usr.last_name, loc.id, loc.country, loc.city

              Batches: 1  Memory Usage: 73kB

              ->  Append  (cost=798.13..5008.80 rows=1111 width=144) (actual time=28.758..28.763 rows=0 loops=1)

                    ->  Hash Join  (cost=798.13..2240.77 rows=555 width=144) (actual time=14.328..14.331 rows=0 loops=1)

                          Output: usr.id, usr.first_name, usr.last_name, loc.id, loc.country, loc.city

                          Inner Unique: true

                          Hash Cond: (usr.loc_id = loc.id)

                          ->  Seq Scan on usr.usr  (cost=0.00..1296.75 rows=55575 width=80) (actual time=0.007..0.007 rows=1 loops=1)

                                Output: usr.id, usr.loc_id, usr.first_name, usr.last_name

                          ->  Hash  (cost=791.23..791.23 rows=552 width=72) (actual time=14.316..14.318 rows=0 loops=1)

                                Output: loc.id, loc.country, loc.city

                                Buckets: 1024  Batches: 1  Memory Usage: 8kB

                                ->  Bitmap Heap Scan on usr.loc  (cost=104.56..791.23 rows=552 width=72) (actual time=14.315..14.316 rows=0 loops=1)

                                      Output: loc.id, loc.country, loc.city

                                      Filter: ('baker'::text <% ((loc.country || ' '::text) || loc.city))

                                      ->  Bitmap Index Scan on loc_country_city_idx  (cost=0.00..104.42 rows=552 width=0) (actual time=14.311..14.311 rows=0 loops=1)

                                            Index Cond: (((loc.country || ' '::text) || loc.city) %> 'baker'::text)

                    ->  Hash Left Join  (cost=2029.53..2762.48 rows=556 width=144) (actual time=14.422..14.424 rows=0 loops=1)

                          Output: usr_1.id, usr_1.first_name, usr_1.last_name, loc_1.id, loc_1.country, loc_1.city

                          Inner Unique: true

                          Hash Cond: (usr_1.loc_id = loc_1.id)

                          ->  Bitmap Heap Scan on usr.usr usr_1  (cost=104.59..836.07 rows=556 width=80) (actual time=14.421..14.422 rows=0 loops=1)

                                Output: usr_1.id, usr_1.loc_id, usr_1.first_name, usr_1.last_name

                                Filter: ('baker'::text <% ((usr_1.first_name || ' '::text) || usr_1.last_name))

                                ->  Bitmap Index Scan on usr_first_name_last_name_idx  (cost=0.00..104.45 rows=556 width=0) (actual time=14.417..14.417 rows=0 loops=1)

                                      Index Cond: (((usr_1.first_name || ' '::text) || usr_1.last_name) %> 'baker'::text)

                          ->  Hash  (cost=1234.42..1234.42 rows=55242 width=72) (never executed)

                                Output: loc_1.id, loc_1.country, loc_1.city

                                ->  Seq Scan on usr.loc loc_1  (cost=0.00..1234.42 rows=55242 width=72) (never executed)

                                      Output: loc_1.id, loc_1.country, loc_1.city

Planning Time: 0.309 ms

Execution Time: 28.912 ms

EXPLAIN