Which index can i use ?

[Abandoned]

Hi..
I want to do index in postgresql & python.
My table:
id(int) | id2(int) | w(int) | d(int)

My query:
select id, w where id=x and id2=y (sometimes and d=z)

I have too many insert and select operation on this table.
And which index type can i use ? Btree, Rtree, Gist or Hash ?
Also I want to unique (id, id2)..
Now this is my index. is it give me good performance ?
CREATE UNIQUE INDEX ind1 ON test USING btree (id, id2)
CREATE INDEX ind2 ON test USING btree (id)
CREATE INDEX ind3 ON test USING btree (id2)
CREATE INDEX ind4 ON test USING btree (w)
CREATE INDEX ind5 ON test USING btree (d)

I'm too sorry my bad english.
King regards..

[Marc 'BlackJack' Rintsch]

On Sun, 28 Oct 2007 06:00:19 -0700, Abandoned wrote:

Hi..
I want to do index in postgresql & python.
My table:
id(int) | id2(int) | w(int) | d(int)

My query:
select id, w where id=x and id2=y (sometimes and d=z)

I have too many insert and select operation on this table.
And which index type can i use ? Btree, Rtree, Gist or Hash ?
Also I want to unique (id, id2)..
Now this is my index. is it give me good performance ?
CREATE UNIQUE INDEX ind1 ON test USING btree (id, id2)
CREATE INDEX ind2 ON test USING btree (id)
CREATE INDEX ind3 ON test USING btree (id2)
CREATE INDEX ind4 ON test USING btree (w)
CREATE INDEX ind5 ON test USING btree (d)

This isn't a Python question. You'll get more and probably better
feedback in a group, mailing list or forum dealing with PostgreSQL.

Ciao,
Marc 'BlackJack' Rintsch

[Gerardo Herzig]

Abandoned wrote:

>Hi..
I want to do index in postgresql & python.
My table:
id(int) | id2(int) | w(int) | d(int)

My query:
select id, w where id=x and id2=y (sometimes and d=z)

I have too many insert and select operation on this table.
And which index type can i use ? Btree, Rtree, Gist or Hash ?
Also I want to unique (id, id2)..
Now this is my index. is it give me good performance ?
CREATE UNIQUE INDEX ind1 ON test USING btree (id, id2)
CREATE INDEX ind2 ON test USING btree (id)
CREATE INDEX ind3 ON test USING btree (id2)
CREATE INDEX ind4 ON test USING btree (w)
CREATE INDEX ind5 ON test USING btree (d)

I'm too sorry my bad english.
King regards..

Well, this is no python-related at all. But, for a start, if you are not
using the 'w' field in the WHERE clause, the ind4 index is not necesary
at all. Having more indexes that you need is a waste of space and
perfomance. It also depends on the amount of records your table has.
-Hash indexes are discouraged
-GiST indexes are for more advanced uses than equality comparisons
-Rtree are used in multidimentional indexing

So, keep the default btree. Do the test with the diff indexes if you
want. You may use EXPLAIN for viewing the index usage.
And dont forget about VACUUM too!

Cheers.
Gerardo