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

[Michael Lewis]

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;

>