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
