MIME-Version: 1.0
References: <CAApHDvqvp3At8++yF8ij06sdcoo1S_b2YoaT9D4Nf+MObzsrLQ@mail.gmail.com>
 <CAApHDvrxxkc9dgoRJUHy6U+XmGjSXRF=fQ-_U1-EvzTjCA9XKw@mail.gmail.com>
 <CALNJ-vSRgdOsMx-oiOZcMJzcxoRCo0nJr=U8us4eJR8R6n9ZtA@mail.gmail.com>
 <CAApHDvpiAUmUMTETAua8XCLFJiFhsZkCwuiW50KDvehexA-SLA@mail.gmail.com>
 <CAKU4AWq5MB_inrYUwPQYXU1fCopCwgYK53N2uYGYFBH3QEhZMA@mail.gmail.com>
 <CAApHDvp8PSY3GJXNzzrBPJtzRgQ-C85OPy-bypFGmwaT3AjCjQ@mail.gmail.com>
 <CAKU4AWrWzXFuGW4d3YTu=TJSWQKTB=J3E8S_5zyuLNR7npomAA@mail.gmail.com>
 <CAApHDvqtwE4bZxAdk+LQgVkKM+Em-sHybON_OX4YbFs+sQUKWg@mail.gmail.com>
 <CAApHDvoe=MqH66pDWynUtdammYHiB8m+zE7LvL_CGuxm1yK4BQ@mail.gmail.com>
 <CAApHDvpYYndDJfGCmNA-o60VLb8D9OB1deh-qcsTiDjxVcFg_w@mail.gmail.com>
 <20220329221615.hule724qcaarhe3y@alap3.anarazel.de> <CAApHDvp+rzxGh58hc3Fmr90PR-M=JOg96X0B5tfvY4pTKqNa3g@mail.gmail.com>
 <CAKU4AWoiqPd_DdOA_0PWZ_e9cV6e1yP6F0VmxaLjedwTWQtBJA@mail.gmail.com> <CAKU4AWpmDGMxQ3nJfj=awQAgcL-jP2p67MtpiHFZX77NaZ2hxQ@mail.gmail.com>
In-Reply-To: <CAKU4AWpmDGMxQ3nJfj=awQAgcL-jP2p67MtpiHFZX77NaZ2hxQ@mail.gmail.com>
From: David Rowley <dgrowleyml@gmail.com>
Date: Tue, 5 Apr 2022 23:49:15 +1200
Message-ID: <CAApHDvrU132PxjuLakFrQhjR_b40LHAqhCzk-q2iGOST+-6dCw@mail.gmail.com>
Subject: Re: Window Function "Run Conditions"
To: Andy Fan <zhihui.fan1213@gmail.com>
Cc: Andres Freund <andres@anarazel.de>, Zhihong Yu <zyu@yugabyte.com>, 
	PostgreSQL Developers <pgsql-hackers@lists.postgresql.org>
Content-Type: multipart/mixed; boundary="0000000000005b03df05dbe6d637"
Archived-At: <https://www.postgresql.org/message-id/CAApHDvrU132PxjuLakFrQhjR_b40LHAqhCzk-q2iGOST%2B-6dCw%40mail.gmail.com>
Precedence: bulk

--0000000000005b03df05dbe6d637
Content-Type: text/plain; charset="UTF-8"

On Tue, 5 Apr 2022 at 19:38, Andy Fan <zhihui.fan1213@gmail.com> wrote:
> 1. We can do more on PASSTHROUGH, we just bypass the window function
> currently,  but IIUC we can ignore all of the following tuples in current partition
> once we go into this mode.  patch 0001 shows what I mean.

Yeah, there is more performance to be had than even what you've done
there.  There's no reason really for spool_tuples() to do
tuplestore_puttupleslot() when we're not in run mode.

The attached should give slightly more performance.  I'm unsure if
there's more that can be done for window aggregates, i.e.
eval_windowaggregates()

I'll consider the idea about doing all the filtering in
nodeWindowAgg.c. For now I made find_window_run_conditions() keep the
qual so that it's still filtered in the subquery level when there is a
PARTITION BY clause. Probably the best way would be to make
nodeWindowAgg.c just loop with a for(;;) loop. I'll need to give it
more thought. I'll do that in the morning.

David

--0000000000005b03df05dbe6d637
Content-Type: text/plain; charset="US-ASCII"; name="windowagg_hacks.patch"
Content-Disposition: attachment; filename="windowagg_hacks.patch"
Content-Transfer-Encoding: base64
Content-ID: <f_l1m2q6tj0>
X-Attachment-Id: f_l1m2q6tj0

ZGlmZiAtLWdpdCBhL3NyYy9iYWNrZW5kL2V4ZWN1dG9yL25vZGVXaW5kb3dBZ2cuYyBiL3NyYy9i
YWNrZW5kL2V4ZWN1dG9yL25vZGVXaW5kb3dBZ2cuYwppbmRleCA4NjlkY2Q3NGRmLi4xOWZlNzIw
ZDljIDEwMDY0NAotLS0gYS9zcmMvYmFja2VuZC9leGVjdXRvci9ub2RlV2luZG93QWdnLmMKKysr
IGIvc3JjL2JhY2tlbmQvZXhlY3V0b3Ivbm9kZVdpbmRvd0FnZy5jCkBAIC0xMjQ4LDYgKzEyNDgs
OSBAQCBzcG9vbF90dXBsZXMoV2luZG93QWdnU3RhdGUgKndpbnN0YXRlLCBpbnQ2NCBwb3MpCiAJ
aWYgKHdpbnN0YXRlLT5wYXJ0aXRpb25fc3Bvb2xlZCkKIAkJcmV0dXJuOwkJCQkJLyogd2hvbGUg
cGFydGl0aW9uIGRvbmUgYWxyZWFkeSAqLwogCisJaWYgKHdpbnN0YXRlLT5zdGF0dXMgPT0gV0lO
RE9XQUdHX1BBU1NUSFJPVUdIKQorCQlwb3MgPSAtMTsKKwogCS8qCiAJICogSWYgdGhlIHR1cGxl
c3RvcmUgaGFzIHNwaWxsZWQgdG8gZGlzaywgYWx0ZXJuYXRlIHJlYWRpbmcgYW5kIHdyaXRpbmcK
IAkgKiBiZWNvbWVzIHF1aXRlIGV4cGVuc2l2ZSBkdWUgdG8gZnJlcXVlbnQgYnVmZmVyIGZsdXNo
ZXMuICBJdCdzIGNoZWFwZXIKQEAgLTEyNTYsNyArMTI1OSw3IEBAIHNwb29sX3R1cGxlcyhXaW5k
b3dBZ2dTdGF0ZSAqd2luc3RhdGUsIGludDY0IHBvcykKIAkgKiBYWFggdGhpcyBpcyBhIGhvcnJp
ZCBrbHVnZSAtLS0gaXQnZCBiZSBiZXR0ZXIgdG8gZml4IHRoZSBwZXJmb3JtYW5jZQogCSAqIHBy
b2JsZW0gaW5zaWRlIHR1cGxlc3RvcmUuICBGSVhNRQogCSAqLwotCWlmICghdHVwbGVzdG9yZV9p
bl9tZW1vcnkod2luc3RhdGUtPmJ1ZmZlcikpCisJZWxzZSBpZiAoIXR1cGxlc3RvcmVfaW5fbWVt
b3J5KHdpbnN0YXRlLT5idWZmZXIpKQogCQlwb3MgPSAtMTsKIAogCW91dGVyUGxhbiA9IG91dGVy
UGxhblN0YXRlKHdpbnN0YXRlKTsKQEAgLTEyOTUsOSArMTI5OCwxMiBAQCBzcG9vbF90dXBsZXMo
V2luZG93QWdnU3RhdGUgKndpbnN0YXRlLCBpbnQ2NCBwb3MpCiAJCQl9CiAJCX0KIAotCQkvKiBT
dGlsbCBpbiBwYXJ0aXRpb24sIHNvIHNhdmUgaXQgaW50byB0aGUgdHVwbGVzdG9yZSAqLwotCQl0
dXBsZXN0b3JlX3B1dHR1cGxlc2xvdCh3aW5zdGF0ZS0+YnVmZmVyLCBvdXRlcnNsb3QpOwotCQl3
aW5zdGF0ZS0+c3Bvb2xlZF9yb3dzKys7CisJCWlmICh3aW5zdGF0ZS0+c3RhdHVzID09IFdJTkRP
V0FHR19SVU4pCisJCXsKKwkJCS8qIFN0aWxsIGluIHBhcnRpdGlvbiwgc28gc2F2ZSBpdCBpbnRv
IHRoZSB0dXBsZXN0b3JlICovCisJCQl0dXBsZXN0b3JlX3B1dHR1cGxlc2xvdCh3aW5zdGF0ZS0+
YnVmZmVyLCBvdXRlcnNsb3QpOworCQkJd2luc3RhdGUtPnNwb29sZWRfcm93cysrOworCQl9CiAJ
fQogCiAJTWVtb3J5Q29udGV4dFN3aXRjaFRvKG9sZGNvbnRleHQpOwo=
--0000000000005b03df05dbe6d637--