Postgres 12

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 TABLE textfun(content TEXT);

INSERT INTO textfun (content)
SELECT (CASE WHEN (random()<=0.3) THEN 'https://mywebsite/nanana/'
WHEN (random()<=0.6) THEN 'https://mywebsite/friendy/'
ELSE 'https://mywebsite/mina/' END) || generate_series(1000000,2000000);

CREATE TABLE

INSERT 0 1000001

CREATE UNIQUE INDEX text_b ON textfun(content text_pattern_ops);

CREATE INDEX

explain analyze SELECT content FROM textfun WHERE content LIKE '1500000%';

QUERY PLAN

Bitmap Heap Scan on textfun  (cost=191.68..7654.53 rows=5000 width=32) (actual time=0.033..0.034 rows=0 loops=1)

  Filter: (content ~~ '1500000%'::text)

  ->  Bitmap Index Scan on text_b  (cost=0.00..190.43 rows=5000 width=0) (actual time=0.030..0.030 rows=0 loops=1)

        Index Cond: ((content ~>=~ '1500000'::text) AND (content ~<~ '1500001'::text))

Planning Time: 0.845 ms

Execution Time: 0.147 ms

EXPLAIN