From: Karl Denninger <karl@denninger.net>
To: Pedro Jiménez <p.jimenez@ismsolar.com>
Cc: pgsql-performance@postgresql.org
Subject: Re: limit order by performance issue
Date: Tue, 16 Oct 2012 14:47:10 -0500
Message-ID: <507DB9BE.6070906@denninger.net> (raw)
In-Reply-To: <507C4B8E.3010806@ismsolar.com>
References: <507C4B8E.3010806@ismsolar.com>
Put an index on time_stamp (I assume there is one on id_signal already)
On 10/15/2012 12:44 PM, Pedro Jiménez 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 = 29660 order by
> 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.
>
>
>
>
--
-- Karl Denninger
/The Market Ticker ®/ <http://market-ticker.org;
Cuda Systems LLC
Reply instructions:
You may reply publicly to this message via plain-text email
using any one of the following methods:
* Reply to all the recipients using the --to and --cc options:
reply via email
To: pgsql-performance@postgresql.org
Cc: karl@denninger.net, p.jimenez@ismsolar.com
Subject: Re: limit order by performance issue
In-Reply-To: <507DB9BE.6070906@denninger.net>
* Save the following mbox file, import it into your mail client,
and reply-to-all from there: mbox
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox