Why is one query consistently ~25ms faster than another in postgres?

Question

A friend wrote a query with the following condition:

    AND ( SELECT count(1) FROM users_alerts_status uas 
         WHERE uas.alert_id = context_alert.alert_id 
         AND   uas.user_id = 18309 
         AND   uas.status = 'read' ) = 0

Seeing this, I suggested we change it to:

    AND NOT EXISTS ( SELECT 1 FROM users_alerts_status uas 
           WHERE uas.alert_id = context_alert.alert_id 
           AND   uas.user_id = 18309 
           AND   uas.status = 'read' )

But in testing, the first version of the query is consistently between 20 and 30ms faster (we tested after restarting the server). Conceptually, what am I missing?

Answer 1

My guess would be that the first one can short circuit; as soon as it sees any rows that match the criteria, it can return the count of 1. The second one needs to check every row (and it returns a row of "1" for every result), so doesn't get the speed benefit of short circuiting.

That being said, doing an EXPLAIN (or whatever your database supports) might give a better insight than my guess.

Answer 2

Conceptually, I'd say that your option is at least as good as the other, at least a little more elegant. I'm not sure if it should be slower or faster - and if those 25ms are relevant. The definite answer, usually, comes by looking at the EXPLAIN output.

What Postgresql version is that? PG 8.4 is said to have some optimizations regarding NOT EXISTS