CODEWITHSUNDEEP
databasepostgresqlindexing
Xeoncross
Xeoncross

Reputation: 57184

How do you define indexes normally in PostgreSQL (coming from MySQL)

Like everything else, it seems PostgreSQL has much more powerful/complex indexing for tables. Perhaps someone can help me to know the default way to index columns.

By default I mean integer/boolean columns which are used 90% of the time to filter table results.

In MySQL I would simply create an index on a column with the same name as that column. I'm not sure what type was used (btree?) or what the implications of naming the index the same thing as the column were - but it worked.

Now moving to PostgreSQL I'm wondering if there is any problem naming the index with the same name (or any reason not too). Also, I'm wondering which type of index should be used for int/bool values.

Upvotes: 2

Views: 1853

Answers (2)

Scott Marlowe
Scott Marlowe

Reputation: 8860

If you want to index booleans use a conditional index. Most boolean values are either evenly distruted, in which case an index on just boole gains you almost nothing, since you'll be reading half one way or the other. But, in instances where you have a boole where 99.999% of the rows are one value and the other 0.001% are the other value, creating an index on JUST that 0.001% makes sense:

create index mostly_true on tablename (somefieldIwantwhenboolfieldisfalse)
  where boolfield is false;

Note that this also works for multicolumn indexes.

create index mostly_true on tablename (col1,col2) where boolfield is false;

Upvotes: 6

Anomie
Anomie

Reputation: 94794

For the most part the default index type (btree) will be fine.

The default index name (if you don't specify one in the CREATE INDEX statement) is based on the table and column name, typically something like table_column_idx. IIRC index names must be unique within a schema, so if you name your indexes with the same name as the columns you may run into trouble if the same column name is used in more than one table in the schema.

Upvotes: 6

Related Questions