Secondary storage continued!
-------------------------------
Lecture exercise today
Still no exam results, sorry about that.
create index playedonradio1_idx on playedonradio(station,songid);
create index playedonradio2_idx on playedonradio(station);
select songid from playedonradio where station = 'mai' ;
select station from playedonradio where songid = 1234 ;
B-trees are super useful
----------------------------
Index on R(X,Y) can be used for many types of searches (to different
levels of effectiveness)
effectiveness = how much of the leaf level do I need to scan
X=1 and Y=5 or X=1 and Y>=5 and Y<=10
X=1 or X>=1 and X<=4
X>=1 and X<=4 and Y=2 (Y condition is mostly irrelevant)
Y=2 (Y condition is irrelevant, scan the whole index)
But wait, are there other types of indices?
------------------------------------------------
R-tree (points, any geometric object)
balanced tree
Postgresql -> GIST trees
Unbalanaced trees -> Quadtrees and k-d trees
Postgresql -> Sp-GIST trees
create extension pg_trgm;
create table rs as select * from rollingstonetop500 ;
alter table rs add review tsvector ;
update rs set review = to_tsvector(critic) ;
create index review_idx on rs using gin(review) ;
Inverted index:
W1 -> t1:2,4 t4:4,6
select a.name, r.album
from artists a, rs r
where r.artistid = a.id
and r.review @@ to_tsquery('(masterpiece | avantgarde) & (comtemporary | modern)');
--------------
Secondary Index vs. Primary Index
-----------------------------------------
select * from playedonradio where station = 'mai';
Secondary indices do not change how the relation is stored on disk
Just creates look up structures
Primary indices change how to store the data
Hashing is a way to create a primary index
Hashing is useful for equality searches
Hash on R(A) is useful for queries: A=x
Primary index structures are useful for some queries but
need to be maintained to remain effective