Re: Invisible Indexes

From: Robert Haas <robertmhaas(at)gmail(dot)com>
To: Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)us>
Cc: Peter Geoghegan <pg(at)bowt(dot)ie>, Andrew Dunstan <andrew(dot)dunstan(at)2ndquadrant(dot)com>, PostgreSQL-development <pgsql-hackers(at)postgresql(dot)org>
Subject: Re: Invisible Indexes
Date: 2018-06-19 18:05:24
Message-ID: CA+Tgmobxpzdf184tH4jcnf=O=A_5rmWNUpmm3tumcFA-rksW+g@mail.gmail.com
Views: Raw Message | Whole Thread | Download mbox | Resend email
Thread:
Lists: pgsql-hackers

On Mon, Jun 18, 2018 at 6:12 PM, Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)us> wrote:
> Peter Geoghegan <pg(at)bowt(dot)ie> writes:
>> On Mon, Jun 18, 2018 at 2:57 PM, Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)us> wrote:
>>> I think the actually desirable way to handle this sort of thing is through
>>> an "index advisor" sort of plugin, which can hide a given index from the
>>> planner without any globally visible side-effects.
>
>> The globally visible side-effects are the point, though. Some users
>> desire cheap insurance against dropping what turns out to be the wrong
>> index.
>
> Perhaps there are use-cases where you want globally visible effects,
> but the primary use-case Andrew cited (i.e. EXPLAIN experimentation)
> would not want that.
>
> Anyway, if we do it with a GUC, the user can control the scope of
> the effects.

Yeah, I agree that a GUC seems more powerful and easier to roll out.
A downside is that there could be cached plans still using that old
index. If we did DDL on the index we could be sure they all got
invalidated, but otherwise how do we know?

BTW, like you, I seem to remember somebody writing an extension that
did added a GUC that did exactly this, and demoing it at a conference.
Maybe Oleg or Teodor?

--
Robert Haas
EnterpriseDB: http://www.enterprisedb.com
The Enterprise PostgreSQL Company

In response to

Responses

Browse pgsql-hackers by date

  From Date Subject
Next Message Euler Taveira 2018-06-19 19:22:34 Re: Invisible Indexes
Previous Message Tom Lane 2018-06-19 17:59:16 Re: Fast default stuff versus pg_upgrade