"Dirschel, Steve" <steve.dirsc...@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
