Обсуждение: Not Picking Index
Hi List,
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.
--
Regards
Gauri
Regards
Gauri
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
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.
On 2/16/07, Michael Fuhr <mike@fuhr.org> wrote:
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
--
Regards
Gauri
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