Any feedback on this query?

From: Mike Christensen <mike(at)kitchenpc(dot)com>
To: "pgsql-general(at)postgresql(dot)org" <pgsql-general(at)postgresql(dot)org>
Subject: Any feedback on this query?
Date: 2011-02-18 07:19:37
Message-ID: AANLkTik2ooVhXa_zECzfdTLASTYmAimMPpabF0sfb-Da@mail.gmail.com
Views: Raw Message | Whole Thread | Download mbox | Resend email
Thread:
Lists: pgsql-general

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

Responses

Browse pgsql-general by date

  From Date Subject
Next Message Magnus Hagander 2011-02-18 07:24:09 Re: database instance creation
Previous Message John R Pierce 2011-02-18 06:51:25 Re: PostgresPlus Windows Installation Error