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