On Tue, 16 Aug 2022 at 21:15, Walter Dörwald <walter@livinglogic.de> 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