From: John Rouillard <rouilj@renesys.com>
To: Richard Neill <rn214@richardneill.org>
Cc: pgsql-performance@postgresql.org <pgsql-performance@postgresql.org>
Subject: Re: Why does the query planner use two full indexes, when a dedicated partial index exists? (solved?)
Date: Mon, 24 Dec 2012 20:42:52 +0000
Message-ID: <20121224204252.GG7039@renesys.com> (raw)
In-Reply-To: <50D8A0D7.2050005@richardneill.org>
References: <50D22DE2.4090301@richardneill.org>
<28778.1355972790@sss.pgh.pa.us>
<50D3CAC4.3070800@richardneill.org>
<50D3D484.6080100@richardneill.org>
<CAMkU=1zTU9RtyEAsAAfC5i0r6c-c=i-tVOPfF8J=Q-FgDQ31Qw@mail.gmail.com>
<50D8A0D7.2050005@richardneill.org>
List-Unsubscribe: <mailto:majordomo@postgresql.org?body=unsub%20pgsql-performance>
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
Reply instructions:
You may reply publicly to this message via plain-text email
using any one of the following methods:
* Reply to all the recipients using the --to and --cc options:
reply via email
To: pgsql-performance@postgresql.org
Cc: rouilj@renesys.com, rn214@richardneill.org
Subject: Re: Why does the query planner use two full indexes, when a dedicated partial index exists? (solved?)
In-Reply-To: <20121224204252.GG7039@renesys.com>
* Save the following mbox file, import it into your mail client,
and reply-to-all from there: mbox
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox