pg.ddx.io pgsql-performance@postgresql.org mailing list archive
help / color / mirror / Atom feedNot Picking Index
10+ messages / 8 participants
[nested] [flat]
* Not Picking Index
@ 2007-02-16 12:56 Gauri Kanekar <meetgaurikanekar@gmail.com>
2007-02-16 14:28 ` Re: Not Picking Index Michael Fuhr <mike@fuhr.org>
0 siblings, 1 reply; 10+ messages in thread
From: Gauri Kanekar @ 2007-02-16 12:56 UTC (permalink / raw)
To: pgsql-performance
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
^ permalink raw reply [nested|flat] 10+ messages in thread
* Re: Not Picking Index
2007-02-16 12:56 Not Picking Index Gauri Kanekar <meetgaurikanekar@gmail.com>
@ 2007-02-16 14:28 ` Michael Fuhr <mike@fuhr.org>
2007-02-16 14:31 ` Re: Not Picking Index Gauri Kanekar <meetgaurikanekar@gmail.com>
0 siblings, 1 reply; 10+ messages in thread
From: Michael Fuhr @ 2007-02-16 14:28 UTC (permalink / raw)
To: Gauri Kanekar <meetgaurikanekar@gmail.com>; +Cc: pgsql-performance
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
^ permalink raw reply [nested|flat] 10+ messages in thread
* Re: Not Picking Index
2007-02-16 12:56 Not Picking Index Gauri Kanekar <meetgaurikanekar@gmail.com>
2007-02-16 14:28 ` Re: Not Picking Index Michael Fuhr <mike@fuhr.org>
@ 2007-02-16 14:31 ` Gauri Kanekar <meetgaurikanekar@gmail.com>
2007-02-16 14:46 ` Re: Not Picking Index Alvaro Herrera <alvherre@commandprompt.com>
2007-02-16 14:53 ` Re: Not Picking Index Tom Lane <tgl@sss.pgh.pa.us>
2007-02-16 18:27 ` Re: Not Picking Index Brad Nicholson <bnichols@ca.afilias.info>
0 siblings, 3 replies; 10+ messages in thread
From: Gauri Kanekar @ 2007-02-16 14:31 UTC (permalink / raw)
To: Michael Fuhr <mike@fuhr.org>; +Cc: pgsql-performance
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
^ permalink raw reply [nested|flat] 10+ messages in thread
* Re: Not Picking Index
2007-02-16 12:56 Not Picking Index Gauri Kanekar <meetgaurikanekar@gmail.com>
2007-02-16 14:28 ` Re: Not Picking Index Michael Fuhr <mike@fuhr.org>
2007-02-16 14:31 ` Re: Not Picking Index Gauri Kanekar <meetgaurikanekar@gmail.com>
@ 2007-02-16 14:46 ` Alvaro Herrera <alvherre@commandprompt.com>
2 siblings, 0 replies; 10+ messages in thread
From: Alvaro Herrera @ 2007-02-16 14:46 UTC (permalink / raw)
To: Gauri Kanekar <meetgaurikanekar@gmail.com>; +Cc: Michael Fuhr <mike@fuhr.org>; pgsql-performance
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.
^ permalink raw reply [nested|flat] 10+ messages in thread
* Re: Not Picking Index
2007-02-16 12:56 Not Picking Index Gauri Kanekar <meetgaurikanekar@gmail.com>
2007-02-16 14:28 ` Re: Not Picking Index Michael Fuhr <mike@fuhr.org>
2007-02-16 14:31 ` Re: Not Picking Index Gauri Kanekar <meetgaurikanekar@gmail.com>
@ 2007-02-16 14:53 ` Tom Lane <tgl@sss.pgh.pa.us>
2007-02-16 18:33 ` Re: Not Picking Index George Pavlov <gpavlov@mynewplace.com>
2 siblings, 1 reply; 10+ messages in thread
From: Tom Lane @ 2007-02-16 14:53 UTC (permalink / raw)
To: Gauri Kanekar <meetgaurikanekar@gmail.com>; +Cc: Michael Fuhr <mike@fuhr.org>; pgsql-performance
"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
^ permalink raw reply [nested|flat] 10+ messages in thread
* Re: Not Picking Index
2007-02-16 12:56 Not Picking Index Gauri Kanekar <meetgaurikanekar@gmail.com>
2007-02-16 14:28 ` Re: Not Picking Index Michael Fuhr <mike@fuhr.org>
2007-02-16 14:31 ` Re: Not Picking Index Gauri Kanekar <meetgaurikanekar@gmail.com>
2007-02-16 14:53 ` Re: Not Picking Index Tom Lane <tgl@sss.pgh.pa.us>
@ 2007-02-16 18:33 ` George Pavlov <gpavlov@mynewplace.com>
2007-02-16 19:27 ` Re: Not Picking Index Tom Lane <tgl@sss.pgh.pa.us>
0 siblings, 1 reply; 10+ messages in thread
From: George Pavlov @ 2007-02-16 18:33 UTC (permalink / raw)
To: Tom Lane <tgl@sss.pgh.pa.us>; pgsql-performance
> 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
^ permalink raw reply [nested|flat] 10+ messages in thread
* Re: Not Picking Index
2007-02-16 12:56 Not Picking Index Gauri Kanekar <meetgaurikanekar@gmail.com>
2007-02-16 14:28 ` Re: Not Picking Index Michael Fuhr <mike@fuhr.org>
2007-02-16 14:31 ` Re: Not Picking Index Gauri Kanekar <meetgaurikanekar@gmail.com>
2007-02-16 14:53 ` Re: Not Picking Index Tom Lane <tgl@sss.pgh.pa.us>
2007-02-16 18:33 ` Re: Not Picking Index George Pavlov <gpavlov@mynewplace.com>
@ 2007-02-16 19:27 ` Tom Lane <tgl@sss.pgh.pa.us>
0 siblings, 0 replies; 10+ messages in thread
From: Tom Lane @ 2007-02-16 19:27 UTC (permalink / raw)
To: George Pavlov <gpavlov@mynewplace.com>; +Cc: pgsql-performance
"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
^ permalink raw reply [nested|flat] 10+ messages in thread
* Re: Not Picking Index
2007-02-16 12:56 Not Picking Index Gauri Kanekar <meetgaurikanekar@gmail.com>
2007-02-16 14:28 ` Re: Not Picking Index Michael Fuhr <mike@fuhr.org>
2007-02-16 14:31 ` Re: Not Picking Index Gauri Kanekar <meetgaurikanekar@gmail.com>
@ 2007-02-16 18:27 ` Brad Nicholson <bnichols@ca.afilias.info>
2007-02-16 18:32 ` Re: Not Picking Index Steinar H. Gunderson <sgunderson@bigfoot.com>
2 siblings, 1 reply; 10+ messages in thread
From: Brad Nicholson @ 2007-02-16 18:27 UTC (permalink / raw)
To: Gauri Kanekar <meetgaurikanekar@gmail.com>; +Cc: Michael Fuhr <mike@fuhr.org>; pgsql-performance
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.
^ permalink raw reply [nested|flat] 10+ messages in thread
* Re: Not Picking Index
2007-02-16 12:56 Not Picking Index Gauri Kanekar <meetgaurikanekar@gmail.com>
2007-02-16 14:28 ` Re: Not Picking Index Michael Fuhr <mike@fuhr.org>
2007-02-16 14:31 ` Re: Not Picking Index Gauri Kanekar <meetgaurikanekar@gmail.com>
2007-02-16 18:27 ` Re: Not Picking Index Brad Nicholson <bnichols@ca.afilias.info>
@ 2007-02-16 18:32 ` Steinar H. Gunderson <sgunderson@bigfoot.com>
0 siblings, 0 replies; 10+ messages in thread
From: Steinar H. Gunderson @ 2007-02-16 18:32 UTC (permalink / raw)
To: pgsql-performance
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/
^ permalink raw reply [nested|flat] 10+ messages in thread
* Fwd: Not Picking Index
@ 2007-02-16 16:06 Mike Gargano <mrg@infoplusonline.com>
0 siblings, 0 replies; 10+ messages in thread
From: Mike Gargano @ 2007-02-16 16:06 UTC (permalink / raw)
To: pgsql-performance
> This is very similar to the problem I posted to this list
> yesterday. Apparently, if you append an empty string to the column
> data in your WHERE clause it will force the planer to treat it as a
> filter and not an index cond. It's extremely ugly, but this method
> doesn't seem to be anymore elegant.
>
> -Mike
> On Feb 16, 2007, at 9:46 AM, Alvaro Herrera wrote:
>
>> 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.
>>
>> ---------------------------(end of
>> broadcast)---------------------------
>> TIP 6: explain analyze is your friend
>>
>
^ permalink raw reply [nested|flat] 10+ messages in thread
end of thread, other threads:[~2007-02-16 19:27 UTC | newest]
Thread overview: 10+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2007-02-16 12:56 Not Picking Index Gauri Kanekar <meetgaurikanekar@gmail.com>
2007-02-16 14:28 ` Michael Fuhr <mike@fuhr.org>
2007-02-16 14:31 ` Gauri Kanekar <meetgaurikanekar@gmail.com>
2007-02-16 14:46 ` Alvaro Herrera <alvherre@commandprompt.com>
2007-02-16 14:53 ` Tom Lane <tgl@sss.pgh.pa.us>
2007-02-16 18:33 ` George Pavlov <gpavlov@mynewplace.com>
2007-02-16 19:27 ` Tom Lane <tgl@sss.pgh.pa.us>
2007-02-16 18:27 ` Brad Nicholson <bnichols@ca.afilias.info>
2007-02-16 18:32 ` Steinar H. Gunderson <sgunderson@bigfoot.com>
2007-02-16 16:06 Fwd: Not Picking Index Mike Gargano <mrg@infoplusonline.com>
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox