From: | Joel Jacobson <joel(at)trustly(dot)com> |
---|---|
To: | Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)us> |
Cc: | Corey Huinker <corey(dot)huinker(at)gmail(dot)com>, obartunov(at)gmail(dot)com, Jaime Casanova <jaime(dot)casanova(at)2ndquadrant(dot)com>, "Joshua D(dot) Drake" <jd(at)commandprompt(dot)com>, Tatsuo Ishii <ishii(at)postgresql(dot)org>, Pg Hackers <pgsql-hackers(at)postgresql(dot)org> |
Subject: | Re: Disabling an index temporarily |
Date: | 2016-03-09 08:34:19 |
Message-ID: | CAASwCXczQe=tfyQK8kkBQ4dMWSbPSz-b56enrE=ybdD3hG_ktQ@mail.gmail.com |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Thread: | |
Lists: | pgsql-hackers |
On Mon, Dec 14, 2015 at 10:27 AM, Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)us> wrote:
> Corey Huinker <corey(dot)huinker(at)gmail(dot)com> writes:
>> So, I'd propose we following syntax:
>> ALTER INDEX foo SET DISABLED
>> -- does the SET indisvalid = false shown earlier.
>
> This is exactly *not* what Tatsuo-san was after, though; he was asking
> for a session-local disable, which I would think would be by far the more
> common use-case. It's hard for me to see much of a reason to disable an
> index globally while still paying all the cost to maintain it. Seems to
> me the typical work flow would be more like "disable index in a test
> session, try all your queries and see how well they work, if you conclude
> you don't need the index then drop it". Or perhaps you could imagine that
> you want the index selected for use only in certain specific sessions ...
> but the above doesn't cater for that use-case either.
>
> Certainly, there's opportunities to improve the flexibility of the
> index-disable specifications in the plug-in Oleg and Teodor did. But
> I think that that is the right basic approach: some sort of SET command,
> not anything that alters the catalogs. We already have lots of
> infrastructure that could handle desires like having specific values
> active in only some sessions.
I searched for "indisvalid" and this thread came up.
I need this exact same thing as Tatsuo-san; a way to session-local
disable index(es),
so that plpgsql functions can avoid certain indexes when they are
created/planned.
How would one go about to implement such a SET command, without
altering the catalog?
I noticed the RelationReloadIndexInfo() which appears to be doing a
light-weight update of index changes,
including "relation->rd_index->indisvalid = index->indisvalid".
Or maybe one could call index_set_state_flags(indexId,
INDEX_DROP_CLEAR_VALID) before the function is compiled/planned,
and then reset it using index_set_state_flags(indexId,
INDEX_CREATE_SET_VALID) after it has been compiled/planned?
If someone could give me guidance on where to start I would be grateful.
Even if I don't succeed implementing this, it's at least fun and
interesting to dig into the postgres source code to learn things.
Thanks
Joel
From | Date | Subject | |
---|---|---|---|
Next Message | Kyotaro HORIGUCHI | 2016-03-09 08:35:10 | Re: PATCH: index-only scans with partial indexes |
Previous Message | Etsuro Fujita | 2016-03-09 08:30:44 | Re: Optimization for updating foreign tables in Postgres FDW |