Re: Consecutive Query Executions with Increasing Execution Time

--000000000000565af00599e894d6
Content-Type: text/plain; charset="UTF-8"

On Tue, Dec 17, 2019 at 8:08 AM Laurenz Albe <[email protected]>
wrote:

> On Mon, 2019-12-16 at 15:50 -0500, Tom Lane wrote:
> > Peter Geoghegan <[email protected]> writes:
> > > Why do the first and the twentieth executions of the query have almost
> > > identical "buffers shared/read" numbers? That seems odd.
> >
> > It's repeat execution of the same query, so that doesn't seem odd to me.
>
> Really?  Shouldn't the blocks be in shared buffers after a couple
> of executions?
>

If it is doing a seq scan (I don't know if it is) they intentionally use a
small ring buffer to, so they evict their own recently used blocks, rather
than evicting other people's blocks.  So these blocks won't build up in
shared_buffers very rapidly just on the basis of repeated seq scans.

Cheers,

Jeff

--000000000000565af00599e894d6
Content-Type: text/html; charset="UTF-8"
Content-Transfer-Encoding: quoted-printable

<div dir=3D"ltr"><div dir=3D"ltr">On Tue, Dec 17, 2019 at 8:08 AM Laurenz A=
lbe &lt;<a href=3D"mailto:[email protected]">[email protected]=
t</a>&gt; wrote:<br></div><div class=3D"gmail_quote"><blockquote class=3D"g=
mail_quote" style=3D"margin:0px 0px 0px 0.8ex;border-left:1px solid rgb(204=
,204,204);padding-left:1ex">On Mon, 2019-12-16 at 15:50 -0500, Tom Lane wro=
te:<br>
&gt; Peter Geoghegan &lt;<a href=3D"mailto:[email protected]" target=3D"_blank">pg=
@bowt.ie</a>&gt; writes:<br>
&gt; &gt; Why do the first and the twentieth executions of the query have a=
lmost<br>
&gt; &gt; identical &quot;buffers shared/read&quot; numbers? That seems odd=
.<br>
&gt; <br>
&gt; It&#39;s repeat execution of the same query, so that doesn&#39;t seem =
odd to me.<br>
<br>
Really?=C2=A0 Shouldn&#39;t the blocks be in shared buffers after a couple<=
br>
of executions?<br></blockquote><div><br></div><div>If it is doing a seq sca=
n (I don&#39;t know if it is) they intentionally use a small ring buffer to=
, so they evict their own recently used blocks, rather than evicting other =
people&#39;s blocks.=C2=A0 So these blocks won&#39;t build up in shared_buf=
fers very rapidly just on the basis of repeated seq scans.</div><div>=C2=A0=
</div><div>Cheers,</div><div><br></div><div>Jeff</div></div></div>

--000000000000565af00599e894d6--