Message-ID: <507DB54F.7000005@optionshouse.com>
Date: Tue, 16 Oct 2012 14:28:15 -0500
From: Shaun Thomas <sthomas@optionshouse.com>
Reply-To: <sthomas@optionshouse.com>
Organization: Peak6
User-Agent: Mozilla/5.0 (X11; Linux x86_64;
	rv:15.0) Gecko/20120912 Thunderbird/15.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>
Content-Type: text/plain; charset="ISO-8859-1"; format=flowed
Content-Length: 1682
Content-Transfer-Encoding: quoted-printable

On 10/15/2012 12:44 PM, Pedro Jim=E9nez wrote:

> select var_value from ism_floatvalues where id_signal =3D 29660 order by
> time_stamp desc limit 1;

Well, we'd have to see an EXPLAIN plan to really know what's going on 
here, but it often boils down to the planner being overly optimistic 
when low limits are specified. I bet you have an index on time_stamp, 
don't you=3F

In that case, the planner would reverse index-scan that index, 
estimating that the chances of it finding ID 29660 are less expensive 
than fetching all of the rows that match the ID directly, and throwing 
away all but 1 row. Remember, it would have to read all of those values 
to know which is the most recent.

You can fix this a couple of ways:

1. Put a two-column index on these values:

CREATE INDEX idx_ordered_signal
     ON ism_floatvalues (id_signal, time_stamp DESC);

Which turns any request for that particular combo into a single index fetch.

2. You can trick the planner by introducing an optimization fence:

SELECT var_value
   FROM (
          SELECT var_value, time_stamp
            FROM ism_floatvalues
           WHERE id_signal =3D 29660
          OFFSET 0
        )
  ORDER BY time_stamp DESC
  LIMIT 1;

Quite a few people will probably grouse at me for giving you that as an 
option, but it does work better than LIMIT 1 more often than it probably 
should.


-- 
Shaun Thomas
OptionsHouse | 141 W. Jackson Blvd. | Suite 500 | Chicago IL, 60604
312-444-8534
sthomas@optionshouse.com

______________________________________________

See http://www.peak6.com/email_disclaimer/ for terms and conditions related to this email