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
