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

[Koen De Groote <kdg.dev@xxxxxxxxx>]

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@xxxxxxxxxxx> wrote:

On Mon, Jun 8, 2020 at 2:34 PM Koen De Groote <kdg.dev@xxxxxxxxx> 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.