Subject: Re: [PG13] Planning (time + buffers) data structure in explain plan
 (format text)
To: David Rowley <dgrowleyml@gmail.com>, Julien Rouhaud <rjuju123@gmail.com>
Cc: Justin Pryzby <pryzby@telsasoft.com>,
 Pierre Giraud <pierre.giraud@dalibo.com>,
 PostgreSQL Developers <pgsql-hackers@lists.postgresql.org>,
 Fujii Masao <fujii@postgresql.org>
References: <07b226e6-fa49-687f-b110-b7c37572f69e@dalibo.com>
 <20200819032706.GQ17022@telsasoft.com> <20200819072133.jnmtux7qbyxywh6r@nol>
 <CAApHDvrDpJNMpXMS3SNkeUitXrZAFRZ3MrzQG6ieC7vQte6aWA@mail.gmail.com>
 <20200819090429.s7hoyyw4a6jumgkc@nol>
 <CAApHDvr0hMeEDB6HE=GE6vc2kJjCoiWe7z+CVyD9CoAUB+Cpjw@mail.gmail.com>
From: Fujii Masao <masao.fujii@oss.nttdata.com>
Message-ID: <e7361ab4-5b6b-9667-e25b-04ae0b070ded@oss.nttdata.com>
Date: Thu, 20 Aug 2020 00:31:48 +0900
User-Agent: Mozilla/5.0 (Macintosh; Intel Mac OS X 10.14; rv:68.0)
 Gecko/20100101 Thunderbird/68.11.0
MIME-Version: 1.0
In-Reply-To: 
 <CAApHDvr0hMeEDB6HE=GE6vc2kJjCoiWe7z+CVyD9CoAUB+Cpjw@mail.gmail.com>
Content-Type: multipart/mixed;
 boundary="------------8FBFAAD4B2CB4496AD3542D3"
Content-Language: en-US
Precedence: bulk

This is a multi-part message in MIME format.
--------------8FBFAAD4B2CB4496AD3542D3
Content-Type: text/plain; charset=utf-8; format=flowed
Content-Transfer-Encoding: 7bit


On 2020/08/19 19:39, David Rowley wrote:
> On Wed, 19 Aug 2020 at 21:05, Julien Rouhaud <rjuju123@gmail.com> wrote:
>>
>> On Wed, Aug 19, 2020 at 08:49:48PM +1200, David Rowley wrote:
>>> However, I'm not quite sure how we should handle if someone does:
>>> EXPLAIN (BUFFERS on, SUMMARY off). Without the summary, there's no
>>> place to print the buffers, which seems bad as they asked for buffers.
>>
>>
>> But this won't be as much a problem if ANALYZE is asked, and having different
>> behaviors isn't appealing.  So maybe it's better to let people get what they
>> asked for even if that's contradictory?
> 
> I'd say BUFFERS on, BUFFERS off is contradictory. I don't think
> BUFFERS, SUMMARY OFF is. It's just that we show the buffer details for
> the planner in the summary.  Since "summary" is not exactly a word
> that describes what you're asking EXPLAIN to do, I wouldn't blame
> users if they got confused as to why their BUFFERS on request was not
> displayed.

Displaying the planner's buffer usage under summary is the root cause of
the confusion? If so, what about displaying that outside summary?
Attached is the POC patch that I'm just thinking.

With the patch, for example, whatever "summary" settng is, "buffers on"
displays the planner's buffer usage if it happens.

=# explain (buffers on, summary off) select * from t;
                      QUERY PLAN
-----------------------------------------------------
  Seq Scan on t  (cost=0.00..32.60 rows=2260 width=8)
  Planning:
    Buffers: shared hit=16 read=6
(3 rows)


If "summary" is enabled, the planning time is also displayed.

=# explain (buffers on, summary on) select * from t;
                      QUERY PLAN
-----------------------------------------------------
  Seq Scan on t  (cost=0.00..32.60 rows=2260 width=8)
  Planning:
    Buffers: shared hit=16 read=6
  Planning Time: 0.904 ms
(4 rows)


If the planner's buffer usage doesn't happen, it's not displayed
(in text format).

=# explain (buffers on, summary on) select * from t;
                      QUERY PLAN
-----------------------------------------------------
  Seq Scan on t  (cost=0.00..32.60 rows=2260 width=8)
  Planning Time: 0.064 ms
(2 rows)

Regards,

-- 
Fujii Masao
Advanced Computing Technology Center
Research and Development Headquarters
NTT DATA CORPORATION

--------------8FBFAAD4B2CB4496AD3542D3
Content-Type: text/plain; charset=UTF-8; x-mac-type="0"; x-mac-creator="0";
 name="explain_exclude_planning_buffers_from_summary.patch"
Content-Transfer-Encoding: base64
Content-Disposition: attachment;
 filename="explain_exclude_planning_buffers_from_summary.patch"

ZGlmZiAtLWdpdCBhL3NyYy9iYWNrZW5kL2NvbW1hbmRzL2V4cGxhaW4uYyBiL3NyYy9iYWNr
ZW5kL2NvbW1hbmRzL2V4cGxhaW4uYwppbmRleCAzMGUwYTdlZTdmLi5jOThjOWI1NTQ3IDEw
MDY0NAotLS0gYS9zcmMvYmFja2VuZC9jb21tYW5kcy9leHBsYWluLmMKKysrIGIvc3JjL2Jh
Y2tlbmQvY29tbWFuZHMvZXhwbGFpbi5jCkBAIC0xMTYsNyArMTE2LDggQEAgc3RhdGljIHZv
aWQgc2hvd19pbnN0cnVtZW50YXRpb25fY291bnQoY29uc3QgY2hhciAqcWxhYmVsLCBpbnQg
d2hpY2gsCiBzdGF0aWMgdm9pZCBzaG93X2ZvcmVpZ25zY2FuX2luZm8oRm9yZWlnblNjYW5T
dGF0ZSAqZnNzdGF0ZSwgRXhwbGFpblN0YXRlICplcyk7CiBzdGF0aWMgdm9pZCBzaG93X2V2
YWxfcGFyYW1zKEJpdG1hcHNldCAqYm1zX3BhcmFtcywgRXhwbGFpblN0YXRlICplcyk7CiBz
dGF0aWMgY29uc3QgY2hhciAqZXhwbGFpbl9nZXRfaW5kZXhfbmFtZShPaWQgaW5kZXhJZCk7
Ci1zdGF0aWMgdm9pZCBzaG93X2J1ZmZlcl91c2FnZShFeHBsYWluU3RhdGUgKmVzLCBjb25z
dCBCdWZmZXJVc2FnZSAqdXNhZ2UpOworc3RhdGljIHZvaWQgc2hvd19idWZmZXJfdXNhZ2Uo
RXhwbGFpblN0YXRlICplcywgY29uc3QgQnVmZmVyVXNhZ2UgKnVzYWdlLAorCQkJCQkJCSAg
Ym9vbCBwbGFubmluZyk7CiBzdGF0aWMgdm9pZCBzaG93X3dhbF91c2FnZShFeHBsYWluU3Rh
dGUgKmVzLCBjb25zdCBXYWxVc2FnZSAqdXNhZ2UpOwogc3RhdGljIHZvaWQgRXhwbGFpbklu
ZGV4U2NhbkRldGFpbHMoT2lkIGluZGV4aWQsIFNjYW5EaXJlY3Rpb24gaW5kZXhvcmRlcmRp
ciwKIAkJCQkJCQkJCUV4cGxhaW5TdGF0ZSAqZXMpOwpAQCAtMjIxLDExICsyMjIsNiBAQCBF
eHBsYWluUXVlcnkoUGFyc2VTdGF0ZSAqcHN0YXRlLCBFeHBsYWluU3RtdCAqc3RtdCwKIAkJ
CQkJIHBhcnNlcl9lcnJwb3NpdGlvbihwc3RhdGUsIG9wdC0+bG9jYXRpb24pKSk7CiAJfQog
Ci0JaWYgKGVzLT5idWZmZXJzICYmICFlcy0+YW5hbHl6ZSkKLQkJZXJlcG9ydChFUlJPUiwK
LQkJCQkoZXJyY29kZShFUlJDT0RFX0lOVkFMSURfUEFSQU1FVEVSX1ZBTFVFKSwKLQkJCQkg
ZXJybXNnKCJFWFBMQUlOIG9wdGlvbiBCVUZGRVJTIHJlcXVpcmVzIEFOQUxZWkUiKSkpOwot
CiAJaWYgKGVzLT53YWwgJiYgIWVzLT5hbmFseXplKQogCQllcmVwb3J0KEVSUk9SLAogCQkJ
CShlcnJjb2RlKEVSUkNPREVfSU5WQUxJRF9QQVJBTUVURVJfVkFMVUUpLApAQCAtNTg2LDgg
KzU4MiwxMyBAQCBFeHBsYWluT25lUGxhbihQbGFubmVkU3RtdCAqcGxhbm5lZHN0bXQsIElu
dG9DbGF1c2UgKmludG8sIEV4cGxhaW5TdGF0ZSAqZXMsCiAJLyogQ3JlYXRlIHRleHR1YWwg
ZHVtcCBvZiBwbGFuIHRyZWUgKi8KIAlFeHBsYWluUHJpbnRQbGFuKGVzLCBxdWVyeURlc2Mp
OwogCi0JaWYgKGVzLT5zdW1tYXJ5ICYmIChwbGFuZHVyYXRpb24gfHwgYnVmdXNhZ2UpKQor
CS8qIFNob3cgYnVmZmVyIHVzYWdlIGluIHBsYW5uaW5nICovCisJaWYgKGJ1ZnVzYWdlKQor
CXsKIAkJRXhwbGFpbk9wZW5Hcm91cCgiUGxhbm5pbmciLCAiUGxhbm5pbmciLCB0cnVlLCBl
cyk7CisJCXNob3dfYnVmZmVyX3VzYWdlKGVzLCBidWZ1c2FnZSwgdHJ1ZSk7CisJCUV4cGxh
aW5DbG9zZUdyb3VwKCJQbGFubmluZyIsICJQbGFubmluZyIsIHRydWUsIGVzKTsKKwl9CiAK
IAlpZiAoZXMtPnN1bW1hcnkgJiYgcGxhbmR1cmF0aW9uKQogCXsKQEAgLTU5NiwxOSArNTk3
LDYgQEAgRXhwbGFpbk9uZVBsYW4oUGxhbm5lZFN0bXQgKnBsYW5uZWRzdG10LCBJbnRvQ2xh
dXNlICppbnRvLCBFeHBsYWluU3RhdGUgKmVzLAogCQlFeHBsYWluUHJvcGVydHlGbG9hdCgi
UGxhbm5pbmcgVGltZSIsICJtcyIsIDEwMDAuMCAqIHBsYW50aW1lLCAzLCBlcyk7CiAJfQog
Ci0JLyogU2hvdyBidWZmZXIgdXNhZ2UgKi8KLQlpZiAoZXMtPnN1bW1hcnkgJiYgYnVmdXNh
Z2UpCi0JewotCQlpZiAoZXMtPmZvcm1hdCA9PSBFWFBMQUlOX0ZPUk1BVF9URVhUKQotCQkJ
ZXMtPmluZGVudCsrOwotCQlzaG93X2J1ZmZlcl91c2FnZShlcywgYnVmdXNhZ2UpOwotCQlp
ZiAoZXMtPmZvcm1hdCA9PSBFWFBMQUlOX0ZPUk1BVF9URVhUKQotCQkJZXMtPmluZGVudC0t
OwotCX0KLQotCWlmIChlcy0+c3VtbWFyeSAmJiAocGxhbmR1cmF0aW9uIHx8IGJ1ZnVzYWdl
KSkKLQkJRXhwbGFpbkNsb3NlR3JvdXAoIlBsYW5uaW5nIiwgIlBsYW5uaW5nIiwgdHJ1ZSwg
ZXMpOwotCiAJLyogUHJpbnQgaW5mbyBhYm91dCBydW50aW1lIG9mIHRyaWdnZXJzICovCiAJ
aWYgKGVzLT5hbmFseXplKQogCQlFeHBsYWluUHJpbnRUcmlnZ2VycyhlcywgcXVlcnlEZXNj
KTsKQEAgLTE5OTYsNyArMTk4NCw3IEBAIEV4cGxhaW5Ob2RlKFBsYW5TdGF0ZSAqcGxhbnN0
YXRlLCBMaXN0ICphbmNlc3RvcnMsCiAKIAkvKiBTaG93IGJ1ZmZlci9XQUwgdXNhZ2UgKi8K
IAlpZiAoZXMtPmJ1ZmZlcnMgJiYgcGxhbnN0YXRlLT5pbnN0cnVtZW50KQotCQlzaG93X2J1
ZmZlcl91c2FnZShlcywgJnBsYW5zdGF0ZS0+aW5zdHJ1bWVudC0+YnVmdXNhZ2UpOworCQlz
aG93X2J1ZmZlcl91c2FnZShlcywgJnBsYW5zdGF0ZS0+aW5zdHJ1bWVudC0+YnVmdXNhZ2Us
IGZhbHNlKTsKIAlpZiAoZXMtPndhbCAmJiBwbGFuc3RhdGUtPmluc3RydW1lbnQpCiAJCXNo
b3dfd2FsX3VzYWdlKGVzLCAmcGxhbnN0YXRlLT5pbnN0cnVtZW50LT53YWx1c2FnZSk7CiAK
QEAgLTIwMTUsNyArMjAwMyw3IEBAIEV4cGxhaW5Ob2RlKFBsYW5TdGF0ZSAqcGxhbnN0YXRl
LCBMaXN0ICphbmNlc3RvcnMsCiAKIAkJCUV4cGxhaW5PcGVuV29ya2VyKG4sIGVzKTsKIAkJ
CWlmIChlcy0+YnVmZmVycykKLQkJCQlzaG93X2J1ZmZlcl91c2FnZShlcywgJmluc3RydW1l
bnQtPmJ1ZnVzYWdlKTsKKwkJCQlzaG93X2J1ZmZlcl91c2FnZShlcywgJmluc3RydW1lbnQt
PmJ1ZnVzYWdlLCBmYWxzZSk7CiAJCQlpZiAoZXMtPndhbCkKIAkJCQlzaG93X3dhbF91c2Fn
ZShlcywgJmluc3RydW1lbnQtPndhbHVzYWdlKTsKIAkJCUV4cGxhaW5DbG9zZVdvcmtlcihu
LCBlcyk7CkBAIC0zMzAxLDcgKzMyODksNyBAQCBleHBsYWluX2dldF9pbmRleF9uYW1lKE9p
ZCBpbmRleElkKQogICogU2hvdyBidWZmZXIgdXNhZ2UgZGV0YWlscy4KICAqLwogc3RhdGlj
IHZvaWQKLXNob3dfYnVmZmVyX3VzYWdlKEV4cGxhaW5TdGF0ZSAqZXMsIGNvbnN0IEJ1ZmZl
clVzYWdlICp1c2FnZSkKK3Nob3dfYnVmZmVyX3VzYWdlKEV4cGxhaW5TdGF0ZSAqZXMsIGNv
bnN0IEJ1ZmZlclVzYWdlICp1c2FnZSwgYm9vbCBwbGFubmluZykKIHsKIAlpZiAoZXMtPmZv
cm1hdCA9PSBFWFBMQUlOX0ZPUk1BVF9URVhUKQogCXsKQEAgLTMzMTcsNiArMzMwNSwxNSBA
QCBzaG93X2J1ZmZlcl91c2FnZShFeHBsYWluU3RhdGUgKmVzLCBjb25zdCBCdWZmZXJVc2Fn
ZSAqdXNhZ2UpCiAJCQkJCQkJCXVzYWdlLT50ZW1wX2Jsa3Nfd3JpdHRlbiA+IDApOwogCQli
b29sCQloYXNfdGltaW5nID0gKCFJTlNUUl9USU1FX0lTX1pFUk8odXNhZ2UtPmJsa19yZWFk
X3RpbWUpIHx8CiAJCQkJCQkJCSAgIUlOU1RSX1RJTUVfSVNfWkVSTyh1c2FnZS0+YmxrX3dy
aXRlX3RpbWUpKTsKKwkJYm9vbAkJc2hvd19wbGFubmluZyA9IChwbGFubmluZyAmJiAoaGFz
X3NoYXJlZCB8fAorCQkJCQkJCQkJCQkJICBoYXNfbG9jYWwgfHwgaGFzX3RlbXAgfHwgaGFz
X3RpbWluZykpOworCisJCWlmIChzaG93X3BsYW5uaW5nKQorCQl7CisJCQlFeHBsYWluSW5k
ZW50VGV4dChlcyk7CisJCQlhcHBlbmRTdHJpbmdJbmZvU3RyaW5nKGVzLT5zdHIsICJQbGFu
bmluZzpcbiIpOworCQkJZXMtPmluZGVudCsrOworCQl9CiAKIAkJLyogU2hvdyBvbmx5IHBv
c2l0aXZlIGNvdW50ZXIgdmFsdWVzLiAqLwogCQlpZiAoaGFzX3NoYXJlZCB8fCBoYXNfbG9j
YWwgfHwgaGFzX3RlbXApCkBAIC0zMzg2LDYgKzMzODMsOSBAQCBzaG93X2J1ZmZlcl91c2Fn
ZShFeHBsYWluU3RhdGUgKmVzLCBjb25zdCBCdWZmZXJVc2FnZSAqdXNhZ2UpCiAJCQkJCQkJ
CSBJTlNUUl9USU1FX0dFVF9NSUxMSVNFQyh1c2FnZS0+YmxrX3dyaXRlX3RpbWUpKTsKIAkJ
CWFwcGVuZFN0cmluZ0luZm9DaGFyKGVzLT5zdHIsICdcbicpOwogCQl9CisKKwkJaWYgKHNo
b3dfcGxhbm5pbmcpCisJCQllcy0+aW5kZW50LS07CiAJfQogCWVsc2UKIAl7Cg==
--------------8FBFAAD4B2CB4496AD3542D3--