Thread: Query Performance

Query Performance

From
"Dirschel, Steve"
Date:

New to Postgres, Oracle background.  With Oracle the amount of work a query does is tracked via logical reads.  Oracle tracks logical and physical reads differently than Postgres.  With Oracle a physical read is always considered a logical read.  So if a query reads 5 blocks are all 5 are read from disk the query would do 5 logical reads, 5 physical reads.  It appears with Postgres Buffers shared hit are reads from memory and Buffer shared read is off disk.  To get total reads one would need to add up shared hits + shared reads.

 

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.

 

With Oracle that would not happen.  If the 2nd execution of the query did all reads from memory the shared hits would be 1051, not 581. 

 

So it appears to me that with Postgres when a query does physical reads it not only has the expense of doing those disk reads but there is also extra work done to increase overall block reads for a query.  But I don’t understand why that would be the case.  Could someone explain why this is happening?

 

Thanks

This e-mail is for the sole use of the intended recipient and contains information that may be privileged and/or confidential. If you are not an intended recipient, please notify the sender by return e-mail and delete this e-mail and any attachments. Certain required legal entity disclosures can be accessed on our website: https://www.thomsonreuters.com/en/resources/disclosures.html
Attachment

Re: Query Performance

From
Tom Lane
Date:
"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
theexecution of the exact same query with same data point.  The only difference is the first execution loaded blocks
intomemory 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