Lente sous-sélection de la requête JPQL GROUP BY

Question

L'exécution du JPQL suivant prend environ 1,5 seconde contre PG 9.6 :

@Query(" SELECT t FROM T t WHERE t.id in ("
     + "   SELECT MAX(t.id) FROM T t, C c "
     + "     WHERE t.m           = :m     "
     + "       AND t.c           = c      "
     + "       AND c.createdDate < :date  "
     + "     GROUP BY t.m, t.p            "
     + "   )                              ")
List<T> tByDate(@Param("m") M m, @Param("date") LocalDateTime date);

Avez-vous des idées pour accélérer le processus sans modifier le modèle de la base de données ?

Tableau T (taille 45k)

CREATE TABLE public.t 
(
  id bigint NOT NULL DEFAULT nextval('t_id_seq'::regclass),
  c_id bigint NOT NULL,
  m_id bigint NOT NULL,
  p_id bigint NOT NULL,
  CONSTRAINT t_pkey PRIMARY KEY (id),
  CONSTRAINT fkcdo362oanw5jshu29kavksyfy FOREIGN KEY (m_id)
    REFERENCES public.m (id) MATCH SIMPLE
    ON UPDATE NO ACTION ON DELETE NO ACTION,
  CONSTRAINT fkjpyqqd0vys4jayau98eij2xv3 FOREIGN KEY (c_id)
    REFERENCES public.c (id) MATCH SIMPLE
    ON UPDATE NO ACTION ON DELETE NO ACTION,
  CONSTRAINT fknjjuiq1kn44mu5299dn67t3np FOREIGN KEY (p_id)
    REFERENCES public.p (id) MATCH SIMPLE
    ON UPDATE NO ACTION ON DELETE NO ACTION
)

Tableau C (taille 45k)

CREATE TABLE public.c
(
  id bigint NOT NULL DEFAULT nextval('c_id_seq'::regclass),
  created_by character varying(255),
  created_date timestamp without time zone NOT NULL,
  m_id bigint,
  CONSTRAINT c_pkey PRIMARY KEY (id),
  CONSTRAINT fkbv33l9w17owvi5kgctqvaepn0 FOREIGN KEY (m_id)
    REFERENCES public.m (id) MATCH SIMPLE
    ON UPDATE NO ACTION ON DELETE NO ACTION
)

Tableau M (taille 200)

CREATE TABLE public.m
(
  id bigint NOT NULL DEFAULT nextval('m_id_seq'::regclass),
  created_by character varying(255),
  created_date timestamp without time zone NOT NULL,
  a_id bigint,
  CONSTRAINT m_pkey PRIMARY KEY (id),
  CONSTRAINT fkikqmae593j3mruwqy84pc56is FOREIGN KEY (a_id)
      REFERENCES public.a (id) MATCH SIMPLE
      ON UPDATE NO ACTION ON DELETE NO ACTION
)

Tableau P (taille 5k)

CREATE TABLE public.p
(
  id bigint NOT NULL,
  created_by character varying(255),
  created_date timestamp without time zone NOT NULL,
  e character varying(255) NOT NULL,
  a_id bigint,
  CONSTRAINT p_pkey PRIMARY KEY (id),
  CONSTRAINT fkehggtafv310ewdtcq772pwl01 FOREIGN KEY (a_id)
      REFERENCES public.a (id) MATCH SIMPLE
      ON UPDATE NO ACTION ON DELETE NO ACTION
)

Answer 1

Il s'agit d'un cas particulier du problème commun du "top N par catégorie". pour lesquels il existe quelques solutions en SQL. Pourquoi ne pas utiliser simplement le SQL natif ? Votre requête pourrait se traduire par :

SELECT (t).*
FROM (
  SELECT t, ROW_NUMBER() OVER (PARTITION BY t.m_id, t.p_id ORDER BY t.id DESC) rn
  FROM t
  JOIN c ON t.c_id = c.id
  WHERE t.m_id = :m
  AND c.created_date < :date
) t
WHERE t.rn = 1

La principale différence réside dans le fait que vous pouvez éviter un accès à l'élément T table.

Bien sûr, je suppose que vous disposez de tous les index appropriés, y compris les index sur toutes les clés étrangères et sur le fichier C.CREATED_DATE colonne. Le résultat peut toujours être mappé à une entité.

Note, J'utilise la syntaxe spécifique de PostgreSQL pour imbriquer les enregistrements. de sorte que la suppression de la RN La colonne de la projection est à nouveau beaucoup plus facile.