Postgres 11

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 or replace function random_text_array(int, int)
returns text[] language sql as
$$
    select array_agg('t' || (round(random()* $1)::text))
    from generate_series(1, round(random() * $2)::int4)
$$;

CREATE FUNCTION

-- Step 1: Create a table with a text array field
CREATE TABLE subscriptions (
    id SERIAL PRIMARY KEY,
    tags TEXT[]
);

-- Step 2: Generate 0.5 million rows with random data
INSERT INTO subscriptions (tags)
SELECT
    random_text_array(100, 5)
FROM generate_series(1, 500000);

-- Step 3: Create two indexes on the text array field to compare btree vs gin
CREATE INDEX idx_subscriptions_btree ON subscriptions USING btree (tags);
CREATE INDEX idx_subscriptions_gin ON subscriptions USING gin (tags);

-- Populate statistics based on the data
analyze subscriptions;

CREATE TABLE

INSERT 0 500000

CREATE INDEX

CREATE INDEX

ANALYZE

-- Strongly discourage sequential scans
SET enable_seqscan = OFF;

-- Step 4: Perform a query using the @> operator

EXPLAIN analyze verbose SELECT * FROM subscriptions WHERE (tags @> ARRAY['t1']);

-- Check the execution plan to see if the index is being used
EXPLAIN analyze verbose SELECT * FROM subscriptions WHERE not (tags @> ARRAY['t1']);

SET

QUERY PLAN

Bitmap Heap Scan on public.subscriptions  (cost=119.84..5038.43 rows=12367 width=47) (actual time=1.838..77.133 rows=12342 loops=1)

  Output: id, tags

  Recheck Cond: (subscriptions.tags @> '{t1}'::text[])

  Heap Blocks: exact=4424

  ->  Bitmap Index Scan on idx_subscriptions_gin  (cost=0.00..116.75 rows=12367 width=0) (actual time=1.332..1.332 rows=12342 loops=1)

        Index Cond: (subscriptions.tags @> '{t1}'::text[])

Planning Time: 0.406 ms

Execution Time: 77.931 ms

EXPLAIN

QUERY PLAN

Seq Scan on public.subscriptions  (cost=10000000000.00..10000011014.00 rows=487633 width=47) (actual time=0.111..169.869 rows=437395 loops=1)

  Output: id, tags

  Filter: (NOT (subscriptions.tags @> '{t1}'::text[]))

  Rows Removed by Filter: 62605

Planning Time: 0.083 ms

Execution Time: 184.431 ms

EXPLAIN