|PostgreSQL Reference Manual - Volume 2 - Programming Guide|
by The PostgreSQL Global Development Group
Paperback (6"x9"), 408 pages
RRP £19.95 ($34.95)
Sales of this book support the PostgreSQL project! Get a printed copy>>>
RESTRICT clause, if provided, names a restriction selectivity
estimation function for the operator. (Note that this is a function
name, not an operator name.)
RESTRICT clauses only make sense for
binary operators that return
boolean. The idea behind a restriction
selectivity estimator is to guess what fraction of the rows in a
table will satisfy a
WHERE-clause condition of the form
column OP constant
for the current operator and a particular constant value.
This assists the optimizer by
giving it some idea of how many rows will be eliminated by
clauses that have this form. (What happens if the constant is on
the left, you may be wondering? Well, that's one of the things that
COMMUTATOR is for...)
Writing new restriction selectivity estimation functions is far beyond the scope of this chapter, but fortunately you can usually just use one of the system's standard estimators for many of your own operators. These are the standard restriction estimators:
It might seem a little odd that these are the categories, but they
make sense if you think about it.
= will typically accept only
a small fraction of the rows in a table;
<> will typically reject
only a small fraction.
< will accept a fraction that depends on
where the given constant falls in the range of values for that table
column (which, it just so happens, is information collected by
ANALYZE and made available to the selectivity estimator).
<= will accept a slightly larger fraction than
< for the same
comparison constant, but they're close enough to not be worth
distinguishing, especially since we're not likely to do better than a
rough guess anyhow. Similar remarks apply to
You can frequently get away with using either
operators that have very high or very low selectivity, even if they
aren't really equality or inequality. For example, the
approximate-equality geometric operators use
eqsel on the assumption that
they'll usually only match a small fraction of the entries in a table.
You can use
scalargtsel for comparisons on data types that
have some sensible means of being converted into numeric scalars for
range comparisons. If possible, add the data type to those understood
by the function
convert_to_scalar() in ‘src/backend/utils/adt/selfuncs.c’.
(Eventually, this function should be replaced by per-data-type functions
identified through a column of the
pg_type system catalog; but that hasn't happened
yet.) If you do not do this, things will still work, but the optimizer's
estimates won't be as good as they could be.
There are additional selectivity estimation functions designed for geometric
operators in ‘src/backend/utils/adt/geo_selfuncs.c’:
contsel. At this writing these are just stubs, but you may want
to use them (or even better, improve them) anyway.
|ISBN 0954612035||PostgreSQL Reference Manual - Volume 2 - Programming Guide||See the print edition|