"Jim C. Nasby" <jnasby@pervasive.com> writes:
> No, this is 8.1.3, and it's a production machine so I'd prefer not to go
> about dropping indexes to get cost comparisons; unless there's some way
> to disable the use of an index in a given backend?
The traditional hack for that is
begin; drop index foo; explain whatever; rollback;
The DROP acquires an exclusive lock on the table, but it's only held for
a very short time while you EXPLAIN (you might want to put the whole
thing in a script file instead of relying on human typing speed). So
unless you've got seriously strict response time requirements, this is
generally OK even in production DBs. You do have to watch out for long
running transactions holding non-exclusive locks, eg don't try this
while a VACUUM is running on the table --- else the DROP blocks on the
vacuum and all other accesses start to queue up behind the DROP.
If the online-index-build patch gets in, there will be a cleaner option
which is to just mark the index disabled in pg_index. That doesn't
require any exclusive lock, indeed won't be visible to other backends at
all if you do it within a transaction as above.
regards, tom lane
