Re: limit order by performance issue

On Wed, Oct 17, 2012 at 6:14 AM, Pedro Jiménez Pérez <p(dot)jimenez(at)ismsolar(dot)com
> wrote:

> select var_value from ism_floatvalues where id_signal = 29660 order by
> time_stamp desc limit 1;
>
> This is what EXPLAIN returns (can't make EXPLAIN ANALYZE because it
> "never" ends):
>
> "Limit (cost=0.00..258.58 rows=1 width=16)"
> " -> Index Scan Backward using ism_floatvalues_index_time_stamp on
> ism_floatvalues (cost=0.00..8912076.82 rows=34466 width=16)"
> " Filter: (id_signal = 29660)"
>
> This is EXPLAIN ANALYZE without "limit 1":

Add (or modify the existing) an index on id_signal, time_stamp desc, and
you're done.

It must be a case of descending time stamps not hitting the filter
condition (id_signal) soon enough.