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 -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general