Lecture 19
-----------------

1. Disk access is slow
2. All data may not fit in memory
3. Optimize number of disk pages read/written for each query

-> Query Cost: number of disk pages read/written


Data page/Data block: 8KB

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


 moviescast            |       38 pages

A single page of moviescast, stores 159 tuples.

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

A single table spans many disk pages, each disk page stores multiple tuples.

-----------

Integer: 8 bytes

SELECT current_setting('block_size');

SELECT relname, relpages
FROM pg_class pc, pg_user pu
WHERE pc.relowner = pu.usesysid and pu.usename = user
ORDER BY relpages desc;

-----------

What is the cost a query:

SELECT * FROM moviescast ;

Cost: 38 pages

SELECT * FROM moviescast  WHERE movieid = 30;


Cost: 38 pages [Sequential Scan]


Index on movieid?


Secondary Access Structures: search index for a query + find matching
tuples + read those tuples from disk

Relation is stored on Disk

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

Dense index: a single node for each tuple

Sparse index: a single node for a range of tuples