📔
Databases
  • Table of contents
  • SQL
    • Getting Started
      • PgAdmin Tool
    • Data Types
      • Arrays
      • Date/Time/Stamps
      • JSON
      • Internal Functions
      • Sequences
      • User Defined Data Types
    • Database
      • Schema
    • Table
      • Aggregation
      • Usefull Functions
      • Combining Tables
      • Constraints
      • Common Table Expression
      • GROUP BY and HAVING
      • OPERATORS
      • ORDER BY and DISTINCT
      • Views
      • WHERE Clause
    • Order of SQL Execution
    • Advance Table
      • Internals
      • Managing Tables
      • Partitioning Tables
      • Pivotal or Crosstab Tables
    • Joins
      • Cross & Natural Joins
      • Full, Multiple & Self Joins
      • Inner Join
      • Left and Right JOIN
    • Functions
      • Cursors
      • PL/pgSQL
      • Stored Procedures
      • Triggers
        • More on Triggers
    • Indexing
      • Custom Indexes
      • GIN Index
      • Indexes
      • SQL
      • Unique Index
    • Summarization
      • SubQueries
      • Window
  • MongoDB
    • Mongo Administration
    • MongoDB Aggregation
    • MQL
  • Redis
  • Cassandra
    • CQL
    • Data Modelling
      • Advance Data Modelling
    • Cassandra Administration
    • Cassandra Features
Powered by GitBook
On this page
  • Data structure
  • Hash Index
  • Brin Index
  • Partial Index
  • Expression Index
  • Heap Index

Was this helpful?

  1. SQL
  2. Indexing

Indexes

PreviousGIN IndexNextSQL

Last updated 3 years ago

Was this helpful?

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

  • for equality operators

  • not for range

  • 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

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.

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

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

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