타임 스탬프별로 분할 된 테이블과 관련된 조인에는 파티션 제약 조건이 사용되지 않습니다. sensor_sample AS(

분할 된 테이블 구조가 다음과 같습니다.

CREATE TABLE measurements (
    sensor_id bigint,
    tx timestamp,
    measurement int
);

CREATE TABLE measurements_201201(
    CHECK (tx >= '2012-01-01 00:00:00'::timestamp without time zone 
       AND tx < ('2012-01-01 00:00:00'::timestamp without time zone + '1 mon'::interval))    
)INHERITS (measurements);
CREATE INDEX ON measurements_201201(sensor_id);
CREATE INDEX ON measurements_201201(tx);
CREATE INDEX ON measurements_201201(sensor_id, tx);
....

등등. 각 테이블에는 약 2 천만 개의 행이 있습니다.

WHERE절 에서 센서 샘플과 타임 스탬프 샘플을 쿼리하면 쿼리 계획에 올바른 테이블이 선택되고 사용중인 인덱스가 표시됩니다 (예 :

SELECT *
FROM measurements
INNER JOIN sensors TABLESAMPLE BERNOULLI (0.01) USING (sensor_id)
WHERE tx BETWEEN '2015-01-04 05:00' AND '2015-01-04 06:00' 
    OR tx BETWEEN '2015-02-04 05:00' AND '2015-02-04 06:00' 
    OR tx BETWEEN '2014-03-05 05:00' AND '2014-04-07 06:00' ;

그러나 CTE를 사용하거나 타임 스탬프 값을 테이블에 넣으면 (임시 테이블의 인덱스와 함께 표시되지 않음).

WITH sensor_sample AS(
    SELECT sensor_id, start_ts, end_ts
    FROM sensors TABLESAMPLE BERNOULLI (0.01)
    CROSS JOIN (VALUES (TIMESTAMP '2015-01-04 05:00', TIMESTAMP '2015-01-04 06:00'),
        (TIMESTAMP '2015-02-04 05:00', TIMESTAMP '2015-02-04 06:00'),
        (TIMESTAMP  '2014-03-05 05:00', '2014-04-07 06:00') ) tstamps(start_ts, end_ts)
)

아래와 같은 것

SET constraint_exclusion = on;
SELECT * FROM measurements
INNER JOIN sensor_sample USING (sensor_id)
WHERE tx BETWEEN start_ts AND end_ts

모든 테이블에서 인덱스 스캔을 수행합니다. 여전히 비교적 빠르지 만 쿼리의 복잡성이 증가함에 따라 seq 스캔으로 전환 될 수 있으며, 이는 제한된 분할 된 테이블 서브 세트 (50-4-5)에서 ~ 40K 행을 검색하는 데 매우 느려질 수 있습니다.

나는 이것 과 같은 것이 문제 라고 우려한다 .

사소한 표현식의 경우 Postgres 쿼리 플래너가 CHECK 제약 조건에 의존 할 수 있음을 이해하도록 쿼리에서 다소 간결한 조건을 반복해야합니다. 중복 된 것 같더라도!

모든 데이터에서 seq 스캔을 실행할 가능성을 줄이기 위해 파티셔닝 및 쿼리 구조를 개선하려면 어떻게해야합니까?

답변

제약 조건 기반 제외 [CBE]는 쿼리를 구문 분석하고 실제 관계에 매핑하고 다시 쓴 직후 쿼리 계획의 초기 단계에서 수행됩니다. ( 내부 , 플래너 / 최적화 단계)

플래너는 “sensor_sample”테이블의 내용을 가정 할 수 없습니다.

따라서 쿼리에 하드 코드 된 값이 없으면 플래너는 “파티션”을 제외하지 않습니다.

CTE 변형에서 어떤 일이 발생하는지 추측합니다 … TABLESAMPLE을 사용하기 때문에 플래너가 제한되며 하위 쿼리의 리터럴이 정적이더라도 전체 하위 쿼리가 일시적으로 처리 될 수 있습니다. ( 그냥 내 추측이야, 나는 플래너 코드에 전문가가 아니다 )

밝은면에서는 부정적인 결과를 갖는 인덱스 스캔이 엄청나게 빠릅니다. (단일 페이지 스캔!) 10000 개가 넘는 파티션이 없으면 귀찮게하지 않습니다.

따라서 질문에 직접 대답하려면 다음을 수행하십시오.

  • 이 데이터 구조를 훨씬 더 개선 할 수는 없습니다.

  • 인덱스 스캔을 고려하면 저렴합니다.

  • 순차적 스캔과 관련하여-예에서 볼 수 있듯이 가능하면 피할 수 있습니다.

답변