Re: ***SPAM*** Re: Can I get the number of results plus the results with a single query?

On 16 Aug 2022, at 0:13, Rob Sargent wrote:

> On 8/15/22 14:37, Perry Smith wrote:
>>
>>
>>> On Aug 15, 2022, at 08:55, David G. Johnston
>>> <david(dot)g(dot)johnston(at)gmail(dot)com> wrote:
>>>
>>> On Monday, August 15, 2022, Perry Smith <pedz(at)easesoftware(dot)com>
>>> wrote:
>>>
>>> I’ve been toying with row_number() and then sort by row_number
>>> descending and pick off the first row as the total number.
>>>
>>>
>>> Use count as a window function.
>>
>> I see others are commenting after David’s update so:
>>
>> Thank you David.
>>
>> This seems to work for me:
>>
>> SELECT count(*) OVER (), id, basename, sha1 FROM dateien WHERE
>> (lower(ext) in ( 'pxd' ) and ftype = 'file') ORDER BY sha1;
>>
>>
>> This has, e.g. 73, in the first column for all of the rows.
>>
> Any comparative timing statistics on that?  Especially on more than
> 73 records returned, because with that few just grab them all and get
> size() or length of what ever collection mechanism you're playing
> with.

I tried with a larger table (739951 records):

```
select e.* from email.email e;
```

takes 50 seconds (as displayed by TablePlus).

```
select count(*) over (), e.* from email.email e;
```

takes 58 seconds.

And doing `select count(*) from email.email e;` takes 2-3 seconds.

Note that in this example the records where fetched over the internet
(i.e. not from a local Postgres installation) and there is no `where`
condition that must be evaluated repeatedly, so other variants might
give better numbers.

Servus,
Walter