> We might even consider taking experts advice on how to tune queries and
> server, but if postgres is going to behave like this, I am not sure we would
> be able to continue with it.
>
> Having said that, I would day again that I am completely new to this
> territory, so I might miss lots and lots of thing.
My two cents: Postgres out of the box might not be a good choice for
data warehouse style queries, that is because it is optimized to run
thousands of small queries (OLTP style processing) and not one big
monolithic query. I've faced similar problems myself before and here
are few tricks i followed to get my elephant do real time adhoc
analysis on a table with ~45 columns and few billion rows in it.
1. Partition your table! use constraint exclusion to the fullest extent
2. Fire multiple small queries distributed over partitions and
aggregate them at the application layer. This is needed because, you
might to exploit all your cores to the fullest extent (Assuming that
you've enough memory for effective FS cache). If your dataset goes
beyond the capability of a single system, try something like Stado
(GridSQL)
3. Storing index on a RAM / faster disk disk (using tablespaces) and
using it properly makes the system blazing fast. CAUTION: This
requires some other infrastructure setup for backup and recovery
4. If you're accessing a small set of columns in a big table and if
you feel compressing the data helps a lot, give this FDW a try -
https://github.com/citusdata/cstore_fdw