Postgres 14

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?.
-- utility function for returning a random string of the given length
CREATE OR REPLACE FUNCTION random_string (string_length integer DEFAULT 10)
RETURNS varchar AS $$
  SELECT array_to_string(
    ARRAY(
      SELECT chr((65 + round(random() * 25))::integer)
      FROM generate_series(1, string_length)
    ), '') $$
LANGUAGE SQL;

-- create our sample users table
CREATE TABLE users (
  id serial PRIMARY KEY,
  email varchar NOT NULL,
  name varchar NOT NULL,
  about varchar
);

-- populate with plenty of random data
INSERT INTO users (name, email, about)
SELECT
  'email-' || random_string (5),
  'name-' || random_string (5),
  'about-' || random_string (30)
FROM generate_series(1, 100000);

CREATE FUNCTION

CREATE TABLE

INSERT 0 100000

EXPLAIN SELECT * FROM users WHERE email ~ 'OAK';

QUERY PLAN

Seq Scan on users  (cost=0.00..2032.39 rows=2865 width=100)

  Filter: ((email)::text ~ 'OAK'::text)

EXPLAIN

EXPLAIN SELECT * FROM users WHERE email ~ 'OAK' ORDER BY name;

QUERY PLAN

Sort  (cost=2196.90..2204.06 rows=2865 width=100)

  Sort Key: name

  ->  Seq Scan on users  (cost=0.00..2032.39 rows=2865 width=100)

        Filter: ((email)::text ~ 'OAK'::text)

EXPLAIN

EXPLAIN ANALYZE SELECT * FROM users WHERE email ~ 'OAK' ORDER BY name;

QUERY PLAN

Sort  (cost=2196.90..2204.06 rows=2865 width=100) (actual time=55.759..55.761 rows=9 loops=1)

  Sort Key: name

  Sort Method: quicksort  Memory: 26kB

  ->  Seq Scan on users  (cost=0.00..2032.39 rows=2865 width=100) (actual time=1.211..55.608 rows=9 loops=1)

        Filter: ((email)::text ~ 'OAK'::text)

        Rows Removed by Filter: 99991

Planning Time: 0.042 ms

Execution Time: 55.779 ms

EXPLAIN

CREATE INDEX users_email_index ON users (email);
EXPLAIN ANALYZE SELECT * FROM users WHERE email ~ 'OAK' ORDER BY name;

CREATE INDEX

QUERY PLAN

Sort  (cost=2626.32..2636.32 rows=4000 width=100) (actual time=51.004..51.007 rows=9 loops=1)

  Sort Key: name

  Sort Method: quicksort  Memory: 26kB

  ->  Seq Scan on users  (cost=0.00..2387.00 rows=4000 width=100) (actual time=1.093..50.974 rows=9 loops=1)

        Filter: ((email)::text ~ 'OAK'::text)

        Rows Removed by Filter: 99991

Planning Time: 0.304 ms

Execution Time: 51.033 ms

EXPLAIN

EXPLAIN ANALYZE SELECT * FROM users WHERE email ~ 'OAK' ORDER BY name;

QUERY PLAN

Sort  (cost=2626.32..2636.32 rows=4000 width=100) (actual time=50.238..50.240 rows=9 loops=1)

  Sort Key: name

  Sort Method: quicksort  Memory: 26kB

  ->  Seq Scan on users  (cost=0.00..2387.00 rows=4000 width=100) (actual time=1.235..50.210 rows=9 loops=1)

        Filter: ((email)::text ~ 'OAK'::text)

        Rows Removed by Filter: 99991

Planning Time: 0.192 ms

Execution Time: 50.267 ms

EXPLAIN

EXPLAIN ANALYZE SELECT * FROM users WHERE email = 'OAK' ORDER BY name;

QUERY PLAN

Sort  (cost=942.34..943.59 rows=500 width=100) (actual time=0.054..0.055 rows=0 loops=1)

  Sort Key: name

  Sort Method: quicksort  Memory: 25kB

  ->  Bitmap Heap Scan on users  (cost=12.29..919.93 rows=500 width=100) (actual time=0.049..0.050 rows=0 loops=1)

        Recheck Cond: ((email)::text = 'OAK'::text)

        ->  Bitmap Index Scan on users_email_index  (cost=0.00..12.17 rows=500 width=0) (actual time=0.047..0.047 rows=0 loops=1)

              Index Cond: ((email)::text = 'OAK'::text)

Planning Time: 0.134 ms

Execution Time: 0.095 ms

EXPLAIN

DROP INDEX users_email_index;
CREATE INDEX users_email_index ON users USING GIN (email gin_trgm_ops);
EXPLAIN ANALYZE SELECT * FROM users WHERE email ~ 'OAK' ORDER BY name;

DROP INDEX

CREATE INDEX

QUERY PLAN

Sort  (cost=1473.32..1483.32 rows=4000 width=100) (actual time=0.297..0.299 rows=9 loops=1)

  Sort Key: name

  Sort Method: quicksort  Memory: 26kB

  ->  Bitmap Heap Scan on users  (cost=47.00..1234.00 rows=4000 width=100) (actual time=0.116..0.271 rows=9 loops=1)

        Recheck Cond: ((email)::text ~ 'OAK'::text)

        Heap Blocks: exact=9

        ->  Bitmap Index Scan on users_email_index  (cost=0.00..46.00 rows=4000 width=0) (actual time=0.062..0.062 rows=9 loops=1)

              Index Cond: ((email)::text ~ 'OAK'::text)

Planning Time: 0.372 ms

Execution Time: 0.332 ms

EXPLAIN