Обсуждение: disabling an index without deleting it?
This might be a weird question...is there any way to disable a particular index without dropping it? There are a few queries I run where I'd like to test out the effects of having (and not having) different indexes on particular query plans and performance. I'd really prefer not to have to drop and ultimately recreate a particular index, as some of the data sets are quite large. So, is there any way to do this, or at least mimic this sort of behavior? Peter
On Tue, Feb 26, 2008 at 2:46 PM, Peter Koczan <pjkoczan@gmail.com> wrote: > This might be a weird question...is there any way to disable a > particular index without dropping it? > > There are a few queries I run where I'd like to test out the effects > of having (and not having) different indexes on particular query plans > and performance. I'd really prefer not to have to drop and ultimately > recreate a particular index, as some of the data sets are quite large. > > So, is there any way to do this, or at least mimic this sort of behavior? The brick to the head method would use set enable_indexscan = off; However, you can delete an index without actually deleting it like so: begin; drop index abc_dx; select .... rollback; and viola, your index is still there. note that there are likely some locking issues with this, so be careful with it in production. But on a test box it's a very easy way to test various indexes.
-----BEGIN PGP SIGNED MESSAGE----- Hash: SHA1 On Tue, 26 Feb 2008 14:57:51 -0600 "Scott Marlowe" <scott.marlowe@gmail.com> wrote: > The brick to the head method would use set enable_indexscan = off; > However, you can delete an index without actually deleting it like so: > > begin; > drop index abc_dx; > select .... > rollback; > > and viola, your index is still there. note that there are likely some > locking issues with this, so be careful with it in production. But on > a test box it's a very easy way to test various indexes. Wouldn't you also bloat the index? Joshua D. Drake - -- The PostgreSQL Company since 1997: http://www.commandprompt.com/ PostgreSQL Community Conference: http://www.postgresqlconference.org/ Donate to the PostgreSQL Project: http://www.postgresql.org/about/donate PostgreSQL SPI Liaison | SPI Director | PostgreSQL political pundit -----BEGIN PGP SIGNATURE----- Version: GnuPG v1.4.6 (GNU/Linux) iD8DBQFHxH6rATb/zqfZUUQRAp//AJ4wKiA4WRprp3L3y9UEAzz2rb2+hACaA9b7 A1k3n6GkyFwx2vrbnpD8CX0= =zYaI -----END PGP SIGNATURE-----
"Joshua D. Drake" <jd@commandprompt.com> writes:
> "Scott Marlowe" <scott.marlowe@gmail.com> wrote:
>> begin;
>> drop index abc_dx;
>> select ....
>> rollback;
>>
>> and viola, your index is still there. note that there are likely some
>> locking issues with this, so be careful with it in production. But on
>> a test box it's a very easy way to test various indexes.
> Wouldn't you also bloat the index?
No, what makes you think that? The index won't change at all in the
above example. The major problem is, as Scott says, that DROP INDEX
takes exclusive lock on the table so any other sessions will be locked
out of it for the duration of your test query.
regards, tom lane
-----BEGIN PGP SIGNED MESSAGE----- Hash: SHA1 On Tue, 26 Feb 2008 17:22:40 -0500 Tom Lane <tgl@sss.pgh.pa.us> wrote: > "Joshua D. Drake" <jd@commandprompt.com> writes: > > "Scott Marlowe" <scott.marlowe@gmail.com> wrote: > >> begin; > >> drop index abc_dx; > >> select .... > >> rollback; > >> > >> and viola, your index is still there. note that there are likely > >> some locking issues with this, so be careful with it in > >> production. But on a test box it's a very easy way to test > >> various indexes. > > > Wouldn't you also bloat the index? > > No, what makes you think that? Well now that I am obviously wrong :P. I was thinking about it from the: BEGIN; DELETE FROM ROLLBACK; Perspective. Sincerely, Joshua D. Drake - -- The PostgreSQL Company since 1997: http://www.commandprompt.com/ PostgreSQL Community Conference: http://www.postgresqlconference.org/ Donate to the PostgreSQL Project: http://www.postgresql.org/about/donate PostgreSQL SPI Liaison | SPI Director | PostgreSQL political pundit -----BEGIN PGP SIGNATURE----- Version: GnuPG v1.4.6 (GNU/Linux) iD8DBQFHxJSyATb/zqfZUUQRAnSPAJkB6Gz0gUTPohXcFak9LbVYIdxCtwCfWvxp gQZymMaKEXfo2Mf1E2BWtUk= =p+EO -----END PGP SIGNATURE-----
2008/2/27, Tom Lane <tgl@sss.pgh.pa.us>: > "Joshua D. Drake" <jd@commandprompt.com> writes: > > "Scott Marlowe" <scott.marlowe@gmail.com> wrote: > > >> begin; > >> drop index abc_dx; > >> select .... > >> rollback; > >> > >> and viola, your index is still there. note that there are likely some > >> locking issues with this, so be careful with it in production. But on > >> a test box it's a very easy way to test various indexes. > > > Wouldn't you also bloat the index? > > > No, what makes you think that? The index won't change at all in the > above example. The major problem is, as Scott says, that DROP INDEX > takes exclusive lock on the table so any other sessions will be locked > out of it for the duration of your test query. Why is the exclusive lock not taken later, so that this method can be used reasonably risk-free on production systems? From what I understand the later would be either a statement that would (potentially) be modifying the index, like an UPDATE or an INSERT, or actual transaction commit. If none of these occur and the transaction is rollbacked, the exclusive lock doesn't have to be taken at all. Markus -- Markus Bertheau Blog: http://www.bluetwanger.de/blog/
On Tue, Feb 26, 2008 at 8:48 PM, Markus Bertheau <mbertheau.pg@googlemail.com> wrote: > 2008/2/27, Tom Lane <tgl@sss.pgh.pa.us>: > > > > "Joshua D. Drake" <jd@commandprompt.com> writes: > > > "Scott Marlowe" <scott.marlowe@gmail.com> wrote: > > > > >> begin; > > >> drop index abc_dx; > > >> select .... > > >> rollback; > > >> > > >> and viola, your index is still there. note that there are likely some > > >> locking issues with this, so be careful with it in production. But on > > >> a test box it's a very easy way to test various indexes. > > > > > Wouldn't you also bloat the index? > > > > > > No, what makes you think that? The index won't change at all in the > > above example. The major problem is, as Scott says, that DROP INDEX > > takes exclusive lock on the table so any other sessions will be locked > > out of it for the duration of your test query. > > Why is the exclusive lock not taken later, so that this method can be > used reasonably risk-free on production systems? From what I > understand the later would be either a statement that would > (potentially) be modifying the index, like an UPDATE or an INSERT, or > actual transaction commit. If none of these occur and the transaction > is rollbacked, the exclusive lock doesn't have to be taken at all. It would rock to be able to do that on a production database. Any Oracle DBA looking over your shoulder would fall to the floor and need resuscitation.
"Markus Bertheau" <mbertheau.pg@googlemail.com> writes:
> 2008/2/27, Tom Lane <tgl@sss.pgh.pa.us>:
>> No, what makes you think that? The index won't change at all in the
>> above example. The major problem is, as Scott says, that DROP INDEX
>> takes exclusive lock on the table so any other sessions will be locked
>> out of it for the duration of your test query.
> Why is the exclusive lock not taken later, so that this method can be
> used reasonably risk-free on production systems?
Er, later than what? Once the DROP is pending, other transactions can
hardly safely use the index for lookups, and what should they do about
insertions?
regards, tom lane
On Tue, Feb 26, 2008 at 10:48 PM, Tom Lane <tgl@sss.pgh.pa.us> wrote: > "Markus Bertheau" <mbertheau.pg@googlemail.com> writes: > > 2008/2/27, Tom Lane <tgl@sss.pgh.pa.us>: > > >> No, what makes you think that? The index won't change at all in the > >> above example. The major problem is, as Scott says, that DROP INDEX > >> takes exclusive lock on the table so any other sessions will be locked > >> out of it for the duration of your test query. > > > Why is the exclusive lock not taken later, so that this method can be > > used reasonably risk-free on production systems? > > Er, later than what? Once the DROP is pending, other transactions can > hardly safely use the index for lookups, and what should they do about > insertions? I see what you're saying. Sadly, my dreams of drop index concurrently appear dashed.
"Markus Bertheau" <mbertheau.pg@googlemail.com> writes:
> On the other hand, if the only reason to have that feature is to plan
> and execute queries pretending that one index doesn't exist, then DROP
> INDEX DEFERRED is not the most straightforward syntax.
Yeah, I was just about to mention that 8.3 has a hook that allows a
plug-in to manipulate the planner's notions of which indexes exist.
Ignoring a specific index would be really trivial.
regards, tom lane
2008/2/27, Scott Marlowe <scott.marlowe@gmail.com>: > On Tue, Feb 26, 2008 at 10:48 PM, Tom Lane <tgl@sss.pgh.pa.us> wrote: > > "Markus Bertheau" <mbertheau.pg@googlemail.com> writes: > > > 2008/2/27, Tom Lane <tgl@sss.pgh.pa.us>: > > > > >> No, what makes you think that? The index won't change at all in the > > >> above example. The major problem is, as Scott says, that DROP INDEX > > >> takes exclusive lock on the table so any other sessions will be locked > > >> out of it for the duration of your test query. > > > > > Why is the exclusive lock not taken later, so that this method can be > > > used reasonably risk-free on production systems? > > > > Er, later than what? Once the DROP is pending, other transactions can > > hardly safely use the index for lookups, and what should they do about > > insertions? > > > I see what you're saying. Sadly, my dreams of drop index concurrently > appear dashed. Maybe a different syntax: DROP INDEX DEFERRED, which will make the current transaction behave as if the index was dropped but not actually drop it until the end of the transaction. Inserts and updates of this and other transactions behave as if the index existed. On the other hand, if the only reason to have that feature is to plan and execute queries pretending that one index doesn't exist, then DROP INDEX DEFERRED is not the most straightforward syntax. Markus -- Markus Bertheau Blog: http://www.bluetwanger.de/blog/
On Tue, 2008-02-26 at 17:22 -0500, Tom Lane wrote:
> "Joshua D. Drake" <jd@commandprompt.com> writes:
> > "Scott Marlowe" <scott.marlowe@gmail.com> wrote:
> >> begin;
> >> drop index abc_dx;
> >> select ....
> >> rollback;
> >>
> >> and viola, your index is still there. note that there are likely some
> >> locking issues with this, so be careful with it in production. But on
> >> a test box it's a very easy way to test various indexes.
>
> > Wouldn't you also bloat the index?
>
> No, what makes you think that? The index won't change at all in the
> above example. The major problem is, as Scott says, that DROP INDEX
> takes exclusive lock on the table so any other sessions will be locked
> out of it for the duration of your test query.
It may cause catalog bloat though, right?
Regards,
Jeff Davis
Jeff Davis <pgsql@j-davis.com> writes:
>>> begin;
>>> drop index abc_dx;
>>> select ....
>>> rollback;
> It may cause catalog bloat though, right?
Not in this particular case; AFAIR this will only result in catalog row
deletions, not updates. So when the deletions roll back, there's no
dead rows to clean up.
regards, tom lane
>>> On Tue, Feb 26, 2008 at 10:48 PM, in message <13604.1204087729@sss.pgh.pa.us>, Tom Lane <tgl@sss.pgh.pa.us> wrote: > Er, later than what? Once the DROP is pending, other transactions can > hardly safely use the index for lookups, and what should they do about > insertions? Out of curiosity, couldn't any transaction using a snapshot prior to the commit of the DROP continue to use it (just like an uncommited DELETE of a row)? The transaction doing the DROP wouldn't maintain it for modifications, which is fine whether it is committed or rolled back. There would just be the matter of "vacuuming" the index out of physical existence once all transactions which could see it are gone. That's probably naive, but I'm curious what I'm missing. -Kevin
"Kevin Grittner" <Kevin.Grittner@wicourts.gov> writes:
> Out of curiosity, couldn't any transaction using a snapshot prior to
> the commit of the DROP continue to use it (just like an uncommited
> DELETE of a row)? The transaction doing the DROP wouldn't maintain
> it for modifications, which is fine whether it is committed or
> rolled back. There would just be the matter of "vacuuming" the
> index out of physical existence once all transactions which could
> see it are gone.
You can't just lazily remove the index after the last xact stops using
it; there has to be an agreed synchronization point among all the
transactions. Otherwise you could have xact A expecting the index to
contain entries from the already-committed xact B, but B thought the
index was dead and didn't bother updating it.
We might be able to do something that would shorten the length of time
that the exclusive lock is held, but AFAICS we couldn't eliminate it
altogether; and I'm unconvinced that merely shortening the interval
is worth much extra complexity.
In the particular case at hand, a planner hook to make it ignore the
index is a far better solution anyway...
regards, tom lane
>>> On Wed, Feb 27, 2008 at 5:00 PM, in message <10385.1204153217@sss.pgh.pa.us>, Tom Lane <tgl@sss.pgh.pa.us> wrote: > "Kevin Grittner" <Kevin.Grittner@wicourts.gov> writes: >> Out of curiosity, couldn't any transaction using a snapshot prior to >> the commit of the DROP continue to use it (just like an uncommited >> DELETE of a row)? The transaction doing the DROP wouldn't maintain >> it for modifications, which is fine whether it is committed or >> rolled back. There would just be the matter of "vacuuming" the >> index out of physical existence once all transactions which could >> see it are gone. > > You can't just lazily remove the index after the last xact stops using > it; there has to be an agreed synchronization point among all the > transactions. Otherwise you could have xact A expecting the index to > contain entries from the already-committed xact B, but B thought the > index was dead and didn't bother updating it. If xact A is using a snapshot from before the commit of the index DROP, it shouldn't see anything done after the drop anyway. If it's using a snapshot from after the DROP, it won't see the index. xact B would only fail to update the index if it was using a snapshot after the drop, so I'm having trouble grasping the sequence of events where this is a problem. Could you outline the series of events where the problem occurs? > In the particular case at hand, a planner hook to make it ignore the > index is a far better solution anyway... Agreed -- I was just curious whether we could eliminate a source of blocking raised in the discussion; and failing that, improve my grasp of the PostgreSQL MVCC implementation. -Kevin
I wrote:
> In the particular case at hand, a planner hook to make it ignore the
> index is a far better solution anyway...
Just as proof of concept, a quick-and-dirty version of this is attached.
It works in 8.3 and up. Sample (after compiling the .so):
regression=# load '/home/tgl/pgsql/planignoreindex.so';
LOAD
regression=# explain select * from tenk1 where unique1 = 42;
QUERY PLAN
-----------------------------------------------------------------------------
Index Scan using tenk1_unique1 on tenk1 (cost=0.00..8.27 rows=1 width=244)
Index Cond: (unique1 = 42)
(2 rows)
regression=# set ignore_index TO 'tenk1_unique1';
SET
regression=# explain select * from tenk1 where unique1 = 42;
QUERY PLAN
---------------------------------------------------------
Seq Scan on tenk1 (cost=0.00..483.00 rows=1 width=244)
Filter: (unique1 = 42)
(2 rows)
regression=#
regards, tom lane
Вложения
"Kevin Grittner" <Kevin.Grittner@wicourts.gov> writes:
> If xact A is using a snapshot from before the commit of the index
> DROP, it shouldn't see anything done after the drop anyway. If
> it's using a snapshot from after the DROP, it won't see the index.
> xact B would only fail to update the index if it was using a
> snapshot after the drop, so I'm having trouble grasping the
> sequence of events where this is a problem. Could you outline
> the series of events where the problem occurs?
You're assuming that the query plan is as new as the snapshot is.
This isn't guaranteed, at least not without the locking that you
seek to eliminate.
regards, tom lane