Re: query performance question

[Date Prev][Date Next][Thread Prev][Thread Next][Date Index][Thread Index]

 



Title: Message
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@xxxxxxxxxxxxxx [mailto:pgsql-performance-owner@xxxxxxxxxxxxxx] On Behalf Of gulsah
Sent: Friday, April 28, 2006 6:31 AM
To: pgsql-performance@xxxxxxxxxxxxxx
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 rates starting at 1¢/min.

[Postgresql General]     [Postgresql PHP]     [PHP Users]     [PHP Home]     [PHP on Windows]     [Kernel Newbies]     [PHP Classes]     [PHP Books]     [PHP Databases]     [Yosemite]

  Powered by Linux