Thread: BUG #18525: Boolean aggregate functions like EVERY/BOOL_AND should shortcut

The following bug has been logged on the website:

Bug reference:      18525
Logged by:          Mario
Email address:      blamario@protonmail.com
PostgreSQL version: 13.10
Operating system:   Linux
Description:

This is a feature request, not a bug strictly speaking. I'm working with a
query of the form

```
SELECT
  EVERY (field1 IS NULL) AS field1,
  EVERY (field2 IS NULL) AS field2,
  ...
  EVERY (field255 IS NULL) AS field255
FROM large_table
```

The goal is to establish if there are any columns that are always NULL, and
if so which ones. I was hoping that the EVERY aggregate functions would
_shortcut_ the aggregate as soon as a non-null value was encountered, and
furthermore that the SELECT statement would stop searching once all results
are established to be false. That's not happening: PostgreSQL takes two
hours to search through the entire long table, returning all false.

PG Bug reporting form <noreply@postgresql.org> writes:
> I was hoping that the EVERY aggregate functions would
> _shortcut_ the aggregate as soon as a non-null value was encountered, and
> furthermore that the SELECT statement would stop searching once all results
> are established to be false.

Don't hold your breath.  That would require poking a lot of holes in
our abstract API for aggregate functions, and the potential return
seems rather, um, specialized.

You can get an efficient plan for what you want along the lines of

select
  exists(select * from large_table where field1 is not null),
  exists(select * from large_table where field2 is not null),
  ...

This will result in a scan per EXISTS() rather than just one scan,
but on the other hand the scans will be smart enough to use indexes
where helpful.  In any case they will stop at first match.

            regards, tom lane