Does PostgreSQL complete its evaluation of BOOL_OR ()?

Question

EXISTS faster than COUNT(*) , as it may be shorted

Many times I would like to check for things in SQL. For example me:

 -- PostgreSQL syntax, SQL standard syntax: SELECT EXISTS (SELECT .. FROM some_table WHERE some_boolean_expression) -- Oracle syntax SELECT CASE WHEN EXISTS (SELECT .. FROM some_table WHERE some_boolean_expression) THEN 1 ELSE 0 END FROM dual 

In most databases, EXISTS is "shorted", i.e. the database may stop looking for rows in the table as soon as it finds one row. This is usually much faster than comparing COUNT(*) >= 1 , as can be seen in this blog post .

Using EXISTS with GROUP BY

Sometimes I would like to do this for each group in a GROUP BY query, i.e. I would like to “aggregate” the meaning of existence. There is no EXISTS aggregate function, but PostgreSQL fortunately supports the BOOL_OR() aggregation function, as in this statement:

 SELECT something, bool_or (some_boolean_expression) FROM some_table GROUP BY something 

The documentation mentions something about COUNT(*) slow due to the obvious sequential scan needed to calculate the score. But, unfortunately, it does not say anything about the BOOL_OR() short circuit. This is true? Does BOOL_OR() leave aggregation of new values ​​as soon as it encounters the first TRUE value?

Answer 1

If you want to check for availability, I usually use the LIMIT / FETCH FIRST 1 ROW ONLY query:

 SELECT .. FROM some_table WHERE some_boolean_expression FETCH FIRST 1 ROW ONLY 

This usually stops execution after the first press.

The same method can be applied using LATERAL for each row (group) from another table.

 SELECT * FROM (SELECT something FROM some_table GROUP BY something ) t1 LEFT JOIN LATERAL (SELECT ... FROM ... WHERE ... FETCH FIRST 1 ROW ONLY) t2 ON (true) 

In t2 you can use the WHERE that matches any row for the group. It was executed only once for each group and was interrupted as soon as the first hit was found. However, whether it works better or worse, of course, depends on your search and indexing predicates.