在没有类型转换的情况下，查询执行时间显著增加

Question

此状态下的查询执行时间超过5分钟。如果我移除任何CCD_ 1转换(参见代码中的注释(，则执行时间变为<500毫秒

例如，如果我将gf.created::DATE更改为gf.created，则性能将显著提高。如果我将gtg.created::DATE更改为gtg.created，也会发生同样的情况。

如果两种::DATE转换各自都显示出出色的性能，为什么在使用这两种转换时会有巨大的差异？

SELECT gtg6.tipo_ganado, COUNT(gtg6.tipo_ganado) animales  
FROM agroapp.ganado g
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  
WHERE gf.isactive = 'Y'
-- HERE CHANGING gf.created::DATE TO gf.created
AND gf.created::DATE <= '20181030'::DATE
GROUP BY gf.ganado_id) gf2 ON (gf2.ganado_id = gf3.ganado_id AND gf2.created = gf3.created)  
WHERE gf3.isactive = 'Y'  
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 gtg5.ganado_id, gtg5.tipo_ganado  
FROM agroapp.ganado_tipo_ganado gtg5  
INNER JOIN (SELECT MAX(gtg3.ganado_tipo_ganado_id) ganado_tipo_ganado_id  
FROM agroapp.ganado_tipo_ganado gtg3  
INNER JOIN (SELECT gtg.ganado_id, MAX(gtg.created) created  
FROM agroapp.ganado_tipo_ganado gtg  
WHERE gtg.isactive = 'Y'
-- OR HERE CHANGING gtg.created::DATE TO gtg.created
AND gtg.created::DATE <= '20181030'::DATE
GROUP BY gtg.ganado_id) gtg2 ON (gtg2.ganado_id = gtg3.ganado_id AND gtg2.created = gtg3.created)  
WHERE gtg3.isactive = 'Y'  
GROUP BY gtg3.ganado_id) gtg4 ON gtg4.ganado_tipo_ganado_id = gtg5.ganado_tipo_ganado_id  
) gtg6 ON gtg6.ganado_id = g.ganado_id  
WHERE g.organizacion_id = 21
GROUP BY gtg6.tipo_ganado
ORDER BY gtg6.tipo_ganado;

表定义
所有3个表都有大约50000行：

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_tipo_ganado
(
ganado_tipo_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,
tipo_ganado character varying(80) NOT NULL,
ganado_id integer NOT NULL,
CONSTRAINT ganado_tipo_ganado_pk PRIMARY KEY (ganado_tipo_ganado_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
)

Answer 1

很可能是因为强制强制强制转换使在列agroapp.ganado_fundo.created上使用索引的选项无效

推测(由于缺乏信息(gf.created属于timestamp with time zone(或::DATE0(类型，替换

AND gf.created::DATE <= '20181030'::DATE

带有：

AND gf.created < '2018-10-31'::timestamp  -- match the data type of the column!

以达到相同的结果，但有指数支持。

如果使用timestamtptz操作，请注意日期的含义：它取决于当前时区。详细信息：

• 在Rails和PostgreSQL中完全忽略时区