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

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 스캔을 실행할 가능성을 줄이기 위해 파티셔닝 및 쿼리 구조를 개선하려면 어떻게해야합니까?

답변