From: | "Dave Dutcher" <dave(at)tridecap(dot)com> |
---|---|
To: | "'gulsah'" <gulsah_129(at)yahoo(dot)com>, <pgsql-performance(at)postgresql(dot)org> |
Subject: | Re: query performance question |
Date: | 2006-05-01 00:36:11 |
Message-ID: | 00a601c66cb7$3f2c9860$8300a8c0@tridecap.com |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Thread: | |
Lists: | pgsql-performance |
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.)
-----Original Message-----
From: pgsql-performance-owner(at)postgresql(dot)org
[mailto:pgsql-performance-owner(at)postgresql(dot)org] On Behalf Of gulsah
Sent: Friday, April 28, 2006 6:31 AM
To: pgsql-performance(at)postgresql(dot)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=1; i<=256 ; i++){
distance += 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
_____
Talk is cheap. Use Yahoo! Messenger to make PC-to-Phone calls. Great
<http://us.rd.yahoo.com/mail_us/taglines/postman7/*http://us.rd.yahoo.co
m/evt=39666/*http://messenger.yahoo.com> rates starting at 1¢/min.
From | Date | Subject | |
---|---|---|---|
Next Message | Scott Sipe | 2006-05-01 08:05:54 | Super-smack? |
Previous Message | Eric Lam | 2006-04-30 23:37:25 | Re: Slow restoration question |