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

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;

And the index for that should be this, I think: "CREATE INDEX CONCURRENTLY
index_test ON item USING btree (still_needs_backup, itemCreated, filepath)
WHERE still_needs_backup(true, false) = true;"
However postgres throws an error here, saying "ERROR: functions in index
predicate must be marked IMMUTABLE".

I tried it also without the first argument, same error.

And I don't think I can do that, because the return is not IMMUTABLE. It is
at best STABLE, but certainly not IMMUTABLE.

So yeah, I'm probably not understanding the suggestion properly? Either
way, I still have questions about the earlier function I created, namely
how reliable that performance is. If not the same thing will happen as with
the re-created index.

Regards,
Koen

On Mon, Jun 8, 2020 at 11:15 PM Michael Lewis <mlewis(at)entrata(dot)com> wrote:

> On Mon, Jun 8, 2020 at 2:34 PM Koen De Groote <kdg(dot)dev(at)gmail(dot)com> wrote:
>
>> So, this query:
>>
>> select * from item where shouldbebackedup=true and
>> itemCreated<='2020-06-05 00:00:00.000' and backupperformed=false order by
>> filepath asc, id asc limit 100 offset 10400;
>>
>> Was made into a function:
>>
>> create or replace function NeedsBackup(text, int, int default 100)
>> returns setof item as $$
>> BEGIN
>> return query select * from item where shouldbebackedup=true and
>> itemCreated<=$1::timestamp without time zone and backupperformed=false
>> order by filepath asc, id asc limit $3 offset $2;
>> END;
>> $$
>> language 'plpgsql';
>>
>
>
> What I had meant was a function perhaps called backup_needed_still(
> backupperformed bool, shouldbebackedup bool) which would return bool; This
> could be written in SQL only with no need for plpgsql. By the way, the
> language name being in single quotes is deprecated.
>
> Then you could create an index on the table- either on the timestamp
> column where that function returns true, or just creating the index
> directly on the boolean result of that function call if that is what is
> needed to get the custom stats from a functional index. Then you would
> include the function call in your query instead of the two individual
> boolean columns.
>