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(10000,20000);

CREATE TABLE

INSERT 0 10001

VACUUM ANALYZE textfun;

VACUUM

CREATE UNIQUE INDEX ON textfun(content COLLATE "C");

CREATE INDEX

EXPLAIN ANALYZE
SELECT content FROM textfun WHERE  content LIKE '1500000%';

QUERY PLAN

Index Only Scan using textfun_content_idx on textfun  (cost=0.29..4.31 rows=1 width=30) (actual time=0.006..0.006 rows=0 loops=1)

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

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

  Heap Fetches: 0

Planning Time: 5.188 ms

Execution Time: 1.110 ms

EXPLAIN

EXPLAIN ANALYZE
SELECT content FROM textfun WHERE content >= 'z';

QUERY PLAN

Seq Scan on textfun  (cost=0.00..199.01 rows=1 width=30) (actual time=2.912..2.912 rows=0 loops=1)

  Filter: (content >= 'z'::text)

  Rows Removed by Filter: 10001

Planning Time: 0.049 ms

Execution Time: 2.922 ms

EXPLAIN

EXPLAIN ANALYZE
SELECT content FROM textfun WHERE content >= 'z' COLLATE "C";

QUERY PLAN

Index Only Scan using textfun_content_idx on textfun  (cost=0.29..4.30 rows=1 width=30) (actual time=0.007..0.007 rows=0 loops=1)

  Index Cond: (content >= 'z'::text COLLATE "C")

  Heap Fetches: 0

Planning Time: 5.562 ms

Execution Time: 0.021 ms

EXPLAIN