Re: How does the planner deal with multiple possible indexes?

From: Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)us>
To: "Jim C(dot) Nasby" <jnasby(at)pervasive(dot)com>
Cc: Gregory Maxwell <gmaxwell(at)gmail(dot)com>, pgsql-hackers(at)postgresql(dot)org
Subject: Re: How does the planner deal with multiple possible indexes?
Date: 2006-07-21 13:55:31
Message-ID: 22031.1153490131@sss.pgh.pa.us
Views: Raw Message | Whole Thread | Download mbox | Resend email
Thread:
Lists: pgsql-hackers

"Jim C. Nasby" <jnasby(at)pervasive(dot)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

In response to

Browse pgsql-hackers by date

  From Date Subject
Next Message Jim C. Nasby 2006-07-21 13:56:53 Re: [HACKERS] Sun Donated a Sun Fire T2000 to the PostgreSQL
Previous Message Hannu Krosing 2006-07-21 13:52:44 Re: How does the planner deal with multiple possible