Postgres가 특정 인덱스를 사용하도록 강제하려면 어떻게 해야 합니까?

Postgres가 특정 인덱스를 사용하도록 강제하려면 어떻게 해야 합니까?

Postgres가 그렇지 않으면 순차적 검색을 수행해야 할 때 인덱스를 사용하도록 강제하려면 어떻게 해야 합니까?

많은 데이터베이스에서 발견되는 일반적인 "인덱스 암시" 기능에 대해 질문하는 경우, PostgreSQL은 이러한 기능을 제공하지 않습니다.이것은 포스트그레가 내린 의식적인 결정이었습니다.SQL팀.대신에 무엇을 할 수 있는지에 대한 좋은 개요는 여기에서 확인할 수 있습니다.그 이유는 기본적으로 데이터가 변경됨에 따라 나중에 더 많은 문제가 발생하는 경향이 있는 성능 해킹인 반면, Postgre는SQL의 최적화 도구는 통계를 기반으로 계획을 재평가할 수 있습니다.다시 말해, 오늘날 좋은 쿼리 계획은 아마도 항상 좋은 쿼리 계획이 아닐 것이며 인덱스 힌트는 항상 특정 쿼리 계획을 강제합니다.

테스트에 유용한 매우 무딘 해머로서, 당신은 다음을 사용할 수 있습니다.enable_seqscan그리고.enable_indexscan매개 변수참조:

• 인덱스 사용 검사 중
• enable_ 변수: 매개 변수

이는 지속적인 프로덕션 용도에 적합하지 않습니다.쿼리 계획 선택에 문제가 있는 경우 쿼리 성능 문제를 추적하기 위한 설명서를 참조해야 합니다.설정만 하지 마십시오.enable_길을 재촉하고 가버립니다.

당신이 색인을 사용하는 아주 좋은 이유가 없다면, Postgres는 올바른 선택을 하고 있을 것입니다. 왜죠?

• 작은 테이블의 경우 순차적 검색을 수행하는 것이 더 빠릅니다.
• 데이터 유형이 제대로 일치하지 않으면 Postgres에서 인덱스를 사용하지 않으므로 적절한 캐스트를 포함해야 할 수 있습니다.
• 플래너 설정으로 인해 문제가 발생할 수 있습니다.

이 오래된 뉴스 그룹 게시물도 참조하십시오.

아마도 사용하는 유일한 타당한 이유일 것입니다.

set enable_seqscan=false

쿼리를 작성할 때 테이블에 대량의 데이터가 있는 경우 쿼리 계획을 신속하게 확인할 수 있습니다.또는 데이터셋이 너무 작다는 이유만으로 쿼리가 인덱스를 사용하지 않는지 신속하게 확인해야 하는 경우에도 마찬가지입니다.

TL;DR

다음 세 가지 명령을 실행하여 문제가 해결되었는지 확인합니다.

ANALYZE;
SET random_page_cost = 1.0;
SET effective_cache_size = 'X GB';    # replace X with total RAM size minus 2 GB

이에 대한 자세한 내용과 배경 정보는 계속 읽어보십시오.

1단계: 표 분석

하기 위한 첫 로 문를해기간첫시를 합니다.ANALYZE;모든 테이블 통계를 업데이트하기 위해 데이터베이스 수퍼유저로 명령합니다.설명서에서 다음을 참조하십시오.

쿼리 플래너는 이러한 통계를 사용하여 쿼리에 대한 가장 효율적인 실행 계획을 결정합니다.

2단계: 올바른 랜덤 페이지 비용 설정

인덱스 검색에는 순차적이지 않은 디스크 페이지 가져오기가 필요합니다.포스트그레SQL은 다음을 사용합니다.random_page_cost순차적 페치와 관련하여 이러한 비순차적 페치의 비용을 추정하는 구성 매개 변수.설명서에서 다음을 참조하십시오.

이 값 [...]을 줄이면 시스템이 인덱스 검색을 선호하고, 높이면 인덱스 검색이 상대적으로 더 비싸 보입니다.

은 기값은 입다니입니다.4.0따라서 캐싱 효과를 고려하여 순차적 페치와 비교하여 평균 비용 계수를 4로 가정합니다.그러나 데이터베이스가 SSD 드라이브에 저장되어 있는 경우에는 실제로 다음을 설정해야 합니다.random_page_cost1.1설명서에 따라 다음을 수행합니다.

스테이트 드라이브에 는 보다 것이 .random_page_cost를 들어, , , .1.1.

또한 인덱스가 대부분(또는 전체적으로) RAM에 캐시되어 있는 경우 인덱스 검색은 항상 디스크 서비스 순차 검색보다 훨씬 빠릅니다.그러나 쿼리 플래너는 인덱스의 어떤 부분이 이미 캐시되었는지 알지 못하므로 잘못된 결정을 내릴 수 있습니다.

데이터베이스 인덱스가 자주 사용되고 시스템에 RAM이 충분할 경우 인덱스가 캐시될 가능성이 높습니다. 이런경우는에,는에,random_page_cost으로 설정할 수 있습니다.1.0또는 심지어 아래 값까지1.0인덱스 검색을 사용하는 것을 적극적으로 선호합니다(문서에서는 권장하지 않음).여러분은 다양한 가치를 실험하고 무엇이 여러분에게 효과가 있는지 봐야 할 것입니다.

참고로 pg_prewarm 확장자를 사용하여 RAM에 인덱스를 명시적으로 캐시할 수도 있습니다.

▁the를 설정할 수 .random_page_cost다음과 같이:

SET random_page_cost = 1.0;

3단계: 올바른 캐시 크기 설정

이상인 에서는 8GB RAM을 해야 .effective_cache_size일반적으로 Postgre에서 사용할 수 있는 메모리 양에 대한 구성 매개 변수데이터 캐싱을 위한 SQL.설명서에서 다음을 참조하십시오.

값이 클수록 인덱스 검색이 사용될 가능성이 높고 값이 작을수록 순차 검색이 사용될 가능성이 높습니다.

와 Postgre의 메모리 양을 변경하지 SQL은 실제로 할당되지만 비용 견적을 계산하는 데만 사용됩니다.적절한 값(전용 데이터베이스 서버의 경우)은 총 RAM 크기에서 2GB를 뺀 값입니다.은 기값은 입다니입니다.4 GB.

▁the를 설정할 수 .effective_cache_size다음과 같이:

SET effective_cache_size = '14 GB';   # e.g. on a dedicated server with 16 GB RAM

4단계: 문제를 영구적으로 해결합니다.

아도사싶것다니입을고하를 사용하고 것입니다.ALTER SYSTEM SET ...또는ALTER DATABASE db_name SET ...새 구성 매개 변수 값을 영구적으로 설정합니다(글로벌 또는 프로세서 단위).매개 변수 설정에 대한 자세한 내용은 설명서를 참조하십시오.

5단계: 추가 리소스

그래도 작동하지 않으면 이 Postgre도 확인해 보십시오.서버 조정에 대한 SQL Wiki 페이지입니다.

가끔은 포스트그SQL이 특정 조건에 대해 최적의 인덱스를 선택하지 못합니다.예를 들어, 수백만 개의 행이 있는 트랜잭션 테이블이 있으며, 그 중 특정 날짜 동안 수백 개의 행이 있고 테이블에는 transaction_id, client_id, date 및 description의 네 개의 인덱스가 있다고 가정합니다.다음 조회를 실행하려고 합니다.

SELECT client_id, SUM(amount)
FROM transactions
WHERE date >= 'yesterday'::timestamp AND date < 'today'::timestamp AND
      description = 'Refund'
GROUP BY client_id

PostgreSQL은 transactions_date_idx 대신 transactions_description_idx 인덱스를 사용하도록 선택할 수 있으며, 이로 인해 쿼리에 1초 미만이 아닌 몇 분이 걸릴 수 있습니다.이 경우 다음과 같은 조건을 퍼지하여 날짜에 인덱스를 강제로 사용할 수 있습니다.

SELECT client_id, SUM(amount)
FROM transactions
WHERE date >= 'yesterday'::timestamp AND date < 'today'::timestamp AND
      description||'' = 'Refund'
GROUP BY client_id

그 질문 자체는 매우 타당하지 않습니다.(예를 들어 enable_seqscan=off를 통해) 강제 적용하는 것은 매우 나쁜 생각입니다.속도가 더 빠를지 확인하는 것이 유용할 수 있지만, 생산 코드는 절대 그런 속임수를 사용해서는 안 됩니다.

대신 - 쿼리 분석을 설명하고, 읽고, Postgre의 이유를 알아봅니다.SQL이 잘못된 계획을 선택했습니다.

웹에는 제가 작성한 explain.depesz.com 이 있습니다.

또 다른 옵션은 freenodeirc 네트워크의 #postgresql 채널에 가입하여 쿼리를 최적화하는 것이 "질문하고 행복하게 답변 받기"의 문제가 아니기 때문에 도움을 청하는 것입니다.그것은 확인할 것이 많고 배울 것이 많은 대화에 더 가깝습니다.

Postgre와 관련하여 한 가지 주목할 점.SQL: 인덱스가 사용될 것으로 예상되지만 사용되지 않는 경우에는 테이블을 진공 분석합니다.

VACUUM ANALYZE schema.table;

이렇게 하면 설계자가 사용하는 통계가 업데이트되어 쿼리를 가장 효율적으로 실행할 수 있는 방법을 결정할 수 있습니다.인덱스가 사용될 수 있습니다.

또 다른 확인 사항은 종류입니다.

인덱스가 int8 열에 있고 숫자로 쿼리하고 있습니까?쿼리는 작동하지만 인덱스는 사용되지 않습니다.

postgres를 밀어넣는 .OFFSET 0

이것은 첫 번째/마지막 요소만 있으면 되는 큰/큰 테이블을 연결하는 요청을 최적화하는 데 유용합니다.

예를 들어, 100k개 이상의 항목이 있는 여러 테이블과 관련된 첫 번째/마지막 20개의 요소를 찾고 있으며, 처음 100개 또는 1000개 항목에 있는 모든 데이터에 대해 모든 쿼리를 구성/연결하지 않습니다.예를 들어, 이 시나리오에서는 순차적 검색을 수행하는 것이 10배 이상 빠른 것으로 나타났습니다.

Postgres가 하위 쿼리를 인라인화하지 않도록 하려면 어떻게 해야 합니까?를 참조하십시오.

인덱스는 특정 상황에서만 사용할 수 있습니다.

1. 예를 들어, 값의 유형은 열의 유형에 적합합니다.
2. 값과 비교하기 전에 열에 대한 작업을 수행하지 않습니다.

모든 열에 인덱스가 3개씩 있는 3개의 열이 있는 고객 테이블을 지정합니다.

create table customer(id numeric(10), age int, phone varchar(200))

데이터베이스가 전화 번호 대신 인덱스 idx_age를 사용하려고 할 수 있습니다.

사용 기간 작업을 수행하여 인덱스 사용 기간을 방해할 수 있습니다.

 select * from customer where phone = '1235' and age+1 = 24 

(비록 당신은 23세를 찾고 있지만)

물론 이것은 매우 간단한 예이며 우편물의 지능은 아마도 올바른 선택을 하기에 충분할 것입니다.하지만 때때로 시스템을 속이는 것 외에는 다른 방법이 없습니다.

또 다른 예는 다음과 같습니다.

select * from customer where phone = '1235' and age::varchar = '23'

그러나 이것은 위의 옵션보다 아마도 더 비쌀 것입니다.

안타깝게도 MSSQL 또는 Sybase에서와 같이 쿼리에 인덱스 이름을 설정할 수 없습니다.

select * from customer (index idx_phone) where phone = '1235' and age = 23.

이것은 이런 문제를 피하는 데 많은 도움이 될 것입니다.

Postgre가 유사한 조건을 두 번 반복하여 인덱스를 사용하도록 암시할 수 있는 경우가 있습니다.

제가 관찰한 구체적인 사례는 포스트를 사용한 것입니다.GISgin인덱스 및 ST_Intel 서술어는 다음과 같습니다.

select *
from address
natural join city
natural join restaurant
where st_within(address.location, restaurant.delivery_area)
and restaurant.delivery_area ~ address.location

첫 번째 서술어는st_within(address.location, restaurant.delivery_area)에 의해 자동으로 됩니다.에서 GIS로 »(restaurant.delivery_area ~ address.location) AND _st_contains(restaurant.delivery_area, address.location)그래서 두 번째 서술어를 추가합니다.restaurant.delivery_area ~ address.location완전히 이중화되어 있습니다.그럼에도 불구하고, 두번째 술어는 설계자가 공간 인덱스를 사용하도록 설득했습니다.address.location필요한 경우에는 실행 시간을 8배로 늘렸습니다.

언급URL : https://stackoverflow.com/questions/309786/how-do-i-force-postgres-to-use-a-particular-index