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

On Tue, Jun 9, 2020 at 02:23:51PM +0200, Koen De Groote 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;
>
>
> 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.

I think you need to look at EXPLAIN ANALYZE and see how close the
estimate is from the actual counts for various stages. The original
query had these quite different, leading to misestimation and wrong
plans. If the new EXPLAIN ANALYZE has estimates closer to actual, the
problem should not reappear.

--
Bruce Momjian <bruce(at)momjian(dot)us> https://momjian.us
EnterpriseDB https://enterprisedb.com

The usefulness of a cup is in its emptiness, Bruce Lee