Specifying a index

[Marcio Caetano]

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.

[Tom Lane]

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

[Marcio Caetano]

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

[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