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

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.