PostgreSQL의 `tsrange`는 어떻게 업무 운영 시간에 대한 쿼리를 단순화할 수 있습니까?

tsrange를 사용한 PostgreSQL의 작업 시간 쿼리

소개

PostgreSQL에서 시간은 특정 엔터티에 대한 작업은 opens_on과 같은 정수 열 쌍으로 저장될 수 있습니다. 평일을 나타내는 closes_on과 시간을 나타내는 opens_at 및 closes_at입니다. 그러나 이러한 데이터에 대한 쿼리 수행은 일정의 중복 및 불규칙성으로 인해 복잡할 수 있습니다.

tsrange를 사용한 대체 접근 방식

이러한 복잡성을 해결하려면 더 효율적인 접근 방식에는 시간대 없이 타임스탬프 범위를 나타내는 tsrange 데이터 유형을 사용하는 것이 포함됩니다. tsrange를 활용하면 작업 시간을 범위 집합으로 저장할 수 있습니다. 이 접근 방식은 여러 가지 이점을 제공합니다.

데이터 구조

CREATE TABLE hoo (
  hoo_id  serial PRIMARY KEY,
  shop_id int NOT NULL, -- REFERENCES shop(shop_id)
  hours   tsrange NOT NULL
);

예를 들어 상점이 수요일 오후 6시부터 목요일 오전 5시(UTC)까지 영업하는 경우 해당 시간은 범위는 be:

'[1996-01-03 18:30, 1996-01-04 05:00]'

함수

CREATE OR REPLACE FUNCTION f_hoo_time(timestamptz)
  RETURNS timestamp
  LANGUAGE sql IMMUTABLE PARALLEL SAFE AS
$func$
SELECT timestamp '1996-01-01' + ( AT TIME ZONE 'UTC' - date_trunc('week',  AT TIME ZONE 'UTC'))
$func$;

CREATE OR REPLACE FUNCTION f_hoo_hours(_from timestamptz, _to timestamptz)
  RETURNS TABLE (hoo_hours tsrange)
  LANGUAGE plpgsql IMMUTABLE PARALLEL SAFE COST 500 ROWS 1 AS
$func$
DECLARE
   ts_from timestamp := f_hoo_time(_from);
   ts_to   timestamp := f_hoo_time(_to);
BEGIN
   -- Perform sanity checks and split ranges at Sunday midnight if necessary.
   ...
END
$func$;

쿼리

이제 원래의 복잡한 쿼리는 다음과 같습니다. 다음과 같이 단순화 된 것으로 대체되었습니다. 명령문:

SELECT *
FROM hoo
WHERE hours @> f_hoo_time(now());

인덱싱

쿼리 성능을 최적화하기 위해 시간에 대한 SP-GiST 인덱스를 생성할 수 있습니다.

CREATE INDEX hoo_hours_spgist_idx on hoo USING spgist (hours);

성능

SP-GiST를 사용한 이 접근 방식 index는 특히 많은 수의 결과를 검색할 때 성능이 크게 향상되었습니다. 다른 색인 생성 전략보다 성능이 뛰어나며 결과 개수가 적거나 많을 때 모두 실행 시간이 단축됩니다.

위 내용은 PostgreSQL의 `tsrange`는 어떻게 업무 운영 시간에 대한 쿼리를 단순화할 수 있습니까?의 상세 내용입니다. 자세한 내용은 PHP 중국어 웹사이트의 기타 관련 기사를 참조하세요!