Re: Query with "LIMIT 1" 10x slower than without LIMIT

On 9/8/2017 1:10 PM, Aaron Tate wrote:
> We have a number of queries that do a lot of summarization of data.
> The base query only returns one row, but due to a Rails quirk, we have
> to call rel.first to get the first (only) element of the list, which
> in some cases uses a "LIMIT 1" for the underlying query. With or
> without the LIMIT 1, the results are identical, but with the LIMIT 1,
> a 400ms query might take 5000ms to execute.
>
> Please note that this does occur on PostgreSQL version 9.6.1, on Linux
> or OSX. We can certainly work around it if we're mindful of it, but
> it's a surprising "gotcha".

1) To make any sense of this would require seeing the actual queries,
along with the schema.

2) A better way of doing that 'rel.first' thing in the general case
would be a query that returns a cursor, then reading the first row from
the cursor, then the whole thing.  of course, you're stuck with whatever
the ORM (Rails) wants to do, so you probably have no option on this.

--
john r pierce, recycling bits in santa cruz