postgresql - How to determine what type of index to use in Postgres? -

i have postgres database has 2 columns not primary keys (nor can be), searched on lot , compared equality 2 columns in other tables.

i believe perfect case adding index tables. have never used indexing on database before trying learn proper way of doing this.

i have learned there multiple types of indexing can pick from. how determine method efficient database? proper method create single index covers both columns?

postgres support b-tree, r-tree, hash, gist , gin indexing types. b-tree indexing common , fits common scenarios. syntax:

create index idex_name on table_name using btree(column1, column2); 

here createindex documentation , here more info on different indextypes in postgres.

what type of index should use depends on types of operations want perform. if want equality checking hash index best. common operations(e.g. comparison, pattern matching) b-tree should used. have never used gist or gin indexing. guru out there?

the documentation describes these types. can better me :)

hope helps.