Re: Which index can i use ?

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)..

OK, well a UNIQUE constraint automatically gives you an index, so
(id,id2) are already indexed.

If you add your own indexes, just use btree (or don't specify anything
and let PostgreSQL choose btree for you). The others are all for
specialised uses.

> 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 is a lot of indexes. Every index will slow down inserts and updates
(the index will need to be updated).

So - start with the unique index and see how that works for you. If you
find problems with some queries look at using EXPLAIN ANALYZE to see the
plan your slow query is using, then we can try again.

Oh, and make sure autovacuum is running to keep the planner informed of
changes in your database.

HTH

--
Richard Huxton
Archonet Ltd