12

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

3
  • 3
    do you have any more information about the filters—ie are all columns equally likely to be used? and are range/equality and 'not null' filters equally likely? Oct 31, 2012 at 13:31
  • 4
    also, have you considered capturing the actual queries run and indexing accordingly? Oct 31, 2012 at 13:36
  • It turned out that PostgreSQL is mostly using only one/two index at a time, that reduces the set to a fairly small table already and then uses seqential scan. e.g. Queries with 6 variables, usually only use one index (in the above described scenario).
    – alfonx
    Nov 16, 2015 at 21:48

2 Answers 2

7

The answer is (almost always) no.

Each index you create means more disk space used, longer maintenance windows, and a higher cost if the table which is never supposed to change (save for being replaced once a year) actually does get changed. They increase the size of your backups and make fitting into your disaster recovery window that much harder. Also, many of those indexes probably will never get used, so they're just dead weight.

Then, you have to think about multi-column indexes. In both of your examples, you have two columns. If you only index single columns, no single non-clustered index may be good enough and so the query would just go back to the clustered index. Then there are the three-column indexes, four-column indexes, etc. etc.

The better bet is to determine which queries are common and start indexing those. I'm not that familiar with postgresql internals, but there are some links floating around which can help set up statistics gathering. Once you get a fairly solid idea of which queries users are actually using, you can create indexes which can help. Even then, this isn't a perfect strategy: you're (metaphorically) building roads where people are walking, but the people creating those paths have to slog through it until you can find out where they're going and build the road to help. This is a reactionary process, but it probably would catch a fairly large percentage of common uses.

Because this is a read-only database, however, could reasonably safely create subsets of common data which fit a large percentage of user requests (either through separate tables or creating materialized views). You can also get away with more non-clustered indexes than you would want to for a typical OLTP table. There are certainly marginal costs to indexes, but they are lower when you don't need to worry too much about insertions, updates, and deletions. And if a large number of queries are built around date or some other similar field, you could possibly partition the table on that column.

2
7

When you say "for performance" you're taking a narrow view - "performance" as SELECT performance only, one query at a time, without considering caching and I/O contention issues.

Yes, having an index on every column might improve read performance. It'll certainly slow write performance (INSERT, UPDATE and DELETE), though that's not a concern for your DB. More subtly, more indexes means more contention for space in RAM to cache the indexes, which means there's a greater chance that a scan of any given index will result in slow disk reads.

Then there's the fact that you can index more than just individual columns. It's often most productive to create multicolumn, partial, expression and/or descending indexes to best satisfy the needs of your queries. You simply cannot create every possible index. Here's a recent example of a real world index you'd never create without analysis of the queries that required it:

CREATE INDEX contfloattable_tag_and_timeseg
ON contfloattable(tagindex, (floor(extract(epoch FROM dateandtime) / 60 / 15) ));

So: you could create an index on every column, but it's a bad idea. Use EXPLAIN AND EXPLAIN ANALYZE, possibly via the auto_explain module, to analyse your workload and queries, and make indexing decisons based on how you actually query the tables. An index you don't use will hurt you in a read/write environment, and won't do any good in a read-only environment.

Don't view queries in isolation. Look at patterns. If query1 seems to want an index on col1 and query2 seems to want an index on both col1 and col2, don't bother creating an index on just col1. Create a composite index on col1, col2; it'll be almost as fast for a search on col1 alone and way better than maintaining two indexes and having them fight over cache space and disk I/O.

BTW, this sounds like it's more of an OLAP workload than an OLTP one, so you might want to consider schemas designed for OLAP workloads like star schema with fact and dimension tables.

3
  • The write performance does not seem to be an issue because the OP stated: "The table does not change at all" and "I replace it once a year".
    – user1822
    Oct 31, 2012 at 14:13
  • @a_horse_with_no_name Agreed, but I'm also aware that others reading this thread won't necessarily consider that - say, when they find it in searches about indexing strategies. Oct 31, 2012 at 14:24
  • Yes, its more a OLAP workload. Thanks for all the considerations.
    – alfonx
    Oct 31, 2012 at 19:42

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.