RE: Strange (and good) side effect of partitioning ?

Hi Tom,

>The default value of constraint_exclusion is "partition", which means
>(you guessed it) that it's applied only to potential partitioning
>constraints. This is a heuristic based on the typical payoff of
>excluding whole partitions versus skipping an empty index scan.
>But if you have a workload where it's really worth spending
>planner cycles looking for self-contradictory queries, you can
>turn it on.

Interesting. Test case was not real but planning times have to be considered from a more general point of view. They are not a problem with our DSS app but we will also migrate our OLTP applications.
Partitioning is something new for me since we currently don't use it for our OLTP apps. It was not a technical choice, partitioning is not included in standard license of our current RDBMS. I will globally check the gain/loss with real workloads anyway.

Best regards,

Phil

________________________________
De : Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)us>
Envoyé : vendredi 15 janvier 2021 03:12
À : Phil Florent <philflorent(at)hotmail(dot)com>
Cc : pgsql-general(at)postgresql(dot)org <pgsql-general(at)postgresql(dot)org>
Objet : Re: Strange (and good) side effect of partitioning ?

I wrote:
> There's no specific mechanism in Postgres that would cause "X between 20
> and 10" to be reduced to constant-false

Wait, I take that back. There is a mechanism that can conclude that
"X >= 20" and "X <= 10" are contradictory, but it's not applied by
default. Observe:

regression=# set constraint_exclusion = default;
SET
regression=# explain select * from tenk1 where unique1 between 20 and 10;
QUERY PLAN
-----------------------------------------------------------------------------
Index Scan using tenk1_unique1 on tenk1 (cost=0.29..8.30 rows=1 width=244)
Index Cond: ((unique1 >= 20) AND (unique1 <= 10))
(2 rows)

regression=# set constraint_exclusion = on;
SET
regression=# explain select * from tenk1 where unique1 between 20 and 10;
QUERY PLAN
------------------------------------------
Result (cost=0.00..0.00 rows=0 width=0)
One-Time Filter: false
(2 rows)

The default value of constraint_exclusion is "partition", which means
(you guessed it) that it's applied only to potential partitioning
constraints. This is a heuristic based on the typical payoff of
excluding whole partitions versus skipping an empty index scan.
But if you have a workload where it's really worth spending
planner cycles looking for self-contradictory queries, you can
turn it on.

regards, tom lane