캔 포스트그레SQL 인덱스 배열 열?

캔 포스트그레SQL 인덱스 배열 열?

문서에서 이 질문에 대한 확실한 답을 찾을 수 없습니다.열이 배열 유형인 경우 입력한 모든 값이 개별적으로 인덱싱됩니까?

하나로 간단한 테이블을 만들었습니다.int[]열에 고유 인덱스를 추가합니다.나는 동일한 int 배열을 추가할 수 없다는 것을 알게 되었고, 이로 인해 인덱스가 각 항목의 인덱스가 아닌 배열 항목의 합성이라고 믿게 되었습니다.

INSERT INTO "Test"."Test" VALUES ('{10, 15, 20}');
INSERT INTO "Test"."Test" VALUES ('{10, 20, 30}');

SELECT * FROM "Test"."Test" WHERE 20 = ANY ("Column1");

인덱스가 이 쿼리에 도움이 됩니까?

예, 배열을 인덱싱할 수 있지만 배열 연산자와 GIN-index 유형을 사용해야 합니다.

예:

    CREATE TABLE "Test"("Column1" int[]);
    INSERT INTO "Test" VALUES ('{10, 15, 20}');
    INSERT INTO "Test" VALUES ('{10, 20, 30}');

    CREATE INDEX idx_test on "Test" USING GIN ("Column1");

    -- To enforce index usage because we have only 2 records for this test... 
    SET enable_seqscan TO off;

    EXPLAIN ANALYZE
    SELECT * FROM "Test" WHERE "Column1" @> ARRAY[20];

결과:

Bitmap Heap Scan on "Test"  (cost=4.26..8.27 rows=1 width=32) (actual time=0.014..0.015 rows=2 loops=1)
  Recheck Cond: ("Column1" @> '{20}'::integer[])
  ->  Bitmap Index Scan on idx_test  (cost=0.00..4.26 rows=1 width=0) (actual time=0.009..0.009 rows=2 loops=1)
        Index Cond: ("Column1" @> '{20}'::integer[])
Total runtime: 0.062 ms

Note

많은 경우 gin__int_ops 옵션이 필요한 것으로 보입니다.

create index <index_name> on <table_name> using GIN (<column> gin__int_ops)

gin__int_ops 옵션이 없는 && 및 @> 연산자와 함께 작동하는 경우는 아직 본 적이 없습니다.

@Tregoreg는 그의 제안된 현상금에 대해 논평에서 문제를 제기했습니다.

현재 답변이 작동하지 않습니다.배열 형식 열에 GIN 인덱스를 사용해도 ANY() 연산자의 성능이 증가하지 않습니다.정말 해결책이 없을까요?

@Frank의 수락된 답변은 배열 연산자를 사용하라는 것으로, Postgres 11에 대해서는 여전히 맞습니다.설명서:

Postgre의 표준 분포SQL에는 배열에 대한 GIN 연산자 클래스가 포함되어 있으며, 다음 연산자를 사용하여 인덱싱된 쿼리를 지원합니다.

<@
@>
=
&&

표준 분포의 GIN 인덱스에 대한 기본 제공 연산자 클래스의 전체 목록은 다음과 같습니다.

Postgres에서 인덱스는 데이터 유형이나 함수 또는 기타 항목이 아닌 연산자(특정 유형에 대해 구현됨)에 바인딩됩니다.그것은 Postgres의 원래 버클리 디자인에서 나온 유산이고 지금은 바꾸기가 매우 어렵습니다.그리고 일반적으로 잘 작동합니다.여기 톰 레인이 이것에 대해 언급하고 있는 pgsql-bugs의 스레드가 있습니다.

_{일부 PostGis 함수(예: )는 이 원리를 위반하는 것처럼 보이지만 그렇지 않습니다.이러한 함수는 각 연산자를 사용하기 위해 내부적으로 다시 작성됩니다.}

인덱싱된 식은 연산자의 왼쪽에 있어야 합니다.대부분의 연산자(위의 모든 연산자 포함)에서 인덱스된 식을 오른쪽에 배치하면 쿼리 플래너가 피연산자를 뒤집어서 이를 달성할 수 있습니다.구성은 다양한 연산자와 함께 사용할 수 있으며 연산자 자체가 아닙니다.로 사용할 경우constant = ANY (array_expression)을 =어레이 요소의 연산자는 자격이 있고 우리는 다음을 위한 정류자가 필요합니다.= ANY()GIN 인덱스가 없습니다.

Postgres는 현재 GIN 색인 가능한 표현식을 도출할 만큼 충분히 똑똑하지 않습니다.일은단.constant = ANY (array_expression)와 완전히 동일하지는 않습니다.array_expression @> ARRAY[constant]어레이 연산자가 NULL 요소가 포함된 경우 오류를 반환하는 동안ANY는 어느할 수 .construct는 NULL을 처리합니다.그리고 데이터 유형 불일치에 대한 다른 결과가 있습니다.

관련 답변:

• Postgres 배열에 값이 있는지 확인합니다.

• JSON 배열에서 요소를 찾기 위한 색인

• SQLAlchemy: PgArray 열 유형을 필터링하는 방법은 무엇입니까?

• IS DISTINCT FROM은 어떤 식으로든 ANEL 또는 ALL과 결합될 수 있습니까?

사이드

어레이 작업 중 (int4,것은 아니다.int2또는int8) 이없NULL값(예와 같이)은 특수하고 빠른 연산자 및 인덱스 지원을 제공하는 추가 모듈을 고려합니다.참조:

• PostgreSQL에서 배열 요소에 대한 인덱스를 만드는 방법은 무엇입니까?
• 요소 순서를 무시한 채 동일한 배열 비교

에 UNIQUE답변되지 않은 질문에 대한 제약:이는 전체 배열 값에 대한 btree 인덱스를 사용하여 구현되며 요소 검색에 전혀 도움이 되지 않습니다.세부사항:

• Postgre는 어떻습니까?SQL에서 고유 제약 조건을 적용합니다. / 어떤 유형의 인덱스를 사용합니까?

이제 개별 배열 요소를 인덱싱할 수 있습니다.예:

CREATE TABLE test (foo int[]);
INSERT INTO test VALUES ('{1,2,3}');
INSERT INTO test VALUES ('{4,5,6}');
CREATE INDEX test_index on test ((foo[1]));
SET enable_seqscan TO off;

EXPLAIN ANALYZE SELECT * from test WHERE foo[1]=1;
                                                QUERY PLAN                                                    
------------------------------------------------------------------------------------------------------------------
 Index Scan using test_index on test  (cost=0.00..8.27 rows=1 width=32) (actual   time=0.070..0.071 rows=1 loops=1)
   Index Cond: (foo[1] = 1)
 Total runtime: 0.112 ms
(3 rows)

이는 적어도 Postgres 9.2.1에서 작동합니다.각 배열 인덱스에 대해 별도의 인덱스를 작성해야 합니다. 이 예에서는 첫 번째 요소만 인덱싱했습니다.

언급URL : https://stackoverflow.com/questions/4058731/can-postgresql-index-array-columns