Обсуждение: How many rows if limit wasn't present?
Ciao! Is it possible to get the number of rows that would have been returned if the LIMIT-clause weren't present in some way after the query was run? Reason for asking is that I have a really big chunk of SQL, which takes time to execute, and whoose result is paginated using a LIMIT- clause, and to get the actual result (before pagination) I have to run the query one more time… Big slow down… Thanks in advance, Victor Spång Arthursson
Victor Spång Arthursson wrote: > Ciao! > > Is it possible to get the number of rows that would have been returned > if the LIMIT-clause weren't present in some way after the query was run? > > Reason for asking is that I have a really big chunk of SQL, which takes > time to execute, and whoose result is paginated using a LIMIT- clause, > and to get the actual result (before pagination) I have to run the > query one more time… Big slow down… No. Standard procedure here is to select the results to a temporary table, or application-level cache etc. -- Richard Huxton Archonet Ltd
Richard Huxton wrote: > Victor Spång Arthursson wrote: > >> Ciao! >> >> Is it possible to get the number of rows that would have been >> returned if the LIMIT-clause weren't present in some way after the >> query was run? >> >> Reason for asking is that I have a really big chunk of SQL, which >> takes time to execute, and whoose result is paginated using a LIMIT- >> clause, and to get the actual result (before pagination) I have to >> run the query one more time… Big slow down… > > > No. Standard procedure here is to select the results to a temporary > table, or application-level cache etc. Just to expand a bit on my own reply - PG will stop processing once it hits the LIMIT. Sometimes it still has to gather all the rows first (e.g. if you ask for the top 10 selling items this month, it needs to calculate all the sales before limiting). Also - you don't need to cache the full result. Sometimes it might make sense to cache just some keys and associated scores and fetch descriptive columns later if required. HTH -- Richard Huxton Archonet Ltd
13 maj 2005 kl. 14.45 skrev Richard Huxton: > Richard Huxton wrote: > >> Victor Spång Arthursson wrote: >> >>> Ciao! >>> >>> Is it possible to get the number of rows that would have been >>> returned if the LIMIT-clause weren't present in some way after >>> the query was run? >>> >>> Reason for asking is that I have a really big chunk of SQL, >>> which takes time to execute, and whoose result is paginated >>> using a LIMIT- clause, and to get the actual result (before >>> pagination) I have to run the query one more time… Big slow down… >>> >> No. Standard procedure here is to select the results to a >> temporary table, or application-level cache etc. >> > > Just to expand a bit on my own reply - PG will stop processing once > it hits the LIMIT. Sometimes it still has to gather all the rows > first (e.g. if you ask for the top 10 selling items this month, it > needs to calculate all the sales before limiting). > > Also - you don't need to cache the full result. Sometimes it might > make sense to cache just some keys and associated scores and fetch > descriptive columns later if required. Figured so my self and changed the code from pg_fetch_all to pg_fetch_assoc, and then I just fetched the 10 or so I needed… Speed up with around 60% :D Now the big question is how to understand the EXPLAIN numbers… Ciao! /.v