Received: from malur.postgresql.org ([217.196.149.56]) by arkaria.postgresql.org with esmtp (Exim 4.80) (envelope-from ) id 1XAzV7-0003ep-FD for pgsql-performance@arkaria.postgresql.org; Sat, 26 Jul 2014 10:46:53 +0000 Received: from localhost ([127.0.0.1] helo=postgresql.org) by malur.postgresql.org with smtp (Exim 4.80) (envelope-from ) id 1XAzV6-0007K8-Gn for pgsql-performance@arkaria.postgresql.org; Sat, 26 Jul 2014 10:46:52 +0000 Received: from makus.postgresql.org ([2001:4800:1501:1::229]) by malur.postgresql.org with esmtps (TLS1.2:DHE_RSA_AES_256_CBC_SHA256:256) (Exim 4.80) (envelope-from ) id 1XAzV3-0007Jz-KQ for pgsql-performance@postgresql.org; Sat, 26 Jul 2014 10:46:50 +0000 Received: from mail-wg0-x233.google.com ([2a00:1450:400c:c00::233]) by makus.postgresql.org with esmtps (TLS1.0:RSA_AES_256_CBC_SHA1:256) (Exim 4.80) (envelope-from ) id 1XAzUt-0003hx-Rh for pgsql-performance@postgresql.org; Sat, 26 Jul 2014 10:46:47 +0000 Received: by mail-wg0-f51.google.com with SMTP id b13so5132935wgh.10 for ; Sat, 26 Jul 2014 03:46:36 -0700 (PDT) DKIM-Signature: v=1; a=rsa-sha256; c=relaxed/relaxed; d=gmail.com; s=20120113; h=from:to:subject:date:message-id:mime-version:content-type :thread-index:content-language; bh=gM/0fqZANBjebN45Rk8qLvcK8qfQ5qFDz0XHRSrxYog=; b=WbyCVZ+Zvviyzv3eXiEBTxvLSj1x+EqIoxxB79xi3TagpI0q5ULhwpyY9zLT7Ua5aH mgGni2hNiN9+GYyWUNqmnUrAPRuKGe1CUa15ZDNFq+Lr5V3GnppsLAKTAZo9O922am9k ZXtEPBn3Uxq3XJVxFZi/QNGQi4e/DmoP5LXjk565yxwc6zqD5cJdJeQbkRtr22fTHQwJ cqVYmxU92gwjHS8LrfXGjgKU6mJBYLYDjmghSM5GoO1ykaDSefSQ1fs8Vvt5JNOJU5Ib XKoXNQ667SNbjdx96gH3GVNP0X7e7F07buxy0kBQkK9+IbcgGRTaU+khdBrHokfjqkhV 3jvg== X-Received: by 10.180.211.237 with SMTP id nf13mr12771950wic.47.1406371596660; Sat, 26 Jul 2014 03:46:36 -0700 (PDT) Received: from Astartes (ip-213-220-249-54.net.upcbroadband.cz. [213.220.249.54]) by mx.google.com with ESMTPSA id sa4sm32757003wjb.45.2014.07.26.03.46.35 for (version=TLSv1 cipher=ECDHE-RSA-AES128-SHA bits=128/128); Sat, 26 Jul 2014 03:46:35 -0700 (PDT) From: =?iso-8859-2?B?Smn47SBO4WR2b3Ju7Ws=?= To: Subject: Cursor + upsert (astronomical data) Date: Sat, 26 Jul 2014 12:46:31 +0200 Message-ID: <01d201cfa8be$dd746880$985d3980$@gmail.com> MIME-Version: 1.0 Content-Type: multipart/alternative; boundary="----=_NextPart_000_01D3_01CFA8CF.A1017E40" X-Mailer: Microsoft Outlook 14.0 Thread-Index: Ac+ouETVGsmXaHmqRQOol9ZAJnjkAw== Content-Language: cs X-Pg-Spam-Score: -2.0 (--) List-Archive: List-Help: List-ID: List-Owner: List-Post: List-Subscribe: List-Unsubscribe: X-Mailing-List: pgsql-performance Precedence: bulk Sender: pgsql-performance-owner@postgresql.org This is a multipart message in MIME format. ------=_NextPart_000_01D3_01CFA8CF.A1017E40 Content-Type: text/plain; charset="iso-8859-2" Content-Transfer-Encoding: 7bit Hello guys. My issue kind of hits multiple topics, but the main question is about performance. I think you need to understand the background a little bit to be able to help me. So I will firstly define the problem and my solutions to it and place the questions for you to the end of this message. Problem: I have a table of observations of objects on the sky. The most important columns are the coordinates (x,y). All other columns in there are just additional information about the observation. The problem is that when I take an image of the same object on the sky twice, the coordinates x,y won't be the same, they will be only close to each other. My task is to generate a common identifier to all of the observations of the same object and assign the observations to it (N:1 relation). The criterium is that all of the observations which are within 1 arcsec of this identifier are considered as the same object. I keep the identifiers in a separate table (objcat) and have a foreign key in the observations table. The reason why I solve the performance issues here is that the table of observations has atm cca 3e8 rows after 1.5 year of gathering the data. The number growth is linear. Technical background: I'm trying to keep the whole algoritm in DB if possible because I have a good PostgreSQL plugin Q3C for indexing the coordinates of the objects on the sky (https://code.google.com/p/q3c/source/browse/README.q3c). It also has quite a few stored procedures to look in that table for near neighbors which is what I'm doing. The function q3c_join(x1, x2, y1, y2, radius) returns true if the object y is within radius of the object x. It simply generates a list of index bitmap or queries with the operators <=, >= which define the position on sky. Asking for the nearest neighbors are then only index scans. Solution: After lot of experimentation with clustering the objects and trying to process them all together in one "silver-bullet" SQL query I decided to use some more simple approach. The main problem with the "process all at once approach" is that the finding neighbours for each observation is in definition quadratic and for 3e8 rows just runs out of disk space (~TBs of memory for the temporary results). The simplest approach I could think of is that I process each row of the 3e8 rows sequentially and ask: Do I have an identifier in the radius of 1 arcsec? No: Generate one and assign me to it. Yes: Update it and assigne me to it. The update is done as weighted average - I keep the number of how many observations the identifier has been computed. The result is that the identifier will have average coordinates of all the observations it identifies - it will be the center. So here I come with my procedure. It has 3 params. The first two are range of oids to list in the table. Used for scaling and parallelization of the algorithm. The third is the radius in which to search for the neighbours. DROP TYPE IF EXISTS coords; CREATE TYPE coords AS ( raj2000 double precision, dej2000 double precision ); DROP FUNCTION IF EXISTS build_catalog(int,int,double precision); CREATE OR REPLACE FUNCTION build_catalog (fromOID int, toOID int, radius double precision) RETURNS VOID AS $$ DECLARE cur1 CURSOR FOR SELECT raj2000, dej2000 FROM \schema.observation AS obs WHERE obs.oid >= fromOID AND obs.oid < toOID; curr_raj2000 double precision; curr_dej2000 double precision; curr_coords_cat coords; cnt int; BEGIN /*SELECT current_setting('transaction_isolation') into tmp; raise notice 'Isolation level %', tmp;*/ OPEN cur1; cnt:=0; LOCK TABLE \schema.objcat IN SHARE ROW EXCLUSIVE MODE; LOOP FETCH cur1 INTO curr_raj2000, curr_dej2000; EXIT WHEN NOT found; WITH upsert AS (UPDATE \schema.objcat SET ipix_cat=q3c_ang2ipix( (raj2000 * weight + curr_raj2000) / (weight + 1), (dej2000 * weight + curr_dej2000) / (weight + 1) ), raj2000 = (raj2000 * weight + curr_raj2000) / (weight + 1), dej2000 = (dej2000 * weight + curr_dej2000) / (weight + 1), weight=weight+1 WHERE q3c_join(curr_raj2000, curr_dej2000, raj2000, dej2000, radius) RETURNING *), ins AS (INSERT INTO \schema.objcat (ipix_cat, raj2000, dej2000, weight) SELECT (q3c_ang2ipix(curr_raj2000, curr_dej2000)), curr_raj2000, curr_dej2000, 1 WHERE NOT EXISTS (SELECT * FROM upsert) RETURNING *) UPDATE \schema.observation SET id_cat = (SELECT DISTINCT id_cat FROM upsert UNION SELECT id_cat FROM ins WHERE id_cat IS NOT NULL LIMIT 1) WHERE CURRENT OF cur1; cnt:=cnt+1; IF ((cnt % 100000 ) = 0) THEN RAISE NOTICE 'Processed % entries', cnt; END IF; END LOOP; CLOSE cur1; END; $$ LANGUAGE plpgsql; Results: When I run the query only once (1 client thread), it runs cca 1 mil rows per hour. Which is days for the whole dataset. When I run it in parallel with that lock to ensure pessimistic synchronization, it runs sequentially too J the other threads just waiting. When I delete that lock and hope to solve the resulting conflicts later, the ssd disk serves up to 4 threads relatively effectively - which can divide my days of time by 4 - still inacceptable. The reason is quite clear here - I'm trying to write something in one cycle of the script to a table - then in the following cycle I need to read that information. Questions for you: 1. The first question is if you can think of a better way how to do this and maybe if SQL is even capable of doing such thing - or do I have to do it in C? Would rewriting the SQL function to C help? 2. Could I somehow bend the commiting during the algorithm for my thing? Ensure that inside one cycle, the whole part of the identifiers table would be kept in memory for faster lookups? 3. Why is this so slow? J It is comparable to the quadratic algorithm in the terms of speed - only does not use any memory. I tried to sum this up the best I could - for more information please don't hesitate to contact me. Thank you very much for even reading this far. Best Regards, Jiri Nadvornik ------=_NextPart_000_01D3_01CFA8CF.A1017E40 Content-Type: text/html; charset="iso-8859-2" Content-Transfer-Encoding: quoted-printable

Hello guys.

 

My issue kind of hits multiple topics, but the main = question is about performance. I think you need to understand the = background a little bit to be able to help me. So I will firstly define = the problem and my solutions to it and place the questions for you to = the end of this message.

 

Problem:

I have a table of observations of objects on the sky. The = most important columns are the coordinates (x,y). All other columns in = there are just additional information about the observation. The problem = is that when I take an image of the same object on the sky twice, the = coordinates x,y won’t be the same, they will be only close to each = other. My task is to generate a common identifier to all of the = observations of the same object and assign the observations to it (N:1 = relation). The criterium is that all of the observations which are = within 1 arcsec of this identifier are considered as the same object. I = keep the identifiers in a separate table (objcat) and have a foreign key = in the observations table.

The reason why I solve the = performance issues here is that the table of observations has atm cca = 3e8 rows after 1.5 year of gathering the data. The number growth is = linear.

 

Technical background:

I’m trying to keep the whole = algoritm in DB if possible because I have a good PostgreSQL plugin Q3C = for indexing the coordinates of the objects on the sky (https://c= ode.google.com/p/q3c/source/browse/README.q3c). It also has quite a = few stored procedures to look in that table for near neighbors which is = what I’m doing. The function q3c_join(x1, x2, y1, y2, radius) = returns true if the object y is within radius of the object x. It simply = generates a list of index bitmap or queries with the operators <=3D, = >=3D which define the position on sky. Asking for the nearest = neighbors are then only index scans.

 

Solution:

After lot of experimentation with = clustering the objects and trying to process them all together in one = “silver-bullet” SQL query I decided to use some more simple = approach. The main problem with the “process all at once = approach” is that the finding neighbours for each observation is = in definition quadratic and for 3e8 rows just runs out of disk space = (~TBs of memory for the temporary results).

The simplest approach I could think = of is that I process each row of the 3e8 rows sequentially and = ask:

Do I = have an identifier in the radius of 1 arcsec?

No: Generate one and assign me to = it.

Yes: = Update it and assigne me to it. The update is done as weighted average = – I keep the number of how many observations the identifier has = been computed. The result is that the identifier will have average = coordinates of all the observations it identifies – it will be the = center.

 

So here I come with my procedure. It has 3 params. The = first two are range of oids to list in the table. Used for scaling and = parallelization of the algorithm. The third is the radius in which to = search for the neighbours.

 

DROP TYPE IF EXISTS = coords;

CREATE TYPE coords AS (

=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0 raj2000 = double precision,

=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0 dej2000 = double precision

=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0 = );

 

 

DROP FUNCTION IF EXISTS build_catalog(int,int,double = precision);

CREATE OR REPLACE FUNCTION build_catalog (fromOID int, = toOID int, radius double precision)

=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0 RETURNS VOID = AS $$

DECLARE

=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0 cur1 CURSOR = FOR

=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0= =A0=A0=A0=A0=A0=A0=A0=A0=A0=A0 SELECT

=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0= =A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0= =A0 raj2000, dej2000

=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0= =A0=A0=A0=A0=A0=A0=A0=A0=A0=A0 FROM

=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0= =A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0= =A0 \schema.observation AS obs

=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0= =A0=A0=A0=A0=A0=A0=A0=A0=A0=A0 WHERE

=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0= =A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0= =A0 obs.oid >=3D fromOID

=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0= =A0=A0=A0=A0=A0=A0=A0=A0=A0=A0 AND

=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0= =A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0= =A0 obs.oid < toOID;

=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0 curr_raj2000 = double precision;

=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0 curr_dej2000 = double precision;

=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0 = curr_coords_cat coords;

=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0 cnt = int;=A0=A0=A0=A0=A0=A0=A0

 

BEGIN

/*SELECT = current_setting('transaction_isolation') into = tmp;

raise = notice 'Isolation level %', tmp;*/

OPEN cur1;

cnt:=3D0;

LOCK TABLE \schema.objcat IN SHARE = ROW EXCLUSIVE MODE;

LOOP

=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0 FETCH cur1 = INTO curr_raj2000, curr_dej2000;

=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0= =A0=A0=A0=A0=A0=A0=A0=A0=A0=A0 EXIT WHEN NOT = found;

=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0 =

=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0 WITH =

=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0= =A0=A0=A0=A0=A0=A0=A0=A0=A0=A0 upsert

=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0 = AS

=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0= =A0=A0=A0=A0=A0=A0=A0=A0=A0=A0 (UPDATE

=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0= =A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0= =A0 \schema.objcat

=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0= =A0=A0=A0=A0=A0=A0=A0=A0=A0=A0 SET

=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0= =A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0= =A0 ipix_cat=3Dq3c_ang2ipix(

=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0= =A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0= =A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0 (raj2000 * weight + = curr_raj2000) / (weight + 1),

=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0= =A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0= =A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0 (dej2000 * weight + = curr_dej2000) / (weight + 1)

=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0= =A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0= =A0 ),

=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0= =A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0= =A0 raj2000 =3D (raj2000 * weight + curr_raj2000) / (weight + = 1),

=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0= =A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0= =A0 dej2000 =3D (dej2000 * weight + curr_dej2000) / (weight + = 1),

=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0= =A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0= =A0 weight=3Dweight+1

=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0= =A0=A0=A0=A0=A0=A0=A0=A0=A0=A0 WHERE

=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0= =A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0= =A0 q3c_join(curr_raj2000, curr_dej2000,

=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0= =A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0= =A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0 raj2000, = dej2000,

=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0= =A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0= =A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0 = radius)

=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0= =A0=A0=A0=A0=A0=A0=A0=A0=A0=A0 RETURNING *),

=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0 ins = AS

=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0 (INSERT INTO =

=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0= =A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0= =A0 \schema.objcat

=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0= =A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0= =A0 (ipix_cat, raj2000, dej2000, weight)

=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0= =A0=A0=A0=A0=A0=A0=A0=A0=A0=A0 SELECT

=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0= =A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0= =A0 (q3c_ang2ipix(curr_raj2000, curr_dej2000)),

=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0= =A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0= =A0 curr_raj2000,

=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0= =A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0= =A0 curr_dej2000,

=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0= =A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0= =A0 1

=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0 WHERE NOT = EXISTS

=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0= =A0=A0=A0=A0=A0=A0=A0=A0=A0=A0 (SELECT * FROM = upsert)

=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0 RETURNING = *)

=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0 UPDATE =

=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0= =A0=A0=A0=A0=A0=A0=A0=A0=A0=A0 = \schema.observation

=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0 SET =

=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0= =A0=A0=A0=A0=A0=A0=A0=A0=A0=A0 id_cat =3D (SELECT = DISTINCT

=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0= =A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0= =A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0 = id_cat

=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0= =A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0= =A0 FROM

=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0= =A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0= =A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0 = upsert

=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0= =A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0= =A0 UNION

=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0= =A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0= =A0 SELECT

=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0= =A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0= =A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0 = id_cat

=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0= =A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0= =A0 FROM

=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0= =A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0= =A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0 = ins

=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0= =A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0= =A0 WHERE id_cat IS NOT NULL

=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0= =A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0= =A0 LIMIT 1)

=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0 WHERE CURRENT = OF cur1;

=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0 = cnt:=3Dcnt+1;

 

=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0 IF ((cnt % = 100000 ) =3D 0) THEN

=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0= =A0=A0=A0=A0=A0=A0=A0=A0=A0=A0 RAISE NOTICE 'Processed % entries', = cnt;

=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0 END = IF;

=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0= =A0=A0=A0=A0=A0=A0=A0=A0=A0=A0

END LOOP;

CLOSE cur1;

END;

$$ LANGUAGE = plpgsql;

 

Results: When I run the query only once (1 client thread), = it runs cca 1 mil rows per hour. Which is days for the whole dataset. = When I run it in parallel with that lock to ensure pessimistic = synchronization, it runs sequentially too J the other = threads just waiting. When I delete that lock and hope to solve the = resulting conflicts later, the ssd disk serves up to 4 threads = relatively effectively – which can divide my days of time by 4 = – still inacceptable.

 

The reason is quite clear here = – I’m trying to write something in one cycle of the script = to a table – then in the following cycle I need to read that = information.

 

Questions for you:

1.       = The first question is = if you can think of a better way how to do this and maybe if SQL is even = capable of doing such thing – or do I have to do it in C? Would = rewriting the SQL function to C help?

2.       = Could I somehow bend = the commiting during the algorithm for my thing? Ensure that inside one = cycle, the whole part of the identifiers table would be kept in memory = for faster lookups?

3.       = Why is this so slow? = J It is comparable to the quadratic algorithm in the terms = of speed – only does not use any memory.

 

I tried to sum this up the best I = could – for more information please don’t hesitate to = contact me.

 

Thank you very much for even reading this = far.

 

Best Regards,

 

Jiri = Nadvornik

 

 

------=_NextPart_000_01D3_01CFA8CF.A1017E40--