Re: Query Performance

"Dirschel, Steve" <steve(dot)dirschel(at)thomsonreuters(dot)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