I use PostgreSQL 9.2. I have a table with ~5 million rows and 150 columns. The table does not change at all (I replace it once a year). Users query this table with all kinds of filters on any some of the columns, e.g.
select * from table where C > 43 and H is not null;
select * from table where A is null and F < 10 and F > 1 and X > 2;
For performance I plan to create an index on every column of the table. Some feeling in my stomach tells me to ask the experts first: Is it good design for the above described use case to create an index on every column?
UPDATE: I have to speculate about real use cases. I can't measure the exact queries yet. This is in design phase.
The server is well equipped with RAM and SSD storage, so queries are already "fast" now, and I can feel the effect of caching when I fire similar queries in sequence.
The columns are of types double, integer, timestamp and geometry (which explicitly gets a 'gist' index).
The queries will include from 1 to 10 columns. Usually ~6. Results will usually be <20k rows. Queries on a column will never relate to another column.
Thanks for all the explanations. What I will do: * select 1/4th of the columns that I think will be most used and create indexes. * wait for more testing/usage and start measuring/analysing the queries and use-cases then.
Thank you