Big array speed issues

From: "Merkel Marcel (CR/AEM4)" <Marcel(dot)Merkel(at)de(dot)bosch(dot)com>
To: <pgsql-performance(at)postgresql(dot)org>
Subject: Big array speed issues
Date: 2006-06-20 09:35:17
Message-ID: EA04C6394A303A4CADF281DE22F232A41D4357@si-mail48.de.bosch.com
Views: Raw Message | Whole Thread | Download mbox | Resend email
Thread:
Lists: pgsql-performance

Hi,

I have some speed issues with a big array in a table. I hope you can
help me to tune my query.

My table looks like this:

Id | timestamp | map
Primary key | timestamp | array of real [34][28]

With an index on timestamp

My query is the following:

Select map[1,1], map[1,2] .... Map[34,28] from table where timestamp > x
and timestamp < y order by timestamp

Expected return is about 5000 rows of the table. I have to run this
query multiple times with different x and y values

The table is huge (about 60000 entries) but will get even much more
bigger.

The query takes ages on a 3.GhZ Xeon processor with 2 GB RAM. I'm using
postgresql 7.4 .

Any hints how I can speedup this ? (use postgres 8.1, change table
setup, query one row or column of the array )

I use libpqxx to access the database. This might be another bottleneck,
but I assume my query and table setup is the bigger bottleneck. Would it
make sense to fetch the whole array ? (Select map from table where ...
and parse the array manually)

Thanks for your help.

Marcel

Responses

Browse pgsql-performance by date

  From Date Subject
Next Message biuro 2006-06-20 12:39:32 Curson prbolem
Previous Message Luke Lonergan 2006-06-20 01:24:02 Re: Some performance numbers, with thoughts