The incorrect cross join in your original query produces an extremely expensive Cartesian product of 6760000000000 rows (2.6e6^2). The count takes a while, producing nonsense. Related:
And it has to be mentioned: there are better ways to mark deleted rows than concatenating the string '[deleted]' into a character column (not even at the begin or end of the string, no, somewhere in the string). Like ... basically any other way. But consider a
timestamptz column named
now() as default value.
There is also the corner case of an empty table, which would result in a "division by zero". Use
NULLIF() to (correctly) get NULL for this case.
SELECT round(count(body LIKE '%[deleted]%' OR NULL)::numeric
/ NULLIF(count(*), 0), 2) AS percent_deleted
round() to round to n fractional digits (which demands
Since you stress the performance of the query: there may be (much) faster solutions for your big table, depending on exact requirements and use case. If ...
- Speed of this query is important / you run it a lot.
- "deleted" rows are rare or rows are wide - which makes a partial index small when compared to the table.
We can assume a pretty good estimate for the total number of rows in the table statistics. That means:
- autovacuum is running.
- no substantial changes to the total row number in very short periods of time (large enough
DELETE that would actually change percentage until reflected in updated table statistics).
Then, this is good enough and many times faster. Create a partial index holding only "deleted" rows:
CREATE INDEX ON comments (comment_id) WHERE body LIKE '%[deleted]%';
The index column (
comment_id) is irrelevant, might even be a constant
TRUE or something. I added the PK column, because that's typically useful for other purposes and it typically does not change, making it comparatively cheap as index column.
Then this results in a very cheap index-only scan on above partial index, plus an even cheaper index scan on the system table
/ (SELECT NULLIF(reltuples, 0)::int
FROM pg_class WHERE oid = 'comment'::regclass), 2) AS percent_deleted
WHERE body LIKE '%[deleted]%';
Counting is relatively slow for big tables in Postgres, this can make a huge difference for getting a low percentage from a big table.