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

Поиск
Список
Период
Сортировка
От Koen De Groote
Тема Re: Index no longer being used, destroying and recreating it restores use.
Дата
Msg-id CAGbX52H_zRZFJwGurV9b+=0JHJmY-4Xrh16UqbQrsiiQ09WSoQ@mail.gmail.com
обсуждение исходный текст
Ответ на Re: Index no longer being used, destroying and recreating it restores use.  (Michael Lewis <mlewis@entrata.com>)
Ответы Re: Index no longer being used, destroying and recreating itrestores use.
Список pgsql-general
Дерево обсуждения
Alright, I've done that, and that seems to be a very good result: https://explain.depesz.com/s/xIph

The method I ended up using:

create or replace function still_needs_backup(shouldbebackedup bool, backupperformed bool)
returns BOOLEAN as $$
   select $1 AND NOT $2;
$$
language sql immutable;

And the index is as suggested.

It seems the amount of rows we end up with has improved.

Thank you for your help. I wasn't aware functions could interact with indexes in such a manner.

Regards,
Koen De Groote

On Mon, Jun 15, 2020 at 8:27 PM Michael Lewis <mlewis@entrata.com> wrote:
On Tue, Jun 9, 2020 at 6:24 AM Koen De Groote <kdg.dev@gmail.com> 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;

I meant something like the below (not tested)-

create or replace function still_needs_backup(shouldbebackedup bool, backupperformed bool)
returns BOOLEAN as $$
BEGIN
   return $1 AND NOT $2;
END;
$$
language sql;

CREATE INDEX CONCURRENTLY index_test ON item USING btree (itemCreated) WHERE still_needs_backup(shouldbebackedup, backupperformed);
ANALYZE item;

В списке pgsql-general по дате отправления: