The method I ended up using:
create or replace function still_needs_backup(shouldbebackedup bool, backupperformed bool)
returns BOOLEAN as $$
select $1 AND NOT $2;
$$
language sql immutable;
And the index is as suggested.
It seems the amount of rows we end up with has improved.
Thank you for your help. I wasn't aware functions could interact with indexes in such a manner.
Regards,
Koen De Groote
Right. In that case, the function I ended up with is this:
create or replace function still_needs_backup(bool, bool)
returns BOOLEAN as $$
BEGIN
PERFORM 1 from item where shouldbebackedup=$1 and backupperformed=$2;
IF FOUND THEN
RETURN TRUE;
ELSE
RETURN FALSE;
END IF;
END;
$$
language plpgsql;
I meant something like the below (not tested)-
create or replace function still_needs_backup(shouldbebackedup bool, backupperformed bool)
returns BOOLEAN as $$
BEGIN
return $1 AND NOT $2;
END;
$$
language sql;
CREATE INDEX CONCURRENTLY index_test ON item USING btree (itemCreated) WHERE still_needs_backup(shouldbebackedup, backupperformed);
ANALYZE item;