Postgres Pro
Downloads
Home   >   mailing lists

Thread: Way to use count() and LIMIT?

Way to use count() and LIMIT?

From
Joe Koenig
Date:
Is there a way to structure a query so you can only run 1 query, get the
full number of rows that would be returned, but then use LIMIT to step
through in groups of 20? For example, a search in my CD's/Rock section
will return 53,000 results. I want to give the user the number of total
results, but also use LIMIT to go through 20 at a time? Does this
require 2 queries? Thanks,

Joe

Re: Way to use count() and LIMIT?

From
Jason Earl
Date:
Sure, just declare a cursor.  Here's a simple one that I use:

    DECLARE raw_data CURSOR FOR
    SELECT
    (SELECT pc FROM curpack1 WHERE curpack1.dt <= caseweights1.dt
     ORDER BY curpack1.dt DESC LIMIT 1) AS "pc",
    dt::date AS "date",
    dt::time AS "time",
    weight AS "weight"
        FROM caseweights1
        WHERE dt >= '%s' AND
        dt < '%s'
        ORDER BY dt;

Then you simply fetch from this cursor (like so):

    FETCH FORWARD 20 IN raw_data;

And you close it with a simple:

    CLOSE raw_data;

Jason


Joe Koenig <joe@jwebmedia.com> writes:

> Is there a way to structure a query so you can only run 1 query, get the
> full number of rows that would be returned, but then use LIMIT to step
> through in groups of 20? For example, a search in my CD's/Rock section
> will return 53,000 results. I want to give the user the number of total
> results, but also use LIMIT to go through 20 at a time? Does this
> require 2 queries? Thanks,
>
> Joe
>
> ---------------------------(end of broadcast)---------------------------
> TIP 2: you can get off all lists at once with the unregister command
>     (send "unregister YourEmailAddressHere" to majordomo@postgresql.org)

Re: Way to use count() and LIMIT?

From
"SHELTON,MICHAEL (Non-HP-Boise,ex1)"
Date:
You will also need to do a select first to get the total count.  You can
store it in a var then pass it back to the user for each 20 or whatever
amount (so each time they know total) or pass it once, then create cursor.

You can also use LIMIT with OFFSET to do a simple select each time for 20 at
a time.

-----Original Message-----
From: Jason Earl [mailto:jason.earl@simplot.com]
Sent: Tuesday, December 18, 2001 12:27 PM
To: joe@jwebmedia.com
Cc: pgsql-general@postgresql.org
Subject: Re: [GENERAL] Way to use count() and LIMIT?



Sure, just declare a cursor.  Here's a simple one that I use:

    DECLARE raw_data CURSOR FOR
    SELECT
    (SELECT pc FROM curpack1 WHERE curpack1.dt <= caseweights1.dt
     ORDER BY curpack1.dt DESC LIMIT 1) AS "pc",
    dt::date AS "date",
    dt::time AS "time",
    weight AS "weight"
        FROM caseweights1
        WHERE dt >= '%s' AND
        dt < '%s'
        ORDER BY dt;

Then you simply fetch from this cursor (like so):

    FETCH FORWARD 20 IN raw_data;

And you close it with a simple:

    CLOSE raw_data;

Jason


Joe Koenig <joe@jwebmedia.com> writes:

> Is there a way to structure a query so you can only run 1 query, get the
> full number of rows that would be returned, but then use LIMIT to step
> through in groups of 20? For example, a search in my CD's/Rock section
> will return 53,000 results. I want to give the user the number of total
> results, but also use LIMIT to go through 20 at a time? Does this
> require 2 queries? Thanks,
>
> Joe
>
> ---------------------------(end of broadcast)---------------------------
> TIP 2: you can get off all lists at once with the unregister command
>     (send "unregister YourEmailAddressHere" to majordomo@postgresql.org)

---------------------------(end of broadcast)---------------------------
TIP 6: Have you searched our list archives?

http://archives.postgresql.org

Re: Way to use count() and LIMIT?

From
Joe Koenig
Date:
I think I wasn't clear enough - I need total rows in the result set, not
in the table. Sorry if that wasn't clear. Thanks for the info,

Joe

"SHELTON,MICHAEL (Non-HP-Boise,ex1)" wrote:
>
> I'd be surprised if a cursor is the solution you want.  A cursor is good if
> you want to "scan" through the rows one at a time via SQL rather than a
> client software program (e.g. PHP with ADOdb or something).  If you wrote a
> function for your DB and needed to access data from a result of a query one
> row at a time (for calculating running totals or something -- bad example,
> but the point is the calculations need to be done _on_ the DB not the
> client) then a cursor is the way to go.
>
> Another thing about cursors is that they tie up the DB resources while they
> are open.  And since I imagine you are wanting to have the client indicate
> when they want to scroll through the next 20 (another app/web request) that
> couldn't be done "inside" the DB with a cursor.
>
> By the way, even if you did use the cursor you'd still need to query the
> table first for the total count before accessing 20 rows at a time, so
> that's a moot point.
>
> Also, I'd be surprised if requesting the total count of rows in a table were
> really a hit at all (especially with no WHERE clause -- in that case the
> count is probably stored somewhere anyway and won't need to be calculated
> dynamically, again, just a guess, but probably true).
>
> Hope that helps,
>
> Mike
>
> -----Original Message-----
> From: Joe Koenig [mailto:joe@jwebmedia.com]
> Sent: Tuesday, December 18, 2001 2:29 PM
> To: SHELTON,MICHAEL (Non-HP-Boise,ex1)
> Cc: 'pgsql-general@postgresql.org'
> Subject: Re: [GENERAL] Way to use count() and LIMIT?
>
> I was currently using a LIMIT and OFFSET to move through 20 at a time. I
> need to know the total for 2 reasons:
>
> 1) To display it to the user
> 2) So my script knows whether or not to put a next button.
>
> I was hoping I could avoid 2 queries. Is the best way to do this to just
> use LIMIT and OFFSET in one query and just do a count() in the first?
> Does using a cursor offer any benefit over the LIMIT and OFFSET method?
> Thanks,
>
> Joe
>
> "SHELTON,MICHAEL (Non-HP-Boise,ex1)" wrote:
> >
> > You will also need to do a select first to get the total count.  You can
> > store it in a var then pass it back to the user for each 20 or whatever
> > amount (so each time they know total) or pass it once, then create cursor.
> >
> > You can also use LIMIT with OFFSET to do a simple select each time for 20
> at
> > a time.
> >
> > -----Original Message-----
> > From: Jason Earl [mailto:jason.earl@simplot.com]
> > Sent: Tuesday, December 18, 2001 12:27 PM
> > To: joe@jwebmedia.com
> > Cc: pgsql-general@postgresql.org
> > Subject: Re: [GENERAL] Way to use count() and LIMIT?
> >
> > Sure, just declare a cursor.  Here's a simple one that I use:
> >
> >     DECLARE raw_data CURSOR FOR
> >     SELECT
> >         (SELECT pc FROM curpack1 WHERE curpack1.dt <= caseweights1.dt
> >          ORDER BY curpack1.dt DESC LIMIT 1) AS "pc",
> >         dt::date AS "date",
> >         dt::time AS "time",
> >         weight AS "weight"
> >         FROM caseweights1
> >         WHERE dt >= '%s' AND
> >         dt < '%s'
> >         ORDER BY dt;
> >
> > Then you simply fetch from this cursor (like so):
> >
> >     FETCH FORWARD 20 IN raw_data;
> >
> > And you close it with a simple:
> >
> >     CLOSE raw_data;
> >
> > Jason
> >
> > Joe Koenig <joe@jwebmedia.com> writes:
> >
> > > Is there a way to structure a query so you can only run 1 query, get the
> > > full number of rows that would be returned, but then use LIMIT to step
> > > through in groups of 20? For example, a search in my CD's/Rock section
> > > will return 53,000 results. I want to give the user the number of total
> > > results, but also use LIMIT to go through 20 at a time? Does this
> > > require 2 queries? Thanks,
> > >
> > > Joe
> > >
> > > ---------------------------(end of broadcast)---------------------------
> > > TIP 2: you can get off all lists at once with the unregister command
> > >     (send "unregister YourEmailAddressHere" to majordomo@postgresql.org)
> >
> > ---------------------------(end of broadcast)---------------------------
> > TIP 6: Have you searched our list archives?
> >
> > http://archives.postgresql.org
> >
> > ---------------------------(end of broadcast)---------------------------
> > TIP 3: if posting/reading through Usenet, please send an appropriate
> > subscribe-nomail command to majordomo@postgresql.org so that your
> > message can get through to the mailing list cleanly

Re: Way to use count() and LIMIT?

From
"SHELTON,MICHAEL (Non-HP-Boise,ex1)"
Date:
I'd be surprised if a cursor is the solution you want.  A cursor is good if
you want to "scan" through the rows one at a time via SQL rather than a
client software program (e.g. PHP with ADOdb or something).  If you wrote a
function for your DB and needed to access data from a result of a query one
row at a time (for calculating running totals or something -- bad example,
but the point is the calculations need to be done _on_ the DB not the
client) then a cursor is the way to go.

Another thing about cursors is that they tie up the DB resources while they
are open.  And since I imagine you are wanting to have the client indicate
when they want to scroll through the next 20 (another app/web request) that
couldn't be done "inside" the DB with a cursor.

By the way, even if you did use the cursor you'd still need to query the
table first for the total count before accessing 20 rows at a time, so
that's a moot point.

Also, I'd be surprised if requesting the total count of rows in a table were
really a hit at all (especially with no WHERE clause -- in that case the
count is probably stored somewhere anyway and won't need to be calculated
dynamically, again, just a guess, but probably true).

Hope that helps,

Mike

-----Original Message-----
From: Joe Koenig [mailto:joe@jwebmedia.com]
Sent: Tuesday, December 18, 2001 2:29 PM
To: SHELTON,MICHAEL (Non-HP-Boise,ex1)
Cc: 'pgsql-general@postgresql.org'
Subject: Re: [GENERAL] Way to use count() and LIMIT?


I was currently using a LIMIT and OFFSET to move through 20 at a time. I
need to know the total for 2 reasons:

1) To display it to the user
2) So my script knows whether or not to put a next button.

I was hoping I could avoid 2 queries. Is the best way to do this to just
use LIMIT and OFFSET in one query and just do a count() in the first?
Does using a cursor offer any benefit over the LIMIT and OFFSET method?
Thanks,

Joe

"SHELTON,MICHAEL (Non-HP-Boise,ex1)" wrote:
>
> You will also need to do a select first to get the total count.  You can
> store it in a var then pass it back to the user for each 20 or whatever
> amount (so each time they know total) or pass it once, then create cursor.
>
> You can also use LIMIT with OFFSET to do a simple select each time for 20
at
> a time.
>
> -----Original Message-----
> From: Jason Earl [mailto:jason.earl@simplot.com]
> Sent: Tuesday, December 18, 2001 12:27 PM
> To: joe@jwebmedia.com
> Cc: pgsql-general@postgresql.org
> Subject: Re: [GENERAL] Way to use count() and LIMIT?
>
> Sure, just declare a cursor.  Here's a simple one that I use:
>
>     DECLARE raw_data CURSOR FOR
>     SELECT
>         (SELECT pc FROM curpack1 WHERE curpack1.dt <= caseweights1.dt
>          ORDER BY curpack1.dt DESC LIMIT 1) AS "pc",
>         dt::date AS "date",
>         dt::time AS "time",
>         weight AS "weight"
>         FROM caseweights1
>         WHERE dt >= '%s' AND
>         dt < '%s'
>         ORDER BY dt;
>
> Then you simply fetch from this cursor (like so):
>
>     FETCH FORWARD 20 IN raw_data;
>
> And you close it with a simple:
>
>     CLOSE raw_data;
>
> Jason
>
> Joe Koenig <joe@jwebmedia.com> writes:
>
> > Is there a way to structure a query so you can only run 1 query, get the
> > full number of rows that would be returned, but then use LIMIT to step
> > through in groups of 20? For example, a search in my CD's/Rock section
> > will return 53,000 results. I want to give the user the number of total
> > results, but also use LIMIT to go through 20 at a time? Does this
> > require 2 queries? Thanks,
> >
> > Joe
> >
> > ---------------------------(end of broadcast)---------------------------
> > TIP 2: you can get off all lists at once with the unregister command
> >     (send "unregister YourEmailAddressHere" to majordomo@postgresql.org)
>
> ---------------------------(end of broadcast)---------------------------
> TIP 6: Have you searched our list archives?
>
> http://archives.postgresql.org
>
> ---------------------------(end of broadcast)---------------------------
> TIP 3: if posting/reading through Usenet, please send an appropriate
> subscribe-nomail command to majordomo@postgresql.org so that your
> message can get through to the mailing list cleanly

Re: Way to use count() and LIMIT?

From
Joe Koenig
Date:
I was currently using a LIMIT and OFFSET to move through 20 at a time. I
need to know the total for 2 reasons:

1) To display it to the user
2) So my script knows whether or not to put a next button.

I was hoping I could avoid 2 queries. Is the best way to do this to just
use LIMIT and OFFSET in one query and just do a count() in the first?
Does using a cursor offer any benefit over the LIMIT and OFFSET method? Thanks,

Joe

"SHELTON,MICHAEL (Non-HP-Boise,ex1)" wrote:
>
> You will also need to do a select first to get the total count.  You can
> store it in a var then pass it back to the user for each 20 or whatever
> amount (so each time they know total) or pass it once, then create cursor.
>
> You can also use LIMIT with OFFSET to do a simple select each time for 20 at
> a time.
>
> -----Original Message-----
> From: Jason Earl [mailto:jason.earl@simplot.com]
> Sent: Tuesday, December 18, 2001 12:27 PM
> To: joe@jwebmedia.com
> Cc: pgsql-general@postgresql.org
> Subject: Re: [GENERAL] Way to use count() and LIMIT?
>
> Sure, just declare a cursor.  Here's a simple one that I use:
>
>     DECLARE raw_data CURSOR FOR
>     SELECT
>         (SELECT pc FROM curpack1 WHERE curpack1.dt <= caseweights1.dt
>          ORDER BY curpack1.dt DESC LIMIT 1) AS "pc",
>         dt::date AS "date",
>         dt::time AS "time",
>         weight AS "weight"
>         FROM caseweights1
>         WHERE dt >= '%s' AND
>         dt < '%s'
>         ORDER BY dt;
>
> Then you simply fetch from this cursor (like so):
>
>     FETCH FORWARD 20 IN raw_data;
>
> And you close it with a simple:
>
>     CLOSE raw_data;
>
> Jason
>
> Joe Koenig <joe@jwebmedia.com> writes:
>
> > Is there a way to structure a query so you can only run 1 query, get the
> > full number of rows that would be returned, but then use LIMIT to step
> > through in groups of 20? For example, a search in my CD's/Rock section
> > will return 53,000 results. I want to give the user the number of total
> > results, but also use LIMIT to go through 20 at a time? Does this
> > require 2 queries? Thanks,
> >
> > Joe
> >
> > ---------------------------(end of broadcast)---------------------------
> > TIP 2: you can get off all lists at once with the unregister command
> >     (send "unregister YourEmailAddressHere" to majordomo@postgresql.org)
>
> ---------------------------(end of broadcast)---------------------------
> TIP 6: Have you searched our list archives?
>
> http://archives.postgresql.org
>
> ---------------------------(end of broadcast)---------------------------
> TIP 3: if posting/reading through Usenet, please send an appropriate
> subscribe-nomail command to majordomo@postgresql.org so that your
> message can get through to the mailing list cleanly

Re: Way to use count() and LIMIT?

From
"SHELTON,MICHAEL (Non-HP-Boise,ex1)"
Date:
Ahh...well, that is different.  What type of client are you using to connect
to the DB and get the info?

Mike

-----Original Message-----
From: Joe Koenig [mailto:joe@jwebmedia.com]
Sent: Tuesday, December 18, 2001 2:39 PM
To: SHELTON,MICHAEL (Non-HP-Boise,ex1)
Cc: 'pgsql-general@postgresql.org'
Subject: Re: [GENERAL] Way to use count() and LIMIT?


I think I wasn't clear enough - I need total rows in the result set, not
in the table. Sorry if that wasn't clear. Thanks for the info,

Joe

"SHELTON,MICHAEL (Non-HP-Boise,ex1)" wrote:
>
> I'd be surprised if a cursor is the solution you want.  A cursor is good
if
> you want to "scan" through the rows one at a time via SQL rather than a
> client software program (e.g. PHP with ADOdb or something).  If you wrote
a
> function for your DB and needed to access data from a result of a query
one
> row at a time (for calculating running totals or something -- bad example,
> but the point is the calculations need to be done _on_ the DB not the
> client) then a cursor is the way to go.
>
> Another thing about cursors is that they tie up the DB resources while
they
> are open.  And since I imagine you are wanting to have the client indicate
> when they want to scroll through the next 20 (another app/web request)
that
> couldn't be done "inside" the DB with a cursor.
>
> By the way, even if you did use the cursor you'd still need to query the
> table first for the total count before accessing 20 rows at a time, so
> that's a moot point.
>
> Also, I'd be surprised if requesting the total count of rows in a table
were
> really a hit at all (especially with no WHERE clause -- in that case the
> count is probably stored somewhere anyway and won't need to be calculated
> dynamically, again, just a guess, but probably true).
>
> Hope that helps,
>
> Mike
>
> -----Original Message-----
> From: Joe Koenig [mailto:joe@jwebmedia.com]
> Sent: Tuesday, December 18, 2001 2:29 PM
> To: SHELTON,MICHAEL (Non-HP-Boise,ex1)
> Cc: 'pgsql-general@postgresql.org'
> Subject: Re: [GENERAL] Way to use count() and LIMIT?
>
> I was currently using a LIMIT and OFFSET to move through 20 at a time. I
> need to know the total for 2 reasons:
>
> 1) To display it to the user
> 2) So my script knows whether or not to put a next button.
>
> I was hoping I could avoid 2 queries. Is the best way to do this to just
> use LIMIT and OFFSET in one query and just do a count() in the first?
> Does using a cursor offer any benefit over the LIMIT and OFFSET method?
> Thanks,
>
> Joe
>
> "SHELTON,MICHAEL (Non-HP-Boise,ex1)" wrote:
> >
> > You will also need to do a select first to get the total count.  You can
> > store it in a var then pass it back to the user for each 20 or whatever
> > amount (so each time they know total) or pass it once, then create
cursor.
> >
> > You can also use LIMIT with OFFSET to do a simple select each time for
20
> at
> > a time.
> >
> > -----Original Message-----
> > From: Jason Earl [mailto:jason.earl@simplot.com]
> > Sent: Tuesday, December 18, 2001 12:27 PM
> > To: joe@jwebmedia.com
> > Cc: pgsql-general@postgresql.org
> > Subject: Re: [GENERAL] Way to use count() and LIMIT?
> >
> > Sure, just declare a cursor.  Here's a simple one that I use:
> >
> >     DECLARE raw_data CURSOR FOR
> >     SELECT
> >         (SELECT pc FROM curpack1 WHERE curpack1.dt <= caseweights1.dt
> >          ORDER BY curpack1.dt DESC LIMIT 1) AS "pc",
> >         dt::date AS "date",
> >         dt::time AS "time",
> >         weight AS "weight"
> >         FROM caseweights1
> >         WHERE dt >= '%s' AND
> >         dt < '%s'
> >         ORDER BY dt;
> >
> > Then you simply fetch from this cursor (like so):
> >
> >     FETCH FORWARD 20 IN raw_data;
> >
> > And you close it with a simple:
> >
> >     CLOSE raw_data;
> >
> > Jason
> >
> > Joe Koenig <joe@jwebmedia.com> writes:
> >
> > > Is there a way to structure a query so you can only run 1 query, get
the
> > > full number of rows that would be returned, but then use LIMIT to step
> > > through in groups of 20? For example, a search in my CD's/Rock section
> > > will return 53,000 results. I want to give the user the number of
total
> > > results, but also use LIMIT to go through 20 at a time? Does this
> > > require 2 queries? Thanks,
> > >
> > > Joe
> > >
> > > ---------------------------(end of
broadcast)---------------------------
> > > TIP 2: you can get off all lists at once with the unregister command
> > >     (send "unregister YourEmailAddressHere" to
majordomo@postgresql.org)
> >
> > ---------------------------(end of broadcast)---------------------------
> > TIP 6: Have you searched our list archives?
> >
> > http://archives.postgresql.org
> >
> > ---------------------------(end of broadcast)---------------------------
> > TIP 3: if posting/reading through Usenet, please send an appropriate
> > subscribe-nomail command to majordomo@postgresql.org so that your
> > message can get through to the mailing list cleanly

Re: Way to use count() and LIMIT?

From
Darren Ferguson
Date:
I see two options but they may not be the correct ones but here goes.

1) Either put a subselect in your select clause that gets the number of
rows

2) Create a plpgsql function that returns the number of rows and put it in
your  select clause.

Both add a little more load to your query but they will give you the
answers. Use explain to figure out which one is less intensive

My 2 cents

Darren

Darren Ferguson
Software Engineer
Openband

On Tue, 18 Dec 2001, Joe Koenig wrote:

> I was currently using a LIMIT and OFFSET to move through 20 at a time. I
> need to know the total for 2 reasons:
>
> 1) To display it to the user
> 2) So my script knows whether or not to put a next button.
>
> I was hoping I could avoid 2 queries. Is the best way to do this to just
> use LIMIT and OFFSET in one query and just do a count() in the first?
> Does using a cursor offer any benefit over the LIMIT and OFFSET method? Thanks,
>
> Joe
>
> "SHELTON,MICHAEL (Non-HP-Boise,ex1)" wrote:
> >
> > You will also need to do a select first to get the total count.  You can
> > store it in a var then pass it back to the user for each 20 or whatever
> > amount (so each time they know total) or pass it once, then create cursor.
> >
> > You can also use LIMIT with OFFSET to do a simple select each time for 20 at
> > a time.
> >
> > -----Original Message-----
> > From: Jason Earl [mailto:jason.earl@simplot.com]
> > Sent: Tuesday, December 18, 2001 12:27 PM
> > To: joe@jwebmedia.com
> > Cc: pgsql-general@postgresql.org
> > Subject: Re: [GENERAL] Way to use count() and LIMIT?
> >
> > Sure, just declare a cursor.  Here's a simple one that I use:
> >
> >     DECLARE raw_data CURSOR FOR
> >     SELECT
> >         (SELECT pc FROM curpack1 WHERE curpack1.dt <= caseweights1.dt
> >          ORDER BY curpack1.dt DESC LIMIT 1) AS "pc",
> >         dt::date AS "date",
> >         dt::time AS "time",
> >         weight AS "weight"
> >         FROM caseweights1
> >         WHERE dt >= '%s' AND
> >         dt < '%s'
> >         ORDER BY dt;
> >
> > Then you simply fetch from this cursor (like so):
> >
> >     FETCH FORWARD 20 IN raw_data;
> >
> > And you close it with a simple:
> >
> >     CLOSE raw_data;
> >
> > Jason
> >
> > Joe Koenig <joe@jwebmedia.com> writes:
> >
> > > Is there a way to structure a query so you can only run 1 query, get the
> > > full number of rows that would be returned, but then use LIMIT to step
> > > through in groups of 20? For example, a search in my CD's/Rock section
> > > will return 53,000 results. I want to give the user the number of total
> > > results, but also use LIMIT to go through 20 at a time? Does this
> > > require 2 queries? Thanks,
> > >
> > > Joe
> > >
> > > ---------------------------(end of broadcast)---------------------------
> > > TIP 2: you can get off all lists at once with the unregister command
> > >     (send "unregister YourEmailAddressHere" to majordomo@postgresql.org)
> >
> > ---------------------------(end of broadcast)---------------------------
> > TIP 6: Have you searched our list archives?
> >
> > http://archives.postgresql.org
> >
> > ---------------------------(end of broadcast)---------------------------
> > TIP 3: if posting/reading through Usenet, please send an appropriate
> > subscribe-nomail command to majordomo@postgresql.org so that your
> > message can get through to the mailing list cleanly
>
> ---------------------------(end of broadcast)---------------------------
> TIP 3: if posting/reading through Usenet, please send an appropriate
> subscribe-nomail command to majordomo@postgresql.org so that your
> message can get through to the mailing list cleanly
>

Re: Way to use count() and LIMIT?

From
Joe Koenig
Date:
PHP 4.1.0 is doing the db queries. Thanks,

Joe

"SHELTON,MICHAEL (Non-HP-Boise,ex1)" wrote:
>
> Ahh...well, that is different.  What type of client are you using to connect
> to the DB and get the info?
>
> Mike
>
> -----Original Message-----
> From: Joe Koenig [mailto:joe@jwebmedia.com]
> Sent: Tuesday, December 18, 2001 2:39 PM
> To: SHELTON,MICHAEL (Non-HP-Boise,ex1)
> Cc: 'pgsql-general@postgresql.org'
> Subject: Re: [GENERAL] Way to use count() and LIMIT?
>
> I think I wasn't clear enough - I need total rows in the result set, not
> in the table. Sorry if that wasn't clear. Thanks for the info,
>
> Joe
>
> "SHELTON,MICHAEL (Non-HP-Boise,ex1)" wrote:
> >
> > I'd be surprised if a cursor is the solution you want.  A cursor is good
> if
> > you want to "scan" through the rows one at a time via SQL rather than a
> > client software program (e.g. PHP with ADOdb or something).  If you wrote
> a
> > function for your DB and needed to access data from a result of a query
> one
> > row at a time (for calculating running totals or something -- bad example,
> > but the point is the calculations need to be done _on_ the DB not the
> > client) then a cursor is the way to go.
> >
> > Another thing about cursors is that they tie up the DB resources while
> they
> > are open.  And since I imagine you are wanting to have the client indicate
> > when they want to scroll through the next 20 (another app/web request)
> that
> > couldn't be done "inside" the DB with a cursor.
> >
> > By the way, even if you did use the cursor you'd still need to query the
> > table first for the total count before accessing 20 rows at a time, so
> > that's a moot point.
> >
> > Also, I'd be surprised if requesting the total count of rows in a table
> were
> > really a hit at all (especially with no WHERE clause -- in that case the
> > count is probably stored somewhere anyway and won't need to be calculated
> > dynamically, again, just a guess, but probably true).
> >
> > Hope that helps,
> >
> > Mike
> >
> > -----Original Message-----
> > From: Joe Koenig [mailto:joe@jwebmedia.com]
> > Sent: Tuesday, December 18, 2001 2:29 PM
> > To: SHELTON,MICHAEL (Non-HP-Boise,ex1)
> > Cc: 'pgsql-general@postgresql.org'
> > Subject: Re: [GENERAL] Way to use count() and LIMIT?
> >
> > I was currently using a LIMIT and OFFSET to move through 20 at a time. I
> > need to know the total for 2 reasons:
> >
> > 1) To display it to the user
> > 2) So my script knows whether or not to put a next button.
> >
> > I was hoping I could avoid 2 queries. Is the best way to do this to just
> > use LIMIT and OFFSET in one query and just do a count() in the first?
> > Does using a cursor offer any benefit over the LIMIT and OFFSET method?
> > Thanks,
> >
> > Joe
> >
> > "SHELTON,MICHAEL (Non-HP-Boise,ex1)" wrote:
> > >
> > > You will also need to do a select first to get the total count.  You can
> > > store it in a var then pass it back to the user for each 20 or whatever
> > > amount (so each time they know total) or pass it once, then create
> cursor.
> > >
> > > You can also use LIMIT with OFFSET to do a simple select each time for
> 20
> > at
> > > a time.
> > >
> > > -----Original Message-----
> > > From: Jason Earl [mailto:jason.earl@simplot.com]
> > > Sent: Tuesday, December 18, 2001 12:27 PM
> > > To: joe@jwebmedia.com
> > > Cc: pgsql-general@postgresql.org
> > > Subject: Re: [GENERAL] Way to use count() and LIMIT?
> > >
> > > Sure, just declare a cursor.  Here's a simple one that I use:
> > >
> > >     DECLARE raw_data CURSOR FOR
> > >     SELECT
> > >         (SELECT pc FROM curpack1 WHERE curpack1.dt <= caseweights1.dt
> > >          ORDER BY curpack1.dt DESC LIMIT 1) AS "pc",
> > >         dt::date AS "date",
> > >         dt::time AS "time",
> > >         weight AS "weight"
> > >         FROM caseweights1
> > >         WHERE dt >= '%s' AND
> > >         dt < '%s'
> > >         ORDER BY dt;
> > >
> > > Then you simply fetch from this cursor (like so):
> > >
> > >     FETCH FORWARD 20 IN raw_data;
> > >
> > > And you close it with a simple:
> > >
> > >     CLOSE raw_data;
> > >
> > > Jason
> > >
> > > Joe Koenig <joe@jwebmedia.com> writes:
> > >
> > > > Is there a way to structure a query so you can only run 1 query, get
> the
> > > > full number of rows that would be returned, but then use LIMIT to step
> > > > through in groups of 20? For example, a search in my CD's/Rock section
> > > > will return 53,000 results. I want to give the user the number of
> total
> > > > results, but also use LIMIT to go through 20 at a time? Does this
> > > > require 2 queries? Thanks,
> > > >
> > > > Joe
> > > >
> > > > ---------------------------(end of
> broadcast)---------------------------
> > > > TIP 2: you can get off all lists at once with the unregister command
> > > >     (send "unregister YourEmailAddressHere" to
> majordomo@postgresql.org)
> > >
> > > ---------------------------(end of broadcast)---------------------------
> > > TIP 6: Have you searched our list archives?
> > >
> > > http://archives.postgresql.org
> > >
> > > ---------------------------(end of broadcast)---------------------------
> > > TIP 3: if posting/reading through Usenet, please send an appropriate
> > > subscribe-nomail command to majordomo@postgresql.org so that your
> > > message can get through to the mailing list cleanly
>
> ---------------------------(end of broadcast)---------------------------
> TIP 5: Have you checked our extensive FAQ?
>
> http://www.postgresql.org/users-lounge/docs/faq.html

Re: Way to use count() and LIMIT?

From
"SHELTON,MICHAEL (Non-HP-Boise,ex1)"
Date:
Sorry I haven't been able to get back to you -- busy at work.

One last comment to try is in MS SQL you can get the number of rows returned
via @@ROW_COUNT -- however I couldn't find the equivalent for PG SQL
(doesn't mean there isn't one).

Anyone else know what might work here?  The important detail is wanting to
get a number of rows in the _result_ not necessarily the table.

Mike

-----Original Message-----
From: Joe Koenig [mailto:joe@jwebmedia.com]
Sent: Tuesday, December 18, 2001 3:54 PM
To: SHELTON,MICHAEL (Non-HP-Boise,ex1)
Cc: 'pgsql-general@postgresql.org'
Subject: Re: [GENERAL] Way to use count() and LIMIT?


PHP 4.1.0 is doing the db queries. Thanks,

Joe

"SHELTON,MICHAEL (Non-HP-Boise,ex1)" wrote:
>
> Ahh...well, that is different.  What type of client are you using to
connect
> to the DB and get the info?
>
> Mike
>
> -----Original Message-----
> From: Joe Koenig [mailto:joe@jwebmedia.com]
> Sent: Tuesday, December 18, 2001 2:39 PM
> To: SHELTON,MICHAEL (Non-HP-Boise,ex1)
> Cc: 'pgsql-general@postgresql.org'
> Subject: Re: [GENERAL] Way to use count() and LIMIT?
>
> I think I wasn't clear enough - I need total rows in the result set, not
> in the table. Sorry if that wasn't clear. Thanks for the info,
>
> Joe
>
> "SHELTON,MICHAEL (Non-HP-Boise,ex1)" wrote:
> >
> > I'd be surprised if a cursor is the solution you want.  A cursor is good
> if
> > you want to "scan" through the rows one at a time via SQL rather than a
> > client software program (e.g. PHP with ADOdb or something).  If you
wrote
> a
> > function for your DB and needed to access data from a result of a query
> one
> > row at a time (for calculating running totals or something -- bad
example,
> > but the point is the calculations need to be done _on_ the DB not the
> > client) then a cursor is the way to go.
> >
> > Another thing about cursors is that they tie up the DB resources while
> they
> > are open.  And since I imagine you are wanting to have the client
indicate
> > when they want to scroll through the next 20 (another app/web request)
> that
> > couldn't be done "inside" the DB with a cursor.
> >
> > By the way, even if you did use the cursor you'd still need to query the
> > table first for the total count before accessing 20 rows at a time, so
> > that's a moot point.
> >
> > Also, I'd be surprised if requesting the total count of rows in a table
> were
> > really a hit at all (especially with no WHERE clause -- in that case the
> > count is probably stored somewhere anyway and won't need to be
calculated
> > dynamically, again, just a guess, but probably true).
> >
> > Hope that helps,
> >
> > Mike
> >
> > -----Original Message-----
> > From: Joe Koenig [mailto:joe@jwebmedia.com]
> > Sent: Tuesday, December 18, 2001 2:29 PM
> > To: SHELTON,MICHAEL (Non-HP-Boise,ex1)
> > Cc: 'pgsql-general@postgresql.org'
> > Subject: Re: [GENERAL] Way to use count() and LIMIT?
> >
> > I was currently using a LIMIT and OFFSET to move through 20 at a time. I
> > need to know the total for 2 reasons:
> >
> > 1) To display it to the user
> > 2) So my script knows whether or not to put a next button.
> >
> > I was hoping I could avoid 2 queries. Is the best way to do this to just
> > use LIMIT and OFFSET in one query and just do a count() in the first?
> > Does using a cursor offer any benefit over the LIMIT and OFFSET method?
> > Thanks,
> >
> > Joe
> >
> > "SHELTON,MICHAEL (Non-HP-Boise,ex1)" wrote:
> > >
> > > You will also need to do a select first to get the total count.  You
can
> > > store it in a var then pass it back to the user for each 20 or
whatever
> > > amount (so each time they know total) or pass it once, then create
> cursor.
> > >
> > > You can also use LIMIT with OFFSET to do a simple select each time for
> 20
> > at
> > > a time.
> > >
> > > -----Original Message-----
> > > From: Jason Earl [mailto:jason.earl@simplot.com]
> > > Sent: Tuesday, December 18, 2001 12:27 PM
> > > To: joe@jwebmedia.com
> > > Cc: pgsql-general@postgresql.org
> > > Subject: Re: [GENERAL] Way to use count() and LIMIT?
> > >
> > > Sure, just declare a cursor.  Here's a simple one that I use:
> > >
> > >     DECLARE raw_data CURSOR FOR
> > >     SELECT
> > >         (SELECT pc FROM curpack1 WHERE curpack1.dt <= caseweights1.dt
> > >          ORDER BY curpack1.dt DESC LIMIT 1) AS "pc",
> > >         dt::date AS "date",
> > >         dt::time AS "time",
> > >         weight AS "weight"
> > >         FROM caseweights1
> > >         WHERE dt >= '%s' AND
> > >         dt < '%s'
> > >         ORDER BY dt;
> > >
> > > Then you simply fetch from this cursor (like so):
> > >
> > >     FETCH FORWARD 20 IN raw_data;
> > >
> > > And you close it with a simple:
> > >
> > >     CLOSE raw_data;
> > >
> > > Jason
> > >
> > > Joe Koenig <joe@jwebmedia.com> writes:
> > >
> > > > Is there a way to structure a query so you can only run 1 query, get
> the
> > > > full number of rows that would be returned, but then use LIMIT to
step
> > > > through in groups of 20? For example, a search in my CD's/Rock
section
> > > > will return 53,000 results. I want to give the user the number of
> total
> > > > results, but also use LIMIT to go through 20 at a time? Does this
> > > > require 2 queries? Thanks,
> > > >
> > > > Joe
> > > >
> > > > ---------------------------(end of
> broadcast)---------------------------
> > > > TIP 2: you can get off all lists at once with the unregister command
> > > >     (send "unregister YourEmailAddressHere" to
> majordomo@postgresql.org)
> > >
> > > ---------------------------(end of
broadcast)---------------------------
> > > TIP 6: Have you searched our list archives?
> > >
> > > http://archives.postgresql.org
> > >
> > > ---------------------------(end of
broadcast)---------------------------
> > > TIP 3: if posting/reading through Usenet, please send an appropriate
> > > subscribe-nomail command to majordomo@postgresql.org so that your
> > > message can get through to the mailing list cleanly
>
> ---------------------------(end of broadcast)---------------------------
> TIP 5: Have you checked our extensive FAQ?
>
> http://www.postgresql.org/users-lounge/docs/faq.html

Re: Way to use count() and LIMIT?

From
Doug McNaught
Date:
"SHELTON,MICHAEL (Non-HP-Boise,ex1)" <michael_shelton@non.hp.com> writes:

> Sorry I haven't been able to get back to you -- busy at work.
>
> One last comment to try is in MS SQL you can get the number of rows returned
> via @@ROW_COUNT -- however I couldn't find the equivalent for PG SQL
> (doesn't mean there isn't one).
>
> Anyone else know what might work here?  The important detail is wanting to
> get a number of rows in the _result_ not necessarily the table.

libpq gives you this information via the PQntuples() function.
Whether PHP gives you access to that info I don't know--see your docs.

-Doug
--
Let us cross over the river, and rest under the shade of the trees.
   --T. J. Jackson, 1863

Re: Way to use count() and LIMIT?

From
"Mitch Vincent"
Date:
see http://www.php.net/manual/en/function.pg-numrows.php

Still, this isn't what you want I'd say.. That just gives you the number of
rows returned by a query -- a query that uses LIMIT X return X or less
records, so that's not the most that pg_numrows() will ever give you is X...

I've run into this before when I did some search engine work with PG, it's a
pain but I've found no workaround... I ended up having to do two queries,
one a count() and one to return the records...

-Mitch


----- Original Message -----
From: "Doug McNaught" <doug@wireboard.com>
To: "SHELTON,MICHAEL (Non-HP-Boise,ex1)" <michael_shelton@non.hp.com>
Cc: <joe@jwebmedia.com>; <pgsql-general@postgresql.org>
Sent: Tuesday, December 18, 2001 4:49 PM
Subject: Re: [GENERAL] Way to use count() and LIMIT?


> "SHELTON,MICHAEL (Non-HP-Boise,ex1)" <michael_shelton@non.hp.com> writes:
>
> > Sorry I haven't been able to get back to you -- busy at work.
> >
> > One last comment to try is in MS SQL you can get the number of rows
returned
> > via @@ROW_COUNT -- however I couldn't find the equivalent for PG SQL
> > (doesn't mean there isn't one).
> >
> > Anyone else know what might work here?  The important detail is wanting
to
> > get a number of rows in the _result_ not necessarily the table.
>
> libpq gives you this information via the PQntuples() function.
> Whether PHP gives you access to that info I don't know--see your docs.
>
> -Doug
> --
> Let us cross over the river, and rest under the shade of the trees.
>    --T. J. Jackson, 1863
>
> ---------------------------(end of broadcast)---------------------------
> TIP 4: Don't 'kill -9' the postmaster
>

Re: Way to use count() and LIMIT?

From
Martijn van Oosterhout
Date:
On Tue, Dec 18, 2001 at 05:24:39PM -0700, Mitch Vincent wrote:
> see http://www.php.net/manual/en/function.pg-numrows.php
>
> Still, this isn't what you want I'd say.. That just gives you the number of
> rows returned by a query -- a query that uses LIMIT X return X or less
> records, so that's not the most that pg_numrows() will ever give you is X...
>
> I've run into this before when I did some search engine work with PG, it's a
> pain but I've found no workaround... I ended up having to do two queries,
> one a count() and one to return the records...

Well, there are conflicting requirements. By using LIMIT the database can
choose a plan better suited to giving a few results quickly but would take
forever if it had to do the whole table.

By saying that you want the total number of rows but only want a few back
you're basically asking the database to execute the entire query and then
toss out most of the result.

I gather the reason you don't actually run the whole query is because the
resultset would be too large? But you still want the database to work out
exactly how many there are.

Maybe just go for an estimate of the total? How big do you expect your
results to be?
--
Martijn van Oosterhout <kleptog@svana.org>
http://svana.org/kleptog/
> Terrorists can only take my life. Only my government can take my freedom.

Re: Way to use count() and LIMIT?

From
"Mitch Vincent"
Date:
> I gather the reason you don't actually run the whole query is because the
> resultset would be too large? But you still want the database to work out
> exactly how many there are.

Not really because it's too large but because there isn't a need.. Imagine
your favorite search engine, you search and the results are displayed "X
Matches, displaying matches 1 to 10"... Same thing here -- at least that's
what my need was... The query executed could get really stout (there were
over 60 searchable fields across a variety of tables with hundreds of
thousands of records in some) so executing the count() query, then the other
certainly added the overhead... A cursor wasn't usable in this situation
because the user could chose to search once, having only ten results
displayed of 10,000 and leave the page (thus leaving me with an open
cursor).....

But again, I see no solution to the above problem and understandably so, if
you LIMIT a result set, you *limit* it -- asking it to contradict itself
doesn't make any sense so I never complained that it wasn't possible :-)

-Mitch

Re: Way to use count() and LIMIT?

From
Martijn van Oosterhout
Date:
On Tue, Dec 18, 2001 at 09:19:46PM -0700, Mitch Vincent wrote:
> > I gather the reason you don't actually run the whole query is because the
> > resultset would be too large? But you still want the database to work out
> > exactly how many there are.
>
> Not really because it's too large but because there isn't a need.. Imagine
> your favorite search engine, you search and the results are displayed "X
> Matches, displaying matches 1 to 10"... Same thing here -- at least that's
> what my need was... The query executed could get really stout (there were
> over 60 searchable fields across a variety of tables with hundreds of
> thousands of records in some) so executing the count() query, then the other
> certainly added the overhead... A cursor wasn't usable in this situation
> because the user could chose to search once, having only ten results
> displayed of 10,000 and leave the page (thus leaving me with an open
> cursor).....

I'd be tempted to simply limit to 100 or so and if you get over 100, say
"matched over 100 documents". As for the cursor, I don't think google
remembers your results while switching between pages. If you switch back and
forth between pages I'm pretty sure the results change from time to time.

If you think about the number is servers they have, caching query results
would be almost as complicated as the searching itself.
--
Martijn van Oosterhout <kleptog@svana.org>
http://svana.org/kleptog/
> Terrorists can only take my life. Only my government can take my freedom.

Re: Way to use count() and LIMIT?

From
Darren Ferguson
Date:
This may be a long shot but you could try a view that has one field and
that field would be the count(*) of the table i don't think this would add
to much overhead.

Just a thought

Darren

Darren Ferguson
Software Engineer
Openband

On Wed, 19 Dec 2001, Martijn van Oosterhout wrote:

> On Tue, Dec 18, 2001 at 09:19:46PM -0700, Mitch Vincent wrote:
> > > I gather the reason you don't actually run the whole query is because the
> > > resultset would be too large? But you still want the database to work out
> > > exactly how many there are.
> >
> > Not really because it's too large but because there isn't a need.. Imagine
> > your favorite search engine, you search and the results are displayed "X
> > Matches, displaying matches 1 to 10"... Same thing here -- at least that's
> > what my need was... The query executed could get really stout (there were
> > over 60 searchable fields across a variety of tables with hundreds of
> > thousands of records in some) so executing the count() query, then the other
> > certainly added the overhead... A cursor wasn't usable in this situation
> > because the user could chose to search once, having only ten results
> > displayed of 10,000 and leave the page (thus leaving me with an open
> > cursor).....
>
> I'd be tempted to simply limit to 100 or so and if you get over 100, say
> "matched over 100 documents". As for the cursor, I don't think google
> remembers your results while switching between pages. If you switch back and
> forth between pages I'm pretty sure the results change from time to time.
>
> If you think about the number is servers they have, caching query results
> would be almost as complicated as the searching itself.
> --
> Martijn van Oosterhout <kleptog@svana.org>
> http://svana.org/kleptog/
> > Terrorists can only take my life. Only my government can take my freedom.
>
> ---------------------------(end of broadcast)---------------------------
> TIP 1: subscribe and unsubscribe commands go to majordomo@postgresql.org
>

Re: Way to use count() and LIMIT?

From
Joe Koenig
Date:
This is exactly right. The table I'm searching has 220,000 records right
now, and growing. The "Rock" category within "CD's" will return over
53,000 results. The count isn't so much for display, as for knowing
whether or not to put a "next" button on the page. I don't want to
always have next buttons, but not always have more results. There is
currently the option to browse through results based on the first letter
of the result, which I guess I could make as the 3rd step and not run
the query that currently returns 53,000 results until they narrow it
down more. I wasn't really expecting this to be possible, but PG does so
many other things that I didn't think was possible, so I thought I'd ask :)

Joe

Mitch Vincent wrote:
>
> > I gather the reason you don't actually run the whole query is because the
> > resultset would be too large? But you still want the database to work out
> > exactly how many there are.
>
> Not really because it's too large but because there isn't a need.. Imagine
> your favorite search engine, you search and the results are displayed "X
> Matches, displaying matches 1 to 10"... Same thing here -- at least that's
> what my need was... The query executed could get really stout (there were
> over 60 searchable fields across a variety of tables with hundreds of
> thousands of records in some) so executing the count() query, then the other
> certainly added the overhead... A cursor wasn't usable in this situation
> because the user could chose to search once, having only ten results
> displayed of 10,000 and leave the page (thus leaving me with an open
> cursor).....
>
> But again, I see no solution to the above problem and understandably so, if
> you LIMIT a result set, you *limit* it -- asking it to contradict itself
> doesn't make any sense so I never complained that it wasn't possible :-)
>
> -Mitch
>
> ---------------------------(end of broadcast)---------------------------
> TIP 1: subscribe and unsubscribe commands go to majordomo@postgresql.org

Re: Way to use count() and LIMIT?

From
Martijn van Oosterhout
Date:
On Wed, Dec 19, 2001 at 08:13:50AM -0600, Joe Koenig wrote:
> This is exactly right. The table I'm searching has 220,000 records right
> now, and growing. The "Rock" category within "CD's" will return over
> 53,000 results. The count isn't so much for display, as for knowing
> whether or not to put a "next" button on the page. I don't want to
> always have next buttons, but not always have more results. There is
> currently the option to browse through results based on the first letter
> of the result, which I guess I could make as the 3rd step and not run
> the query that currently returns 53,000 results until they narrow it
> down more. I wasn't really expecting this to be possible, but PG does so
> many other things that I didn't think was possible, so I thought I'd ask :)

But that's easy. Simply ask the database for one more record that you're
going to display. If you get that extra record, then display the next
button, otherwise don't. The count is irrelevent.

--
Martijn van Oosterhout <kleptog@svana.org>
http://svana.org/kleptog/
> Terrorists can only take my life. Only my government can take my freedom.

Re: Way to use count() and LIMIT?

From
Joe Koenig
Date:
That's so rediculously simple it hurts...I'm an idiot...Thank you VERY much.

Joe

Martijn van Oosterhout wrote:
>
> On Wed, Dec 19, 2001 at 08:13:50AM -0600, Joe Koenig wrote:
> > This is exactly right. The table I'm searching has 220,000 records right
> > now, and growing. The "Rock" category within "CD's" will return over
> > 53,000 results. The count isn't so much for display, as for knowing
> > whether or not to put a "next" button on the page. I don't want to
> > always have next buttons, but not always have more results. There is
> > currently the option to browse through results based on the first letter
> > of the result, which I guess I could make as the 3rd step and not run
> > the query that currently returns 53,000 results until they narrow it
> > down more. I wasn't really expecting this to be possible, but PG does so
> > many other things that I didn't think was possible, so I thought I'd ask :)
>
> But that's easy. Simply ask the database for one more record that you're
> going to display. If you get that extra record, then display the next
> button, otherwise don't. The count is irrelevent.
>
> --
> Martijn van Oosterhout <kleptog@svana.org>
> http://svana.org/kleptog/
> > Terrorists can only take my life. Only my government can take my freedom.

Re: Way to use count() and LIMIT?

From
"Greg Sabino Mullane"
Date:
-----BEGIN PGP SIGNED MESSAGE-----
Hash: SHA1

> Simply ask the database for one more record that you're going to
> display. If you get that extra record, then display the next button,
> otherwise don't. The count is irrelevent

Great idea. If you are using DBI, you can even do a $sth->rows()
call to avoid the overhead of grabbing the data via a fetch.
(unless you are using fetchall/selectall)

Still, it seems that there should be a way to grab the information,
especially if you have an "ORDER BY" clause in your query - after
all, that means at some point, postgres knows how many records
matched before it ORDERS and LIMITS them.

Something like this:

SELECT pg_get_totalrows, artist, title, year
FROM giantcdcollection
WHERE genre = 'Rock'
ORDER BY artist, title
LIMIT 21
OFFSET 200

should be possible with no slow down of query time, I would think.
Inefficient in that it returns the same number 21 times, but
a bargain compared to the current way to do it (separate count query).

Greg Sabino Mullane
greg@turnstep.com
PGP Key: 0x14964AC8 200112191051
-----BEGIN PGP SIGNATURE-----
Comment: http://www.turnstep.com/pgp.html

iD8DBQE8ILggvJuQZxSWSsgRAny6AKCG/DrD2dhft6/kwjiHJ5a5jPwvFQCfeoHJ
Ej56nl3x5+snq9wynedCfUo=
=LFIM
-----END PGP SIGNATURE-----

Re: Way to use count() and LIMIT?

From
Tom Lane
Date:
"Greg Sabino Mullane" <greg@turnstep.com> writes:
> Still, it seems that there should be a way to grab the information,
> especially if you have an "ORDER BY" clause in your query - after
> all, that means at some point, postgres knows how many records
> matched before it ORDERS and LIMITS them.

Not necessarily; it could be using an indexscan to do the ordering.

            regards, tom lane