From 8f321bd16cdb11307f17007eb3c55b65d7d323ef Mon Sep 17 00:00:00 2001 From: Tomas Vondra Date: Sat, 14 Mar 2020 14:55:59 +0100 Subject: Use functional dependencies to estimate ScalarArrayOpExpr Until now functional dependencies supported only simple equality clauses and clauses that can be trivially translated to equalities. This commit allows estimation of some ScalarArrayOpExpr (IN/ANY) clauses. For IN clauses we can do this thanks to using operator with equality semantics, which means an IN clause WHERE c IN (1, 2, ..., N) can be translated to WHERE (c = 1 OR c = 2 OR ... OR c = N) IN clauses are now considered compatible with functional dependencies, and rely on the same assumption of consistency of queries with data (which is an assumption we already used for simple equality clauses). This applies also to ALL clauses with an equality operator, which can be considered equivalent to IN clause. ALL clauses are still considered incompatible, although there's some discussion about maybe relaxing this in the future. Author: Pierre Ducroquet Reviewed-by: Tomas Vondra, Dean Rasheed Discussion: https://www.postgresql.org/message-id/flat/13902317.Eha0YfKkKy%40pierred-pdoc --- doc/src/sgml/perform.sgml | 3 ++- 1 file changed, 2 insertions(+), 1 deletion(-) (limited to 'doc/src') diff --git a/doc/src/sgml/perform.sgml b/doc/src/sgml/perform.sgml index 0f61b0995d3..ab090441cfe 100644 --- a/doc/src/sgml/perform.sgml +++ b/doc/src/sgml/perform.sgml @@ -1203,7 +1203,8 @@ SELECT stxname, stxkeys, stxddependencies Functional dependencies are currently only applied when considering - simple equality conditions that compare columns to constant values. + simple equality conditions that compare columns to constant values, + and IN clauses with constant values. They are not used to improve estimates for equality conditions comparing two columns or comparing a column to an expression, nor for range clauses, LIKE or any other type of condition. -- cgit v1.2.3