Skip site navigation (1) Skip section navigation (2)

index structure for 114-dimension vector

From: Andrew Lazarus <andrew(at)pillette(dot)com>
To: pgsql-performance(at)postgresql(dot)org
Subject: index structure for 114-dimension vector
Date: 2007-04-20 19:07:29
Message-ID: (view raw, whole thread or download thread mbox)
Lists: pgsql-performance
I have a table with 2.5 million real[] arrays. (They are points in a
time series.) Given a new array X, I'd like to find, say, the 25
closest to X in some sense--for simplification, let's just say in the
usual vector norm. Speed is critical here, and everything I have tried
has been too slow.

I imported the cube contrib package, and I tried creating an index on
a cube of the last 6 elements, which are the most important. Then I
tested the 2.5MM rows for being contained within a tolerance of the
last 6 elements of X, +/- 0.1 in each coordinate, figuring that would
be an indexed search (which I CLUSTERED on). I then ran the sort on
this smaller set. The index was used, but it was still too slow. I
also tried creating new columns with rounded int2 values of the last 6
coordinates and made a multicolumn index.

For each X the search is taking about 4-15 seconds which is above my
target at least one order of magnitude. Absolute numbers are dependent
on my hardware and settings, and some of this can be addressed with
configuration tweaks, etc., but first I think I need to know the
optimum data structure/indexing strategy.

Is anyone on the list experienced with this sort of issue?

Andrew Lazarus  andrew(at)pillette(dot)com

In response to


pgsql-performance by date

Next:From: Jeff DavisDate: 2007-04-20 21:41:24
Subject: Re: index structure for 114-dimension vector
Previous:From: chrisjDate: 2007-04-20 14:23:58
Subject: seeking advise on char vs text or varchar in search table

Privacy Policy | About PostgreSQL
Copyright © 1996-2017 The PostgreSQL Global Development Group