Thread: Any feedback on this query?

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?

Mike

On 18 February 2011 07:19, Mike Christensen <mike@kitchenpc.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;

Regards,
Dean

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