[SQL] 어떻게 특정 인덱스를 사용하는 포스트 그레스를 강요하는 걸까?

어떻게 특정 인덱스를 사용하는 포스트 그레스를 강요하는 걸까?

어떻게하면 그렇지 않으면 순차 검색을하고 주장 할 때 인덱스를 사용하는 포스트 그레스를 강요하는 걸까?

해결법

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

   1.이 기능은 많은 데이터베이스에있는 "힌트 인덱스를"일반에 대한 요구하고 가정하면, PostgreSQL는 이러한 기능을 제공하지 않습니다. 이것은 PostgreSQL의 팀에 의해 의식적인 결정이었다. 당신이 대신 할 수있는하는 것은 여기에서 찾을 수 있습니다 왜의 좋은 개요. 그 이유는 PostgreSQL을의 최적화 수있는 반면 통계를 기반으로 계획을 다시 평가하고, 나중에 데이터의 변경 선 아래로 더 많은 문제를 야기하는 경향이 성능 해킹하는 것이 기본적이다. 즉, 어떤 좋은 쿼리 계획은 오늘 아마 모든 시간을위한 좋은 쿼리 계획되지 않습니다 야기 할 수 있으며 인덱스 힌트는 모든 시간을 특정 쿼리 계획을 강제로.

   이 기능은 많은 데이터베이스에있는 "힌트 인덱스를"일반에 대한 요구하고 가정하면, PostgreSQL는 이러한 기능을 제공하지 않습니다. 이것은 PostgreSQL의 팀에 의해 의식적인 결정이었다. 당신이 대신 할 수있는하는 것은 여기에서 찾을 수 있습니다 왜의 좋은 개요. 그 이유는 PostgreSQL을의 최적화 수있는 반면 통계를 기반으로 계획을 다시 평가하고, 나중에 데이터의 변경 선 아래로 더 많은 문제를 야기하는 경향이 성능 해킹하는 것이 기본적이다. 즉, 어떤 좋은 쿼리 계획은 오늘 아마 모든 시간을위한 좋은 쿼리 계획되지 않습니다 야기 할 수 있으며 인덱스 힌트는 모든 시간을 특정 쿼리 계획을 강제로.

   테스트를 위해 매우 무딘 망치, 유용, 당신은 enable_seqscan 및 enable_indexscan 매개 변수를 사용할 수 있습니다. 보다:

   이러한 지속적인 제품 사용에 적합하지 않습니다. 당신은 쿼리 계획 선택에 문제가있는 경우 쿼리 성능 문제를 추적하는 설명서를 참조해야한다. 뿐만 아니라 설정 enable_의 PARAMS를 수행하고 도보 거리.

   인덱스를 사용하기위한 아주 좋은 이유가 없다면, 포스트 그레스는 올바른 선택을 할 수있다. 왜?

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

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

   2.사용하는 아마 유일하게 유효한 이유

   사용하는 아마 유일하게 유효한 이유

   set enable_seqscan=false
   

   당신이있는 거 쿼리를 작성하고 신속하게 쿼리 계획이 실제로이 테이블 (들)에 많은 양의 데이터를했다 할 것입니다 무엇을보고 싶어 할 때입니다. 또는 물론 당신은 데이터 세트가 너무 작기 때문에 쿼리는 단순히 인덱스를 사용하지 않는 것을 신속하게 확인해야합니다.

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

   3.때로는 PostgreSQL은 특정 조건에 대해 인덱스 최선의 선택을하는 데 실패합니다. 예를 들어, 특정 일에 대한 수백가있는 수백만 개의 행과 거래 테이블이 있다고 가정하고, 테이블은 네 개의 인덱스가 있습니다 TRANSACTION_ID, CLIENT_ID, 날짜 및 설명을. 다음과 같은 쿼리를 실행하려면 :

   때로는 PostgreSQL은 특정 조건에 대해 인덱스 최선의 선택을하는 데 실패합니다. 예를 들어, 특정 일에 대한 수백가있는 수백만 개의 행과 거래 테이블이 있다고 가정하고, 테이블은 네 개의 인덱스가 있습니다 TRANSACTION_ID, CLIENT_ID, 날짜 및 설명을. 다음과 같은 쿼리를 실행하려면 :

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

   PostgreSQL는 대신 1 초 미만의 몇 분을 복용 쿼리로 이어질 수 대신 transactions_date_idx의 인덱스 transactions_description_idx를 사용하도록 선택할 수 있습니다. 이 경우,이 같은 조건을 fudging 날짜에 인덱스를 사용하여 강제 할 수 있습니다 :

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

4. ==============================

   4.인덱스 스캔의 예상 비용이 너무 높은 정확하게 현실을 반영하지 않는 경우이 문제는 일반적으로 발생합니다. 이 문제를 해결하기 위해 당신은 random_page_cost 구성 매개 변수를 낮출 필요가있다. Postgres의 문서에서 :

   인덱스 스캔의 예상 비용이 너무 높은 정확하게 현실을 반영하지 않는 경우이 문제는 일반적으로 발생합니다. 이 문제를 해결하기 위해 당신은 random_page_cost 구성 매개 변수를 낮출 필요가있다. Postgres의 문서에서 :

   당신은 낮은 값이 실제로 포스트 그레스 인덱스를 사용하도록 (하지만 테스트 용으로 만이 사용) 여부를 확인할 수 있습니다 :

   EXPLAIN <query>;              # Uses sequential scan
   SET random_page_cost = 1;
   EXPLAIN <query>;              # May use index scan now
   

   당신은 SET random_page_cost = DEFAULT로 디폴트 값을 복원 할 수 있습니다; 다시.

   인덱스 스캔은 비 순차적 디스크 페이지 페치가 필요합니다. 포스트 그레스 용도 순차 페치 관련하여 그러한 비 순차적 인출 비용을 추정 random_page_cost. 디폴트 값은 따라서 순차적 인출 (고려 캐싱 효과 복용)에 비해 4 평균 비용 인자를 가정하면, 4.0이다.

   문제는 그러나이 기본 값은 다음과 같은 중요한 실제 시나리오에 적합하다는 것이다 :

   1) 솔리드 스테이트 드라이브

   문서의 인정으로 :

   PostgresConf 2018에서 발언에서이 슬라이드의 마지막 지점에 따르면, random_page_cost는 솔리드 스테이트 드라이브 (SSD) 1.0과 2.0 사이의 값으로 설정해야합니다.

   2) 데이터 캐시

   필요한 인덱스 데이터가 이미 RAM에 캐시되는 경우, 인덱스 스캔은 항상 순차 검색보다 훨씬 빠를 것이다. 문서는 말합니다 :

   문제는 물론 쉽게 관련 데이터가 캐쉬되어 있는지의 여부를 알 수 없다는 것입니다. 특정 인덱스를 자주 조회하는 경우 시스템에 충분한 RAM이있는 경우 그러나, 그리고, 다음 데이터는 가능성이 캐시 할 수 있으며, random_page_cost는 낮은 값으로 설정해야합니다. 당신은 다른 값을 실험하고 당신을 위해 작동하는 것을 볼 수있을 것이다.

   또한 명시 적 데이터 캐싱에 대한 pg_prewarm 확장을 사용할 수 있습니다.

5. ==============================

   5.자체에 대한 질문은 매우 유효하지 않습니다. (예를 들어 떨어져 enable_seqscan =을 수행하여) 강제하는 것은 매우 좋은 생각이다. 더 빠르게 될 것입니다 경우 확인하는 데 유용 할 수 있지만 생산 코드는 트릭을 사용해서는 안됩니다.

   자체에 대한 질문은 매우 유효하지 않습니다. (예를 들어 떨어져 enable_seqscan =을 수행하여) 강제하는 것은 매우 좋은 생각이다. 더 빠르게 될 것입니다 경우 확인하는 데 유용 할 수 있지만 생산 코드는 트릭을 사용해서는 안됩니다.

   대신 - 설명, 쿼리의 분석을 읽고, PostgreSQL의이 계획 (당신의 의견에) 나쁜 선택 이유를 찾을 수 없습니다.

   그 중 하나는 explain.depesz.com입니다 - - 내가 쓴 독서에 도움이 출력을 분석하고 설명하는 웹 도구가 있습니다.

   또 다른 옵션은 Freenode의의 IRC 네트워크에 #postgresql 채널에 가입하고, 사람들에게 얘기하면이 도와 - 쿼리를 최적화하는 "대답은 행복 얻을 질문을"의 문제가 아니므로. 그것을 확인하는 많은 것들로, 많은 것들을 배울 수에 더 많은 대화 같다.

6. ==============================

   6.푸시 포스트 그레스에 대한 트릭은 seqscan가 하위 쿼리에서 오프셋 0을 추가가 선호하는 것입니다

   푸시 포스트 그레스에 대한 트릭은 seqscan가 하위 쿼리에서 오프셋 0을 추가가 선호하는 것입니다

   모두 당신이 필요 / 단지 N 최초의 마지막 요소 인 경우에 큰 / 큰 테이블을 연결하는 최적화 요청 편리합니다.

   당신은 100,000 (또는 그 이상) 항목, 아무 소용 건물을 가진 여러 테이블을 포함하는 첫번째 / 마지막 20 개 요소를 찾고 있습니다 말할 수 / 모든 통해 데이터를 모든 쿼리를 연결하는 것은 당신을 위해 무엇을 찾고있을거야 것은 처음 100 또는 1000에있을 때 항목. 예를 들어,이 시나리오에서는 순차적 인 스캔을 수행하는 빠른 10 배 이상으로 밝혀졌습니다.

   어떻게 하위 쿼리를 인라인에서 포스트 그레스를 방지 할 수 있습니다 참조하십시오?

7. ==============================

   7.해당 제품은 무료 아니지만 엔터프라이즈 DB의 PostgresPlus 고급 서버 제품은 오라클 힌트 구문을 지원합니다.

   해당 제품은 무료 아니지만 엔터프라이즈 DB의 PostgresPlus 고급 서버 제품은 오라클 힌트 구문을 지원합니다.

from https://stackoverflow.com/questions/309786/how-do-i-force-postgres-to-use-a-particular-index by cc-by-sa and MIT license