Comment « tsrange » de PostgreSQL peut-il simplifier les requêtes pendant les heures d'ouverture ?

Requête d'heures de fonctionnement dans PostgreSQL à l'aide de tsrange

Introduction

Dans PostgreSQL, les heures de fonctionnement pour une entité particulière peut être stocké sous forme de paires de colonnes entières, telles que opens_on et closes_on, représentant le jour de la semaine, et opens_at et closes_at, représentant l'heure de la journée. Cependant, effectuer des requêtes sur de telles données peut être complexe en raison de chevauchements et d'irrégularités potentiels dans le calendrier.

Approche alternative avec tsrange

Pour répondre à ces complexités, une approche plus efficace Cette approche implique l'utilisation du type de données tsrange, qui représente une plage d'horodatages sans fuseaux horaires. En utilisant tsrange, nous pouvons stocker les heures de fonctionnement sous forme d'ensembles de plages. Cette approche offre plusieurs avantages :

Structure des données

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

Par exemple, si un magasin est ouvert du mercredi 18h au jeudi 5h UTC, les horaires correspondants la portée serait be:

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

Fonctions

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$;

Requête

La requête complexe d'origine peut désormais être remplacé par le texte simplifié suivant déclaration :

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

Indexation

Pour optimiser les performances des requêtes, un index SP-GiST sur les heures peut être créé :

CREATE INDEX hoo_hours_spgist_idx on hoo USING spgist (hours);

Performance

Cette approche utilisant l'index SP-GiST offre des améliorations significatives des performances, notamment lors de la recherche de un grand nombre de résultats. Il surpasse les autres stratégies d'indexation et réduit le temps d'exécution pour les nombres de résultats faibles et élevés.

Ce qui précède est le contenu détaillé de. pour plus d'informations, suivez d'autres articles connexes sur le site Web de PHP en chinois!