Thread: How many rows if limit wasn't present?

How many rows if limit wasn't present?

From
Victor Spång Arthursson
Date:
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

Re: How many rows if limit wasn't present?

From
Richard Huxton
Date:
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


Re: How many rows if limit wasn't present?

From
Richard Huxton
Date:
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


Re: How many rows if limit wasn't present?

From
Victor Spång Arthursson
Date:
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