Received: from malur.postgresql.org ([217.196.149.56]) by arkaria.postgresql.org with esmtps (TLS1.3) tls TLS_ECDHE_RSA_WITH_AES_256_GCM_SHA384 (Exim 4.94.2) (envelope-from ) id 1tI4ob-00BxNz-Pu for pgsql-hackers@arkaria.postgresql.org; Mon, 02 Dec 2024 11:42:09 +0000 Received: from localhost ([127.0.0.1] helo=malur.postgresql.org) by malur.postgresql.org with esmtp (Exim 4.94.2) (envelope-from ) id 1tI4oY-00GfMQ-V2 for pgsql-hackers@arkaria.postgresql.org; Mon, 02 Dec 2024 11:42:08 +0000 Received: from magus.postgresql.org ([2a02:c0:301:0:ffff::29]) by malur.postgresql.org with esmtps (TLS1.3) tls TLS_ECDHE_RSA_WITH_AES_256_GCM_SHA384 (Exim 4.94.2) (envelope-from ) id 1tI4oY-00GfMH-IQ for pgsql-hackers@lists.postgresql.org; Mon, 02 Dec 2024 11:42:07 +0000 Received: from mail-ej1-x636.google.com ([2a00:1450:4864:20::636]) by magus.postgresql.org with esmtps (TLS1.3) tls TLS_ECDHE_RSA_WITH_AES_128_GCM_SHA256 (Exim 4.94.2) (envelope-from ) id 1tI4oW-000dpj-Cg for pgsql-hackers@postgresql.org; Mon, 02 Dec 2024 11:42:07 +0000 Received: by mail-ej1-x636.google.com with SMTP id a640c23a62f3a-aa545dc7105so656468866b.3 for ; Mon, 02 Dec 2024 03:42:04 -0800 (PST) DKIM-Signature: v=1; a=rsa-sha256; c=relaxed/relaxed; d=pgmustard.com; s=google; t=1733139724; x=1733744524; darn=postgresql.org; h=cc:to:subject:message-id:date:from:in-reply-to:references :mime-version:from:to:cc:subject:date:message-id:reply-to; bh=+rPwZZ/qUmX25H2YF/zUoTN1Um6c9vFMRVZqBpxFLR0=; b=FDKuiPBJ6pHoaHAAOjm/GMm/bSA19W6g+aHoJcmhELrjw4CKWNHT8xfKoSg//u0lyz 0rDkIK23VrFFNJjt6v/lAYmRSuxjerHCxS0JZio/jw0ChbQ3w6Dr1nNVWtjeafwZOTJk YxM0q4GQVnrNFeUuPr5+NI2VCobRh7EiB4wgGszwfTJfDqgemjQoWv5iGuh7gS2/g5Wm gOEuJPAPcoB6qaSp43mSjBUmF+2zlsA0O+SEKhO8Rzdetw7HroSkNzQD3WXh8YLVlnc9 ZW2AACmXpOXqhpOZ86X/k9BrQp90FVgOcIo4Zl24weAcBZxYQcNvfmT3pDefl8h8njyC 701Q== X-Google-DKIM-Signature: v=1; a=rsa-sha256; c=relaxed/relaxed; d=1e100.net; s=20230601; t=1733139724; x=1733744524; h=cc:to:subject:message-id:date:from:in-reply-to:references :mime-version:x-gm-message-state:from:to:cc:subject:date:message-id :reply-to; bh=+rPwZZ/qUmX25H2YF/zUoTN1Um6c9vFMRVZqBpxFLR0=; b=b2TEKvAyo0jLQaoA0HoC5/nkUZawCth1HwO7l3Btsnd5fuuDQn1umye9tUk9COxAt1 8EhV4HJWYLM5w1v8uByqcJnBOv4TJ/em7U5SbJZCYdPf3SFdf6x/HrYAJ/JC9OPKkQYo 8Tqyt173JDkb5ZavtSPR52kLTuO1CGkON9jB/VBEwUqEUrgEIVSqnWrk/uMZVBcpKtnE Nd7Spgq3+9kpEd6pmYR4OCL/0b4Qbdft+qn8PW6R5XI7NXictLv1u7Qq5nqIzw21Vtus obLsHf2xC0EI5nUvLandX197K5JQSwCaXppFxafPktsap+6BLcIGRt6daLBJEgyE8P/B vKsA== X-Forwarded-Encrypted: i=1; AJvYcCWn3kVA1ZnNBw6WvSW6ABQ6FmdRjYZWmZxeELLdjX/ihc5Zd0B0IstdLMh+FCWqONPKqNyoPgGoMJSf6eps@postgresql.org X-Gm-Message-State: AOJu0YyurCwK4G466mIw2IymcJb8umJZilq4ezp9TFQnqMbX3Wxi5Nmt mMKiVjN+ss38nR/lhBCjxV9lsRWk+QucZ4dmqsos5x2205jXqggnbs+DcScHJ8IZfYgQbujO1iP Ss9Dz0IuLncl00ZpaJUscN9M5Ck28h6zdzsN3Tg== X-Gm-Gg: ASbGncuRDXl3mbHEIEjA+cyu3Jp7/FpeFQA5lV9fXkpLBKjnHO2RuV9QZBtmf+PoGrA JqIKojMCSvo7ifeve2etODHokdL6EpGVn X-Google-Smtp-Source: AGHT+IHwKeNCC3MyAhYzMKYFzz3e4XaOzIFhJFDq3peksETTpo0nMhm7aT4jgae2TAOrp2XHAdcdR8MnYw5JM+ofo6A= X-Received: by 2002:a17:906:3d31:b0:a9a:61d:7084 with SMTP id a640c23a62f3a-aa581065dd5mr1765209466b.50.1733139723830; Mon, 02 Dec 2024 03:42:03 -0800 (PST) MIME-Version: 1.0 References: <28e887b58c2e8b89f2753e6b11079e63d27ce613.camel@cybertec.at> In-Reply-To: <28e887b58c2e8b89f2753e6b11079e63d27ce613.camel@cybertec.at> From: Michael Christofides Date: Mon, 2 Dec 2024 11:41:48 +0000 Message-ID: Subject: Re: Proposals for EXPLAIN: rename ANALYZE to EXECUTE and extend VERBOSE To: Laurenz Albe Cc: Vik Fearing , David Rowley , Guillaume Lelarge , Greg Sabino Mullane , Robert Haas , "David G. Johnston" , Nikolay Samokhvalov , Tom Lane , pgsql-hackers@postgresql.org Content-Type: multipart/alternative; boundary="00000000000095e2930628480979" List-Id: List-Help: List-Subscribe: List-Post: List-Owner: List-Archive: Archived-At: Precedence: bulk --00000000000095e2930628480979 Content-Type: text/plain; charset="UTF-8" Content-Transfer-Encoding: quoted-printable > v4 patch attached Thank you Guillaume, and nice work! I tried to see if there was anywhere else in the documentation that would need updating, but it looks like you covered everywhere already. > I'm with Robert in that I've not found the buffer counts to be all that useful most of the time. I find the buffer counts especially helpful for educating newer folks on why things are slow, even when they are not necessary for spotting the issue (for more advanced users). One of my hopes is that by educating and empowering newer users on how I/O relates to performance issues, fewer cases will get escalated to more experienced folks. > the cases I've seen most recently are those where the output is mind-numbingly long already. Are you mostly seeing query plans that have stumped other people already (eg second or third line support), so perhaps seeing more complex plans than the average user? Both Depesz[1] and Tensor[2] have archives of publicly submitted plans, which I found helpful for checking how slow plans look for users of those tools. I have a similar archive, and while we do not publish them (and there are plenty of huge plans) it also suggests that the majority of slow plans people are reviewing have fewer than 20 nodes. I realise it=E2=80=99s optimistic to think that the time experienced hacker= s would lose having to sift through longer plans would be gained back by having to do so less often, but I thought it was worth raising as part of the aim. I also looked into the Slow Query Questions page on the wiki that we ask people to review before posting to pgsql-performance, and noticed that has suggested requesting buffers for the past 12 years[3]. =E2=80=94 Michael [1]: https://explain.depesz.com/history [2]: https://explain.tensor.ru/archive [3]: https://wiki.postgresql.org/index.php?title=3DSlow_Query_Questions&diff=3D1= 8308&oldid=3D16800 --00000000000095e2930628480979 Content-Type: text/html; charset="UTF-8" Content-Transfer-Encoding: quoted-printable
> v4 patch attached

Thank you Guillaume, and nice work! I tried to see if t= here was anywhere else in the documentation that would need updating, but i= t looks like you covered everywhere already.
<= /div>

> I'm with Robert in that I've no= t found the buffer counts to be all that useful most of the time.

I = find the buffer counts especially helpful for educating newer folks on why = things are slow, even when they are not necessary for spotting the issue (f= or more advanced users). One of my hopes is that by educating and empowerin= g newer users on how I/O relates to performance issues, fewer cases will ge= t escalated to more experienced folks.

> the cases I've seen = most recently are those where the output is mind-numbingly long already.

Are you mostly seeing query plans that have stumped other people alrea= dy (eg second or third line support), so perhaps seeing more complex plans = than the average user?

Both Depesz[1] and Te= nsor[2] have archives of publicly submitted plans, which I found helpful fo= r checking how slow plans look for users of those tools. I have a similar a= rchive, and while we do not publish them (and there are plenty of huge plan= s) it also suggests that the majority of slow plans people are reviewing ha= ve fewer than 20 nodes.

= I realise it=E2=80=99s optimistic to think that the time experienced hacker= s would lose having to sift through longer plans would be gained back by ha= ving to do so less often, but I thought it was worth raising as part of the= aim.

I also looked into= the Slow Query Questions page on the wiki that we ask people to review bef= ore posting to pgsql-performance, and noticed that has suggested requesting= buffers for the past 12 years[3].

=E2=80=94
Mi= chael

=
--00000000000095e2930628480979--