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