On Mon, Oct 15, 2012 at 5:59 PM, houmanb <hou...@gmx.at> wrote: > Dear all, > We have a DB containing transactional data. > There are about *50* to *100 x 10^6* rows in one *huge* table. > We are using postgres 9.1.6 on linux with a *SSD card on PCIex* providing > us > a constant seeking time. > > A typical select (see below) takes about 200 secs. As the database is the > backend for a web-based reporting facility 200 to 500 or even more secs > response times are not acceptable for the customer. > > Is there any way to speed up select statements like this: > > SELECT > SUM(T.x), > SUM(T.y), > SUM(T.z), > AVG(T.a), > AVG(T.b) > FROM T > GROUP BY > T.c > WHERE > T.creation_date=$SOME_DATE; > > There is an Index on T.c. But would it help to partition the table by T.c? > It should be mentioned, that T.c is actually a foreign key to a Table > containing a > tiny number of rows (15 rows representing different companies). >
How selective is T.creation_date? Looks like an index on this column would be better than T.c (could use also, of course), which would be also true for the partitioning - something like per month or per year partitioning. > my postgres.conf is actually the default one, despite the fact that we > increased the value for work_mem=128MB > > How much memory do you have? Could you increase shared_buffers? Also with a SSD you could decrease random_page_cost a little bit. See [1]. [1] http://wiki.postgresql.org/wiki/Tuning_Your_PostgreSQL_Server Regards. -- Matheus de Oliveira Analista de Banco de Dados PostgreSQL Dextra Sistemas - MPS.Br nível F! www.dextra.com.br/postgres
