Straying a little bit, but I think this is a good example to show how the database works differently with joins verses subqueries.
Here's runrig's query plan on my version of postgres.
EXPLAIN ANALYZE
SELECT u1.uptime_id as uid, u1.uptime_value
FROM uptime u1
WHERE u1.uptime_value > (SELECT u2.uptime_value
FROM uptime u2
WHERE u2.uptime_id = u1.uptime_id+1);
QUERY PLAN
+
+
----------------------------------------------------------------------
+---------------------------------------
Seq Scan on uptime u1 (cost=0.00..25022.50 rows=334 width=8) (actual
+ time=0.207..0.614 rows=3 loops=1)
Filter: (uptime_value > (subplan))
SubPlan
-> Seq Scan on uptime u2 (cost=0.00..25.00 rows=6 width=4) (act
+ual time=0.015..0.026 rows=1 loops=16)
Filter: (uptime_id = ($0 + 1))
Total runtime: 0.688 ms
Here's my query plan using join.
EXPLAIN ANALYZE
SELECT u1.uptime_id as uid, u1.uptime_value
FROM uptime u1, uptime u2
WHERE u2.uptime_id = u1.uptime_id+1
AND u1.uptime_value > u2.uptime_value;
QUERY PLAN
+
+
----------------------------------------------------------------------
+----------------------------------------------
Merge Join (cost=139.66..247.18 rows=1667 width=8) (actual time=0.45
+3..0.654 rows=3 loops=1)
Merge Cond: ("outer"."?column3?" = "inner".uptime_id)
Join Filter: ("outer".uptime_value > "inner".uptime_value)
-> Sort (cost=69.83..72.33 rows=1000 width=8) (actual time=0.193.
+.0.250 rows=16 loops=1)
Sort Key: (u1.uptime_id + 1)
-> Seq Scan on uptime u1 (cost=0.00..20.00 rows=1000 width=
+8) (actual time=0.020..0.102 rows=16 loops=1)
-> Sort (cost=69.83..72.33 rows=1000 width=8) (actual time=0.159.
+.0.221 rows=16 loops=1)
Sort Key: u2.uptime_id
-> Seq Scan on uptime u2 (cost=0.00..20.00 rows=1000 width=
+8) (actual time=0.005..0.076 rows=16 loops=1)
Total runtime: 0.765 ms
In this case the subquery is faster, but you'll notice its getting 1 row at a time for 16 loops. The joined query should be a lot faster as the data set grows larger.
I just tested this with a total of 131072 rows. The joined query took a total of 3872.267 ms. I am still waiting for the subquery to return.
Update: The select with subquery finally returned, 19074438.347 ms