Received: from localhost (maia-3.hub.org [200.46.204.184]) by postgresql.org (Postfix) with ESMTP id 7B1659FB2A6 for ; Fri, 20 Apr 2007 21:05:32 -0300 (ADT) Received: from postgresql.org ([200.46.204.71]) by localhost (mx1.hub.org [200.46.204.184]) (amavisd-maia, port 10024) with ESMTP id 06885-06 for ; Fri, 20 Apr 2007 21:05:23 -0300 (ADT) X-Greylist: from auto-whitelisted by SQLgrey-1.7.4 Received: from pillette.com (adsl-67-119-5-202.dsl.snfc21.pacbell.net [67.119.5.202]) by postgresql.org (Postfix) with ESMTP id 31A279FA29D for ; Fri, 20 Apr 2007 21:05:28 -0300 (ADT) Received: from dhcp202.pillette.com (dhcp202.pillette.com [192.168.1.202]) by pillette.com (8.11.6/8.11.6) with ESMTP id l3L05M412995; Fri, 20 Apr 2007 17:05:22 -0700 Date: Fri, 20 Apr 2007 17:28:20 -0700 From: Andrew Lazarus X-Mailer: The Bat! (v3.98.4) Home Organization: Pillette Investment Management X-Priority: 3 (Normal) Message-ID: <3810566934.20070420172820@pillette.com> To: Mark Kirkwood CC: pgsql-performance@postgresql.org Subject: Re: index structure for 114-dimension vector In-Reply-To: <46294FEB.9010909@paradise.net.nz> References: <01db01c781e4$c700bf30$2e00a8c0@tridecap.com> <1217328326.20070420120729@pillette.com> <1177105284.28383.31.camel@dogma.v10.wvs> <46294FEB.9010909@paradise.net.nz> MIME-Version: 1.0 Content-Type: multipart/mixed; boundary="----------91F25E3D532797" X-Virus-Scanned: Maia Mailguard 1.0.1 X-Archive-Number: 200704/353 X-Sequence-Number: 24230 ------------91F25E3D532797 Content-Type: text/plain; charset=us-ascii Content-Transfer-Encoding: 7bit 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)) MK> Cheers MK> Mark -- Sincerely, Andrew Lazarus mailto:andrew@pillette.com ------------91F25E3D532797 Content-Type: text/x-vcard; name="vCard.VCF" Content-transfer-encoding: quoted-printable Content-Disposition: attachment; filename="vCard.VCF" BEGIN:VCARD VERSION:2.1 N:Lazarus;Andrew;;;Ph.D. FN:Andrew Lazarus, Ph.D. EMAIL;PREF;INTERNET:andrew@pillette.com TITLE:Director of R&D ADR;WORK:;800-366-0688;3028 Fillmore Street;San Francisco;CA;94123;USA LABEL;WORK;ENCODING=3DQUOTED-PRINTABLE:800-366-0688=3D0D=3D0A3028 Fillmore = S=3D treet=3D0D=3D0ASan Francisco=3D0D=3D0ACA=3D0D=3D0A94123=3D0D=3D0AUSA X-GENDER:Male REV:18991230T080000Z END:VCARD ------------91F25E3D532797--