Received: from malur.postgresql.org ([217.196.149.56]) by arkaria.postgresql.org with esmtp (Exim 4.84_2) (envelope-from ) id 1eAABB-0002KW-33 for pgsql-sql@arkaria.postgresql.org; Thu, 02 Nov 2017 07:44:45 +0000 Received: from localhost ([127.0.0.1] helo=postgresql.org) by malur.postgresql.org with smtp (Exim 4.84_2) (envelope-from ) id 1eAABA-0005k5-7h for pgsql-sql@arkaria.postgresql.org; Thu, 02 Nov 2017 07:44:44 +0000 Received: from makus.postgresql.org ([2001:4800:1501:1::229]) by malur.postgresql.org with esmtps (TLS1.2:ECDHE_RSA_AES_256_CBC_SHA384:256) (Exim 4.84_2) (envelope-from ) id 1eAAA8-0002AH-1d for pgsql-sql@postgresql.org; Thu, 02 Nov 2017 07:43:40 +0000 Received: from host3.dynacom.ondsl.gr ([62.103.35.211] helo=smadev.internal.net) by makus.postgresql.org with esmtps (TLS1.2:ECDHE_RSA_AES_256_CBC_SHA384:256) (Exim 4.84_2) (envelope-from ) id 1eAAA3-0000YG-Pv for pgsql-sql@postgresql.org; Thu, 02 Nov 2017 07:43:38 +0000 Received: from smadev.internal.net (smadev [10.9.200.131]) by smadev.internal.net (8.15.2/8.15.2) with ESMTP id vA27hSbZ023854 for ; Thu, 2 Nov 2017 09:43:28 +0200 (EET) (envelope-from achill@matrix.gatewaynet.com) Subject: Re: join tables by nearest timestamp To: pgsql-sql@postgresql.org References: <282b209c-eaac-2f22-ae04-6ecfdac5284b@matrix.gatewaynet.com> <53afa96e-2c45-bb10-51f3-2a0640c5d743@matrix.gatewaynet.com> From: Achilleas Mantzios Message-ID: Date: Thu, 2 Nov 2017 09:43:28 +0200 User-Agent: Mozilla/5.0 (X11; FreeBSD amd64; rv:52.0) Gecko/20100101 Thunderbird/52.3.0 MIME-Version: 1.0 In-Reply-To: Content-Type: multipart/alternative; boundary="------------6ED4B4BF783AB18D57782AAD" Content-Language: en-US List-Archive: List-Help: List-ID: List-Owner: List-Post: List-Subscribe: List-Unsubscribe: X-Mailing-List: pgsql-sql Precedence: bulk Sender: pgsql-sql-owner@postgresql.org This is a multi-part message in MIME format. --------------6ED4B4BF783AB18D57782AAD Content-Type: text/plain; charset=utf-8; format=flowed Content-Transfer-Encoding: 8bit On 01/11/2017 15:12, Brice André wrote: > After some tests, I have some performance issues with this solution. It seems that for each row that satisfies first event condition, all possible results of second join table search are tested. > > On my first attempts, this was reasonable because I tested on only one day. But request time increases exponentially with size of searched interval. > > As I have multi-row index (event type and timestamp) on this table, for me, an ideal request could only check two entries of second event type for each first event type entry. But with left outer > join request, optimizer does not seem able to do that. > > Any idea on how to improve perfs? You may try with a subselect. First just query from the left table, and in the select list, append a subselect, at first returning only one column. > > Regards, > Brice > > Le mer. 1 nov. 2017 à 09:47, Brice André > a écrit : > > Many thanks Achilleas. I did not think to use an outer join in combination with Distnct and order by clauses, which seems to be the key to my problem. > > I slighly adapted your proposal to match my DB schema, but also to select the real nearest point (and not the nearest one after). I defined a function 'abs' that computes the absolute value from > a timestamp and the query looks like : > > SELECT DISTINCT ON (l1."ID") (l1."Time"-l2."Time") as time_diff, l1.*,l2.* from > "KnxBusAccess" l1 > LEFT OUTER JOIN > "KnxBusAccess" l2 > ON ('t') > where > l1."ToGroupAddress" = '2/0/1' AND > l1."Time" >=  (now()-interval '1 day') AND > l2."ToGroupAddress" = '2/5/1' AND > l2."Time" >=  (now()-interval '1 day') > order by l1."ID", abs(l2."Time"-l1."Time") > > the "l1."Time" >=  (now()-interval '1 day')" and "l2."Time" >=  (now()-interval '1 day')" are thereto use an index in order to limit the values to an acceptable range (I have years of records > and with an outer join and without this, the query never finishes). > > Thannks, this solves my issue. > > Regards, > Brice > > > > 2017-11-01 9:11 GMT+01:00 Achilleas Mantzios >: > > On 01/11/2017 10:06, Achilleas Mantzios wrote: > > On 01/11/2017 07:53, Brice André wrote: > > Dear all, > > I am running a postgresql 9.1 server and I have a table containing events information with, for each entry, an event type, a timestamp, and additional information. > > I would want to write a query that would return all events of type 'a', but each returned entry should be associated to the neraest event of type 'b' (ideally, the nearest, non > taking into account if it happened before or after, but if not possible, it could be the first happening just after). > > By searching on the web, I found a solution base on a "LEFT JOIN LATERAL", but this is not supported by postgresql 9.1 (and I cannot update my server) : > > SELECT * > FROM > (SELECT * FROM events WHERE type = 'a' ) as t1 > LEFT JOIN LATERAL > (SELECT * FROM events WHERE type = 'b' AND timestamp >= t1.timestamp ORDER BY timestamp LIMIT  1) as t2 > ON TRUE; > > Any idea on how to adapt this query so that it runs on 9.1 ? Or any other idea on how to perform my query ? > > smth like : > > SELECT l1.*,l2.logtime,l2.category,l2.username from logging l1 LEFT OUTER JOIN  logging l2 ON ('t') where l1.category='vsl.login' AND (l2.category IS NULL OR > l2.category='vsl.SpareCases') AND (l2.logtime IS NULL OR l2.logtime>=l1.logtime) order by l1.logtime; > > oopss, sorry I forgot, you'll have to add a DISTINCT ON and order by l2.logtime in order to have what you want : > > SELECT DISTINCT ON (l1.logtime,l1.category,l1.username,l1.action) l1.*,l2.logtime,l2.category,l2.username from logging l1 LEFT OUTER JOIN  logging l2 ON ('t') where l1.category='vsl.login' > AND (l2.category IS NULL OR l2.category='vsl.SpareCases') AND (l2.logtime IS NULL OR l2.logtime>=l1.logtime) order by l1.logtime,l1.category,l1.username,l1.action,l2.logtime; > > > > > > Thanks in advance, > Brice > > > > > -- > Achilleas Mantzios > IT DEV Lead > IT DEPT > Dynacom Tankers Mgmt > > > > -- > Sent via pgsql-sql mailing list (pgsql-sql@postgresql.org ) > To make changes to your subscription: > http://www.postgresql.org/mailpref/pgsql-sql > > -- Achilleas Mantzios IT DEV Lead IT DEPT Dynacom Tankers Mgmt --------------6ED4B4BF783AB18D57782AAD Content-Type: text/html; charset=utf-8 Content-Transfer-Encoding: 8bit
On 01/11/2017 15:12, Brice André wrote:
After some tests, I have some performance issues with this solution. It seems that for each row that satisfies first event condition, all possible results of second join table search are tested.

On my first attempts, this was reasonable because I tested on only one day. But request time increases exponentially with size of searched interval.

As I have multi-row index (event type and timestamp) on this table, for me, an ideal request could only check two entries of second event type for each first event type entry. But with left outer join request, optimizer does not seem able to do that.

Any idea on how to improve perfs?

You may try with a subselect. First just query from the left table, and in the select list, append a subselect, at first returning only one column.


Regards,
Brice

Le mer. 1 nov. 2017 à 09:47, Brice André <brice@famille-andre.be> a écrit :
Many thanks Achilleas. I did not think to use an outer join in combination with Distnct and order by clauses, which seems to be the key to my problem.

I slighly adapted your proposal to match my DB schema, but also to select the real nearest point (and not the nearest one after). I defined a function 'abs' that computes the absolute value from a timestamp and the query looks like :

SELECT DISTINCT ON (l1."ID") (l1."Time"-l2."Time") as time_diff, l1.*,l2.* from
"KnxBusAccess" l1
LEFT OUTER JOIN
"KnxBusAccess" l2
ON ('t')
where
l1."ToGroupAddress" = '2/0/1' AND
l1."Time" >=  (now()-interval '1 day') AND
l2."ToGroupAddress" = '2/5/1' AND
l2."Time" >=  (now()-interval '1 day')
order by l1."ID", abs(l2."Time"-l1."Time")

the "l1."Time" >=  (now()-interval '1 day')" and "l2."Time" >=  (now()-interval '1 day')" are thereto use an index in order to limit the values to an acceptable range (I have years of records and with an outer join and without this, the query never finishes).

Thannks, this solves my issue.

Regards,
Brice



2017-11-01 9:11 GMT+01:00 Achilleas Mantzios <achill@matrix.gatewaynet.com>:
On 01/11/2017 10:06, Achilleas Mantzios wrote:
On 01/11/2017 07:53, Brice André wrote:
Dear all,

I am running a postgresql 9.1 server and I have a table containing events information with, for each entry, an event type, a timestamp, and additional information.

I would want to write a query that would return all events of type 'a', but each returned entry should be associated to the neraest event of type 'b' (ideally, the nearest, non taking into account if it happened before or after, but if not possible, it could be the first happening just after).

By searching on the web, I found a solution base on a "LEFT JOIN LATERAL", but this is not supported by postgresql 9.1 (and I cannot update my server) :

SELECT *
FROM
(SELECT * FROM events WHERE type = 'a' ) as t1
LEFT JOIN LATERAL
(SELECT * FROM events WHERE type = 'b' AND timestamp >= t1.timestamp ORDER BY timestamp LIMIT  1) as t2
ON TRUE;

Any idea on how to adapt this query so that it runs on 9.1 ? Or any other idea on how to perform my query ?
smth like :

SELECT l1.*,l2.logtime,l2.category,l2.username from logging l1 LEFT OUTER JOIN  logging l2 ON ('t') where l1.category='vsl.login' AND (l2.category IS NULL OR l2.category='vsl.SpareCases') AND (l2.logtime IS NULL OR l2.logtime>=l1.logtime) order by l1.logtime;
 oopss, sorry I forgot, you'll have to add a DISTINCT ON and order by l2.logtime in order to have what you want :

SELECT DISTINCT ON (l1.logtime,l1.category,l1.username,l1.action) l1.*,l2.logtime,l2.category,l2.username from logging l1 LEFT OUTER JOIN  logging l2 ON ('t') where l1.category='vsl.login' AND (l2.category IS NULL OR l2.category='vsl.SpareCases') AND (l2.logtime IS NULL OR l2.logtime>=l1.logtime) order by l1.logtime,l1.category,l1.username,l1.action,l2.logtime;





Thanks in advance,
Brice



--
Achilleas Mantzios
IT DEV Lead
IT DEPT
Dynacom Tankers Mgmt



--
Sent via pgsql-sql mailing list (pgsql-sql@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-sql


-- 
Achilleas Mantzios
IT DEV Lead
IT DEPT
Dynacom Tankers Mgmt
--------------6ED4B4BF783AB18D57782AAD--