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
