I'm approaching the end of my rope here. I have a large database.
250 million rows (ish). Each row has potentially about 500 pieces of
data, although most of the columns are sparsely populated.
What I'm trying to do is, essentially, search for sub-sets of that
data based on arbitrary queries of those data columns. the queries
would be relatively simple ("dirbtl is not null and qqrq between 20
and 40"). After the database is built, it is read only.
So, I started with maybe 10-15 fields in a main table, as most records
have values for those fields. Then had individual tables for the
other values. The idea is that the percentage of rows with values
drops off significantly after those main tables. That, an each
individual query looks at probably 3 or 4 fields in total. The
performance of those queries was pretty bad. Its got to join large
numbers of values, which didn't really work out well.
So, went the other direction completely. I rebuilt the database with
a much larger main table. Any values with 5% or greater filled in
rows were added to this table. Maybe 130 columns. Indexes applied to
most of these. Some limited testing with a smaller table seemed to
indicate that queries on a single table without a join would work much
faster.
So, built that huge table. now query time is terrible. Maybe a
minute or more for simple queries.
I'm running vacuum/analyze right now (which is also taking forever, BTW).
The box has 15 g of ram. I made the shared_buffers setting to 8 or 9
gig. My first question, what would be better to bump up to increase
the performance? I thought that was the field to jack up to improve
query time or index caching, but I've read conflicting data. The 15
ram is available.
I originally had this in mysql. Also bad performance. I understand
how to optimize that much better, but it just wasn't cutting it.
Anyway, help with tuning the settings would be greatly appreciated.
Advice on how best to lay this out would also be helpful (I know its
difficult without serious detail).
Thanks in advance,
-Kevin
