Index scans
----------------

Answering basic selection queries:


SELECT <attrs X> FROM R WHERE <cond C> ;

PAGES(R)

Index I cost of <C> = number of leaf pages in I that needs to be scanned

Relation look up cost of <C> = how many tuples from R to be read after
scanning the index and its cost.

----------------------------

Sequential scan:

Cost = PAGES(R)

---------------------------

Index scan:

Scan index I1 for condition C, and read matching tuples from R to
output attributes X

Cost = Cost of scanning the index + Cost of reading tuples from R

Index I cost of <C> = number of index pages in I that needs to be
scanned to find matching tuples for condition C

Relation look up cost of <C> = how many tuples from R to be read after
scanning the index and its cost.


Example:

SELECT A,B,C FROM R WHERE D='a' AND 10 =< E AND E <= 30;

PAGES(R) = 12,500 
TUPLES(R) = 250,000
-- 20 tuples per page in R --

How many tuples match?

D='a':                          5,000 tuples 
10 =< E AND E <= 30:            300 tuples
D='a' AND 10 =< E AND E <= 30:  10 tuples


Index I1 on R(D): 3 levels and 5000 nodes in the leaf level

250,000/5000 = 500 tuples per page at the leaf level

Index scan cost = 11 nodes + 2 = 13 nodes
Worst case: all matching 5,000 tupl,es are in 5,000 separate pages

Cost of this query using I1: 13 + 5,000 = 5,013

-------------


Index I4 on R(E,D): 3 levels and 2,000 nodes at the leaf level

250,000/2,000 = 125 tuples per page at the leaf level


Scan I4 for the E condition, 300 tuples/125 = about 3 nodes in I4 leaf level

Cost = 2 + 4 = 6 nodes

I will read only 10 tuples from R

Cost this query = 10 + 6 = 16

--------------------

Index only scan:


SELECT A,B,C FROM R WHERE D='a' AND 10 =< E AND E <= 30;

PAGES(R) = 12,500 
TUPLES(R) = 250,000
-- 20 tuples per page in R --


Index I5 on R(D,E,A,B,C)

Scan the index: find tuples matching condition C and attributes X
needed for the output.

---------------------------
Multiple index scan: scan multiple indices and intersect in memory
and if needed, read matching tuples

SELECT A,B,C FROM R WHERE D='a' AND 10 =< E AND E <= 30;

Index I1 on R(D): 
Index I1 on R(E): 

Read multiple indices, intersect in memory (e.g.using hashing), then
read matching tuples from R


---------------------------

1. Sequential Scan

2. Index scan: scan index + read R

3. Index only scan: scan only index (if it has all attributes needed for query)

4. Multiple index scan: scan multiple indices