Alright, I've done that, and that seems to be a very good result: https://explain.depesz.com/s/xIph
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 On Mon, Jun 15, 2020 at 8:27 PM Michael Lewis <mle...@entrata.com> wrote: > On Tue, Jun 9, 2020 at 6:24 AM Koen De Groote <kdg....@gmail.com> wrote: > >> 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; > >>
