Обсуждение: Two indexes on same column
Hi, what index is used (and according to what rules) when there are two (or more) different indexes defined on one column? Assume: CREATE TABLE example ( id SERIAL PRIMARY KEY, ...); CREATE INDEX example_id_idx ON example USING hash (id); By default there are btree index created and the hash index is then created. So there are two indexes on column "id". Are there described somewhere what index is used and when? Does it depend on query analyzer and planner? Thanks Vlastik
On Wed, Jul 20, 2011 at 2:39 PM, Vlastimil Krejcir <krejcir@ics.muni.cz> wrote:
Hi,
what index is used (and according to what rules) when there are two (or more) different indexes defined on one column? Assume:
CREATE TABLE example (
id SERIAL PRIMARY KEY,
...);
CREATE INDEX example_id_idx ON example USING hash (id);
By default there are btree index created and the hash index is then created. So there are two indexes on column "id". Are there described somewhere what index is used and when? Does it depend on query analyzer and planner?
Thanks
You are right, depends on the optimizer and query to which index to choose. EXPLAIN command on the query will give you the optimizer path.
---
Regards,
Raghavendra
EnterpriseDB Corporation
Hi, I was reading about indexes and I have a question: is possible to use "hash" like index method in primary key constraints?? thanks 2011/7/20 Raghavendra <raghavendra.rao@enterprisedb.com>: > > On Wed, Jul 20, 2011 at 2:39 PM, Vlastimil Krejcir <krejcir@ics.muni.cz> > wrote: >> >> Hi, >> >> what index is used (and according to what rules) when there are two (or >> more) different indexes defined on one column? Assume: >> >> CREATE TABLE example ( >> id SERIAL PRIMARY KEY, >> ...); >> CREATE INDEX example_id_idx ON example USING hash (id); >> >> By default there are btree index created and the hash index is then >> created. So there are two indexes on column "id". Are there described >> somewhere what index is used and when? Does it depend on query analyzer and >> planner? >> >> Thanks >> > > You are right, depends on the optimizer and query to which index to choose. > EXPLAIN command on the query will give you the optimizer path. > > --- > Regards, > Raghavendra > EnterpriseDB Corporation > Blog: http://raghavt.blogspot.com/ > > -- ---------------------------------------------------------- Visita : http://www.eqsoft.net ---------------------------------------------------------- Sigueme en Twitter : http://www.twitter.com/ernestoq