Thread: [BUGS] Query with "LIMIT 1" 10x slower than without LIMIT
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".
Cheers,
Aaron Tate
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 -- Sent via pgsql-bugs mailing list (pgsql-bugs@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-bugs