Postgres 17

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?.

select version();

version

PostgreSQL 17.2 (Debian 17.2-1.pgdg120+1) on x86_64-pc-linux-gnu, compiled by gcc (Debian 12.2.0-14) 12.2.0, 64-bit

SELECT 1

CREATE TABLE benchmark_logs (
    id SERIAL PRIMARY KEY,
    message TEXT
);

CREATE TABLE

CREATE INDEX idx_gin_logs_message_tsvector
ON benchmark_logs USING GIN (to_tsvector('english', message))
WITH (fastupdate = off)

CREATE INDEX

INSERT INTO benchmark_logs (message) VALUES ('foo'), ('bar'), ('baz');

INSERT 0 3

EXPLAIN SELECT COUNT(*) -- Obviously the plan could change depending on the selected columns but this
  FROM benchmark_logs   -- is meant to be a demonstration that Postgres is smart enough to pick the index
  WHERE to_tsvector('english', message) @@ to_tsquery('english', 'research')

QUERY PLAN

Aggregate  (cost=19.57..19.58 rows=1 width=8)

  ->  Bitmap Heap Scan on benchmark_logs  (cost=8.59..19.55 rows=6 width=0)

        Recheck Cond: (to_tsvector('english'::regconfig, message) @@ '''research'''::tsquery)

        ->  Bitmap Index Scan on idx_gin_logs_message_tsvector  (cost=0.00..8.58 rows=6 width=0)

              Index Cond: (to_tsvector('english'::regconfig, message) @@ '''research'''::tsquery)

EXPLAIN