Re: [PERFORM] disabling an index without deleting it?
On Tue, 2008-02-26 at 17:22 -0500, Tom Lane wrote: Joshua D. Drake [EMAIL PROTECTED] writes: Scott Marlowe [EMAIL PROTECTED] 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 ---(end of broadcast)--- TIP 7: You can help support the PostgreSQL project by donating at http://www.postgresql.org/about/donate
Re: [PERFORM] disabling an index without deleting it?
Jeff Davis [EMAIL PROTECTED] 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 ---(end of broadcast)--- TIP 1: if posting/reading through Usenet, please send an appropriate subscribe-nomail command to [EMAIL PROTECTED] so that your message can get through to the mailing list cleanly
Re: [PERFORM] disabling an index without deleting it?
On Tue, Feb 26, 2008 at 10:48 PM, in message [EMAIL PROTECTED], Tom Lane [EMAIL PROTECTED] 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 ---(end of broadcast)--- TIP 1: if posting/reading through Usenet, please send an appropriate subscribe-nomail command to [EMAIL PROTECTED] so that your message can get through to the mailing list cleanly
Re: [PERFORM] disabling an index without deleting it?
Kevin Grittner [EMAIL PROTECTED] 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 ---(end of broadcast)--- TIP 4: Have you searched our list archives? http://archives.postgresql.org
Re: [PERFORM] disabling an index without deleting it?
On Wed, Feb 27, 2008 at 5:00 PM, in message [EMAIL PROTECTED], Tom Lane [EMAIL PROTECTED] wrote: Kevin Grittner [EMAIL PROTECTED] 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 ---(end of broadcast)--- TIP 9: In versions below 8.0, the planner will ignore your desire to choose an index scan if your joining column's datatypes do not match
Re: [PERFORM] disabling an index without deleting it?
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 binYjGH7NXzJe.bin Description: planignoreindex.c ---(end of broadcast)--- TIP 5: don't forget to increase your free space map settings
Re: [PERFORM] disabling an index without deleting it?
Kevin Grittner [EMAIL PROTECTED] 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 ---(end of broadcast)--- TIP 2: Don't 'kill -9' the postmaster
Re: [PERFORM] disabling an index without deleting it?
On Tue, Feb 26, 2008 at 2:46 PM, Peter Koczan [EMAIL PROTECTED] 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. ---(end of broadcast)--- TIP 7: You can help support the PostgreSQL project by donating at http://www.postgresql.org/about/donate
Re: [PERFORM] disabling an index without deleting it?
-BEGIN PGP SIGNED MESSAGE- Hash: SHA1 On Tue, 26 Feb 2008 14:57:51 -0600 Scott Marlowe [EMAIL PROTECTED] 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- ---(end of broadcast)--- TIP 7: You can help support the PostgreSQL project by donating at http://www.postgresql.org/about/donate
Re: [PERFORM] disabling an index without deleting it?
-BEGIN PGP SIGNED MESSAGE- Hash: SHA1 On Tue, 26 Feb 2008 17:22:40 -0500 Tom Lane [EMAIL PROTECTED] wrote: Joshua D. Drake [EMAIL PROTECTED] writes: Scott Marlowe [EMAIL PROTECTED] 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- ---(end of broadcast)--- TIP 7: You can help support the PostgreSQL project by donating at http://www.postgresql.org/about/donate
Re: [PERFORM] disabling an index without deleting it?
2008/2/27, Tom Lane [EMAIL PROTECTED]: Joshua D. Drake [EMAIL PROTECTED] writes: Scott Marlowe [EMAIL PROTECTED] 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/ ---(end of broadcast)--- TIP 2: Don't 'kill -9' the postmaster
Re: [PERFORM] disabling an index without deleting it?
On Tue, Feb 26, 2008 at 8:48 PM, Markus Bertheau [EMAIL PROTECTED] wrote: 2008/2/27, Tom Lane [EMAIL PROTECTED]: Joshua D. Drake [EMAIL PROTECTED] writes: Scott Marlowe [EMAIL PROTECTED] 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. ---(end of broadcast)--- TIP 9: In versions below 8.0, the planner will ignore your desire to choose an index scan if your joining column's datatypes do not match
Re: [PERFORM] disabling an index without deleting it?
Markus Bertheau [EMAIL PROTECTED] writes: 2008/2/27, Tom Lane [EMAIL PROTECTED]: 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 ---(end of broadcast)--- TIP 1: if posting/reading through Usenet, please send an appropriate subscribe-nomail command to [EMAIL PROTECTED] so that your message can get through to the mailing list cleanly
Re: [PERFORM] disabling an index without deleting it?
On Tue, Feb 26, 2008 at 10:48 PM, Tom Lane [EMAIL PROTECTED] wrote: Markus Bertheau [EMAIL PROTECTED] writes: 2008/2/27, Tom Lane [EMAIL PROTECTED]: 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. ---(end of broadcast)--- TIP 1: if posting/reading through Usenet, please send an appropriate subscribe-nomail command to [EMAIL PROTECTED] so that your message can get through to the mailing list cleanly
Re: [PERFORM] disabling an index without deleting it?
Markus Bertheau [EMAIL PROTECTED] 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 ---(end of broadcast)--- TIP 5: don't forget to increase your free space map settings
Re: [PERFORM] disabling an index without deleting it?
2008/2/27, Scott Marlowe [EMAIL PROTECTED]: On Tue, Feb 26, 2008 at 10:48 PM, Tom Lane [EMAIL PROTECTED] wrote: Markus Bertheau [EMAIL PROTECTED] writes: 2008/2/27, Tom Lane [EMAIL PROTECTED]: 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/ ---(end of broadcast)--- TIP 3: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faq
