Re: temporarily deactivate an index

On Sun, Jun 8, 2008 at 7:55 AM, Jaime Casanova <systemguards(at)gmail(dot)com> wrote:
> On Sun, Jun 8, 2008 at 1:34 AM, Scott Marlowe <scott(dot)marlowe(at)gmail(dot)com> wrote:
>> On Sat, Jun 7, 2008 at 5:16 PM, Viktor Rosenfeld
>> <rosenfel(at)informatik(dot)hu-berlin(dot)de> wrote:
>>>>
>>>> Try this:
>>>>
>>>> begin;
>>>> drop indexname;
>>>> explain analyze select ...;
>>>> rollback;
>>>
>>> That works, but I'm still looking for another way to deactivate the index.
>>> The reason being, that my query load is randomly generated by a Java
>>> program and I don't want to go and change the SQL compiler.
>>
>> Sorry, I'm out of ideas. I mean, you can turn off all indexes with
>> set enable_indexscan=off but there's no other way to do it cheaply and
>> in such a fine grained way.
>>
>
> with "enable_indexscan=off" you're not turning indexes off but putting
> a high cost in using them... the effect, most of the time, is that
> indexes will not be used but you can't be sure...
>
> the better solution was the first one: dropping the index inside a
> transaction, execute the explain analyze of the query and rollback the
> transaction

True. Also there's been some talk lately about how to add query
planner controls to postgresql to control the planner. This being an
open source project, I think it's pretty reasonable to put the onus of
that kind of thing on the user community to take a first whack at
before someone spends a lot of time instrumenting it in full in the
core.