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 curio开发者_运维技巧us 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?

---

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

---

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