13 maj 2005 kl. 14.45 skrev Richard Huxton:
Richard Huxton wrote:
Victor Spång Arthursson wrote:
No. Standard procedure here is to select the results to a temporary table, or application-level cache etc.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…
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 ---------------------------(end of broadcast)--------------------------- TIP 2: you can get off all lists at once with the unregister command (send "unregister YourEmailAddressHere" to majordomo@xxxxxxxxxxxxxx)
