Слишком медленный запрос только для 4 таблиц по 50000 строк в каждой

Question

Слишком медленный запрос только для 4 таблиц по 50000 строк в каждой

Я боролся в течение нескольких часов, и я не могу найти, почему этот запрос занимает слишком много времени (> 60 минут). Все 4 таблицы имеют менее 50.000 записей.

Также, если я удаляю какую-либо таблицу (gel6, gf6 или ger6), выполнение запроса занимает менее 500 мс. Что я делаю неправильно?

Объясните план: https://explain.depesz.com/s/ldm2

 SELECT COUNT(*)
 FROM agroapp.ganado g
 INNER JOIN (SELECT gel5.ganado_id, gel5.estado_leche  
             FROM agroapp.ganado_estado_leche gel5  
             INNER JOIN (SELECT MAX(gel3.ganado_estado_leche_id) ganado_estado_leche_id  
                         FROM agroapp.ganado_estado_leche gel3  
                         INNER JOIN (SELECT gel.ganado_id, MAX(gel.created) created  
                                     FROM agroapp.ganado_estado_leche gel   
                                     GROUP BY gel.ganado_id) gel2 ON (gel2.ganado_id = gel3.ganado_id AND gel2.created = gel3.created)  
                         GROUP BY gel3.ganado_id) gel4 ON gel4.ganado_estado_leche_id = gel5.ganado_estado_leche_id  
            ) gel6 ON gel6.ganado_id = g.ganado_id
 INNER JOIN (SELECT gf5.ganado_id, gf5.fundo_id  
             FROM agroapp.ganado_fundo gf5  
             INNER JOIN (SELECT MAX(gf3.ganado_fundo_id) ganado_fundo_id  
                         FROM agroapp.ganado_fundo gf3  
                         INNER JOIN (SELECT gf.ganado_id, MAX(gf.created) created  
                                     FROM agroapp.ganado_fundo gf  
                                     GROUP BY gf.ganado_id) gf2 ON (gf2.ganado_id = gf3.ganado_id AND gf2.created = gf3.created)    
                         GROUP BY gf3.ganado_id) gf4 ON gf4.ganado_fundo_id = gf5.ganado_fundo_id  
            ) gf6 ON gf6.ganado_id = g.ganado_id
 INNER JOIN (SELECT ger5.ganado_id, ger5.estado_reproductivo  
             FROM agroapp.ganado_estado_reproductivo ger5  
             INNER JOIN (SELECT MAX(ger3.ganado_estado_reproductivo_id) ganado_estado_reproductivo_id  
                         FROM agroapp.ganado_estado_reproductivo ger3  
                         INNER JOIN (SELECT ger.ganado_id, MAX(ger.created) created  
                                     FROM agroapp.ganado_estado_reproductivo ger  
                                     GROUP BY ger.ganado_id) ger2 ON (ger2.ganado_id = ger3.ganado_id AND ger2.created = ger3.created)  
                         GROUP BY ger3.ganado_id) ger4 ON ger4.ganado_estado_reproductivo_id = ger5.ganado_estado_reproductivo_id  
            ) ger6 ON ger6.ganado_id = g.ganado_id
WHERE g.organizacion_id = 21

таблицы

CREATE TABLE agroapp.ganado_estado_leche
(
  ganado_estado_leche_id serial NOT NULL,
  organizacion_id integer NOT NULL,
  isactive character(1) NOT NULL DEFAULT 'Y'::bpchar,
  created timestamp without time zone NOT NULL DEFAULT now(),
  createdby numeric(10,0) NOT NULL,
  updated timestamp without time zone NOT NULL DEFAULT now(),
  updatedby numeric(10,0) NOT NULL,
  estado_leche character varying(80) NOT NULL,
  ganado_id integer NOT NULL,
  fecha_manejo timestamp without time zone NOT NULL,
  CONSTRAINT ganado_estado_leche_pk PRIMARY KEY (ganado_estado_leche_id),
  CONSTRAINT ganado_fk FOREIGN KEY (ganado_id)
      REFERENCES agroapp.ganado (ganado_id) MATCH SIMPLE
      ON UPDATE NO ACTION ON DELETE NO ACTION
)

CREATE TABLE agroapp.ganado_fundo
(
  ganado_fundo_id serial NOT NULL,
  organizacion_id integer NOT NULL,
  isactive character(1) NOT NULL DEFAULT 'Y'::bpchar,
  created timestamp without time zone NOT NULL DEFAULT now(),
  createdby numeric(10,0) NOT NULL,
  updated timestamp without time zone NOT NULL DEFAULT now(),
  updatedby numeric(10,0) NOT NULL,
  fundo_id integer NOT NULL,
  ganado_id integer NOT NULL,
  CONSTRAINT ganado_fundo_pk PRIMARY KEY (ganado_fundo_id),
  CONSTRAINT ganado_fk FOREIGN KEY (ganado_id)
      REFERENCES agroapp.ganado (ganado_id) MATCH SIMPLE
      ON UPDATE NO ACTION ON DELETE NO ACTION
)

CREATE TABLE agroapp.ganado_estado_reproductivo
(
  ganado_estado_reproductivo_id serial NOT NULL,
  organizacion_id integer NOT NULL,
  isactive character(1) NOT NULL DEFAULT 'Y'::bpchar,
  created timestamp without time zone NOT NULL DEFAULT now(),
  createdby numeric(10,0) NOT NULL,
  updated timestamp without time zone NOT NULL DEFAULT now(),
  updatedby numeric(10,0) NOT NULL,
  estado_reproductivo character varying(80) NOT NULL,
  ganado_id integer NOT NULL,
  fecha_manejo timestamp without time zone NOT NULL,
  CONSTRAINT ganado_estado_reproductivo_pk PRIMARY KEY (ganado_estado_reproductivo_id),
  CONSTRAINT ganado_fk FOREIGN KEY (ganado_id)
      REFERENCES agroapp.ganado (ganado_id) MATCH SIMPLE
      ON UPDATE NO ACTION ON DELETE NO ACTION
)

CREATE TABLE agroapp.ganado
(
  ganado_id serial NOT NULL,
  organizacion_id integer NOT NULL,
  isactive character(1) NOT NULL DEFAULT 'Y'::bpchar,
  created timestamp without time zone NOT NULL DEFAULT now(),
  createdby numeric(10,0) NOT NULL,
  updated timestamp without time zone NOT NULL DEFAULT now(),
  updatedby numeric(10,0) NOT NULL,
  fecha_nacimiento timestamp without time zone NOT NULL,
  tipo_ganado character varying(80) NOT NULL,
  diio_id integer NOT NULL,
  fundo_id integer NOT NULL,
  raza_id integer NOT NULL,
  estado_reproductivo character varying(80) NOT NULL,
  estado_leche character varying(80),
  CONSTRAINT ganado_pk PRIMARY KEY (ganado_id),
  CONSTRAINT diio_fk FOREIGN KEY (diio_id)
      REFERENCES agroapp.diio (diio_id) MATCH SIMPLE
      ON UPDATE NO ACTION ON DELETE NO ACTION,
  CONSTRAINT fundo_fk FOREIGN KEY (fundo_id)
      REFERENCES agroapp.fundo (fundo_id) MATCH SIMPLE
      ON UPDATE NO ACTION ON DELETE NO ACTION,
  CONSTRAINT raza_fk FOREIGN KEY (raza_id)
      REFERENCES agroapp.raza (raza_id) MATCH SIMPLE
      ON UPDATE NO ACTION ON DELETE NO ACTION
)

0

postgresql postgresql-performance

Источник

user3862823 04 дек '18 в 20:32

1 ответ

Другие вопросы по тегам postgresql postgresql-performance

user939860 11 дек '18 в 00:59 2018-12-11 00:59 · Answer 1 · 2018-12-11 00:59

Дизайн стола

Это выглядит очень похоже на boolean столбец (да / нет):
```
isactive character(1) NOT NULL DEFAULT 'Y'::bpchar
```
Если это так, заменить на:
```
isactive bool NOT NULL DEFAULT TRUE
```
Если вы можете использовать несколько часовых поясов, используйте timestamptz вместо timestamp Вот:
```
created timestamp without time zone NOT NULL DEFAULT now(),
```

По умолчанию now() производит timestamptz и после приведения присваивания результаты в текущем времени в соответствии с часовым поясом сеанса. Т.е. значение меняется с timezone сеанса, который является хитрой точкой отказа. Увидеть:
- полное игнорирование часовых поясов в Rails и PostgreSQL

А также:

createdby numeric(10,0) NOT NULL

и другие. похоже, они действительно должны быть просто integer, (Или, может быть bigint если вы действительно думаете, что могли бы прожечь более 2147483648 номеров...)

запрос

Глядя на первый подзапрос:

 SELECT gel5.ganado_id, gel5.estado_leche  
 FROM agroapp.ganado_estado_leche gel5  
 INNER JOIN (
    SELECT MAX(gel3.ganado_estado_leche_id) ganado_estado_leche_id  
    FROM agroapp.ganado_estado_leche gel3  
    INNER JOIN (
       SELECT gel.ganado_id, MAX(gel.created) created  
       FROM agroapp.ganado_estado_leche gel   
       GROUP BY gel.ganado_id
       ) gel2 ON (gel2.ganado_id = gel3.ganado_id AND gel2.created = gel3.created)  
    GROUP BY gel3.ganado_id
    ) gel4 ON gel4.ganado_estado_leche_id = gel5.ganado_estado_leche_id

Внутренний подзапрос получает макс. created в ganado_id, следующий максимум ganado_estado_leche_id из этих строк. И, наконец, вы присоединитесь и получите все ganado_id которые появляются в сочетании с указанным максимумом ganado_estado_leche_id на раздел. Мне трудно это понять, но это можно упростить до:

SELECT gel2.ganado_id
FROM   agroapp.ganado_estado_leche gel2
JOIN  (
   SELECT DISTINCT ON (ganado_id) ganado_estado_leche_id
   FROM   agroapp.ganado_estado_leche
   ORDER  BY ganado_id, created DESC NULLS LAST, ganado_estado_leche_id DESC NULLS LAST
   ) gel1 USING (ganado_estado_leche_id)

Увидеть:

Выберите первую строку в каждой группе GROUP BY?

Похоже, неправильный запрос для меня. То же самое с остальной частью запроса: объединения умножают строки нечетным способом. Не уверен, что вы пытаетесь сосчитать, но я сомневаюсь, что запрос имеет значение именно это. Вы не предоставили достаточно информации, чтобы понять это.