limit order by performance issue

From: Pedro Jiménez <p(dot)jimenez(at)ismsolar(dot)com>
To: pgsql-performance(at)postgresql(dot)org
Subject: limit order by performance issue
Date: 2012-10-15 17:44:46
Message-ID: 507C4B8E.3010806@ismsolar.com
Views: Raw Message | Whole Thread | Download mbox | Resend email
Thread:
Lists: pgsql-performance

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.

Responses

Browse pgsql-performance by date

  From Date Subject
Next Message henk de wit 2012-10-15 17:50:18 Re: Query with limit goes from few ms to hours
Previous Message Joshua D. Drake 2012-10-15 17:18:55 Re: WebSphere Application Server support for postgres