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

From Walter Dörwald
Subject Re: ***SPAM*** Re: Can I get the number of results plus the results with a single query?
Date
Msg-id 2C5B341A-F784-4962-9F02-00980F1C13AF@livinglogic.de
Whole thread Raw
In response to Re: ***SPAM*** Re: Can I get the number of results plus the results with a single query?  (Rob Sargent <robjsargent@gmail.com>)
Responses Re: ***SPAM*** Re: Can I get the number of results plus the results with a single query?
List pgsql-general

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.g.johnston@gmail.com> wrote:

On Monday, August 15, 2022, Perry Smith <pedz@easesoftware.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

pgsql-general by date:

Previous
From: Abdul Qoyyuum
Date:
Subject: Re: Help regarding Multi Tenancy with PostgreSQL
Next
From: David Rowley
Date:
Subject: Re: ***SPAM*** Re: Can I get the number of results plus the results with a single query?