X-Original-To: pgsql-performance-postgresql.org@localhost.postgresql.org Received: from localhost (mx1.hub.org [200.46.208.251]) by postgresql.org (Postfix) with ESMTP id 653959FB21A for ; Sun, 30 Apr 2006 21:36:30 -0300 (ADT) Received: from postgresql.org ([200.46.204.71]) by localhost (mx1.hub.org [200.46.208.251]) (amavisd-new, port 10024) with ESMTP id 07118-05 for ; Sun, 30 Apr 2006 21:36:16 -0300 (ADT) X-Greylist: from auto-whitelisted by SQLgrey- Received: from globalrelay.com (mail1.globalrelay.com [216.18.71.77]) by postgresql.org (Postfix) with ESMTP id BB5F09FB211 for ; Sun, 30 Apr 2006 21:36:14 -0300 (ADT) X-Virus-Scanned: Scanned by GRC-AntiVirus Gateway X-GR-Acctd: YES Received: from [67.90.96.2] (HELO DaveEMachine) by globalrelay.com (CommuniGate Pro SMTP 4.2.3) with ESMTP id 89033057; Sun, 30 Apr 2006 17:36:11 -0700 From: "Dave Dutcher" To: "'gulsah'" , Subject: Re: query performance question Date: Sun, 30 Apr 2006 19:36:11 -0500 Message-ID: <00a601c66cb7$3f2c9860$8300a8c0@tridecap.com> MIME-Version: 1.0 Content-Type: multipart/alternative; boundary="----=_NextPart_000_00A7_01C66C8D.56569060" X-Priority: 3 (Normal) X-MSMail-Priority: Normal X-Mailer: Microsoft Outlook, Build 10.0.2627 X-MimeOLE: Produced By Microsoft MimeOLE V6.00.2900.2869 In-Reply-To: <20060428113059.57107.qmail@web51009.mail.yahoo.com> Importance: Normal X-Virus-Scanned: Maia Mailguard 1.0.1 X-Archive-Number: 200604/703 X-Sequence-Number: 18787 This is a multi-part message in MIME format. ------=_NextPart_000_00A7_01C66C8D.56569060 Content-Type: text/plain; charset="iso-8859-1" Content-Transfer-Encoding: quoted-printable You are pulling a fair amount of data from the database and doing a lot of computation in the SQL. I'm not sure how fast this query could be expected to run, but I had one idea. If you've inserted and deleted a lot into this table, you will need to run vacuum ocasionally. If you haven't been doing that, I would try a VACUUM FULL ANALYZE on the table. (That will take a lock on the table and prevent clients from reading data while it is running.) =20 =20 -----Original Message----- From: pgsql-performance-owner@postgresql.org [mailto:pgsql-performance-owner@postgresql.org] On Behalf Of gulsah Sent: Friday, April 28, 2006 6:31 AM To: pgsql-performance@postgresql.org Subject: [PERFORM] query performance question Hi, I have a performance problem with Postgresql version 8.1 installed on a Fedora Core release 4 (Stentz) with kernel version 2.6.11. The machine I am working on has 512MB of RAM and Pentium III 800 MHz CPU. I have only one table in the database which consists of 256 columns and 10000 rows. Each column is of float type and each row corresponds to a vector in my application. What I want to do is to compute the distance between a predefined vector in hand and the ones in the database. The computation proceeds according to the following pseudocode: for(i=3D1; i<=3D256 ; i++){ distance +=3D abs(x1_i - x2_i); } where x1_i denotes the vector in hand's i coordinate and x2_i denotes the i coordinate of the vector in the database. The distance computation have to be done for all the vectors in the database by means of a query and the result set should be sorted in terms of the computed distances. When I implement the query and measure the time spent for it in an application I see that the query is handled in more than 8 seconds which is undesirable in my application. Here what I want to ask you all is that, is it a normal performance for a computer with the properties that I have mentioned above? Is there any solution in your mind to increase the performance of my query? To make it more undestandable, I should give the query for vectors with size 3, but in my case their size is 256. select id as vectorid, abs(40.9546-x2_1)+abs(-72.9964-x2_2)+abs(53.5348-x2_3) as distance from vectordb order by distance Thank you all for your help. - gulsah _____ =20 Talk is cheap. Use Yahoo! Messenger to make PC-to-Phone calls. Great rates starting at 1=A2/min. ------=_NextPart_000_00A7_01C66C8D.56569060 Content-Type: text/html; charset="iso-8859-1" Content-Transfer-Encoding: quoted-printable Message
You=20 are pulling a fair amount of data from the database and doing a lot of=20 computation in the SQL.  I'm not sure how fast this query could be = expected=20 to run, but I had one idea.  If you've=20 inserted and deleted a lot into this table, you will need to run vacuum=20 ocasionally.  If you haven't been doing that, I would try a VACUUM = FULL=20 ANALYZE on the table.  (That will take a lock on the table and = prevent=20 clients from reading data while it is running.)
 
 
-----Original Message-----
From:=20 pgsql-performance-owner@postgresql.org=20 [mailto:pgsql-performance-owner@postgresql.org] On Behalf Of=20 gulsah
Sent: Friday, April 28, 2006 6:31 = AM
To:=20 pgsql-performance@postgresql.org
Subject: [PERFORM] query=20 performance question

Hi,

I have a = performance=20 problem with Postgresql version 8.1 installed on a Fedora Core release = 4=20 (Stentz) with kernel version 2.6.11.

The machine I am working = on has=20 512MB of RAM and Pentium III 800 MHz CPU.

I have only one table = in the=20 database which consists of 256 columns and 10000 rows. Each column is = of float=20 type and each row corresponds to a vector in my application. What I = want to do=20 is to compute the distance between a predefined vector in hand and the = ones in=20 the database.

The computation proceeds according to the = following=20 pseudocode:

        = for(i=3D1;=20 i<=3D256 ;=20 = i++){
          &nbs= p;    =20 distance +=3D abs(x1_i - = x2_i);
       =20 }

where x1_i denotes the vector in hand's i coordinate and x2_i = denotes=20 the i
coordinate of the vector in the database.

The distance = computation have to be done for all the vectors in the database
by = means of=20 a query and the result set should be sorted in terms of = the
computed=20 distances.

When I implement the query and measure the time = spent for it=20 in an application
I see that the query is handled in more than 8 = seconds=20 which is undesirable in
my application.

Here what I want to = ask you=20 all is that, is it a normal performance for a
computer with the = properties=20 that I have mentioned above? Is there any solution
in your mind to = increase=20 the performance of my query?

To make it more undestandable, I = should=20 give the query for vectors with size
3, but in my case their size = is=20 256.

select
id as=20 vectorid,
abs(40.9546-x2_1)+abs(-72.9964-x2_2)+abs(53.5348-x2_3) as = distance
from vectordb
order by distance

Thank you all = for your=20 help.


-
gulsah

Talk is cheap. Use Yahoo! Messenger to make PC-to-Phone calls. Great=20 rates starting at 1=A2/min. ------=_NextPart_000_00A7_01C66C8D.56569060--