PostgreSQL Inserted rows differ from select

Question

I have a problem with an INSERT in PostgreSQL. I have this query:

INSERT INTO track_segments(tid, gdid1, gdid2, distance, speed)
SELECT * FROM (
SELECT DISTINCT ON (pga.gdid) 
pga.tid as ntid,
pga.gdid as gdid1, pgb.gdid as gdid2,
ST_Distance(pga.geopoint, pgb.geopoint) AS segdist, 
(ST_Distance(pga.geopoint, pgb.geopoint) / EXTRACT(EPOCH FROM (pgb.timestamp - pga.timestamp + interval '0.1 second'))) as speed
FROM fl_pure_geodata AS pga
LEFT OUTER JOIN fl_pure_geodata AS pgb ON (pga.timestamp < pgb.timestamp AND pga.tid = pgb.tid) 
ORDER BY pga.gdid ASC) AS sq
WHERE sq.gdid2 IS NOT NULL;

to fill a table with pairwise connected segements of geopoints. When I run the SELECT alone I get the correct pairs, but when I use it in the statement above, then some are paired the wrong way or not at all. Here's what I mean:

result of SELECT alone:

tid;gdid1;gdid2;distance;speed
"0f6fd522-5f1e-49a4-b85e-50f11ef7f908";10;11;34.105058803;31.0045989118182
"0f6fd522-5f1e-49a4-b85e-50f11ef7f908";11;12;90.099603143;14.7704267447541
"0f6fd522-5f1e-49a4-b85e-50f11ef7f908";12;13;23.331326565;21.2102968772727

result after INSERT with the same SELECT:

tid;gdid1;gdid2;distance;speed
"0f6fd522-5f1e-49a4-b85e-50f11ef7f908";10;12;122.574;17.2639603638028
"0f6fd522-5f1e-49a4-b85e-50f11ef7f908";11;12;90.0996;14.7704267447541
"0f6fd522-5f1e-49a4-b85e-50f11ef7f908";12;13;23.3313;21.2102968772727

What be the cause of that? It's exactly the same SELECT statement for the INSERT, so why does it give different results?

BTW: the WHERE sq.gdid2 IS NOT NULL; effectively transforms the LEFT join into a plain join. — joop
– joop, Commented Mar 11, 2016 at 16:17
Note 2: the (pga.timestamp < pgb.timestamp AND pga.tid = pgb.tid) join condition produces an (N*(N-1)/2) sized (partial) carthesian produkt per tid. Is that what you want? — joop
– joop, Commented Mar 11, 2016 at 16:25
@joop oh, that's right. I initially wanted to have the nulls displayed, to see if the last point correctly doesn't connect to the next one with different tid. It was kinda late when I wrote that, forgot about the JOINs. Thanks for the correction! — rfreytag
– rfreytag, Commented Mar 11, 2016 at 17:02

Ihor Romanchenko · Accepted Answer · 2016-03-11 15:31:52Z

2

DISTINCT ON (pga.gdid) can pick any row from a set with equal pga.gdid. You can get different result even by execution the same query for several times. Add additional ordering to get consistent results. something like: pga.gdid ASC, pgb.gdid ASC

BTW You may want to order by pga.gdid ASC, pgb.timestamp - pga.timestamp ASC to get the "next" point.

BTW2 It may be easier to use lead() or lag() window functions to calculate differences between current row and next/previous. This way you wont need a self join and will likely get better performance.

edited Mar 11, 2016 at 15:31

answered Mar 11, 2016 at 15:21

Ihor Romanchenko

29k9 gold badges56 silver badges45 bronze badges

Sign up to request clarification or add additional context in comments.

4 Comments

rfreytag Over a year ago

ah, yeah! That makes sense, I hope it works. The old query took 5 seconds, now with the additional order it's already at 2min and counting...

Ihor Romanchenko Over a year ago

"getting correct result" often takes more time than "getting some result".

Ihor Romanchenko Over a year ago

@pfannkuchen_gesicht see my edit for a possible way to get better performance.

rfreytag Over a year ago

Just tested with lead()... and holy crap, it only takes 0.3% of the time the original query took.

Castillo · Accepted Answer · 2016-03-11 15:17:24Z

1

You are ordering your query results only by the column pga.gdid, which is the same in all the rows, so postgres will order the results in a different way each time you do the select query.

edited Mar 11, 2016 at 15:17

answered Mar 11, 2016 at 15:01

Castillo

1458 bronze badges

4 Comments

rfreytag Over a year ago

what do you mean? The gdid are sequential, the first column is the tid, the second and third columns are the gdids

rfreytag Over a year ago

about your edit: I'm not ordering by the pga.tid though.

Castillo Over a year ago

Sorry, im at the phone. I mean that if you order by one column and you have múltiple results with the same value the order of the results is going to be different each time.

rfreytag Over a year ago

ah, I see, I have to order by pgb.gdid as well to make it work properly. Alright thanks!

Collectives™ on Stack Overflow

PostgreSQL Inserted rows differ from select

2 Answers 2

4 Comments

4 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

4 Comments

4 Comments

Your Answer

Sign up or log in

Post as a guest

Related