Re: Any feedback on this query?

On Fri, Feb 18, 2011 at 1:05 AM, Dean Rasheed <dean(dot)a(dot)rasheed(at)gmail(dot)com> wrote:
> On 18 February 2011 07:19, Mike Christensen <mike(at)kitchenpc(dot)com> 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