CODEWITHSUNDEEP
sqlpostgresql
Wells
Wells

Reputation: 10969

A quick aside on indexes & postgres

I've got these three here columns on a table: year, pid (integer), and code (varchar). Want to index each one. Which is better (I'm using postgres, but I'm curious about systems in general): go all CREATE INDEX myidx ON mytable USING btree (year, pid, code); or go btree individual index on each three columns?

What are the implications of these two approaches?

Upvotes: 3

Views: 169

Answers (2)

piotrp
piotrp

Reputation: 3854

Don't create indexes on random columns, always create them according to the way you use a table. I.e. if you will be using a query like SELECT ... WHERE year = 2011 AND pid = 2, create an index on (year, pid). An index allows you to execute some queries more efficiently, but it also takes up disk space and makes inserts slower.

You can get a general idea of what indexes are good by reading 3 ways MySQL uses indexes and Indexes in MySQL (two short blog posts explaining how MySQL handles indexes). Just remember that each DBMS will work a bit differently.

Which is better

The only valid answer is - it depends (on your usage scenarios, amount of stored data, column selectivity, ...).

Upvotes: 2

mkk
mkk

Reputation: 7693

The difference between them is as follows. Assume you have columns A, B, C and you put one complex index on them in exactly this order. now, when you do

 SELECT * from table where A = .. AND B = .. AND C =

then the index will be used and this is the most efficient usage of this index.

if you have query

SELECT * from table where A = ..

then the index will still be used. However, the idnex will not (updated thanks to comment: or only partially for 2nd and 3rd example) be used in:

SELECT * from table where B ..
SELECT * from table where A = .. AND C = .. AND B = ..
SELECT * from table where A = .. AND C = ..

however again will be used in:

SELECT * from table where A = .. AND B = ..

hope this helps. The order is the KEY here. And of course what you need. If you have three queries like

SELECT * ... where A
SELECT * ... where B
SELECT * ... where C

then of course make 3 indexes on single column. however if you have queries with AND, first make sure they have then same order and then go ahead and make 1 index for 3 columns

Upvotes: 2

Related Questions