cost into account while planning.

instead of only one.  This should speed up planning (only one hash path
to consider for a given pair of relations) as well as allow more effective
hashing, when there are multiple hashable joinclauses.

parameter to allow it to be forced off for comparison purposes.
Add ORDER BY clauses to a bunch of regression test queries that will
otherwise produce randomly-ordered output in the new regime.

Reused the Expr node to hold DISTINCT which strongly resembles
 the existing OP info. Define DISTINCT_EXPR which strongly resembles
 the existing OPER_EXPR opType, but with handling for NULLs required
 by SQL99.
We have explicit support for single-element DISTINCT comparisons
 all the way through to the executor. But, multi-element DISTINCTs
 are handled by expanding into a comparison tree in gram.y as is done for
 other row comparisons. Per discussions, it might be desirable to move
 this into one or more purpose-built nodes to be handled in the backend.
Define the optional ROW keyword and token per SQL99.
 This allows single-element row constructs, which were formerly disallowed
 due to shift/reduce conflicts with parenthesized a_expr clauses.
Define the SQL99 TREAT() function. Currently, use as a synonym for CAST().

comments on one of the optimizer functions a lot more
clear, adds a summary of the recent KSQO discussion to the
comments in the code, adds regression tests for the bug with
sequence state Tom fixed recently and another reg. test, and
removes some PostQuel legacy stuff: ExecAppend -> ExecInsert,
ExecRetrieve -> ExecSelect, etc.

Error messages remain unchanged until a vote.

Neil Conway

Neil Conway

comments on one of the optimizer functions a lot more
clear, adds a summary of the recent KSQO discussion to the
comments in the code, adds regression tests for the bug with
sequence state Tom fixed recently and another reg. test, and
removes some PostQuel legacy stuff: ExecAppend -> ExecInsert,
ExecRetrieve -> ExecSelect, etc. This was changed because the
elog() messages from this routine are user-visible, so we
should be using the SQL terms.

Neil Conway

some kibitzing from Tom Lane.  Not everything works yet, and there's
no documentation or regression test, but let's commit this so Joe
doesn't need to cope with tracking changes in so many files ...

now has an RTE of its own, and references to its outputs now are Vars
referencing the JOIN RTE, rather than CASE-expressions.  This allows
reverse-listing in ruleutils.c to use the correct alias easily, rather
than painfully reverse-engineering the alias namespace as it used to do.
Also, nested FULL JOINs work correctly, because the result of the inner
joins are simple Vars that the planner can cope with.  This fixes a bug
reported a couple times now, notably by Tatsuo on 18-Nov-01.  The alias
Vars are expanded into COALESCE expressions where needed at the very end
of planning, rather than during parsing.
Also, beginnings of support for showing plan qualifier expressions in
EXPLAIN.  There are probably still cases that need work.
initdb forced due to change of stored-rule representation.

mergeclause is which when extracting selectivity info.

in RestrictInfo nodes, instead of recomputing on every use.

both input streams to the end.  If one variable's range is much less
than the other, an indexscan-based merge can win by not scanning all
of the other table.  Per example from Reinhard Max.

tests pass.

pgsql-hackers.  pg_opclass now has a row for each opclass supported by each
index AM, not a row for each opclass name.  This allows pg_opclass to show
directly whether an AM supports an opclass, and furthermore makes it possible
to store additional information about an opclass that might be AM-dependent.
pg_opclass and pg_amop now store "lossy" and "haskeytype" information that we
previously expected the user to remember to provide in CREATE INDEX commands.
Lossiness is no longer an index-level property, but is associated with the
use of a particular operator in a particular index opclass.

Along the way, IndexSupportInitialize now uses the syscaches to retrieve
pg_amop and pg_amproc entries.  I find this reduces backend launch time by
about ten percent, at the cost of a couple more special cases in catcache.c's
IndexScanOK.

Initial work by Oleg Bartunov and Teodor Sigaev, further hacking by Tom Lane.

initdb forced.

nodeHash.c will compute it (by sharing code).

should be computed from total number of distinct values in whole
relation, not # distinct values we expect to have after restriction
clauses are applied.

of costsize.c routines to pass Query root, so that costsize can figure
more things out by itself and not be so dependent on its callers to tell
it everything it needs to know.  Use selectivity of hash or merge clause
to estimate number of tuples processed internally in these joins
(this is more useful than it would've been before, since eqjoinsel is
somewhat more accurate than before).

create_index_paths are not immediately discarded, but are available for
subsequent planner work.  This allows avoiding redundant syscache lookups
in several places.  Change interface to operator selectivity estimation
procedures to allow faster and more flexible estimation.
Initdb forced due to change of pg_proc entries for selectivity functions!

between index order and table order.

collected by ANALYZE.  Also, add some modest amount of intelligence to
guesses that are used for varlena columns in the absence of any ANALYZE
statistics.  The 'width' reported by EXPLAIN is finally something less
than totally bogus for varlena columns ... and, in consequence, hashjoin
estimating should be a little better ...

a separate statement (though it can still be invoked as part of VACUUM, too).
pg_statistic redesigned to be more flexible about what statistics are
stored.  ANALYZE now collects a list of several of the most common values,
not just one, plus a histogram (not just the min and max values).  Random
sampling is used to make the process reasonably fast even on very large
tables.  The number of values and histogram bins collected is now
user-settable via an ALTER TABLE command.

There is more still to do; the new stats are not being used everywhere
they could be in the planner.  But the remaining changes for this project
should be localized, and the behavior is already better than before.

A not-very-related change is that sorting now makes use of btree comparison
routines if it can find one, rather than invoking '<' twice.

Without this, it was making some pretty silly decisions about whether an
expensive sub-SELECT should be the inner or outer side of a join...

relations.  It's not very bright, but at least it now knows that
A LEFT JOIN B must produce at least as many rows as are in A ...

avoid repeated evaluations in cost_qual_eval().  This turns out to save
a useful fraction of planning time.  No change to external representation
of RestrictInfo --- although that node type doesn't appear in stored
rules anyway.

Fix misspelling of disbursion to dispersion.

(Don't forget that an alias is required.)  Views reimplemented as expanding
to subselect-in-FROM.  Grouping, aggregates, DISTINCT in views actually
work now (he says optimistically).  No UNION support in subselects/views
yet, but I have some ideas about that.  Rule-related permissions checking
moved out of rewriter and into executor.
INITDB REQUIRED!

materialized tupleset is small enough) instead of a temporary relation.
This was something I was thinking of doing anyway for performance, and Jan
says he needs it for TOAST because he doesn't want to cope with toasting
noname relations.  With this change, the 'noname table' support in heap.c
is dead code, and I have accordingly removed it.  Also clean up 'noname'
plan handling in planner --- nonames are either sort or materialize plans,
and it seems less confusing to handle them separately under those names.

That means you can now set your options in either or all of $PGDATA/configuration,
some postmaster option (--enable-fsync=off), or set a SET command. The list of
options is in backend/utils/misc/guc.c, documentation will be written post haste.

pg_options is gone, so is that pq_geqo config file. Also removed were backend -K,
-Q, and -T options (no longer applicable, although -d0 does the same as -Q).

Added to configure an --enable-syslog option.

changed all callers from TPRINTF to elog(DEBUG)

fmgr_faddr() in favor of new-style calls.  Lots of cleanup of
sloppy casts to use XXXGetDatum and DatumGetXXX ...

table for an average of NTUP_PER_BUCKET tuples/bucket, but cost_hashjoin
was assuming a target load of one tuple/bucket.  This was causing a
noticeable underestimate of hashjoin costs.

to next integer.  Previously, if selectivity was small, we could compute
very tiny scan cost on the basis of estimating that only 0.001 tuple
would be fetched, which is silly.  This naturally led to some rather
silly plans...

costs using the inner path's parent->rows count as the number of tuples
processed per inner scan iteration.  This is wrong when we are using an
inner indexscan with indexquals based on join clauses, because the rows
count in a Relation node reflects the selectivity of the restriction
clauses for that rel only.  Upshot was that if join clause was very
selective, we'd drastically overestimate the true cost of the join.
Fix is to calculate correct output-rows estimate for an inner indexscan
when the IndexPath node is created and save it in the path node.
Change of path node doesn't require initdb, since path nodes don't
appear in saved rules.