Re: Is there a way to temporarily disable a index

That is it possible to tell the planner that index is off limits i.e. don't
ever generate a plan using it?

Rationale: Schema changes on big tables. I might have convinced myself /
strong beliefs that for all queries that I need to be fast the planner does
not need to use a given index (e.g. other possible plans are fast enough).
However if I just drop the index and it turns out I'm wrong I might be in a
world of pain because it might just take way to long to recreate the index.

I know that I can use pg_stat* to figure out if an index is used at all.
But in the presense of multiple indices and complex queries the planner
might prefer the index-to-be-dropped but the difference to the alternatives
available is immaterial.

The current best alternative we have is to test such changes on a testing
database that gets regularly restored from production. However at least in
our case we simply don't know all possible queries (and logging all of them
is not an option).

Cheers,

Bene

Benedikt Grundmann wrote
> That is it possible to tell the planner that index is off limits i.e.
> don't
> ever generate a plan using it?
>
> Rationale: Schema changes on big tables. I might have convinced myself /
> strong beliefs that for all queries that I need to be fast the planner
> does
> not need to use a given index (e.g. other possible plans are fast enough).
> However if I just drop the index and it turns out I'm wrong I might be in
> a
> world of pain because it might just take way to long to recreate the
> index.
>
> I know that I can use pg_stat* to figure out if an index is used at all.
> But in the presense of multiple indices and complex queries the planner
> might prefer the index-to-be-dropped but the difference to the
> alternatives
> available is immaterial.
>
> The current best alternative we have is to test such changes on a testing
> database that gets regularly restored from production. However at least
> in
> our case we simply don't know all possible queries (and logging all of
> them
> is not an option).
>
> Cheers,
>
> Bene

Worth double-checking in test but...

BEGIN;
DROP INDEX ...;
EXPLAIN ANALYZE SELECT ...
ROLLBACK;

Index dropping is transactional so your temporary action lasts until you
abort said transaction.

Though given your knowledge limitations this really isn't an improvement...

Catalog hacking could work but not recommended (nor do I know the proper
commands and limitations). Do you need the database/table to accept writes
during the testing period?

You can avoid all indexes, but not a named subset, using a configuration
parameter.

David J.

--
View this message in context: http://postgresql.1045698.n5.nabble.com/Is-there-a-way-to-temporarily-disable-a-index-tp5811249p5811290.html
Sent from the PostgreSQL - hackers mailing list archive at Nabble.com.

David G Johnston <david(dot)g(dot)johnston(at)gmail(dot)com> writes:
> Benedikt Grundmann wrote
>> That is it possible to tell the planner that index is off limits i.e.
>> don't ever generate a plan using it?

> Catalog hacking could work but not recommended (nor do I know the proper
> commands and limitations). Do you need the database/table to accept writes
> during the testing period?

Hacking pg_index.indisvalid could work, given a reasonably recent PG.
I would not try it in production until I'd tested it ;-)

regards, tom lane

On 2014-07-11 11:07:21 -0400, Tom Lane wrote:
> David G Johnston <david(dot)g(dot)johnston(at)gmail(dot)com> writes:
> > Benedikt Grundmann wrote
> >> That is it possible to tell the planner that index is off limits i.e.
> >> don't ever generate a plan using it?
>
> > Catalog hacking could work but not recommended (nor do I know the proper
> > commands and limitations). Do you need the database/table to accept writes
> > during the testing period?
>
> Hacking pg_index.indisvalid could work, given a reasonably recent PG.
> I would not try it in production until I'd tested it ;-)

Works, but IIRC can cause problems at least < 9.4 because concurrent
cache builds might miss the pg_index row...

Greetings,

Andres Freund

--
Andres Freund http://www.2ndQuadrant.com/
PostgreSQL Development, 24x7 Support, Training & Services

Andres Freund <andres(at)2ndquadrant(dot)com> writes:
> On 2014-07-11 11:07:21 -0400, Tom Lane wrote:
>> Hacking pg_index.indisvalid could work, given a reasonably recent PG.
>> I would not try it in production until I'd tested it ;-)

> Works, but IIRC can cause problems at least < 9.4 because concurrent
> cache builds might miss the pg_index row...

If you're talking about SnapshotNow hazards, I think the risk would be
minimal, and probably no worse than cases that the system will cause
by itself.

regards, tom lane

On 2014-07-11 11:20:08 -0400, Tom Lane wrote:
> Andres Freund <andres(at)2ndquadrant(dot)com> writes:
> > On 2014-07-11 11:07:21 -0400, Tom Lane wrote:
> >> Hacking pg_index.indisvalid could work, given a reasonably recent PG.
> >> I would not try it in production until I'd tested it ;-)
>
> > Works, but IIRC can cause problems at least < 9.4 because concurrent
> > cache builds might miss the pg_index row...
>
> If you're talking about SnapshotNow hazards, I think the risk would be
> minimal, and probably no worse than cases that the system will cause
> by itself.

Yes, SnapshotNow. I could reproduce it causing 'spurious' HOT updates
and missing index inserts a while back. And I don't think it's
comparable with normal modifications. Those either have a modification
blocking lock or use heap_inplace...

Greetings,

Andres Freund

--
Andres Freund http://www.2ndQuadrant.com/
PostgreSQL Development, 24x7 Support, Training & Services

Andres Freund <andres(at)2ndquadrant(dot)com> writes:
> On 2014-07-11 11:20:08 -0400, Tom Lane wrote:
>> If you're talking about SnapshotNow hazards, I think the risk would be
>> minimal, and probably no worse than cases that the system will cause
>> by itself.

> Yes, SnapshotNow. I could reproduce it causing 'spurious' HOT updates
> and missing index inserts a while back. And I don't think it's
> comparable with normal modifications. Those either have a modification
> blocking lock or use heap_inplace...

I still think the risk is minimal, but if the OP was worried about this
he could take out an AccessExclusive lock on the parent table for long
enough to commit the pg_index change.

regards, tom lane

On Fri, Jul 11, 2014 at 11:07:21AM -0400, Tom Lane wrote:
> David G Johnston <david(dot)g(dot)johnston(at)gmail(dot)com> writes:
> > Benedikt Grundmann wrote
> >> That is it possible to tell the planner that index is off limits
> >> i.e.
> >> don't ever generate a plan using it?
>
> > Catalog hacking could work but not recommended (nor do I know the
> > proper
> > commands and limitations). Do you need the database/table to accept
> > writes
> > during the testing period?
>
> Hacking pg_index.indisvalid could work, given a reasonably recent PG.
> I would not try it in production until I'd tested it ;-)

I wonder whether this should be exposed at the SQL level? Hacking
pg_index is left to superusers, but the creator of an index (or the
owner of the schema) might want to experiment with disabling indices
while debugging query plans as well.

Turns out this is already in the TODO, Steve Singer has requested this
(in particular, "ALTER TABLE ... ENABLE|DISABLE INDEX ...") in
http://www.postgresql.org/message-id/87hbegz5ir.fsf@cbbrowne.afilias-int.info
(as linked to from the TODO wiki page), but the neighboring discussion
was mostly about FK constraints.

Thoughts?

Michael

On Fri, Jul 11, 2014 at 12:12 PM, Michael Banck <mbanck(at)gmx(dot)net> wrote:

> On Fri, Jul 11, 2014 at 11:07:21AM -0400, Tom Lane wrote:
> > David G Johnston <david(dot)g(dot)johnston(at)gmail(dot)com> writes:
> > > Benedikt Grundmann wrote
> > >> That is it possible to tell the planner that index is off limits
> > >> i.e.
> > >> don't ever generate a plan using it?
> >
> > > Catalog hacking could work but not recommended (nor do I know the
> > > proper
> > > commands and limitations). Do you need the database/table to accept
> > > writes
> > > during the testing period?
> >
> > Hacking pg_index.indisvalid could work, given a reasonably recent PG.
> > I would not try it in production until I'd tested it ;-)
>
> I wonder whether this should be exposed at the SQL level? Hacking
> pg_index is left to superusers, but the creator of an index (or the
> owner of the schema) might want to experiment with disabling indices
> while debugging query plans as well.
>
> Turns out this is already in the TODO, Steve Singer has requested this
> (in particular, "ALTER TABLE ... ENABLE|DISABLE INDEX ...") in
>
> http://www.postgresql.org/message-id/87hbegz5ir.fsf@cbbrowne.afilias-int.info
> (as linked to from the TODO wiki page), but the neighboring discussion
> was mostly about FK constraints.
>
> Thoughts?
>
>
> Michael
>

Apparently work is ongoing on to allow EXPLAIN to calculate the impact a
particular index has on table writes. What is needed is a mechanism to
temporarily facilitate the remove impact of specific indexes on reads
without ​having to disable the index for writing. Ideally on a per-query
basis so altering the catalog doesn't make sense. I know we do not want
traditional planner hints but in the spirit of the existing
enable_indexscan GUC there should be a "
disable_readofindex='table1.index1,table1.index2,table2.index1' " GUC
capability that would allow for session, user, or system-level control of
which indexes are to be used during table reads.

David J.