[SQL] 어떻게 PostgreSQL의에서 배열의 요소에 대한 인덱스를 만드는 방법?

어떻게 PostgreSQL의에서 배열의 요소에 대한 인덱스를 만드는 방법?

이 스키마로 :

create table object (
   obj_id      serial      primary key,
   name        varchar(80) not null unique,
   description text,
   tag_arr     int[]
);

create table tag (
   tag_id      serial      primary key,
   label       varchar(20) not null unique
);

객체는 부착 된 태그의 수를 가질 수있다. 대신 객체 X 태그 테이블에, 나는 그들이 개체 기록에 쉽게 인출 할 수 있도록 배열에 tag_ids을 유지하고 싶습니다.

어떻게 tar_arr의 각 요소는 인덱스 그래서 객체에 인덱스를 만들려면 어떻게해야합니까?

즉,이 문제를 해결하기 위해 더 나은 방법이 말했다?

이것은 달성 할 수있다 :

create table obj_x_tag(
   obj_id    references object,
   tag_id    references tag,
   constraint obj_x_tag_pk primary key( obj_id, tag_id )
);

select obj_id, name, description, array_agg( tag_id )
from object o
join obj_x_tag x using( obj_id )
group by 1, 2;

하지만 나에게 그것은) (단순히 교차 테이블과 array_agg와 열 및 분배에 tag_ids의 배열을 유지하는 것이 더 의미가 있습니다

배열에 결과를 변환 : 그것은 PostgreSQL의 SQL을 사용하도록 제안했다. 언급 된 문제는 "이 아니라 실제로 개별 인덱스 어레이 값이지만 인덱스 전체 어레이 않음"이다

또한 사용 페이지의 메서드가 호출 될 때 intArr과 요점 (또는 진) 지수에 제안했다. 문제는 - 나 - 나를 위해 그것의 직관을 - 인덱스가 반드시 배열의 한 요소를 찾기 위해 최적화되지 표준 페이지 설정 기반의 배열 사업자에 대해, 오히려 하나 개의 배열이 서로 다른, 교차을 포함하는 경우 것 같다 현명한 크기 및 속도 - 방식 등 다양한 솔루션은 좁은 문제 올바른지있다. 또한, 호출 될 때 intArr 확장이 그 유용성을 제한 INT64이나 숯을 피복하지 INT에 한정되는 것으로 보인다.

해결법

1. ==============================

   1.당신은 표준 포스트 그레스 어떤 1 차원 배열에 GIN 인덱스를 생성 할 수 있습니다. 여기에 수동 (마지막 장)의 세부 사항.

   당신은 표준 포스트 그레스 어떤 1 차원 배열에 GIN 인덱스를 생성 할 수 있습니다. 여기에 수동 (마지막 장)의 세부 사항.

   정수 배열을 운영하는 동안 (일반 INT4는 INT2 또는 INT8없이 NULL 값되지 않음) 추가 제공된 모듈 intarray는 더 많은 사업자와 일반적으로 우수한 성능을 제공합니다. 그것을 (데이터베이스 당 한 번)을 설치합니다

   CREATE EXTENSION intarray;
   

   당신은 정수 배열에 GIN 또는 GIST 인덱스를 생성 할 수 있습니다. 설명서의 사례가있다. EXTENSION는 PostgreSQL을 9.1 이상이 필요합니다 CREATE. 이전 버전의 경우에는 제공된 스크립트를 실행해야합니다.

2. ==============================

   2.기존의 솔루션은 태그의 테이블 태그와 객체 사이에 여러 일대을 사용하는 것입니다. 그럼 당신은 색인을 통해 하나의 select 문에서 태그 테이블과 풀의 모든 가입 할 수 있습니다. 당신이 프로그래밍 모델과 함께 행복하지 않다면, 지역 친화적 인 ORM 공급 업체에 확인하십시오.

   기존의 솔루션은 태그의 테이블 태그와 객체 사이에 여러 일대을 사용하는 것입니다. 그럼 당신은 색인을 통해 하나의 select 문에서 태그 테이블과 풀의 모든 가입 할 수 있습니다. 당신이 프로그래밍 모델과 함께 행복하지 않다면, 지역 친화적 인 ORM 공급 업체에 확인하십시오.

   나는 어떤 방법으로 PostgreSQL의 전문가가 아니지만,이 배열에 대한 좋은 유스 케이스처럼 보이지 않는다.

3. ==============================

   3.나는 동일을위한 PostgreSQL의 최적화 된 내부 함수를 볼 수 없기 때문, 내 해결 방법입니다

   나는 동일을위한 PostgreSQL의 최적화 된 내부 함수를 볼 수 없기 때문, 내 해결 방법입니다

   CREATE FUNCTION unnest_with_idx(anyarray) RETURNS 
   table(idx integer, val anyelement) AS $$ 
      SELECT generate_series(1,array_upper($1,1)) as idx, unnest($1) as val;
   $$ LANGUAGE SQL IMMUTABLE;
   -- Test:
   SELECT idx,val from unnest_with_idx(array[1,20,3,5]) as t;
   

   내부 기능이있는 경우 점검 내용은 "PostgreSQL을 사용하여 액세스 배열 내부 인덱스에?"참조 질문.

   @JimNasby 주석 후 편집

   SELECT * FROM unnest(array[20,11,3,5]) WITH ORDINALITY;
   

   WITH 순서와는 어레이 인덱스 새로운 열 "에 순서"를 생성한다. 이 자습서를 참조하십시오.

   pg9.5의 +에서는 JSON 배열에 대해서도 잘 작동합니다!

    SELECT * FROM jsonb_array_elements( '[20,11,3,5]'::JSONB ) WITH ORDINALITY
   

from https://stackoverflow.com/questions/10867577/how-to-create-an-index-for-elements-of-an-array-in-postgresql by cc-by-sa and MIT license