Received: from magus.postgresql.org ([2a02:c0:301:0:ffff::29])
	by malur.postgresql.org with esmtp (Exim 4.72)
	(envelope-from <karl@denninger.net>)
	id 1TOD6p-0003D3-UZ
	for pgsql-performance@postgresql.org; Tue, 16 Oct 2012 19:47:24 +0000
Received: from wsip-70-169-168-7.pn.at.cox.net ([70.169.168.7] helo=FS.denninger.net)
	by magus.postgresql.org with esmtp (Exim 4.72)
	(envelope-from <karl@denninger.net>)
	id 1TOD6k-0004hd-HI
	for pgsql-performance@postgresql.org; Tue, 16 Oct 2012 19:47:22 +0000
Received: from [127.0.0.1] (localhost [127.0.0.1])
	by FS.denninger.net (8.14.4/8.13.1) with ESMTP id q9GJlG0r049518
	for <pgsql-performance@postgresql.org>; Tue, 16 Oct 2012 14:47:16 -0500 (CDT)
	(envelope-from karl@denninger.net)
Received: from [127.0.0.1] [192.168.1.40] by Spamblock-sys (LOCAL);
	Tue Oct 16 14:47:16 2012
Message-ID: <507DB9BE.6070906@denninger.net>
Date: Tue, 16 Oct 2012 14:47:10 -0500
From: Karl Denninger <karl@denninger.net>
User-Agent: Mozilla/5.0 (Windows NT 6.1; WOW64; rv:16.0) Gecko/20121010 Thunderbird/16.0.1
MIME-Version: 1.0
To: =?ISO-8859-1?Q?Pedro_Jim=E9nez?= <p.jimenez@ismsolar.com>
CC: pgsql-performance@postgresql.org
Subject: Re: limit order by performance issue
References: <507C4B8E.3010806@ismsolar.com>
In-Reply-To: <507C4B8E.3010806@ismsolar.com>
X-Enigmail-Version: 1.4.5
Content-Type: multipart/alternative;
 boundary="------------010603000103080304060303"
X-Antivirus: avast! (VPS 121016-0, 10/16/2012), Outbound message
X-Antivirus-Status: Clean
X-Pg-Spam-Score: -0.9 (/)
X-Archive-Number: 201210/216
X-Sequence-Number: 48175

This is a multi-part message in MIME format.
--------------010603000103080304060303
Content-Type: text/plain; charset=ISO-8859-1
Content-Transfer-Encoding: quoted-printable
X-MIME-Autoconverted: from 8bit to quoted-printable by FS.denninger.net id q9GJlG0r049518

Put an index on time_stamp (I assume there is one on id_signal already)

On 10/15/2012 12:44 PM, Pedro Jim=E9nez wrote:
> Hello,
>   I'm trying to do a simple SQL query over Postgresl 9.0 running on
> Ubuntu.
>
> I have a large table (over 100 million records) with three fields,
> id_signal (bigint), time_stamp (timestamp) and var_value (float).
>
> My query looks like this:
>
> select var_value from ism_floatvalues where id_signal =3D 29660 order b=
y
> time_stamp desc limit 1;
>
> So I want to select the last value from a determinated ID (is_signal).
>
> This query runs FOREVER, while if I delete "limit 1" it runs
> instantly....
>
> Any help?
>
> Regards.
>
>
>
>

--=20
-- Karl Denninger
/The Market Ticker =AE/ <http://market-ticker.org>
Cuda Systems LLC

--------------010603000103080304060303
Content-Type: text/html; charset=ISO-8859-1
Content-Transfer-Encoding: 7bit

<html>
  <head>
    <meta content="text/html; charset=ISO-8859-1"
      http-equiv="Content-Type">
  </head>
  <body bgcolor="#FFFFFF" text="#000000">
    Put an index on time_stamp (I assume there is one on id_signal
    already)<br>
    <br>
    <div class="moz-cite-prefix">On 10/15/2012 12:44 PM, Pedro Jim&eacute;nez
      wrote:<br>
    </div>
    <blockquote cite="mid:507C4B8E.3010806@ismsolar.com" type="cite">Hello,
      <br>
      &nbsp; I'm trying to do a simple SQL query over Postgresl 9.0 running
      on Ubuntu.
      <br>
      <br>
      I have a large table (over 100 million records) with three fields,
      id_signal (bigint), time_stamp (timestamp) and var_value (float).
      <br>
      <br>
      My query looks like this:
      <br>
      <br>
      select var_value from ism_floatvalues where id_signal = 29660
      order by time_stamp desc limit 1;
      <br>
      <br>
      So I want to select the last value from a determinated ID
      (is_signal).
      <br>
      <br>
      This query runs FOREVER, while if I delete "limit 1" it runs
      instantly....
      <br>
      <br>
      Any help?
      <br>
      <br>
      Regards.
      <br>
      <br>
      <br>
      <br>
      <br>
    </blockquote>
    <br>
    <div class="moz-signature">-- <br>
      -- Karl Denninger<br>
      <a href="http://market-ticker.org"><i>The Market Ticker &reg;</i></a><br>
      Cuda Systems LLC</div>
  </body>
</html>

--------------010603000103080304060303--