Re: Can I get the number of results plus the results with a single query? - Mailing list pgsql-general

From Peter J. Holzer
Subject Re: Can I get the number of results plus the results with a single query?
Date
Msg-id 20220816204238.sfrtsl73z4yoc2d2@hjp.at
Whole thread Raw
In response to Re: Can I get the number of results plus the results with a single query?  (Bryn Llewellyn <bryn@yugabyte.com>)
Responses Re: Can I get the number of results plus the results with a single query?
List pgsql-general
On 2022-08-15 12:20:44 -0700, Bryn Llewellyn wrote:
>
>             pedz@easesoftware.com wrote:
>
>             Currently I’m doing this with two queries such as:
>
>                 SELECT COUNT(*) FROM table WHERE …. expression …
>                 SELECT * FROM table WHERE …. expression …
>
>             But this requires two queries. Is there a way to do the same thing
>             with just one quer?
>
>
>         david.g.johnston@gmail.com wrote:
>
>         Use count as a window function.
>
>
>     hjp-pgsql@hjp.at wrote:
>
>     I don't think there can be [a way to do the same thing with just one
>     query].

That's not quite what I meant. I meant "I don't think there can be what
you want with just one query",


> How about this:
>
> create table t(k serial primary key, v int not null);
> insert into t(v) values (7), (19), (42), (57), (100), (200), (300);
>
> create function f()
>   returns table(z text)
>   language plpgsql
>   stable
> as $body$
> declare
>   r int not null := 0;
>   results constant int[] :=
>     (
>       select array_agg(v order by v) from t where v < 100
>     );
> begin
>   z := 'Count(*): '||cardinality(results);    return next;

I may be missing something but I don't see how this solves the problem.

The OP wants some kind of progress indicator. To be useful, such an
indicator should be approximately linear in time. I.e. if your query
returns 10000 rows in 5 minutes (yes, that's slow, but you don't need a
progress bar for fast queries), it should display "0/10000" after 0
seconds, "33/10000" after 1 second, "2000/10000" after 1 minute, etc.
That ideal is of course unrealistic, it's quite ok if it displays
"0/unknown" fpr a few seconds and speeds up and slows down during
execution. But if it displays "0/unknown" for 4 minutes and 55 seconds
and then counts up to 10000 during the last 5 seconds, the progress
indicator is useless.

You are stuffing the whole result into an array and THEN counting the
number of elements. So when you get to the count all of the work (except
sending the result to the client) is already done, so there is little
point in displaying a progress indicator.

        hp

--
   _  | Peter J. Holzer    | Story must make more sense than reality.
|_|_) |                    |
| |   | hjp@hjp.at         |    -- Charles Stross, "Creative writing
__/   | http://www.hjp.at/ |       challenge!"

Attachment

pgsql-general by date:

Previous
From: "Bzm@g"
Date:
Subject: Re: Would it be possible to add functions to tab-completion in psql?
Next
From: Bryn Llewellyn
Date:
Subject: Re: Can I get the number of results plus the results with a single query?