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

[download]

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

[download]

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

In reply to Re: OT: peak values with SQL by eclark
in thread OT: peak values with SQL by revdiablo

Title:
Use:  <p> text here (a paragraph) </p>
and:  <code> code here </code>
to format your post, it's "PerlMonks-approved HTML":


  • Posts are HTML formatted. Put <p> </p> tags around your paragraphs. Put <code> </code> tags around your code and data!
  • Titles consisting of a single word are discouraged, and in most cases are disallowed outright.
  • Read Where should I post X? if you're not absolutely sure you're posting in the right place.
  • Please read these before you post! —
  • Posts may use any of the Perl Monks Approved HTML tags:
    a, abbr, b, big, blockquote, br, caption, center, col, colgroup, dd, del, details, div, dl, dt, em, font, h1, h2, h3, h4, h5, h6, hr, i, ins, li, ol, p, pre, readmore, small, span, spoiler, strike, strong, sub, summary, sup, table, tbody, td, tfoot, th, thead, tr, tt, u, ul, wbr
  • You may need to use entities for some characters, as follows. (Exception: Within code tags, you can put the characters literally.)
            For:     Use:
    & &amp;
    < &lt;
    > &gt;
    [ &#91;
    ] &#93;
  • Link using PerlMonks shortcuts! What shortcuts can I use for linking?
  • See Writeup Formatting Tips and other pages linked from there for more info.