| From: | Bruce Momjian <bruce(at)momjian(dot)us> |
|---|---|
| To: | Koen De Groote <kdg(dot)dev(at)gmail(dot)com> |
| Cc: | Michael Lewis <mlewis(at)entrata(dot)com>, Adrian Klaver <adrian(dot)klaver(at)aklaver(dot)com>, Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)us>, PostgreSQL General <pgsql-general(at)lists(dot)postgresql(dot)org> |
| Subject: | Re: Index no longer being used, destroying and recreating it restores use. |
| Date: | 2020-06-16 10:00:38 |
| Message-ID: | 20200616100038.GA31183@momjian.us |
| Views: | Raw Message | Whole Thread | Download mbox | Resend email |
| Thread: | |
| Lists: | pgsql-general |
On Tue, Jun 16, 2020 at 11:49:15AM +0200, Koen De Groote wrote:
> 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.
This blog entry explains how statistics on expression/function indexes
can help:
https://momjian.us/main/blogs/pgblog/2017.html#February_20_2017
--
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
| From | Date | Subject | |
|---|---|---|---|
| Next Message | Eugene Pazhitnov | 2020-06-16 10:52:32 | pg_repack: WARNING: relation must have a primary key or not-null unique keys |
| Previous Message | Koen De Groote | 2020-06-16 09:49:15 | Re: Index no longer being used, destroying and recreating it restores use. |