# Indexes

## Data structure

* Data structure used : B-Tree
* Self Balancing Index
* SELECT, INSERT, DELETE and sequential access  in logarithmic time
* Can be used for most of operations and column type
* supports unique condition
* Used in Primary Key
* Used with operators 
* Used when pattern matching

## Hash Index

<https://codingsight.com/hash-index-understanding-hash-indexes/>

* for equality operators
* not for range&#x20;
* Larger than btree in size

## Brin Index

* block range index
* block data -> min to max value
* smaller index
* less costly to maintain than btree index
* Can be used on very large table

```sql
create table t_big
(
    id   serial,
    name text
);

drop table t_big;

insert into t_big (name)
select 'adam'
from generate_series(1, 2000000);


CREATE INDEX CONCURRENTLY brin_index
    ON public.t_big USING brin
    (id);

create index btree_index on t_big(id);

select pg_size_pretty(pg_total_relation_size('t_big'));

select pg_size_pretty(pg_indexes_size('t_big'));

drop index brin_index;

drop index btree_index;

explain analyse
select *
from t_big
where id = 9999;

explain analyse
select id
from t_big
order by id desc limit 100;
```

## Partial Index

* To performance of the query while reducing the index size.

```sql
create index if not exists partial_inx on t_big(id) where id > 1000000;


explain analyse
select *
from t_big
where id = 10000033;


explain analyse
select *
from t_big
where id = 99999;
```

## Expression Index

* PostgreSQL will use this index when WHERE clause or ORDER BY clause in statement
* Very Expensive to use

```sql
CREATE TABLE IF NOT EXISTS t_dates AS
SELECT d, repeat(md5(d::text),10) as padding
    FROM generate_series
        (timestamp '1800-01-01', timestamp '2100-01-01', interval '1 day') s(d);

select * from t_dates limit 10;

vacuum analyse t_dates;

EXPLAIN ANALYSE
SELECT *
FROM t_dates
WHERE d BETWEEN '2001-01-01' AND '2001-01-31';


CREATE INDEX IF NOT EXISTS idx_t_dates_d on t_dates (d);

ANALYSE t_dates;

EXPLAIN ANALYSE
SELECT *
FROM t_dates
WHERE d BETWEEN '2001-01-01' AND '2001-01-31';

EXPLAIN ANALYSE
SELECT *
FROM t_dates
WHERE EXTRACT(DAY FROM d) = 1;

CREATE INDEX idx_expr_t_dates on t_dates (extract(day from d));

ANALYSE t_dates;

EXPLAIN ANALYSE
SELECT *
FROM t_dates
WHERE EXTRACT(DAY FROM d) = 1;
```

## Heap Index


---

# Agent Instructions: Querying This Documentation

If you need additional information that is not directly available in this page, you can query the documentation dynamically by asking a question.

Perform an HTTP GET request on the current page URL with the `ask` query parameter:

```
GET https://dev117uday.gitbook.io/databases/sql/indexing/indexes.md?ask=<question>
```

The question should be specific, self-contained, and written in natural language.
The response will contain a direct answer to the question and relevant excerpts and sources from the documentation.

Use this mechanism when the answer is not explicitly present in the current page, you need clarification or additional context, or you want to retrieve related documentation sections.