Database 3:PostgreSQL Indexes, B-tree

1

1

An index is an additional database structure built with the specified columns of an existing table.

The index optimizes queries that reference its columns, which allows the query planner to avoid a full table scan. The size and speed of an index depend on its design and the type of data it holds.

The CREATE INDEX command creates an index for its specified table columns. The B-tree indexing method defines an index structure optimized for equality and range queries.

//Indexes are most effective on large datasets
CREATE TABLE tavole (
    id  SERIAL PRIMARY KEY, codice  INT
);

INSERT INTO tavole (codice, nome)
    SELECT FLOOR(RANDOM() * 10000) FROM GENERATE_SERIES(1, 10000);

//If no specified the database creates a B-tree index
CREATE INDEX indice ON tavole(codice);

//We disable the sec. scan to make the index be used by the query planner.
SET enable_seqscan = off;
SET enable_seqscan = on;

//Explain Analyze returns the scan stats
explain analyze select * from tavole where codice > 5000;

Sequential query scan and Bitmap index scan statistics

The query planner uses an index if its overhead cost is lower than that of a sequential scan. It also depends on the query clauses (e.g., LIKE or JOIN), as some are not supported by certain index types.

An index can be implicitly created by a constraint and is automatically deleted when its associated table is dropped.

//The unique constraint implicitly creates a b-tree index
CREATE TABLE intervalli (
    inizio INTEGER, fine INTEGER,
    UNIQUE (inizio, fine)
);

//Equivalent to an EXCLUDE with extendable comparison conditions
CREATE TABLE intervalli1 (
    inizio INTEGER, fine INTEGER,
    EXCLUDE USING btree (inizio WITH =, fine with =)
);

The CREATE INDEX includes the option argument using the WITH keyword. It configures the index access method but offers a more limited set of available options compared to other index types.

//FILLFACTOR sets the percentage of a node that can be filled with entries.
CREATE INDEX my_index ON my_table (my_column) WITH (FILLFACTOR = 90);

1

1

1

1

1

1

1

1

1

1

1

1

1

1

1

1

1

1

1

1

1

1

1

1

1