From: Tom Lane <tgl@sss.pgh.pa.us>
To: "Dirschel, Steve" <steve.dirschel@thomsonreuters.com>
cc: "pgsql-performance@lists.postgresql.org"
 <pgsql-performance@lists.postgresql.org>
Subject: Re: Query Performance
In-reply-to: 
 <DM6PR03MB4332CC5841DDD299A01EF189FAE39@DM6PR03MB4332.namprd03.prod.outlook.com>
References: 
 <DM6PR03MB4332CC5841DDD299A01EF189FAE39@DM6PR03MB4332.namprd03.prod.outlook.com>
Comments: In-reply-to "Dirschel, Steve" <steve.dirschel@thomsonreuters.com>
	message dated "Wed, 21 Jul 2021 17:13:05 -0000"
MIME-Version: 1.0
Content-Type: text/plain; charset="us-ascii"
Content-ID: <713201.1626890615.1@sss.pgh.pa.us>
Content-Transfer-Encoding: quoted-printable
Date: Wed, 21 Jul 2021 14:03:35 -0400
Message-ID: <713202.1626890615@sss.pgh.pa.us>
Archived-At: 
 <https://www.postgresql.org/message-id/713202.1626890615%40sss.pgh.pa.us>
Precedence: bulk

"Dirschel, Steve" <steve.dirschel@thomsonreuters.com> writes:
> I have a sample query that is doing more work if some of the reads are p=
hysical reads and I'm trying to understand why.  If you look at attached Q=
ueryWithPhyReads.txt it shows the query did Buffers: shared hit=3D171 read=
=3D880.  So it did 171 + 880 =3D 1051 total block reads (some logical, som=
e physical).  QueryWithNoPhyReads.txt shows execution statistics of the ex=
ecution of the exact same query with same data point.  The only difference=
 is the first execution loaded blocks into memory so this execution had al=
l shared hits.  In this case the query did this much work:  Buffers: share=
d hit=3D581.

You haven't provided a lot of context for this observation, but I can
think of at least one explanation for the discrepancy.  If the first
query was the first access to these tables after a bunch of updates,
it would have been visiting a lot of now-dead row versions.  It would
then have marked the corresponding index entries dead, resulting in the
second execution not having to visit as many heap pages.

			regards, tom lane