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
Date: Wed, 21 Jul 2021 14:03:35 -0400
Message-ID: <713202.1626890615@sss.pgh.pa.us> (raw)
In-Reply-To: <DM6PR03MB4332CC5841DDD299A01EF189FAE39@DM6PR03MB4332.namprd03.prod.outlook.com>
References: <DM6PR03MB4332CC5841DDD299A01EF189FAE39@DM6PR03MB4332.namprd03.prod.outlook.com>
"Dirschel, Steve" <steve.dirschel@thomsonreuters.com> writes:
> I have a sample query that is doing more work if some of the reads are physical reads and I'm trying to understand why. If you look at attached QueryWithPhyReads.txt it shows the query did Buffers: shared hit=171 read=880. So it did 171 + 880 = 1051 total block reads (some logical, some physical). QueryWithNoPhyReads.txt shows execution statistics of the execution of the exact same query with same data point. The only difference is the first execution loaded blocks into memory so this execution had all shared hits. In this case the query did this much work: Buffers: shared hit=581.
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
Reply instructions:
You may reply publicly to this message via plain-text email
using any one of the following methods:
* Reply to all the recipients using the --to and --cc options:
reply via email
To: pgsql-performance@postgresql.org
Cc: tgl@sss.pgh.pa.us, steve.dirschel@thomsonreuters.com, pgsql-performance@lists.postgresql.org
Subject: Re: Query Performance
In-Reply-To: <713202.1626890615@sss.pgh.pa.us>
* Save the following mbox file, import it into your mail client,
and reply-to-all from there: mbox
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox