X-Original-To: pgsql-performance-postgresql.org@localhost.postgresql.org Received: from localhost (unknown [200.46.204.144]) by svr1.postgresql.org (Postfix) with ESMTP id 0D4FF528A8 for ; Tue, 21 Jun 2005 18:20:30 -0300 (ADT) Received: from svr1.postgresql.org ([200.46.204.71]) by localhost (av.hub.org [200.46.204.144]) (amavisd-new, port 10024) with ESMTP id 11128-02 for ; Tue, 21 Jun 2005 21:20:20 +0000 (GMT) Received: from sss.pgh.pa.us (sss.pgh.pa.us [66.207.139.130]) by svr1.postgresql.org (Postfix) with ESMTP id 3464852822 for ; Tue, 21 Jun 2005 18:20:19 -0300 (ADT) Received: from sss2.sss.pgh.pa.us (tgl@localhost [127.0.0.1]) by sss.pgh.pa.us (8.13.1/8.13.1) with ESMTP id j5LLK7hg017469; Tue, 21 Jun 2005 17:20:07 -0400 (EDT) To: Tobias Brox Cc: John A Meinel , Yves Vindevogel , pgsql-performance@postgresql.org Subject: Re: Limit clause not using index In-reply-to: <20050621194639.GG22746@oppetid.no> References: <42B8275C.8040300@arbash-meinel.com> <7e514564b9a159480857de53cc1764fd@implements.be> <42B82ED0.1010009@arbash-meinel.com> <20050621194639.GG22746@oppetid.no> Comments: In-reply-to Tobias Brox message dated "Tue, 21 Jun 2005 21:46:39 +0200" Date: Tue, 21 Jun 2005 17:20:07 -0400 Message-ID: <17468.1119388807@sss.pgh.pa.us> From: Tom Lane X-Virus-Scanned: by amavisd-new at hub.org X-Spam-Status: No, hits=0.006 tagged_above=0 required=5 tests=AWL X-Spam-Level: X-Archive-Number: 200506/413 X-Sequence-Number: 13050 Tobias Brox writes: > [John A Meinel - Tue at 10:14:24AM -0500] >> I believe if you drop the indexes inside a transaction, they will still >> be there for other queries, and if you rollback instead of commit, you >> won't lose anything. > Has anyone tested this? Certainly. Bear in mind though that DROP INDEX will acquire exclusive lock on the index's table, so until you roll back, no other transaction will be able to touch the table at all. So the whole thing may be a nonstarter in a production database anyway :-(. You can probably get away with BEGIN; DROP INDEX ... EXPLAIN ... ROLLBACK; if you fire it from a script rather than by hand --- but EXPLAIN ANALYZE might be a bad idea ... regards, tom lane