temporarily deactivate an index

On Sat, Jun 7, 2008 at 8:05 AM, Viktor Rosenfeld
<rosenfel@informatik.hu-berlin.de> wrote:

Hi,

I'm experimenting with different indexes to speed up my queries and I was
wondering if it is possible to temporarily deactivate an index, so it won't
be considered in the evaluation of query plans. The reason is that dropping
and then rebuilding an index takes up time which I would rather not wait.

I couldn't find a command to do that, but I'm guessing that it should be
possible by manipulating a structure in the catalog?

Try this:

begin;
drop indexname;
explain analyze select ...;
rollback;

Hi Scott,

Am 07.06.2008 um 16:53 schrieb Scott Marlowe:

I'm experimenting with different indexes to speed up my queries and
I was
wondering if it is possible to temporarily deactivate an index, so
it won't
be considered in the evaluation of query plans. The reason is that
dropping
and then rebuilding an index takes up time which I would rather not
wait.

I couldn't find a command to do that, but I'm guessing that it
should be
possible by manipulating a structure in the catalog?

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.

Cheers,
Viktor

On Sat, Jun 7, 2008 at 5:16 PM, Viktor Rosenfeld
<rosenfel@informatik.hu-berlin.de> wrote:

Hi Scott,

Am 07.06.2008 um 16:53 schrieb Scott Marlowe:

I'm experimenting with different indexes to speed up my queries and I was
wondering if it is possible to temporarily deactivate an index, so it
won't
be considered in the evaluation of query plans. The reason is that
dropping
and then rebuilding an index takes up time which I would rather not wait.

I couldn't find a command to do that, but I'm guessing that it should be
possible by manipulating a structure in the catalog?

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.

On Sun, Jun 8, 2008 at 1:34 AM, Scott Marlowe <scott.marlowe@gmail.com> wrote:

On Sat, Jun 7, 2008 at 5:16 PM, Viktor Rosenfeld
<rosenfel@informatik.hu-berlin.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

--
regards,
Jaime Casanova
Soporte y capacitación de PostgreSQL
Guayaquil - Ecuador
Cel. (593) 87171157

On Sun, Jun 8, 2008 at 7:55 AM, Jaime Casanova <systemguards@gmail.com> wrote:

On Sun, Jun 8, 2008 at 1:34 AM, Scott Marlowe <scott.marlowe@gmail.com> wrote:

On Sat, Jun 7, 2008 at 5:16 PM, Viktor Rosenfeld
<rosenfel@informatik.hu-berlin.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.

Viktor Rosenfeld <rosenfel@informatik.hu-berlin.de> writes:

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.

Well, you're going to have to change *something* on the client side,
assuming you want this behavioral change to affect only some queries.

Otherwise, in versions that have CREATE INDEX CONCURRENTLY you could
mark the index not ready to use yet (see pg_index.indisvalid or
pg_index.indisready depending on version).

Another line of attack, which I think only works in 8.3 and up,
is to make a planner plugin that disables specific indexes from
being considered. In fact I think I'd made a toy one of those
last year [ rummages... ] Yeah, here it is. Code attached.
No documentation, but basically you'd build it using pgxs and
then do
LOAD '$libdir/planignoreindex';
SET ignore_index = name-of-index;
Again this would be difficult to do without any client changes.

regards, tom lane

On Sun, Jun 8, 2008 at 11:45 AM, Tom Lane <tgl@sss.pgh.pa.us> wrote:

Viktor Rosenfeld <rosenfel@informatik.hu-berlin.de> writes:

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.

Well, you're going to have to change *something* on the client side,
assuming you want this behavioral change to affect only some queries.

Otherwise, in versions that have CREATE INDEX CONCURRENTLY you could
mark the index not ready to use yet (see pg_index.indisvalid or
pg_index.indisready depending on version).

if i mark the index not ready (using pg_index.indisvalid or
pg_index.indisready depending on version), will the index be updated
if in another transaction i make an insert?

--
regards,
Jaime Casanova
Soporte y capacitación de PostgreSQL
Guayaquil - Ecuador
Cel. (593) 87171157

Jaime Casanova escribi�:

if i mark the index not ready (using pg_index.indisvalid or
pg_index.indisready depending on version), will the index be updated
if in another transaction i make an insert?

Yes (in fact that's the whole point of having an index marked "not
valid/ready").

--
Alvaro Herrera http://www.CommandPrompt.com/
PostgreSQL Replication, Consulting, Custom Development, 24x7 support