clear markdown compare help best fiddles feedback dbanow.uk a leap of faith?
clear markdown donate comments/suggestions/bugs a leap of faith? bible365 diddy dollings
By using db<>fiddle, you agree to license everything you submit by Creative Commons CC0. 921333 fiddles created (11963 in the last week).

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
 hidden batch(es)


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


CREATE INDEX ON comment (comment_id) WHERE body LIKE '%[deleted]%';
 hidden batch(es)


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
 hidden batch(es)


SET enable_seqscan = off;
 hidden batch(es)


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)
 hidden batch(es)