Date: Sat, 21 Apr 2007 12:42:35 +1200
From: Mark Kirkwood <markir@paradise.net.nz>
Subject: Re: index structure for 114-dimension vector
In-reply-to: <3810566934.20070420172820@pillette.com>
To: Andrew Lazarus <andrew@pillette.com>
Cc: pgsql-performance@postgresql.org
Message-id: <46295DFB.7020607@paradise.net.nz>
MIME-version: 1.0
Content-type: text/plain; charset=ISO-8859-1; format=flowed
Content-transfer-encoding: 7bit
References: <b42b73150704170917n33ebcdc8wc852a3fa02fd8bb5@mail.gmail.com>
	<01db01c781e4$c700bf30$2e00a8c0@tridecap.com>
	<b42b73150704181716wb1600aey8761b6793d3fd702@mail.gmail.com>
	<1217328326.20070420120729@pillette.com>
	<1177105284.28383.31.camel@dogma.v10.wvs>
	<46294FEB.9010909@paradise.net.nz>
	<3810566934.20070420172820@pillette.com>
User-Agent: Thunderbird 1.5.0.10 (X11/20070313)

Andrew Lazarus wrote:
> Because I know the 25 closest are going to be fairly close in each
> coordinate, I did try a multicolumn index on the last 6 columns and
> used a +/- 0.1 or 0.2 tolerance on each. (The 25 best are very probably inside
> that hypercube on the distribution of data in question.)
> 
> This hypercube tended to have 10-20K records, and took at least 4
> seconds to retrieve. I was a little surprised by how long that took.
> So I'm wondering if my data representation is off the wall.
> 
> I should mention I also tried a cube index using gist on all 114
> elements, but CREATE INDEX hadn't finished in 36 hours, when I killed
> it, and I wasn't in retrospect sure an index that took something like
> 6GB by itself would be helpful on a 2GB of RAM box.
> 
> MK> I don't think that will work for the vector norm i.e:
> 
> MK> |x - y| = sqrt(sum over j ((x[j] - y[j])^2))
> 
> 

Sorry, in that case it probably *is* worth trying out 6 single column 
indexes and seeing if they get bitmap and'ed together...

Mark