postgresql index

[ 인덱스 종류 ]

1. B-tree index (default)

   1) Partial indexes : 조건에 맞게 인덱스 생성

      create index on test(type) where type is not null

    2) indexes on expressions : function등을 사용한 컬럼에 인덱스 생성

      create index on test (lower(name));

      select * from test where lower(first_name) = 'foo';    

      CREATE TABLE employee (employee_id INT PRIMARY KEY, supervisor_id INT);

       ALTER TABLE employee ADD CONSTRAINT supervisor_id_fkey FOREIGN KEY   

       (supervisor_id) REFERENCES employee(employee_id);

      CREATE UNIQUE INDEX ON employee ((1)) WHERE supervisor_id IS NULL;

 

2. Hash indexes :  동등 조건 비교시, replicatoin 복제시 데이터가 복제 되지 않을수 있음.

3. Generalized inverted index (GIN) : 여러값을 하나에 row 맵핑 ( arrays and full-text searchs 이용)

4. Generalized Search Tree (GiST) : 지리 정보 데이터 인덱스, full-text search

5. Block range index (BRIN) : 9.5 이후 버전, 사이즈가 제한된 큰 데이터 사용

* 여러개 컬럼에 인덱스 생성은 b-tree, GIN, GiST만 지원이 되고 최대 32개 컬럼으로 생성

[ 인덱스 rebuild ]

postgresql은 동일 컬럼에 대한 중복 인덱스를 생성할수 있음.

인덱스 rebuild시 동일 컬럼 항목에 동일한 인덱스 생성후 필요없는 항목을 삭제.

(reindex를 사용하여 기존 인덱스를 rebuild시 락이 발생할수 있음)

reindex  index  index명

is equal to

create index index2 on test(id);

drop index index1;

alter index index2 rename to index1; 

[인덱스 생성구문]

CREATE [ UNIQUE ] INDEX [ CONCURRENTLY ] [ [ IF NOT EXISTS ] name ] ON [ ONLY ] table_name [ USING method ]

    ( { column_name | ( expression ) } [ COLLATE collation ] [ opclass ] [ ASC | DESC ] [ NULLS { FIRST | LAST } ] [, ...] )

    [ INCLUDE ( column_name [, ...] ) ]

    [ WITH ( storage_parameter = value [, ... ] ) ]

    [ TABLESPACE tablespace_name ]

    [ WHERE predicate ]

create index on test.test (lower(name));