Re: Index no longer being used, destroying and recreating it restores use.

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 <mlewis(at)entrata(dot)com> wrote:

> On Tue, Jun 9, 2020 at 6:24 AM Koen De Groote <kdg(dot)dev(at)gmail(dot)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;
>
>>