I have a locally hosted table comment on Postgres 9.5 with ~ 2.6 million rows. When I run the following query, it executes in .867 ms:

FROM comment
WHERE body LIKE '%[deleted]%';

But the following query takes > 50 seconds (stopped measuring at this point):

FROM comment c1, comment c2
WHERE c1.body LIKE '%[deleted]%';

I ask because I'm trying to implement a query like the following:

SELECT CAST(COUNT(c1.body)/COUNT(c2.body) as percent_deleted
FROM comment c1, comment c2
WHERE c1.body LIKE '%[deleted]%' 
AND c1.id = c2.id;
  • c1 and c2 refer to the same table. Mar 25, 2017 at 21:54
  • 2
    why do you cross join with itself?
    – McNets
    Mar 25, 2017 at 21:55
  • I've updated the op Mar 25, 2017 at 22:02

4 Answers 4


As far as I understand if you want to know which is % of 'deleted' rows, do not cross join with the same table, get total rows and calculate the result on your initial query.

with totalc2 as 
    select count(*) nrows
    from comment
SELECT CAST(COUNT(c1.body) AS float) /totalc2.nrows) as percent_deleted
FROM comment c1
WHERE c1.body LIKE '%[deleted]%' 

You can use it as a subquery:

SELECT CAST(COUNT(c1.body) AS float) / (select count(*) from comment) as percent_deleted
FROM comment c1
WHERE c1.body LIKE '%[deleted]%' 
  • Ah, okay. I was wondering whether I could avoid using a with statement, but that'll work fine. Thanks for the help Mar 25, 2017 at 22:08
  • 1
    (Made a small edit to add an "AS float" to your "CAST")
    – joanolo
    Mar 25, 2017 at 22:31
  • @joanolo I'm sorry I've misunderstood and edited it again, thanks
    – McNets
    Mar 25, 2017 at 22:36
  • You can just cast once: float / integer is legal, and will return a float.
    – joanolo
    Mar 25, 2017 at 22:47


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 deleted_at with now() as default value.

Short solution

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
FROM   comment;

dbfiddle here

Optionally, round() to round to n fractional digits (which demands numeric).


Fast solution

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 ...

  1. Speed of this query is important / you run it a lot.
  2. "deleted" rows are rare or rows are wide - which makes a partial index small when compared to the table.
  3. 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 INSERT or 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 pg_class:

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

dbfiddle here

Counting is relatively slow for big tables in Postgres, this can make a huge difference for getting a low percentage from a big table.



This seems to be a simple conditional aggregation:

   COUNT(case when body LIKE '%[deleted]%' then 1 end)
 / COUNT(c2.body) as percent_deleted
FROM comment

Edit: As @joanolo commented you must either CAST one of the counts to a Float/Decimal to avoid integer arithmetic or multiply like this to get percentage:

   100.00 * COUNT(case when body LIKE '%[deleted]%' then 1 end)
 / COUNT(c2.body) as percent_deleted
FROM comment
  • When using PostgreSQL, you would get an integer/integer division = 0. Other databases would make an implicit cast to float before making the division. In the case of PostgreSQL, the cast must be explicit.
    – joanolo
    Mar 25, 2017 at 22:28
  • Thanks for spotting, of course, this is Standard SQL behaviour, not just PostgreSQL.
    – dnoeth
    Mar 25, 2017 at 22:41

If you want to optimize the query (to make sure your table is only scanned once), you can also do it this way:

    (CAST(deleted_count AS float) / full_count) AS deleted_ratio
         count(CASE WHEN body LIKE '%[deleted]%' THEN 1 END) AS deleted_count,
         count(*) AS full_count
    ) AS q0 ;

You can check it at http://rextester.com/IMVFMF59176

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.