[postgis-users] CPU tuning

Mon Jan 9 04:56:13 PST 2017

Hi,

Thanks for your answer and this way of calculation. I don't know which machine you have but on mine, Option 1 launched from pgadmin takes 2,564,423 ms  (about 45 mn) a bit less than my original code (2,823,272 ms). So if this takes less than 1 second on yours, there is definitively a problem on my configuration or my machine which is nevertheless a very slow one : Hp Envy (Windows 10, i7-6500 CPU 2,5 Ghz 8go). I'm using postgresql/postgis 9.4 with its standard configuration.

During the option 1 query, the processor used again 30/35% CPU during all time. Difference is about memory, it grows up to 1Gb (shared buffer is set to this value) and then begin using disk about 10 Mo/s.  So I'm still wondering why the cpu doesn't go further than 30% and why this take such a difference on your machine. Is it a linux OS ?

Olivier

De : postgis-users [mailto:postgis-users-bounces at lists.osgeo.org] De la part de Rémi Cura
Envoyé : lundi 9 janvier 2017 10:52
À : PostGIS Users Discussion
Objet : Re: [postgis-users] CPU tuning

Hey,

I

'm afraid you may not use the most efficient approach.

Assuming you want to find for each node of table x the closest node of table y,

it takes less than 1 second on my computer.

DROP TABLE  IF EXISTS  test_1; 
CREATE TABLE test_1 AS
SELECT s AS gid, ST_makePoint(random()*1000,random()*1000) AS geom
FROM generate_series(1,12000) AS s ;
CREATE INDEX ON test_1 USING GIST(geom) ; 
ALTER TABLE test_1 ADD PRIMARY KEY (gid) ; 

DROP TABLE  IF EXISTS  test_2; 
CREATE TABLE test_2 AS
SELECT s AS gid, ST_makePoint( random()*1000,random()*1000) AS geom
FROM generate_series(1,50000) AS s ;
CREATE INDEX ON test_2 USING GIST(geom) ; 
ALTER TABLE test_2 ADD PRIMARY KEY (gid) ;  

-- option 1 : you have lots of ram : brute force
DROP TABLE IF EXISTS test_results ;
CREATE TABLE test_results AS 
SELECT DISTINCT ON (test_1.gid ) test_1.gid AS gid1, test_2.gid AS gid2-- , ST_MakeLine(test_1.geom,test_2.geom) AS sline
FROM test_1, test_2
ORDER BY test_1.gid ,ST_Distance(test_1.geom,test_2.geom) ASC ;

--option2  : you have limited ram : snipping : force index use

DROP FUNCTION IF EXISTS test_sdist(geom1 geometry) ; 
CREATE OR REPLACE FUNCTION test_sdist(geom1 geometry, OUT gid2 bigint, OUT geom2 geometry) AS
 $$
    --given a point, find the closes one in test_2
    DECLARE 
    BEGIN 
        SELECT gid, geom INTO gid2, geom2
        FROM test_2
        ORDER BY ST_Distance(geom2,test_2.geom) ASC
        LIMIT 1  ;
        RETURN ; 
    END;
$$ LANGUAGE plpgsql; 

DROP TABLE IF EXISTS test_result_2 ;
CREATE TABLE IF NOT EXISTS test_result_2 AS 
SELECT test_1.gid AS gid1, f.gid2 AS gid2
FROM test_1, test_sdist(test_1.geom) AS f 

--option 3 : you could use a recursive CTE to avoid creating a dedicated dummy function, 

--           this would be a pain to write though

Cheers,

Rémi-C

2017-01-08 16:36 GMT+01:00 Olivier Leprêtre <o.lepretre at gmail.com>:

Hi,

I have a pgsql postgis function that last about an hour on an Hp Envy (W10, i7-6500 CPU 2,5 Ghz 8go). This function is calculating minimum distance between each nodes of table x (12000 nodes) and table y (42000 nodes)

FOR row IN 

                   SELECT code,ST_AsEwkt(ST_StartPoint(geom)) as geom1  FROM t_x

LOOP

                   FOR row1 IN

                            SELECT code, ST_Distance(ST_AsEwkt(geom)::text,row.geom1) FROM t_y order by ST_Distance(ST_AsEwkt(geom)::text,row.geom1) LIMIT 1

                   LOOP

                   END LOOP;

....

What I don't understand is that postgresql server process CPU is stable during all that time at 30/31% with no other process running. Details of each processor 1 to 3 shows that all 4 uses 30%. Disk and network usage are 0% (database is local) Ram usage is 47% and Postgresql server uses process only 7,4 Mo.

My question is "Why postgres process uses such a precise and constant cpu and doesn't grow up to more than 30% ?" I didn't find any parameter forcing such a limit in order to reduce duration of this calculation.

Thanks for any answer,

Olivier

  _____  

 <https://www.avast.com/sig-email?utm_medium=email&utm_source=link&utm_campaign=sig-email&utm_content=emailclient> Avast logo

L'absence de virus dans ce courrier électronique a été vérifiée par le logiciel antivirus Avast. 
www.avast.com <https://www.avast.com/sig-email?utm_medium=email&utm_source=link&utm_campaign=sig-email&utm_content=emailclient>  

_______________________________________________
postgis-users mailing list
 <mailto:postgis-users at lists.osgeo.org> postgis-users at lists.osgeo.org
 <http://lists.osgeo.org/mailman/listinfo/postgis-users> http://lists.osgeo.org/mailman/listinfo/postgis-users

---
L'absence de virus dans ce courrier électronique a été vérifiée par le logiciel antivirus Avast.
https://www.avast.com/antivirus
-------------- next part --------------
An HTML attachment was scrubbed...
URL: <http://lists.osgeo.org/pipermail/postgis-users/attachments/20170109/20f01136/attachment.html>
-------------- next part --------------
A non-text attachment was scrubbed...
Name: image001.png
Type: image/png
Size: 1588 bytes
Desc: not available
URL: <http://lists.osgeo.org/pipermail/postgis-users/attachments/20170109/20f01136/attachment.png>