Query execution time increased dramatically without type cast

Question

The query in this state takes more than 5 minutes to execute. If I remove any of the ::DATE conversions (see comment in code) the execution time goes < 500 ms.

For example, if I change gf.created::DATE to gf.created the performance is dramatically increased. Same happens if I change gtg.created::DATE to gtg.created.

Why is there a huge difference when using both ::DATE conversions if each shows great performance on its own?

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;

Table definitions
All 3 tables have around 50000 rows:

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
)

Please **[edit]** your question and add the `create table` statements for the tables in question (including all indexes) and the [execution plan](https://www.postgresql.org/docs/current/static/using-explain.html) generated using **`explain (analyze, buffers)`** (not just a "simple" explain). [Formatted text](http://stackoverflow.com/help/formatting) please, [no screen shots](http://meta.stackoverflow.com/questions/285551/why-may-i-not-upload-images-of-code-on-so-when-asking-a-question/285557#285557) or upload the plan to https://explain.depesz.com — , Dec 04 '18 at 13:41

score 2 · Accepted Answer · answered Dec 04 '18 at 13:53

2

Most probably because the forced cast voids the option to use an index on the column agroapp.ganado_fundo.created

Guessing (for lack of information) that gf.created is of type timestamp with time zone (or timestamp), replace

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

with:

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

to achieve the same result, but with index support.

If you operate with timestamtptz, be aware of implications on the date: it depends on the current time zone. Details:

Ignoring time zones altogether in Rails and PostgreSQL

answered Dec 04 '18 at 13:53

Erwin Brandstetter

605,456
145
1,078
1,228

You are right about match data type. The only thing is that gf.created can be something like '2018-10-30 16:54:23.2562' and I need to remove hour to make it match a date. – danielctf Dec 04 '18 at 14:09
Look closely: that's already taken care of by comparing with `< '2018-10-31'::timestamp`, which is short for `< '2018-10-31 00:00'::timestamp`, i.e. 00:00 hours of the *next* day. – Erwin Brandstetter Dec 08 '18 at 02:48

Query execution time increased dramatically without type cast

1 Answers1