Re: limit order by performance issue

On 10/15/2012 12:44 PM, Pedro Jiménez wrote:

> select var_value from ism_floatvalues where id_signal = 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?

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 = 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(at)optionshouse(dot)com

______________________________________________

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

From pgsql-performance-owner(at)postgresql(dot)org Tue Oct 16 19:47:24 2012
Received: from magus.postgresql.org ([2a02:c0:301:0:ffff::29])
by malur.postgresql.org with esmtp (Exim 4.72)
(envelope-from <karl(at)denninger(dot)net>)
id 1TOD6p-0003D3-UZ
for pgsql-performance(at)postgresql(dot)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(at)denninger(dot)net>)
id 1TOD6k-0004hd-HI
for pgsql-performance(at)postgresql(dot)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(at)postgresql(dot)org>; Tue, 16 Oct 2012 14:47:16 -0500 (CDT)
(envelope-from karl(at)denninger(dot)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(dot)6070906(at)denninger(dot)net>
Date: Tue, 16 Oct 2012 14:47:10 -0500
From: Karl Denninger <karl(at)denninger(dot)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énez?= <p(dot)jimenez(at)ismsolar(dot)com>
CC: pgsql-performance(at)postgresql(dot)org
Subject: Re: limit order by performance issue
References: <507C4B8E(dot)3010806(at)ismsolar(dot)com>
In-Reply-To: <507C4B8E(dot)3010806(at)ismsolar(dot)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é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

--------------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(dot)3010806(at)ismsolar(dot)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--