Lecture 20 part 2:

Other index structures:

PRIMARY
---------------------------

Hashing


CREATE INDEX name ON table USING HASH (column);

CREATE INDEX R ON table USING HASH (A);

Useful for searching on equality, not on range searches
A=10
A='ABC'


Clustering: sorting


CREATE INDEX I1 on movieroles(actorid, movieid);

CLUSTER movieroles USING I1;

Great for queries:

SELECT  * FROM movieroles WHERE actorid = 20;

SELECT  * FROM actor a, movieroles mr WHERE a.name = 'KB' and a.actorid = mr.actorid ;


SECONDARY - WHAT TO INDEX ON?
--------------------------------

single attribute, multiple attributes

index on a condition for all tuples:

CREATE INDEX test1_lower_col1_idx ON test1 (lower(col1));


partial index on a subset of tuples:

CREATE INDEX access_log_client_ip_ix ON access_log (client_ip)
WHERE NOT (client_ip > inet '192.168.100.0' AND
           client_ip < inet '192.168.100.255');


constraint satisfaction:

CREATE UNIQUE INDEX name ON table (column [, ...]);


OTHER TYPES OF SECONDARY INDICES
-------------------------------------

GIST / GIN / Sp-GIST (non-balanced)

Index spatial data, X,Y

R-tree -> B-Tree

Different types on indexes:

R-trees  (Type of GIST)


SP-Gist: non-balanced trees.





Punched cards!

https://computerhistory.org/blog/the-bourne-collection-online-search-is-older-than-you-think/

CREATE INDEX name ON table USING GIST (column [ { DEFAULT | tsvector_ops } (siglen = number) ] );

    Creates a GiST (Generalized Search Tree)-based index. The column
    can be of tsvector or tsquery type. Optional integer parameter
    siglen determines signature length in bytes (see below for
    details).

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



Inverted files


CREATE INDEX name ON table USING GIN (column);

    Creates a GIN (Generalized Inverted Index)-based index. The column
    must be of tsvector type.

select to_tsvector('english',make) from signatures ;

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