Обсуждение: Not Picking Index

On Fri, Feb 16, 2007 at 06:26:51PM +0530, Gauri Kanekar wrote:
> I want to run a Select Query on a table. But i dont want the query to pick a
> index defined on that table.
>
> So can i instruct the planner not to pick that index.

Why don't you want the planner to use the index?  Is there a specific
index you want to ignore or do you want the planner to ignore all
indexes?  What problem are you trying to solve?

--
Michael Fuhr

Gauri Kanekar escribió:
> I want the planner to ignore a specific index.
> I am testing some query output. For that purpose i dont want the index.
> I that possible to ignore a index by the planner.

Sure:

BEGIN
DROP INDEX foo
SELECT ....
ROLLBACK

--
Alvaro Herrera                                http://www.CommandPrompt.com/
The PostgreSQL Company - Command Prompt, Inc.

"Gauri Kanekar" <meetgaurikanekar@gmail.com> writes:
> I want the planner to ignore a specific index.
> I am testing some query output. For that purpose i dont want the index.
> I that possible to ignore a index by the planner.

    begin;
    drop index soandso;
    explain analyze ...;
    rollback;

Note the DROP INDEX will acquire exclusive lock on the table, so this
might not be the greatest thing to do in a production environment.
In PG 8.2 and up there is a sneakier way to do it that won't acquire
any more lock than the statement-under-test does:

    begin;
    update pg_index set indisvalid = false
      where indexrelid = 'soandso'::regclass;
    explain analyze ...;
    rollback;

            regards, tom lane

On Fri, 2007-02-16 at 20:01 +0530, Gauri Kanekar wrote:
>
> I want the planner to ignore a specific index.
> I am testing some query output. For that purpose i dont want the
> index.
> I that possible to ignore a index by the planner.

If the indexed field is an intger, add 0 to it.


--
Brad Nicholson  416-673-4106
Database Administrator, Afilias Canada Corp.

On Fri, Feb 16, 2007 at 01:27:46PM -0500, Brad Nicholson wrote:
> If the indexed field is an intger, add 0 to it.

Won't that also invalidate the statistics?

/* Steinar */
--
Homepage: http://www.sesse.net/

> Note the DROP INDEX will acquire exclusive lock on the table, so this
> might not be the greatest thing to do in a production environment.
> In PG 8.2 and up there is a sneakier way to do it that won't acquire
> any more lock than the statement-under-test does:
>
>     begin;
>     update pg_index set indisvalid = false
>       where indexrelid = 'soandso'::regclass;
>     explain analyze ...;
>     rollback;

this really smacks of that four-letter word that starts with h... -- i
am glad we have finally come around on the subject :-)

seriously, this is a great technique and an enormous time saver during
query optimization. thanks for sharing!

george

"George Pavlov" <gpavlov@mynewplace.com> writes:
>> In PG 8.2 and up there is a sneakier way to do it that won't acquire
>> any more lock than the statement-under-test does:
>>
>> begin;
>> update pg_index set indisvalid = false
>> where indexrelid = 'soandso'::regclass;
>> explain analyze ...;
>> rollback;

> this really smacks of that four-letter word that starts with h... -- i
> am glad we have finally come around on the subject :-)

indisvalid isn't a hint; it was necessary to make CREATE INDEX CONCURRENTLY
work.  But if you want to (mis?)use it as a hint, you can ...

            regards, tom lane