Minimum buffer requirement when pipelining join operation

Slideshow:

A general buffer minimalization problem...

Problem description:

Note: this type of problem is harder than then ones we need to solve for query optimization (see later)

Buffer minimalization problem 1: ⋈₁ and ⋈₂ are 1-pass

Problem description:

Problem 1: ⋈₁ and ⋈₂ are using the 1-pass algorithm

Buffer minimalization problem 1: ⋈₁ and ⋈₂ are 1-pass

Consider the execution of the query plan:

Buffer minimalization problem 1: ⋈₁ and ⋈₂ are 1-pass

Consider the execution of the query plan:

Note: you cannot free the buffers used to index R (B(R)) while scanning tuples from S

Buffer minimalization problem 1: ⋈₁ and ⋈₂ are 1-pass

Consider the execution of the query plan:

Note: you can free the buffers used to index R (B(R)) because ⋈₁ is done !

Buffer minimalization problem 1: ⋈₁ and ⋈₂ are 1-pass

# buffers used in the query processing in various stages:

⋈₁'s phase 1: B(R) + 1
⋈₁'s phase 2: B(R) + 1 + B(R⋈S)
⋈₂'s phase 1: B(R⋈S) + 1

Example analysis

Statistics on the input relations:

Find the minimum # buffers needed to execute the following query plan:

Where ⋈₁ and ⋈₂ are using the 1-pass algorithm

Example analysis

Statistics on the input relations:

Processing step 1:

Example analysis

Statistics on the input relations:

Preparing for processing step 2:

Example analysis

Statistics on the input relations:

Processing step 2:

Example analysis

Statistics on the input relations:

Processing step 3:

Minimum # buffers needed = 6001 (used in step 2 of the processing)

Buffer minimalization problem 2: ⋈₁ is 1-pass and ⋈₂ is 2-pass hashing

(Finding this expression will be a homework problem)

Buffer minimalization problem 2: ⋈₁ is 1-pass and ⋈₂ is 2-pass hashing - Example

Buffer minimalization problem 2: ⋈₁ is 1-pass and ⋈₂ is 2-pass hashing - Example

How to execute the query plan with 1006 buffers:

Buffer minimalization problem 2: ⋈₁ is 1-pass and ⋈₂ is 2-pass hashing - Example

How to execute the query plan with 1006 buffers:

Pass 1 of ⋈₂ will use the (remaining) 5 buffers to hash its input into 5 (smaller) chunks

Buffer minimalization problem 2: ⋈₁ is 1-pass and ⋈₂ is 2-pass hashing - Example

How to execute the query plan with 1006 buffers:

Notice that the chunk size is approximately B(R⋈S)/5 = 5000/5 = 1000 blks

Notice that the query execution is currently using the # available buffers...

Buffer minimalization problem 2: ⋈₁ is 1-pass and ⋈₂ is 2-pass hashing - Example

How to execute the query plan with 1006 buffers:

Buffer minimalization problem 2: ⋈₁ is 1-pass and ⋈₂ is 2-pass hashing - Example

How to execute the query plan with 1006 buffers:

We must hash the tuples in the 2nd relation T:

Buffer minimalization problem 2: ⋈₁ is 1-pass and ⋈₂ is 2-pass hashing - Example

How to execute the query plan with 1006 buffers:

Buffer minimalization problem 2: ⋈₁ is 1-pass and ⋈₂ is 2-pass hashing - Example

How to execute the query plan with 1006 buffers:

Notice that the chunk size ~= 1000 blks and we can run the 1-pass algorithm on each chunk !!

Buffer minimalization problem 2: ⋈₁ is 1-pass and ⋈₂ is 2-pass hashing - Example

Also: you cannot execute this query plan using 1005 buffers !

The resulting chunks hashed will have size = 5000/4 = 1250 > 1006 buffers !!

So you need a minumum of 1006 buffers

Postscript

Finding the best (implementation) algorithm is a "search" problem which is much simpler than this minimalization problem

Minimum buffer requirement in pipelining
- Consider a 2 staged join operation where tuples are passed using pipelining:
  Problem:

Example 1: ⋈₁ = 1-pass algorithm and ⋈₂ = 1-pass algorithm

Find the minimum buffer requirement for the pipelined execution:

⋈₁ = 1-pass algorithm --- assuming that B(R) ≤ B(S)

(B(R) ≤ B(S) is guaranteed after we perform the optimization on the logical query plan)

The one-pass ⋈₁ will:
Buffer utilization:
Buffer utilization so far = B(R) + 1
While ⋈₁ is active, the ⋈₁ operation will output tuples that will be indexed by the 1-pass algorithm ⋈₂:
Buffer utilization = B(R) + 1 + B(R⋈S)
When ⋈₁ operation is completed (done), we can free the buffers and use 1 buffer to scan the relation T:
Simultaneous buffer utilization = 1 + B(R⋈S)

Conclusion:

Minumum buffer requirement (M) ≥ B(R) + 1 + B(R⋈S)
(= the highest buffer utilization during the entire process)

Example:

R(a,b) S(b,c) T(c,d) ================================================== a V(R,a) = 100 b V(R,b) = 200 V(S,b) = 100 c V(S,c) = 500 V(T,c) = 20 ================================================= B(R) = 1000 B(S) = 1000 B(T) = 1000

The minimum # buffers needed to execute the following pipelined join operation:

is computed as follows:

R ⋈₁ S will index R with 1000 buffers and use 1 buffer to scan S:
# buffers used = B(R) + 1 = 1000 + 1 = 1001

The size of R ⋈ S is estimated as:

B(R) × B(S) B( R ⋈ S ) = ----------------------- max( V(R,b), V(S,b) ) 1000 × 1000 = -------------- = 5000 blocks max(200,100)

Therefore, the one-pass ⋈₂ operation will need 5000 buffers to index tuples in R ⋈ S :

# buffers used = 1000 + 1 + 5000

When R ⋈₁ S is done, we only need 5000 buffers to store the tuples of R ⋈₁ S:
(The freed buffers can be used in the operators higher up in the query tree if any)

Maximum buffer requirement:

M ≥ 1000 + 1 + 5000 = 6001

Example 2: ⋈₁ = 1-pass algorithm and ⋈₂ = 2-pass hash algorithm

The minimum buffer requirement for the pipelined execution of:

where:

⋈₁ = 1-pass algorithm
⋈₂ = 2-pass hashing-based algorithm

is:

B(R) + sqrt( B(R)² + 4 B(R⋈S) ) M ≥ ---------------------------------- + 1 2

Example:

R(a,b) S(b,c) T(c,d) ================================================== a V(R,a) = 100 b V(R,b) = 200 V(S,b) = 100 c V(S,c) = 500 V(T,c) = 20 ================================================= B(R) = 1000 B(S) = 1000 B(T) = 1000

The minimum number of memory buffers required to execute:

is:

1000 + sqrt(1000² + 4×5000) M ≥ ------------------------------ + 1 2 1000 + 1010 = ------------- + 1 = 1006 buffers 2

How to execute the query using 1006 buffers:

The one-pass ⋈₁ will:
Buffer utilization:
Buffer utilization so far = 1000 + 1 = 1001 buffers
While ⋈₁ is active, the ⋈₁ operation will output tuples into the 2-pass hash-based algorithm for ⋈₂:
⋈₂ will use the remaining 5 buffers
The size of each chunk is (approximately) = B(R ⋈ S)/5 = 5000/5 = 1000 block :
Simultaneous buffer utilization = 1000 + 1 + 5 = 1006 buffers
When ⋈₁ operation is completed (done), we can free up all buffers used:
because the hashed backets are stored on disk
Simultaneous buffer utilization = 0 buffers
We must use 5 buckets to hash the relation T:
(Because the same # buckets must be used to ensure the same hash bucket for identical keys)

Simultaneous buffer utilization = 5 + 1
After we hash relation T, all buffers can be free up:
In pass 2 of the 2-pass hash-based join, we use a 1-pass join algorithm to process each chunk (size of each chunk being indexed ~= 1000 blocks):
Buffer requirement to for pass 2: 1000 + 1

Therefore:

We can use 1006 memory buffer to execute:

(The general formula will be a homework question)

Minimum buffer requirement vs "adequate" buffer requirement

We saw an 2 examples on how to determine:

Given a physical query plan where ⋈₁ and ⋈₂ uses a given implementating algorithms:
Problem:

Fact:

This is not the type of questions that is solved when we try to find the best physical query plan !!!
Finding the best physical query plan is an adequacy problem, and not a minimalization problem....

Finding the best physical query plan:

Given a logical query plan where ⋈₁ and ⋈₂ do not have an assigned implementating algorithms and M buffers:
Problem: