Skip site navigation (1) Skip section navigation (2)

pgsql: Improve estimation of IN/NOT IN by assuming array elements ared

From: Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)us>
To: pgsql-committers(at)postgresql(dot)org
Subject: pgsql: Improve estimation of IN/NOT IN by assuming array elements ared
Date: 2012-03-08 04:00:52
Message-ID: (view raw, whole thread or download thread mbox)
Lists: pgsql-committers
Improve estimation of IN/NOT IN by assuming array elements are distinct.

In constructs such as "x IN (1,2,3,4)" and "x <> ALL(ARRAY[1,2,3,4])",
we formerly always used a general-purpose assumption that the probability
of success is independent for each comparison of "x" to an array element.
But in real-world usage of these constructs, that's a pretty poor
assumption; it's much saner to assume that the array elements are distinct
and so the match probabilities are disjoint.  Apply that assumption if the
operator appears to behave as equality (for ANY) or inequality (for ALL).
But fall back to the normal independent-probabilities calculation if this
yields an impossible result, ie probability > 1 or < 0.  We could protect
ourselves against bad estimates even more by explicitly checking for equal
array elements, but that is expensive and doesn't seem worthwhile: doing
it would amount to optimizing for poorly-written queries at the expense
of well-written ones.

Daniele Varrazzo and Tom Lane, after a suggestion by Ants Aasma



Modified Files
src/backend/utils/adt/selfuncs.c |   74 ++++++++++++++++++++++++++++++++++++--
1 files changed, 71 insertions(+), 3 deletions(-)

pgsql-committers by date

Next:From: Heikki LinnakangasDate: 2012-03-08 09:14:26
Subject: pgsql: Silence warning about unused variable,when building without ass
Previous:From: Tom LaneDate: 2012-03-08 00:26:07
Subject: pgsql: Fix indentation of \d footers for non-ASCII cases.

Privacy Policy | About PostgreSQL
Copyright © 1996-2018 The PostgreSQL Global Development Group