| Commit message (Collapse) | Author | Age |
|---|
| ... | |
| |
|
|
| |
to trouble when trying to EXPLAIN VERBOSE a plan containing one.
|
| |
|
|
|
|
|
|
|
|
|
|
|
| |
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.
|
| |
|
|
|
|
| |
running gcc and HP's cc with warnings cranked way up. Signed vs unsigned
comparisons, routines declared static and then defined not-static,
that kind of thing. Tedious, but perhaps useful...
|
| |
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
| |
Implement TIME WITH TIME ZONE type (timetz internal type).
Remap length() for character strings to CHAR_LENGTH() for SQL92
and to remove the ambiguity with geometric length() functions.
Keep length() for character strings for backward compatibility.
Shrink stored views by removing internal column name list from visible rte.
Implement min(), max() for time and timetz data types.
Implement conversion of TIME to INTERVAL.
Implement abs(), mod(), fac() for the int8 data type.
Rename some math functions to generic names:
round(), sqrt(), cbrt(), pow(), etc.
Rename NUMERIC power() function to pow().
Fix int2 factorial to calculate result in int4.
Enhance the Oracle compatibility function translate() to work with string
arguments (from Edwin Ramirez).
Modify pg_proc system table to remove OID holes.
|
| | |
|
| |
|
|
|
|
|
|
| |
integers) to be strings instead of 'double'. We convert from string form
to internal representation only after type resolution has determined the
correct type for the constant. This eliminates loss-of-precision worries
and gets rid of the change in behavior seen at 17 digits with the
previous kluge.
|
| |
|
|
|
|
|
|
|
|
| |
represent the result of a binary-compatible type coercion. At runtime
it just evaluates its argument --- but during type resolution, exprType
will pick up the output type of the RelabelType node instead of the type
of the argument. This solves some longstanding problems with dropped
type coercions, an example being 'select now()::abstime::int4' which
used to produce date-formatted output, not an integer, because the
coercion to int4 was dropped on the floor.
|
| |
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
| |
accesses versus sequential accesses, a (very crude) estimate of the
effects of caching on random page accesses, and cost to evaluate WHERE-
clause expressions. Export critical parameters for this model as SET
variables. Also, create SET variables for the planner's enable flags
(enable_seqscan, enable_indexscan, etc) so that these can be controlled
more conveniently than via PGOPTIONS.
Planner now estimates both startup cost (cost before retrieving
first tuple) and total cost of each path, so it can optimize queries
with LIMIT on a reasonable basis by interpolating between these costs.
Same facility is a win for EXISTS(...) subqueries and some other cases.
Redesign pathkey representation to achieve a major speedup in planning
(I saw as much as 5X on a 10-way join); also minor changes in planner
to reduce memory consumption by recycling discarded Path nodes and
not constructing unnecessary lists.
Minor cleanups to display more-plausible costs in some cases in
EXPLAIN output.
Initdb forced by change in interface to index cost estimation
functions.
|
| |
|
|
|
|
|
| |
SELECT a FROM t1 tx (a);
Allow join syntax, including queries like
SELECT * FROM t1 NATURAL JOIN t2;
Update RTE structure to hold column aliases in an Attr structure.
|
| |
|
|
|
|
|
|
|
|
|
| |
fields in JoinPaths --- turns out that we do need that after all :-(.
Also, rearrange planner so that only one RelOptInfo is created for a
particular set of joined base relations, no matter how many different
subsets of relations it can be created from. This saves memory and
processing time compared to the old method of making a bunch of RelOptInfos
and then removing the duplicates. Clean up the jointree iteration logic;
not sure if it's better, but I sure find it more readable and plausible
now, particularly for the case of 'bushy plans'.
|
| |
|
|
|
|
| |
nonoverlap_sets() and is_subset() to list.c, where they should have lived
to begin with, and rename to nonoverlap_setsi and is_subseti since they
only work on integer lists.
|
| |
|
|
| |
nodes...
|
| |
|
|
|
|
|
|
|
|
|
|
|
| |
SELECT DISTINCT ON (expr [, expr ...]) targetlist ...
and there is a check to make sure that the user didn't specify an ORDER BY
that's incompatible with the DISTINCT operation.
Reimplement nodeUnique and nodeGroup to use the proper datatype-specific
equality function for each column being compared --- they used to do
bitwise comparisons or convert the data to text strings and strcmp().
(To add insult to injury, they'd look up the conversion functions once
for each tuple...) Parse/plan representation of DISTINCT is now a list
of SortClause nodes.
initdb forced by querytree change...
|
| |
|
|
|
|
| |
* Portions Copyright (c) 1996-2000, PostgreSQL, Inc
to all files copyright Regents of Berkeley. Man, that's a lot of files.
|
| |
|
|
|
|
| |
pghackers discussion of 5-Jan-2000. The amopselect and amopnpages
estimators are gone, and in their place is a per-AM amcostestimate
procedure (linked to from pg_am, not pg_amop).
|
| |
|
|
|
|
| |
SQL cast constructs can be performed during expression transformation
instead of during parsing. This allows constructs like x::numeric(9,2)
and x::int2::float8 to behave as one would expect.
|
| |
|
|
|
|
| |
choke on relation or attribute names containing spaces, quotes, or other
special characters. This fixes a TODO item. It also forces initdb,
since stored rule strings change.
|
| |
|
|
|
| |
code cleanup; no major improvements yet. However, EXPLAIN does produce
more intuitive outputs for nested loops with indexscans now...
|
| |
|
|
|
|
|
|
|
|
|
|
|
| |
SELECT null::text;
SELECT int4fac(null);
work as expected now. In some cases a NULL must be surrounded by
parentheses:
SELECT 2 + null; fails
SELECT 2 + (null); OK
This is a grammatical ambiguity that seems difficult to avoid. Other
than that, NULLs seem to behave about like you'd expect. The internal
implementation is that NULL constants are typed as UNKNOWN (like
untyped string constants) until the parser can deduce the right type.
|
| |
|
|
| |
Jan
|
| | |
|
| |
|
|
| |
Note this forces initdb because of change of Aggref node in stored rules.
|
| |
|
|
|
|
|
| |
yet, but at least we can give a better error message:
regression=> select count(distinct f1) from int4_tbl;
ERROR: aggregate(DISTINCT ...) is not implemented yet
instead of 'parser: parse error at or near distinct'.
|
| | |
|
| | |
|
| | |
|
| |
|
|
|
|
|
|
|
|
|
|
|
| |
subselects can only appear on the righthand side of a binary operator.
That's still true for quantified predicates like x = ANY (SELECT ...),
but a subselect that delivers a single result can now appear anywhere
in an expression. This is implemented by changing EXPR_SUBLINK sublinks
to represent just the (SELECT ...) expression, without any 'left hand
side' or combining operator --- so they're now more like EXISTS_SUBLINK.
To handle the case of '(x, y, z) = (SELECT ...)', I added a new sublink
type MULTIEXPR_SUBLINK, which acts just like EXPR_SUBLINK used to.
But the grammar will only generate one for a multiple-left-hand-side
row expression.
|
| |
|
|
|
|
|
|
| |
Apparently, back in the dim reaches of prehistory, the parser couldn't
be trusted to label Const nodes with the correct constbyval value ...
and someone preferred to patch around this in copyObject rather than
fix the problem at the source. The problem is long gone, but the hack
lingered on. Until now.
|
| |
|
|
|
|
|
|
|
|
|
| |
mentioned in FROM but not elsewhere in the query: such tables should be
joined over anyway. Aside from being more standards-compliant, this allows
removal of some very ugly hacks for COUNT(*) processing. Also, allow
HAVING clause without aggregate functions, since SQL does. Clean up
CREATE RULE statement-list syntax the same way Bruce just fixed the
main stmtmulti production.
CAUTION: addition of a field to RangeTblEntry nodes breaks stored rules;
you will have to initdb if you have any rules.
|
| |
|
|
|
|
|
|
|
|
|
|
|
|
|
| |
expressions in CREATE TABLE. There is no longer an emasculated expression
syntax for these things; it's full a_expr for constraints, and b_expr
for defaults (unfortunately the fact that NOT NULL is a part of the
column constraint syntax causes a shift/reduce conflict if you try a_expr.
Oh well --- at least parenthesized boolean expressions work now). Also,
stored expression for a column default is not pre-coerced to the column
type; we rely on transformInsertStatement to do that when the default is
actually used. This means "f1 datetime default 'now'" behaves the way
people usually expect it to.
BTW, all the support code is now there to implement ALTER TABLE ADD
CONSTRAINT and ALTER TABLE ADD COLUMN with a default value. I didn't
actually teach ALTER TABLE to call it, but it wouldn't be much work.
|
| |
|
|
|
|
|
|
|
|
|
| |
Frankpitt, plus some improvements from yours truly. The simplifier depends
on the proiscachable field of pg_proc to tell it whether a function is
safe to pre-evaluate --- things like nextval() are not, for example.
Update pg_proc.h to contain reasonable cacheability information; as of
6.5.* hardly any functions were marked cacheable. I may have erred too
far in the other direction; see recent mail to pghackers for more info.
This update does not force an initdb, exactly, but you won't see much
benefit from the simplifier until you do one.
|
| |
|
|
| |
robust, since it's about to get used much more heavily.
|
| |
|
|
|
|
|
|
|
|
| |
and fix_opids processing to a single recursive pass over the plan tree
executed at the very tail end of planning, rather than haphazardly here
and there at different places. Now that tlist Vars do not get modified
until the very end, it's possible to get rid of the klugy var_equal and
match_varid partial-matching routines, and just use plain equal()
throughout the optimizer. This is a step towards allowing merge and
hash joins to be done on expressions instead of only Vars ...
|
| |
|
|
|
|
|
|
|
|
|
|
|
| |
sort order down into planner, instead of handling it only at the very top
level of the planner. This fixes many things. An explicit sort is now
avoided if there is a cheaper alternative (typically an indexscan) not
only for ORDER BY, but also for the internal sort of GROUP BY. It works
even when there is no other reason (such as a WHERE condition) to consider
the indexscan. It works for indexes on functions. It works for indexes
on functions, backwards. It's just so cool...
CAUTION: I have changed the representation of SortClause nodes, therefore
THIS UPDATE BREAKS STORED RULES. You will need to initdb.
|
| |
|
|
|
|
|
|
|
| |
store all ordering information in pathkeys lists (which are now lists of
lists of PathKeyItem nodes, not just lists of lists of vars). This was
a big win --- the code is smaller and IMHO more understandable than it
was, even though it handles more cases. I believe the node changes will
not force an initdb for anyone; planner nodes don't show up in stored
rules.
|
| |
|
|
|
| |
l2 contained more than one entry, there would be duplicates in the output
list. Miscellaneous code beautification in other routines, too.
|
| |
|
|
|
|
|
|
|
|
|
|
|
|
| |
> >
> > was implemented by Jan Wieck.
> > His work is for ascending order cases.
> >
> > Here is a patch to prevent sorting also in descending
> > order cases.
> > Because I had already changed _bt_first() to position
> > backward correctly before v6.5,this patch would work.
> >
Hiroshi Inoue
Inoue@tpf.co.jp
|
| |
|
|
| |
detected this omission before. Miscellaneous other cleanups.
|
| |
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
| |
optimizer rather than parser. This has many advantages, such as not
getting fooled by chance uses of operator names ~ and ~~ (the operators
are identified by OID now), and not creating useless comparison operations
in contexts where the comparisons will not actually be used as indexquals.
The new code also recognizes exact-match LIKE and regex patterns, and
produces an = indexqual instead of >= and <=.
This change does NOT fix the problem with non-ASCII locales: the code
still doesn't know how to generate an upper bound indexqual for non-ASCII
collation order. But it's no worse than before, just the same deficiency
in a different place...
Also, dike out loc_restrictinfo fields in Plan nodes. These were doing
nothing useful in the absence of 'expensive functions' optimization,
and they took a considerable amount of processing to fill in.
|
| |
|
|
|
|
|
| |
The only place it was being used was as temporary storage in indxpath.c,
and the logic was wrong: the same restrictinfo node could get chosen to
carry the info for two different joins. Right fix is to return a second
list of unjoined-relids parallel to the list of clause groups.
|
| |
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
| |
identified by Hiroshi (incorrect cost attributed to OR clauses
after multiple passes through set_rest_selec()). I think the code
was trying to allow selectivities of OR subclauses to be passed in
from outside, but noplace was actually passing any useful data, and
set_rest_selec() was passing wrong data.
Restructure representation of "indexqual" in IndexPath nodes so that
it is the same as for indxqual in completed IndexScan nodes: namely,
a toplevel list with an entry for each pass of the index scan, having
sublists that are implicitly-ANDed index qual conditions for that pass.
You don't want to know what the old representation was :-(
Improve documentation of OR-clause indexscan functions.
Remove useless 'notclause' field from RestrictInfo nodes. (This might
force an initdb for anyone who has stored rules containing RestrictInfos,
but I do not think that RestrictInfo ever appears in completed plans.)
|
| | |
|
| | |
|
| | |
|
| | |
|
| | |
|
| | |
|
| | |
|
| |
|
|
|
| |
regression=> select sum(q1) from int8_tbl group by q2 order by sum(q1);
NOTICE: equal: don't know whether nodes of type 107 are equal
|
| | |
|
