Re: Query with high planning time at version 11.1 compared versions10.5 and 11.0

Hi,

On 2018/12/05 6:55, Alvaro Herrera wrote:
> I noticed another interesting thing, which is that if I modify the query
> to actually reference some partition that I do have (as opposed to the
> above, which just takes 30s to prune everything) the plan is mighty
> curious ... if only because in one of the Append nodes, partitions have
> not been pruned as they should.
> 
> So, at least two bugs here,
> 1. the equivalence-class related slowness,
> 2. the lack of pruning
> 
>                                                                                            QUERY PLAN
                                                                          
 
>
─────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────
>  Hash Join  (cost=1159.13..25423.65 rows=1 width=24)
>    Hash Cond: (abs((p.plusalesprice - p_875.plusalesprice)) = (max(abs((p_877.plusalesprice -
p_879.plusalesprice)))))
>    ->  Nested Loop  (cost=1000.00..25264.52 rows=1 width=20)
>          Join Filter: ((p.loccd = p_875.loccd) AND (p.fecha = p_875.fecha))
>          ->  Gather  (cost=1000.00..25154.38 rows=875 width=16)
>                Workers Planned: 2
>                ->  Parallel Append  (cost=0.00..24066.88 rows=875 width=16)
>                      ->  Parallel Seq Scan on precio_125 p  (cost=0.00..27.50 rows=1 width=16)
>                            Filter: ((fecha >= '1990-05-06 00:00:00'::timestamp without time zone) AND (fecha <=
'1999-05-0700:00:00'::timestamp without time zone) AND (pluid = 2))
 

[ Parallel SeqScan on precio_126 to precio_998  ]

>                      ->  Parallel Seq Scan on precio_999 p_874  (cost=0.00..27.50 rows=1 width=16)
>                            Filter: ((fecha >= '1990-05-06 00:00:00'::timestamp without time zone) AND (fecha <=
'1999-05-0700:00:00'::timestamp without time zone) AND (pluid = 2))
 

As you can see from the "Filter: " property above, the baserestrictinfo of
this Append's parent relation is:

BETWEEN '1990-05-06' AND '1999-05-07'

which selects partitions for all days from '1990-05-06' (precio_125) up to
'1992-09-26' (precio_999).

>          ->  Materialize  (cost=0.00..79.52 rows=2 width=16)
>                ->  Append  (cost=0.00..79.51 rows=2 width=16)
>                      ->  Seq Scan on precio_125 p_875  (cost=0.00..39.75 rows=1 width=16)
>                            Filter: ((fecha >= '1990-05-06 00:00:00'::timestamp without time zone) AND (fecha <=
'1990-05-0700:00:00'::timestamp without time zone) AND (pluid = 2))
 
>                      ->  Seq Scan on precio_126 p_876  (cost=0.00..39.75 rows=1 width=16)
>                            Filter: ((fecha >= '1990-05-06 00:00:00'::timestamp without time zone) AND (fecha <=
'1990-05-0700:00:00'::timestamp without time zone) AND (pluid = 2))
 

Whereas for this Append, it is BETWEEN '1990-05-06' AND '1990-05-07'.

>    ->  Hash  (cost=159.12..159.12 rows=1 width=4)
>          ->  Aggregate  (cost=159.10..159.11 rows=1 width=4)
>                ->  Nested Loop  (cost=0.00..159.10 rows=1 width=8)
>                      Join Filter: ((p_877.loccd = p_879.loccd) AND (p_877.fecha = p_879.fecha))
>                      ->  Append  (cost=0.00..79.51 rows=2 width=16)
>                            ->  Seq Scan on precio_125 p_877  (cost=0.00..39.75 rows=1 width=16)
>                                  Filter: ((fecha >= '1990-05-06 00:00:00'::timestamp without time zone) AND (fecha <=
'1990-05-0700:00:00'::timestamp without time zone) AND (pluid = 2))
 
>                            ->  Seq Scan on precio_126 p_878  (cost=0.00..39.75 rows=1 width=16)
>                                  Filter: ((fecha >= '1990-05-06 00:00:00'::timestamp without time zone) AND (fecha <=
'1990-05-0700:00:00'::timestamp without time zone) AND (pluid = 2))
 
>                      ->  Materialize  (cost=0.00..79.52 rows=2 width=16)
>                            ->  Append  (cost=0.00..79.51 rows=2 width=16)
>                                  ->  Seq Scan on precio_125 p_879  (cost=0.00..39.75 rows=1 width=16)
>                                        Filter: ((fecha >= '1990-05-06 00:00:00'::timestamp without time zone) AND
(fecha<= '1990-05-07 00:00:00'::timestamp without time zone) AND (pluid = 2))
 
>                                  ->  Seq Scan on precio_126 p_880  (cost=0.00..39.75 rows=1 width=16)
>                                        Filter: ((fecha >= '1990-05-06 00:00:00'::timestamp without time zone) AND
(fecha<= '1990-05-07 00:00:00'::timestamp without time zone) AND (pluid = 2))
 

And also for these two Appends.

So, I don't think there's anything funny going on with pruning here, maybe
just a typo in the query (1999 looks very much like 1990 to miss the typo
maybe.)  I fixed the query to change '1999-05-07' to '1990-05-07' of the
first Append's parent relation and I get the following planning time with
the patch I posted above with 2 partitions selected under each Append as
expected.

 Planning Time: 536.947 ms
 Execution Time: 1.304 ms
(31 rows)

Even without changing 1999 to 1990, the planning time with the patch is:

 Planning Time: 4669.685 ms
 Execution Time: 110.506 ms
(1777 rows)

Thanks,
Amit