0

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)

recording(rid,made, budget)

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?

Read in

Pages(uses(rid,name,opus,royalty))

or

Pages(uses(opus,royalty))

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?

1

To answer your first question, it depends on if the RDBMS has stored the data as rowstore or columnstore. For a rowstore table (much more common), pages are organized into rows. You would read all columns for a row into memory. For a columnstore table, pages are organized into columns. You could just read only the columns relevant to the query into memory.

To answer your second question, why must "recording" be the outer table? What if the "uses" table has zero rows? Wouldn't it be more efficient to pick that as the outer table? Putting that aside, it's a very general question. You haven't listed a specific RDBMS platform. If there are no sufficient indexes the "recording" table then I would expect the optimizer to generally filter out pages using the filter on the "made" column before performing the join. I can't think of an advantage in doing it another way.

Why does the query planner need to calculate the number of disk page reads done? I don't follow that part.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.