Query slows with additional FROM item (even with no extra actions)

Question

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:

SELECT COUNT(body)
FROM comment
WHERE body LIKE '%[deleted]%';

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

SELECT COUNT(c1.body)
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. – Leopold Boom Mar 25 '17 at 21:54 — Leopold Boom, Mar 25 '17 at 21:54
why do you cross join with itself? – McNets Mar 25 '17 at 21:55 — McNets, Mar 25 '17 at 21:55

McNets · Accepted Answer · 2017-03-25T22:49:38.703

2

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]%'

edited Mar 25 '17 at 22:49

answered Mar 25 '17 at 22:07

McNets

23,749
10
48
88

Ah, okay. I was wondering whether I could avoid using a with statement, but that'll work fine. Thanks for the help – Leopold Boom Mar 25 '17 at 22:08
1

(Made a small edit to add an "AS float" to your "CAST") – joanolo Mar 25 '17 at 22:31
@joanolo I'm sorry I've misunderstood and edited it again, thanks – McNets Mar 25 '17 at 22:36
You can just cast once: float / integer is legal, and will return a float. – joanolo Mar 25 '17 at 22:47

score 2 · Answer 2 · edited May 23 '17 at 12:40

Preface

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:

What does [FROM x, y] mean in Postgres?

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

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

Fast way to discover the row count of a table in PostgreSQL

dnoeth · Answer 3 · 2017-03-25T22:40:27.697

1

This seems to be a simple conditional aggregation:

SELECT
   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:

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

edited Mar 25 '17 at 22:40

answered Mar 25 '17 at 22:21

dnoeth

4,196
11
14

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 '17 at 22:28
Thanks for spotting, of course, this is Standard SQL behaviour, not just PostgreSQL. – dnoeth Mar 25 '17 at 22:41

score 0 · Answer 4 · answered Mar 25 '17 at 22:26

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

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

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

Query slows with additional FROM item (even with no extra actions)

4 Answers4

Preface

Short solution

Fast solution