대형 테이블의 최근 행에 대한 쿼리 성능 최적화

부분, 멀티 컬럼 인덱스 에 (person_id, created)의사 -와 IMMUTABLE조건이 도움이 될 것이다 (많은). 성능을 유지하려면 수시로 재생성해야합니다.

테이블이 그다지 크지 않은 경우 일반 다중 열 인덱스를 크게 단순화하고 사용할 수 있습니다.
또는 Postgres 12 이상 (기능이 마침내 완성 된 경우)에서 테이블 파티셔닝 을 고려 하십시오 .

기본 함수 는 3 일 이상 전의 일정한 시점을 제공합니다 (귀하의 경우에는 유닉스 시대로 표시됨).

CREATE OR REPLACE FUNCTION f_orders_idx_start()
  RETURNS int LANGUAGE sql IMMUTABLE PARALLEL SAFE COST 1 AS
'SELECT 1387497600';

PARALLEL SAFEPostgres 10 이상에만 해당됩니다.
1387497600결과 :

SELECT extract(epoch from now())::integer - 259200;
-- 259200 being the result of 60 * 60 * 24 * 3

이 의사 조건을 기반으로 부분 색인 을 작성하십시오 IMMUTABLE.

CREATE INDEX orders_created_recent_idx ON orders (person_id, created)
WHERE created >= f_orders_idx_start();

동일한 조건을 기반으로 쿼리 를 작성하십시오.

SELECT *
FROM   orders
WHERE  person_id = 1
AND    created >= f_orders_idx_start()  -- match partial idx condition
AND    created >= extract(epoch from now())::integer - 259200;  -- actual condition

라인 AND created >= f_orders_idx_start()은 중복 된 것처럼 보이지만 Postgres가 부분 인덱스를 사용하도록 설득하는 데 도움이됩니다.

재 작성 기능과 색인 기능 을 언제든지. 매일 밤 cron-job을 사용하는 경우 :

CREATE OR REPLACE FUNCTION f_orders_reindex_partial()
  RETURNS void AS
$func$
DECLARE
   -- 3 days back, starting at 00:00
   _start int := extract(epoch from now()::date -3)::int;
BEGIN       
   IF _start = f_orders_idx_start() THEN
      -- do nothing, nothing changes.
   ELSE
      DROP INDEX IF EXISTS orders_created_recent_idx;
      -- Recreate IMMUTABLE function
      EXECUTE format('
         CREATE OR REPLACE FUNCTION f_orders_idx_start()
           RETURNS int LANGUAGE sql IMMUTABLE PARALLEL SAFE COST 1 AS
         $$SELECT %s $$'
       , _start
      );
      -- Recreate partial index
      CREATE INDEX orders_created_recent_idx ON orders (person_id, created)
      WHERE created >= f_orders_idx_start();
   END IF;    
END
$func$  LANGUAGE plpgsql;

그런 다음 인덱스를 리베이스하려면 다음을 호출하십시오 (이상적으로는 동시로드가 거의 또는 전혀 없음).

SELECT f_orders_reindex_partial();  -- that's all

동시로드로 인해 인덱스를 삭제하고 다시 만들 수없는 경우 REINDEX CONCURRENTLYPostgres 12 이상을 고려하십시오 . 아주 간단합니다.

REINDEX INDEX orders_created_recent_idx;

이 함수를 호출하지 않더라도 모든 쿼리는 계속 작동합니다. 부분 인덱스가 증가함에 따라 시간이 지남에 따라 성능이 서서히 저하됩니다.

저는이 체제를 몇 개의 큰 테이블과 유사한 요구 사항과 함께 성공적으로 사용하고 있습니다. 매우 빠릅니다.

Postgres 9.2 이상의 경우 테이블에 작은 열이 몇 개만 있고 테이블이 많이 작성되지 않은 경우 포함 인덱스 를 만드는 데 비용이들 수 있습니다 .

CREATE INDEX orders_created_recent_idx ON orders (person_id, created, id)
WHERE created >= f_orders_idx_start();

Postgres 11 이상에서는 다음을 INCLUDE대신 사용할 수 있습니다 .

CREATE INDEX orders_created_recent_idx ON orders (person_id, created) INCLUDE (id)
WHERE created >= f_orders_idx_start();