PostgreSQL에서 인덱스 작업에 관한 몇 가지 질문이 있습니다. Friends
다음 색인 이있는 테이블이 있습니다.
Friends ( user_id1 ,user_id2)
user_id1
및 user_id2
외국인 열쇠 user
표
-
이것들은 동등합니까? 그렇지 않다면 왜?
Index(user_id1,user_id2) and Index(user_id2,user_id1)
-
기본 키 (user_id1, user_id2)를 생성하면 자동으로 인덱스를 생성하고
첫 번째 질문의 색인이 동일하지 않으면 위의 기본 키 명령에서 어떤 색인이 작성됩니까?
답변
다음은 여러 열 인덱스 의 두 번째 열 에서 테이블을 쿼리 한 결과입니다 .
효과는 누구나 쉽게 재현 할 수 있습니다. 집에서 해보십시오.
23322 행의 실제 데이터베이스의 중간 크기 테이블을 사용하여 Debian 에서 PostgreSQL 9.0.5로 테스트했습니다 . 테이블 adr
(주소)과 att
(속성) 간의 n : m 관계를 구현 하지만 여기서는 관련이 없습니다. 단순화 된 스키마 :
CREATE TABLE adratt (
adratt_id serial PRIMARY KEY
, adr_id integer NOT NULL
, att_id integer NOT NULL
, log_up timestamp(0) NOT NULL DEFAULT (now())::timestamp(0)
, CONSTRAINT adratt_uni UNIQUE (adr_id, att_id)
);
UNIQUE
제약 효과적으로 고유 인덱스를 구현합니다. 평범한 색인으로 테스트를 반복하여 예상대로 동일한 결과를 얻었습니다.
CREATE INDEX adratt_idx ON adratt(adr_id, att_id)
테이블은 adratt_uni
인덱스에서 클러스터되고 테스트 전에 실행되었습니다.
CLUSTER adratt;
ANALYZE adratt;
쿼리에 대한 순차적 스캔 (adr_id, att_id)
은 가능한 한 빠릅니다. 다중 열 인덱스는 여전히 두 번째 인덱스 열의 쿼리 조건에 계속 사용됩니다.
캐시를 채우기 위해 쿼리를 두 번 실행했으며 비슷한 결과를 얻기 위해 10 회 실행 중 최고를 선택했습니다.
1. 두 열을 모두 사용하여 쿼리
SELECT *
FROM adratt
WHERE att_id = 90
AND adr_id = 10;
adratt_id | adr_id | att_id | log_up
-----------+--------+--------+---------------------
123 | 10 | 90 | 2008-07-29 09:35:54
(1 row)
출력 EXPLAIN ANALYZE
:
Index Scan using adratt_uni on adratt (cost=0.00..3.48 rows=1 width=20) (actual time=0.022..0.025 rows=1 loops=1) Index Cond: ((adr_id = 10) AND (att_id = 90)) Total runtime: 0.067 ms
2. 첫 번째 열을 사용한 쿼리
SELECT * FROM adratt WHERE adr_id = 10
adratt_id | adr_id | att_id | log_up
-----------+--------+--------+---------------------
126 | 10 | 10 | 2008-07-29 09:35:54
125 | 10 | 13 | 2008-07-29 09:35:54
4711 | 10 | 21 | 2008-07-29 09:35:54
29322 | 10 | 22 | 2011-06-06 15:50:38
29321 | 10 | 30 | 2011-06-06 15:47:17
124 | 10 | 62 | 2008-07-29 09:35:54
21913 | 10 | 78 | 2008-07-29 09:35:54
123 | 10 | 90 | 2008-07-29 09:35:54
28352 | 10 | 106 | 2010-11-22 12:37:50
(9 rows)
출력 EXPLAIN ANALYZE
:
Index Scan using adratt_uni on adratt (cost=0.00..8.23 rows=9 width=20) (actual time=0.007..0.023 rows=9 loops=1) Index Cond: (adr_id = 10) Total runtime: 0.058 ms
3. 두 번째 열을 사용한 쿼리
SELECT * FROM adratt WHERE att_id = 90
adratt_id | adr_id | att_id | log_up
-----------+--------+--------+---------------------
123 | 10 | 90 | 2008-07-29 09:35:54
180 | 39 | 90 | 2008-08-29 15:46:07
...
(83 rows)
출력 EXPLAIN ANALYZE
:
Index Scan using adratt_uni on adratt (cost=0.00..818.51 rows=83 width=20) (actual time=0.014..0.694 rows=83 loops=1) Index Cond: (att_id = 90) Total runtime: 0.849 ms
4. indexscan & bitmapscan 비활성화
SET enable_indexscan = off;
SELECT * FROM adratt WHERE att_id = 90
EXPLAIN ANALYZE의 결과 :
Bitmap Heap Scan on adratt (cost=779.94..854.74 rows=83 width=20) (actual time=0.558..0.743 rows=83 loops=1) Recheck Cond: (att_id = 90) -> Bitmap Index Scan on adratt_uni (cost=0.00..779.86 rows=83 width=0) (actual time=0.544..0.544 rows=83 loops=1) Index Cond: (att_id = 90) Total runtime: 0.894 ms
SET enable_bitmapscan = off
SELECT * FROM adratt WHERE att_id = 90
출력 EXPLAIN ANALYZE
:
Seq Scan on adratt (cost=0.00..1323.10 rows=83 width=20) (actual time=0.009..2.429 rows=83 loops=1) Filter: (att_id = 90) Total runtime: 2.680 ms
결론
예상 한대로 다중 열 인덱스는 두 번째 열의 쿼리에만 사용됩니다.
예상 한 것처럼 효과는 떨어지지 만 쿼리는 인덱스가없는 것 보다 여전히 3 배 빠릅니다 .
인덱스 스캔을 비활성화 한 후 쿼리 플래너는 비트 맵 힙 스캔을 선택합니다.이 스캔은 거의 빠른 속도로 수행됩니다. 이 기능을 비활성화 한 후에 만 순차적 스캔으로 돌아갑니다.
답변
다시 1) 그렇습니다.
두 열을 모두 사용하는 쿼리의 경우 예를 들어 where (user_id1, user_id2) = (1,2)
어떤 인덱스가 생성되는지는 중요하지 않습니다.
열 중 하나에 만 조건이있는 쿼리의 경우, 예를 들어 where user_id1 = 1
일반적으로 “주요”열만 옵티 마이저에 의한 비교에 사용될 수 있기 때문에 중요합니다. 따라서 where user_id1 = 1
인덱스 (user_id1, user_id2)를 사용할 수 있지만 모든 경우에 대해 인덱스 (user_id2, user_id1)를 사용할 수는 없습니다.
이 문제를 해결 한 후 (Erwin이 우리에게 작동하는 설정을 친절하게 보여준 후), 최적화 기가 후행 열을 사용할 수있는 상황을 아직 알지 못했지만 이것은 두 번째 열의 데이터 분포에 크게 의존하는 것으로 보입니다. 어디에서든.
인덱스 정의의 시작 부분에없는 열을 사용할 수도있는 Oracle 11도 있습니다.
re 2) 예, 인덱스를 생성합니다
기본 키를 추가하면 기본 키에 사용 된 열 또는 열 그룹에 고유 한 btree 인덱스가 자동으로 생성됩니다.
re 2a) Primary Key (user_id1,user_id2)
는 (user_id1, user_id2)에 색인을 생성합니다 (이러한 기본 키를 작성하면 매우 쉽게 알 수 있습니다)
매뉴얼의 색인에 관한 장 을 읽으 십시오. 기본적으로 위의 모든 질문에 대답합니다.
또한 어떤 인덱스를 생성해야합니까? by depesz는 인덱스 열 및 기타 인덱스 관련 항목의 순서를 잘 설명합니다.
답변
광고 1)
PostgreSQL 에는 @a_horse_with_no_name describe과 같은 제한 사항이 있습니다 . 버전 8.0 다중 컬럼 인덱스 까지는 선행 컬럼의 쿼리에만 사용할 수있었습니다. 버전 8.1에서 개선되었습니다. Postgres 10 의 현재 매뉴얼 (업데이트 됨)에 설명되어 있습니다.
다중 열 B- 트리 인덱스는 인덱스 열의 하위 집합을 포함하는 쿼리 조건과 함께 사용할 수 있지만 선행 (가장 왼쪽) 열에 제약 조건이있는 경우 인덱스가 가장 효율적입니다. 정확한 규칙은 선행 열에 대한 동등 제한 조건과 동등 제한 조건이없는 첫 번째 열에 대한 부등식 제한 조건을 사용하여 스캔되는 인덱스 부분을 제한하는 것입니다. 이 열의 오른쪽에있는 열에 대한 제약 조건이 인덱스에서 확인되므로 테이블 방문을 적절하게 저장하지만 스캔해야하는 인덱스 부분을 줄이지 않습니다. 예를 들어, 인덱스가 켜져
(a, b, c)
있고 쿼리 조건이 주어지면 = 5WHERE a = 5 AND b >= 42 AND c < 77
를 사용하여 첫 번째 항목에서 인덱스를 스캔해야합니다.a
b
==를 사용하여 마지막 항목을 통해 42까지 증가a
합니다.c
> = 77 인 인덱스 항목은 건너 뛰지 만 여전히 스캔해야합니다. 이 인덱스는 원칙에 제약이 쿼리에 사용될 수b
및 / 또는c
에 아무런 제약 조건a
대부분의 경우 계획이 순차적 테이블이 인덱스를 사용을 통해 스캔 선호 있도록하지만, 전체 인덱스 스캔되어야 할 것이다 -.
강조합니다. 경험으로부터 확인할 수 있습니다.
또한 여기에 나중에 답변을 추가 한 테스트 사례를 참조 하십시오 .
답변
이것은 Jack의 답변에 대한 답변 이지만 댓글은 그렇지 않습니다.
버전 9.2 이전의 PostgreSQL에는 커버링 인덱스 가 없었습니다 . MVCC 모델로 인해 가시성을 확인하려면 결과 집합의 모든 튜플을 방문해야합니다. Oracle을 생각하고있을 수 있습니다.
PostgreSQL 개발자는 “인덱스 전용 스캔” 에 대해 이야기 합니다 . 실제로이 기능은 Postgres 9.2와 함께 릴리스되었습니다. 커밋 메시지를 읽습니다 .
Depesz는 매우 유익한 블로그 게시물을 작성했습니다 .
실제 취재 지수 (업데이트)는 INCLUDE
Postgres 11 절과 함께 도입되었습니다 .
이것도 조금 벗어났습니다.
인덱스의 ‘전체 스캔’이 인덱스에 나타나지 않는 테이블의 추가 열로 인해 인덱스 된 테이블의 ‘전체 스캔’보다 빠르다는 사실에 의존합니다.
내 다른 답변에 대한 의견에서보고 된 것처럼 두 정수 테이블로 테스트를 수행했지만 다른 것은 없습니다. 인덱스는 테이블과 동일한 열을 보유합니다. btree 인덱스의 크기는 테이블 크기의 2/3 정도입니다. 요인 3의 속도 향상을 설명하기에는 충분하지 않습니다. 설정에 따라 두 열로, 100000 행으로 더 많은 테스트를 실행했습니다. PostgreSQL 9.0 설치에서 결과는 일관되었습니다.
테이블에 추가 열이 있으면 인덱스의 속도 향상이 더 중요해 지지만 이것이 유일한 요인은 아닙니다 .
요점을 요약하면 다음과 같습니다.
-
다중 열 인덱스는 선행이 아닌 열에 대한 쿼리와 함께 사용할 수 있지만 선택 기준 (결과에서 적은 비율의 행)에 대한 속도는 요소 3에 불과합니다. 튜플이 클수록 높고 결과 집합의 테이블 부분이 많을수록 낮습니다.
-
성능이 중요한 경우 해당 열에 추가 색인을 작성하십시오.
-
관련된 모든 열이 인덱스 (커버 인덱스)에 포함되고 모든 관련된 행 (블록 당)이 모든 트랜잭션에 표시되면 9.2 이상에서 “인덱스 전용 스캔” 을 얻을 수 있습니다 .
답변
이것들은 동등합니까? 그렇지 않다면 왜?
인덱스 (user_id1, user_id2) 및 인덱스 (user_id2, user_id1)
이것들은 동일하지 않으며 일반적으로 말해서 색인 (bar, baz)은 형식의 쿼리에 효율적이지 않습니다. select * from foo where baz=?
Erwin 은 그러한 인덱스가 실제로 쿼리 속도를 높일 수 있음 을 보여 주 었지만,이 효과는 제한적이며 일반적으로 인덱스가 조회를 개선 할 것으로 예상하는 순서와 같지 않습니다. 인덱스의 ‘전체 스캔’이 종종 있다는 사실에 의존합니다 인덱스에 나타나지 않는 테이블의 추가 열로 인해 인덱스 된 테이블의 ‘전체 스캔’보다 빠릅니다.
요약 : 인덱스는 선행이 아닌 열의 쿼리에도 도움이 될 수 있지만 두 가지 보조적이며 비교적 사소한 방법 중 하나를 사용하지만 일반적으로 인덱스가 btree 구조로 인해 도움이 될 것으로 예상되는 방식은 아닙니다.
1. 테이블 조회가 (거기에 그들 중 몇 가지하거나 클러스터 때문에) 저렴 : 전체 인덱스 스캔은 테이블과 하나의 전체 검사보다 훨씬 저렴 경우 인덱스가 도울 수있는 두 가지 방법 NB 있습니다 또는 2. 인덱스입니다 커버 그래서 전혀 테이블 조회가 없습니다 죄송합니다, Erwins 코멘트보기 여기가
테스트 베드 :
create table foo(bar integer not null, baz integer not null, qux text not null);
insert into foo(bar, baz, qux)
select random()*100, random()*100, 'some random text '||g from generate_series(1,10000) g;
쿼리 1 (인덱스 없음, 74 버퍼 치기 ) :
explain (buffers, analyze, verbose) select max(qux) from foo where baz=0;
QUERY PLAN
--------------------------------------------------------------------------------------------------------------
Aggregate (cost=181.41..181.42 rows=1 width=32) (actual time=3.301..3.302 rows=1 loops=1)
Output: max(qux)
Buffers: shared hit=74
-> Seq Scan on stack.foo (cost=0.00..181.30 rows=43 width=32) (actual time=0.043..3.228 rows=52 loops=1)
Output: bar, baz, qux
Filter: (foo.baz = 0)
Buffers: shared hit=74
Total runtime: 3.335 ms
쿼리 2 (인덱스-옵티마이 저는 인덱스를 무시하고 74 버퍼를 다시 칩니다 ) :
create index bar_baz on foo(bar, baz);
explain (buffers, analyze, verbose) select max(qux) from foo where baz=0;
QUERY PLAN
--------------------------------------------------------------------------------------------------------------
Aggregate (cost=199.12..199.13 rows=1 width=32) (actual time=3.277..3.277 rows=1 loops=1)
Output: max(qux)
Buffers: shared hit=74
-> Seq Scan on stack.foo (cost=0.00..199.00 rows=50 width=32) (actual time=0.043..3.210 rows=52 loops=1)
Output: bar, baz, qux
Filter: (foo.baz = 0)
Buffers: shared hit=74
Total runtime: 3.311 ms
쿼리 2 (인덱스 사용-최적화 프로그램을 사용하여 트릭 사용) :
explain (buffers, analyze, verbose) select max(qux) from foo where bar>-1000 and baz=0;
QUERY PLAN
-------------------------------------------------------------------------------------------------------------------------
Aggregate (cost=115.56..115.57 rows=1 width=32) (actual time=1.495..1.495 rows=1 loops=1)
Output: max(qux)
Buffers: shared hit=36 read=30
-> Bitmap Heap Scan on stack.foo (cost=73.59..115.52 rows=17 width=32) (actual time=1.370..1.428 rows=52 loops=1)
Output: bar, baz, qux
Recheck Cond: ((foo.bar > (-1000)) AND (foo.baz = 0))
Buffers: shared hit=36 read=30
-> Bitmap Index Scan on bar_baz (cost=0.00..73.58 rows=17 width=0) (actual time=1.356..1.356 rows=52 loops=1)
Index Cond: ((foo.bar > (-1000)) AND (foo.baz = 0))
Buffers: shared read=30
Total runtime: 1.535 ms
따라서이 경우 인덱스를 통한 액세스는 30 개의 버퍼에 도달 합니다 (인덱싱 측면에서 ‘약간 빠름’) .YMMV는 테이블 및 인덱스의 상대적 크기와 필터링 된 행 수 및 클러스터링 특성에 따라 다릅니다. 테이블의 데이터
대조적으로 선행 열의 쿼리는 인덱스의 btree 구조를 사용합니다.이 경우에는 2 개의 버퍼가 발생합니다 .
explain (buffers, analyze, verbose) select max(qux) from foo where bar=0;
QUERY PLAN
------------------------------------------------------------------------------------------------------------------------
Aggregate (cost=75.70..75.71 rows=1 width=32) (actual time=0.172..0.173 rows=1 loops=1)
Output: max(qux)
Buffers: shared hit=38
-> Bitmap Heap Scan on stack.foo (cost=4.64..75.57 rows=50 width=32) (actual time=0.036..0.097 rows=59 loops=1)
Output: bar, baz, qux
Recheck Cond: (foo.bar = 0)
Buffers: shared hit=38
-> Bitmap Index Scan on bar_baz (cost=0.00..4.63 rows=50 width=0) (actual time=0.024..0.024 rows=59 loops=1)
Index Cond: (foo.bar = 0)
Buffers: shared hit=2
Total runtime: 0.209 ms