Re: Query with high planning time at version 11.1 compared versions 10.5 and 11.0

Thanks a lot Justin,

At this moment I can not help you with what you asked for, but tomorrow
morning I will send other information.
I believe Postgres 11.1 is somehow taking a lot of planning time when
analyzing which partitions are needed in execution.

Sanyo

Em ter, 27 de nov de 2018 às 22:44, Justin Pryzby <pryzby(at)telsasoft(dot)com>
escreveu:

> On Tue, Nov 27, 2018 at 9:17 AM Sanyo Moura <sanyo(dot)moura(at)tatic(dot)net> wrote:
> >>> I'm running performance tests for my application at version 11.1 and
> >>> encountered queries with high planning time compared to the same
> planning,
> >>> running at versions 10.5 and 11.0.
>
> I was able to reproduce this behavior.
>
> For my version of the query:
>
> On PG10.6
> | Result (cost=0.00..0.00 rows=0 width=24)
> | One-Time Filter: false
> |Time: 408.335 ms
>
> On PG11.1
> | Result (cost=0.00..0.00 rows=0 width=24)
> | One-Time Filter: false
> |Time: 37487.364 ms (00:37.487)
>
> Perf shows me:
> 47.83% postmaster postgres [.] bms_overlap
> 45.30% postmaster postgres [.] add_child_rel_equivalences
> 1.26% postmaster postgres [.]
> generate_join_implied_equalities_for_ecs
>
> CREATE TABLE producto (pluid int unique);
> CREATE TABLE almacen (loccd int unique);
> CREATE TABLE precio(fecha timestamp, pluid int, loccd int, plusalesprice
> int) PARTITION BY RANGE (fecha);
> SELECT 'CREATE TABLE public.precio_'||i||' PARTITION OF public.precio
> (PRIMARY KEY (fecha, pluid, loccd), CONSTRAINT precio_20170301_almacen_fk
> FOREIGN KEY (loccd) REFERENCES public.almacen (loccd) MATCH SIMPLE ON
> UPDATE NO ACTION ON DELETE NO ACTION, CONSTRAINT
> precio_20170301_producto_fk FOREIGN KEY (pluid) REFERENCES public.producto
> (pluid) MATCH SIMPLE ON UPDATE NO ACTION ON DELETE NO ACTION) FOR VALUES
> FROM ('''||a||''')TO('''||b||''') TABLESPACE pg_default' FROM (SELECT
> '1990-01-01'::timestamp+(i||'days')::interval a,
> '1990-01-02'::timestamp+(i||'days')::interval b, i FROM
> generate_series(1,999) i)x;
>
> \gexec
> \timing
> explain SELECT l_variacao.fecha, l_variacao.loccd , l_variacao.pant ,
> l_variacao.patual , max_variacao.var_max FROM (SELECT p.fecha, p.loccd,
> p.plusalesprice patual, da.plusalesprice pant, abs(p.plusalesprice -
> da.plusalesprice) as var from precio p, (SELECT p.fecha, p.plusalesprice,
> p.loccd from precio p WHERE p.fecha between '2017-03-01' and '2017-03-02'
> and p.pluid = 2) da WHERE p.fecha between '2017-03-01' and '2017-03-02' and
> p.pluid = 2 and p.loccd = da.loccd and p.fecha = da.fecha) l_variacao,
> (SELECT max(abs(p.plusalesprice - da.plusalesprice)) as var_max from precio
> p, (SELECT p.fecha, p.plusalesprice, p.loccd from precio p WHERE p.fecha
> between '2017-03-01' and '2017-03-02' and p.pluid = 2) da WHERE p.fecha
> between '2017-03-01' and '2017-03-02' and p.pluid = 2 and p.loccd =
> da.loccd and p.fecha = da.fecha) max_variacao WHERE max_variacao.var_max =
> l_variacao.var;
>
> Since I don't know the original table definitions, I removed two "+1" from
> the
> given sql to avoid: "ERROR: operator does not exist: timestamp without
> time zone + integer"
>
> Justin
>