Received: from malur.postgresql.org ([217.196.149.56]) by arkaria.postgresql.org with esmtp (Exim 4.72) (envelope-from ) id 1TnErU-0005OK-En for pgsql-performance@arkaria.postgresql.org; Mon, 24 Dec 2012 20:43:00 +0000 Received: from localhost ([127.0.0.1] helo=postgresql.org) by malur.postgresql.org with smtp (Exim 4.72) (envelope-from ) id 1TnErT-0004HN-I7 for pgsql-performance@arkaria.postgresql.org; Mon, 24 Dec 2012 20:42:59 +0000 Received: from magus.postgresql.org ([87.238.57.229]) by malur.postgresql.org with esmtp (Exim 4.72) (envelope-from ) id 1TnErS-0004HH-HZ for pgsql-performance@postgresql.org; Mon, 24 Dec 2012 20:42:58 +0000 Received: from mailhub1.ep.renesys.com ([195.160.236.248]) by magus.postgresql.org with esmtp (Exim 4.72) (envelope-from ) id 1TnErP-0008Ri-7U for pgsql-performance@postgresql.org; Mon, 24 Dec 2012 20:42:57 +0000 Received: from ops05.psm1.renesys.com (ops05.psm1.renesys.com [10.200.1.25]) (using TLSv1 with cipher DHE-RSA-AES256-SHA (256/256 bits)) (No client certificate requested) by mailhub1.ep.renesys.com (Postfix) with ESMTP id 33A86671F0; Mon, 24 Dec 2012 20:42:53 +0000 (UTC) Received: (from rouilj@localhost) by ops05.psm1.renesys.com (8.13.8/8.13.8/Submit) id qBOKgqmT006522; Mon, 24 Dec 2012 20:42:52 GMT Date: Mon, 24 Dec 2012 20:42:52 +0000 From: John Rouillard To: Richard Neill Cc: "pgsql-performance@postgresql.org" Subject: Re: Why does the query planner use two full indexes, when a dedicated partial index exists? (solved?) Message-ID: <20121224204252.GG7039@renesys.com> References: <50D22DE2.4090301@richardneill.org> <28778.1355972790@sss.pgh.pa.us> <50D3CAC4.3070800@richardneill.org> <50D3D484.6080100@richardneill.org> <50D8A0D7.2050005@richardneill.org> MIME-Version: 1.0 Content-Type: text/plain; charset=us-ascii Content-Disposition: inline In-Reply-To: <50D8A0D7.2050005@richardneill.org> User-Agent: Mutt/1.5.20 (2009-06-14) X-Pg-Spam-Score: -1.9 (-) List-Archive: List-Help: List-ID: List-Owner: List-Post: List-Subscribe: List-Unsubscribe: X-Mailing-List: pgsql-performance Precedence: bulk Sender: pgsql-performance-owner@postgresql.org On Mon, Dec 24, 2012 at 06:37:11PM +0000, Richard Neill wrote: > [...] > So... problem solved for me: I just have to reindex every few hours. > BUT, this suggests a few remaining things: > [...] > 2. Is there any way to force the planner to use (or ignore) a > specific index, for testing purposes, short of actually dropping the > index? > This would be very useful for debugging, especially given that query > plans can only really be fully tested on production systems, and > that dropping indexes is rather a bad thing to do when live > operation is simultaneously happening on that server! I believe that: BEGIN; drop index .... explain analyze ... explain analyze ... ROLLBACK; will do what you want. IIUC Postgres allows DDL within transactions and thus be rolled back and the operations within the transaction aren't visible to your other queries running outside the transaction. http://wiki.postgresql.org/wiki/Transactional_DDL_in_PostgreSQL:_A_Competitive_Analysis and http://www.postgresql.org/docs/9.2/static/sql-dropindex.html -- -- rouilj John Rouillard System Administrator Renesys Corporation 603-244-9084 (cell) 603-643-9300 x 111 -- Sent via pgsql-performance mailing list (pgsql-performance@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-performance