On Tue, 16 Aug 2022 at 21:15, Walter Dörwald <walter@xxxxxxxxxxxxxx> wrote: > select count(*) over (), e.* from email.email e; Depending on the complexity of the query, putting the count(*) as a subquery in the SELECT clause might execute more quickly. i.e. select (select count(*) from email.email) c, * from email.emails; A non-correlated subquery will become an "initplan", which will execute only once. The reason the window aggregate might be slower is due to the fact that internally PostgreSQL will store tuples in the window frame in a tuplestore. In this example, all rows will be put in that tuple store at once. These tuple stores will spill to disk when they exceed work_mem. On the other hand, the window aggregate version could be faster if fetching each tuple from the outer query was sufficiently more expensive than storing it in a tuple store. That could easily happen if the query contained expensive joins or many rows were filtered out. That could be more expensive as the subquery version would have to do that twice whereas the window agg version would only have to do that once. David
