Postgres 9.6

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 comment (
   comment_id serial PRIMARY KEY
 , body text
);

INSERT INTO comment(body)
VALUES ('foo')
     , ('bar [deleted]')
     , ('not really [deleted')
     , ('[deleted]')
     , ('baz [deleted]')
;
5 rows affected

SELECT round(count(body LIKE '%[deleted]%' OR NULL)::numeric
           / NULLIF(count(*), 0), 2) AS percent_deleted
FROM   comment;


percent_deleted


0.60


CREATE INDEX ON comment (comment_id) WHERE body LIKE '%[deleted]%';
SELECT round(count(*)::numeric
          / (SELECT NULLIF(reltuples, 0)::int8 FROM pg_class WHERE oid = 'comment'::regclass), 2) AS percent_deleted
FROM   comment
WHERE  body LIKE '%[deleted]%';


percent_deleted


0.60


SET enable_seqscan = off;
EXPLAIN
SELECT round(count(*)::numeric
          / (SELECT NULLIF(reltuples, 0)::int8 FROM pg_class WHERE oid = 'comment'::regclass), 2) AS percent_deleted
FROM   comment
WHERE  body LIKE '%[deleted]%';


QUERY PLAN


Aggregate  (cost=16.44..16.46 rows=1 width=32)

  InitPlan 1 (returns $0)

    ->  Index Scan using pg_class_oid_index on pg_class  (cost=0.27..8.30 rows=1 width=8)

          Index Cond: (oid = '171580'::oid)

  ->  Index Only Scan using comment_comment_id_idx on comment  (cost=0.13..8.14 rows=1 width=0)