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

[Michael Lewis <mlewis@xxxxxxxxxxx>]

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.