Search Postgresql Archives

Re: Any feedback on this query?

[Date Prev][Date Next][Thread Prev][Thread Next][Date Index][Thread Index]

 



On Fri, Feb 18, 2011 at 1:05 AM, Dean Rasheed <dean.a.rasheed@xxxxxxxxx> wrote:
> On 18 February 2011 07:19, Mike Christensen <mike@xxxxxxxxxxxxx> wrote:
>> Here's my query:
>>
>> SELECT R.RecipeId, R.Title, R.Description, R.ImageUrl, R.Rating,
>> R.PrepTime, R.CookTime, R.OwnerId, U.Alias
>> FROM Recipes R
>> INNER JOIN Users U ON U.UserId = R.OwnerId
>> WHERE (R.PrepTime <= :maxprep)
>> ORDER BY R.Rating DESC LIMIT 100;
>> SELECT COUNT(*) FROM Recipes R
>> WHERE (R.PrepTime <= :maxprep);
>>
>> The idea is I can show the top 100 matches, and then in the UI say:
>>
>> "Displaying top 100 results out of 150 recipes."
>>
>> I'm guessing doing two queries (one to get the top 100 rows and the
>> other to get the total DB count) is faster than getting all the rows
>> and trimming the data in code (there could be tens of thousands).
>> What I'm guessing is since Postgres just ran the query, the second
>> query will be near instant since any relevant data is still in memory.
>>
>> BTW, the query can potentially be way more complicated depending on
>> the user-entered search criteria.
>>
>> Feedback on this approach?
>>
>
> The second query by itself isn't guaranteed to return the same count
> that the first query would without the limit, unless you have FK and
> NOT NULL constraints on OwnerId.
>
> If you're on 8.4 or later, you could use a window function to return
> the count in the first query. I'm not sure that there will be much
> difference in performance, but it will be less prone to errors having
> only one WHERE clause to maintain. So something like:
>
> SELECT R.RecipeId, R.Title, R.Description, R.ImageUrl, R.Rating,
> R.PrepTime, R.CookTime, R.OwnerId, U.Alias,
> count(*) OVER ()
> FROM Recipes R
> INNER JOIN Users U ON U.UserId = R.OwnerId
> WHERE (R.PrepTime <= :maxprep)
> ORDER BY R.Rating DESC LIMIT 100;

Oh very interesting!  I will look into this method, it looks a lot cleaner..

FYI, yes OwnerId is NOT NULL and has a FK constraint.

Thanks!

Mike

-- 
Sent via pgsql-general mailing list (pgsql-general@xxxxxxxxxxxxxx)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general


[Date Prev][Date Next][Thread Prev][Thread Next][Date Index][Thread Index]
[Index of Archives]     [Postgresql Jobs]     [Postgresql Admin]     [Postgresql Performance]     [Linux Clusters]     [PHP Home]     [PHP on Windows]     [Kernel Newbies]     [PHP Classes]     [PHP Books]     [PHP Databases]     [Postgresql & PHP]     [Yosemite]
  Powered by Linux