By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
448,561 Members | 1,243 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 448,561 IT Pros & Developers. It's quick & easy.

Specifying a index

P: n/a
Hello !

Consider the examples bellow:

1- I create a table named TBA with columns ( A int , B char(10) )
2- I create an B-Tree index named IDXA using the column A
3- I create an GIST (or other) index named IDXB using the column A

So, I ask you:

Is it possible specify which index (IDXA or IDXB) my SELECT will use in a query ?
If so, how ?

Thanks in advance.

Marcio Caetano.
Nov 22 '05 #1
Share this Question
Share on Google+
3 Replies


P: n/a
li**********@yahoo.com.br (Marcio Caetano) writes:
1- I create a table named TBA with columns ( A int , B char(10) )
2- I create an B-Tree index named IDXA using the column A
3- I create an GIST (or other) index named IDXB using the column A Is it possible specify which index (IDXA or IDXB) my SELECT will use in a query ?


No, not directly, but why would you create duplicate indexes? It would
be reasonable to have two indexes on the same column if they supported
different operator classes, but in that case the operator used in a
particular query would determine which index is applicable.

regards, tom lane

---------------------------(end of broadcast)---------------------------
TIP 8: explain analyze is your friend

Nov 22 '05 #2

P: n/a
Tom

In fact I'm creating two indexes (with different methods) in the same
column in order to analyze and register its performance. I need to
know wich one will be better when this table store more than
20.000.000 records.

What did you mean when you said "No, not directly" ? Is there an
indirect way of specifying a index ?

Regards,

Márcio Caetano.

tg*@sss.pgh.pa.us (Tom Lane) wrote in message news:<28*************@sss.pgh.pa.us>...
li**********@yahoo.com.br (Marcio Caetano) writes:
1- I create a table named TBA with columns ( A int , B char(10) )
2- I create an B-Tree index named IDXA using the column A
3- I create an GIST (or other) index named IDXB using the column A

Is it possible specify which index (IDXA or IDXB) my SELECT will use in a query ?


No, not directly, but why would you create duplicate indexes? It would
be reasonable to have two indexes on the same column if they supported
different operator classes, but in that case the operator used in a
particular query would determine which index is applicable.

regards, tom lane

---------------------------(end of broadcast)---------------------------
TIP 8: explain analyze is your friend

Nov 22 '05 #3

P: n/a
Ben
On Tue, 03 Feb 2004 07:04:05 -0800, Marcio Caetano wrote:
Consider the examples bellow:

1- I create a table named TBA with columns ( A int , B char(10) )
2- I create an B-Tree index named IDXA using the column A
3- I create an GIST (or other) index named IDXB using the column A

So, I ask you:

Is it possible specify which index (IDXA or IDXB) my SELECT will
use in a query ?
If so, how ?


Why not:

1 - Create the table
2 - Create index of type A
3 - ANALYZE the table so the DB knows the index
4 - Time the operations of interest
5 - Drop the index
5 - Create index of type B
6 - ANALYZE the table again
7 - Time the results

Tom, wouldn't that seem to do what he wants?

???

--Ben

Nov 22 '05 #4

This discussion thread is closed

Replies have been disabled for this discussion.