I'm trying to understand how the number of disk page reads for a block nested loop join is calculated (depending on the query) and how optimisations are applied to read in as few pages as possible.
If I have two relations (primary keys in bold)
uses(rid, name, opus, royalty)
We have buffer space enough to store 25 pages.
And an example query
SELECT recording.rid recording.budget uses.opus uses.royalty FROM recording JOIN uses USING (rid) WHERE recording.made = 1978
My first question is as pages, which contain records, are stored on disk, would the optimiser read in the pages which contain the columns we are selecting rather than reading all the columns in? Can the optimiser read in specific columns of a row in a page or does it have to read the entire page in first and then project out the columns it wants? If it's the former, we can read more rows in per page and hence less disk reads right?
My second question is, if we do the block nested loop join
uses BNLJ recording
Since filtering is done on recording.made = 1978 and uses is used as the outer loop, when reading in a page of recording, does the optimiser filter out pages which are not made = 1978 first then do the join? i.e check if the page it has read into memory is a page which contains made = 1978? If so, how does the query planner calculate the number of disk page reads done? Because for every 23 pages of uses that is read in, 1 page of recording is read in so it has to store the filtered page somewhere?