summaryrefslogtreecommitdiff
path: root/src/include/optimizer
Commit message (Collapse)AuthorAgeFilesLines
* Change the planner-to-executor API so that the planner tells the executorTom Lane2007-01-102-6/+7
| | | | | | | | | | | | | | | | which comparison operators to use for plan nodes involving tuple comparison (Agg, Group, Unique, SetOp). Formerly the executor looked up the default equality operator for the datatype, which was really pretty shaky, since it's possible that the data being fed to the node is sorted according to some nondefault operator class that could have an incompatible idea of equality. The planner knows what it has sorted by and therefore can provide the right equality operator to use. Also, this change moves a couple of catalog lookups out of the executor and into the planner, which should help startup time for pre-planned queries by some small amount. Modify the planner to remove some other cavalier assumptions about always being able to use the default operators. Also add "nulls first/last" info to the Plan node for a mergejoin --- neither the executor nor the planner can cope yet, but at least the API is in place.
* Update CVS HEAD for 2007 copyright. Back branches are typically notBruce Momjian2007-01-0523-46/+46
| | | | back-stamped for this.
* Restructure operator classes to allow improved handling of cross-data-typeTom Lane2006-12-231-1/+3
| | | | | | | | | | | | | | | | cases. Operator classes now exist within "operator families". While most families are equivalent to a single class, related classes can be grouped into one family to represent the fact that they are semantically compatible. Cross-type operators are now naturally adjunct parts of a family, without having to wedge them into a particular opclass as we had done originally. This commit restructures the catalogs and cleans up enough of the fallout so that everything still works at least as well as before, but most of the work needed to actually improve the planner's behavior will come later. Also, there are not yet CREATE/DROP/ALTER OPERATOR FAMILY commands; the only way to create a new family right now is to allow CREATE OPERATOR CLASS to make one by default. I owe some more documentation work, too. But that can all be done in smaller pieces once this infrastructure is in place.
* Fix planner to do the right thing when a degenerate outer join (one whoseTom Lane2006-12-121-2/+3
| | | | | | | joinclause doesn't use any outer-side vars) requires a "bushy" plan to be created. The normal heuristic to avoid joins with no joinclause has to be overridden in that case. Problem is new in 8.2; before that we forced the outer join order anyway. Per example from Teodor.
* pgindent run for 8.2.Bruce Momjian2006-10-045-15/+15
|
* Increase default value of effective_cache_size to 128MB, per discussion.Tom Lane2006-09-251-2/+2
|
* Improve usage of effective_cache_size parameter by assuming that all theTom Lane2006-09-192-4/+5
| | | | | | | | | | | tables in the query compete for cache space, not just the one we are currently costing an indexscan for. This seems more realistic, and it definitely will help in examples recently exhibited by Stefan Kaltenbrunner. To get the total size of all the tables involved, we must tweak the handling of 'append relations' a bit --- formerly we looked up information about the child tables on-the-fly during set_append_rel_pathlist, but it needs to be done before we start doing any cost estimation, so push it into the add_base_rels_to_query scan.
* Add INSERT/UPDATE/DELETE RETURNING, with basic docs and regression tests.Tom Lane2006-08-121-1/+4
| | | | | | | | plpgsql support to come later. Along the way, convert execMain's SELECT INTO support into a DestReceiver, in order to eliminate some ugly special cases. Jonah Harris and Tom Lane
* Add support for multi-row VALUES clauses as part of INSERT statementsJoe Conway2006-08-022-2/+6
| | | | | | (e.g. "INSERT ... VALUES (...), (...), ...") and elsewhere as allowed by the spec. (e.g. similar to a FROM clause subselect). initdb required. Joe Conway and Tom Lane.
* Change the relation_open protocol so that we obtain lock on a relationTom Lane2006-07-311-2/+2
| | | | | | | | | | | | (table or index) before trying to open its relcache entry. This fixes race conditions in which someone else commits a change to the relation's catalog entries while we are in process of doing relcache load. Problems of that ilk have been reported sporadically for years, but it was not really practical to fix until recently --- for instance, the recent addition of WAL-log support for in-place updates helped. Along the way, remove pg_am.amconcurrent: all AMs are now expected to support concurrent update.
* Convert effective_cache_size to an integer, for better integration withPeter Eisentraut2006-07-261-3/+3
| | | | upcoming units feature.
* Change LIMIT/OFFSET to use int8Bruce Momjian2006-07-261-2/+2
| | | | Dhanaraj M
* In the recent changes to make the planner account better for cacheTom Lane2006-07-222-4/+4
| | | | | | | effects in a nestloop inner indexscan, I had only dealt with plain index scans and the index portion of bitmap scans. But there will be cache benefits for the heap accesses of bitmap scans too, so fix cost_bitmap_heap_scan() to account for that.
* Fix some missing inclusions identified with new pgcheckdefines tool.Tom Lane2006-07-151-1/+2
|
* Allow include files to compile own their own.Bruce Momjian2006-07-131-3/+1
| | | | | | | Strip unused include files out unused include files, and add needed includes to C files. The next step is to remove unused include files in C files.
* Revise the planner's handling of "pseudoconstant" WHERE clauses, that isTom Lane2006-07-014-10/+11
| | | | | | | | | | | | | | | | | | | clauses containing no variables and no volatile functions. Such a clause can be used as a one-time qual in a gating Result plan node, to suppress plan execution entirely when it is false. Even when the clause is true, putting it in a gating node wins by avoiding repeated evaluation of the clause. In previous PG releases, query_planner() would do this for pseudoconstant clauses appearing at the top level of the jointree, but there was no ability to generate a gating Result deeper in the plan tree. To fix it, get rid of the special case in query_planner(), and instead process pseudoconstant clauses through the normal RestrictInfo qual distribution mechanism. When a pseudoconstant clause is found attached to a path node in create_plan(), pull it out and generate a gating Result at that point. This requires special-casing pseudoconstants in selectivity estimation and cost_qual_eval, but on the whole it's pretty clean. It probably even makes the planner a bit faster than before for the normal case of no pseudoconstants, since removing pull_constant_clauses saves one useless traversal of the qual tree. Per gripe from Phil Frost.
* Make the planner estimate costs for nestloop inner indexscans on the basisTom Lane2006-06-063-10/+11
| | | | | | | | | | | | | | | | | | | | | that the Mackert-Lohmann formula applies across all the repetitions of the nestloop, not just each scan independently. We use the M-L formula to estimate the number of pages fetched from the index as well as from the table; that isn't what it was designed for, but it seems reasonably applicable anyway. This makes large numbers of repetitions look much cheaper than before, which accords with many reports we've received of overestimation of the cost of a nestloop. Also, change the index access cost model to charge random_page_cost per index leaf page touched, while explicitly not counting anything for access to metapage or upper tree pages. This may all need tweaking after we get some field experience, but in simple tests it seems to be giving saner results than before. The main thing is to get the infrastructure in place to let cost_index() and amcostestimate functions take repeated scans into account at all. Per my recent proposal. Note: this patch changes pg_proc.h, but I did not force initdb because the changes are basically cosmetic --- the system does not look into pg_proc to decide how to call an index amcostestimate function, and there's no way to call such a function from SQL at all.
* Increase the default value of cpu_index_tuple_cost from 0.001 to 0.005.Tom Lane2006-06-051-2/+2
| | | | | | This shouldn't affect simple indexscans much, while for bitmap scans that are touching a lot of index rows, this seems to bring the estimates more in line with reality. Per recent discussion.
* Add a GUC parameter seq_page_cost, and use that everywhere we formerlyTom Lane2006-06-051-6/+9
| | | | | | | | assumed that a sequential page fetch has cost 1.0. This patch doesn't in itself change the system's behavior at all, but it opens the door to people adopting other units of measurement for EXPLAIN costs. Also, if we ever decide it's worth inventing per-tablespace access cost settings, this change provides a workable intellectual framework for that.
* Update copyright for 2006. Update scripts.Bruce Momjian2006-03-0523-46/+46
|
* Improve my initial, rather hacky implementation of joins to appendTom Lane2006-02-051-8/+1
| | | | | | | | relations: fix the executor so that we can have an Append plan on the inside of a nestloop and still pass down outer index keys to index scans within the Append, then generate such plans as if they were regular inner indexscans. This avoids the need to evaluate the outer relation multiple times.
* Fix constraint exclusion to work in inherited UPDATE/DELETE queriesTom Lane2006-02-041-2/+3
| | | | | | | | | ... in fact, it will be applied now in any query whatsoever. I'm still a bit concerned about the cycles that might be expended in failed proof attempts, but given that CE is turned off by default, it's the user's choice whether to expend those cycles or not. (Possibly we should change the simple bool constraint_exclusion parameter to something more fine-grained?)
* Teach planner to convert simple UNION ALL subqueries into append relations,Tom Lane2006-02-033-9/+13
| | | | | | | | | thereby sharing code with the inheritance case. This puts the UNION-ALL-view approach to partitioned tables on par with inheritance, so far as constraint exclusion is concerned: it works either way. (Still need to update the docs to say so.) The definition of "simple UNION ALL" is a little simpler than I would like --- basically the union arms can only be SELECT * FROM foo --- but it's good enough for partitioned-table cases.
* Update random() usage so ranges are inclusive/exclusive as required.Bruce Momjian2006-02-031-2/+2
|
* Restructure planner's handling of inheritance. Rather than processingTom Lane2006-01-312-8/+16
| | | | | | | | | | | | | inheritance trees on-the-fly, which pretty well constrained us to considering only one way of planning inheritance, expand inheritance sets during the planner prep phase, and build a side data structure that can be consulted later to find which RTEs are members of which inheritance sets. As proof of concept, use the data structure to plan joins against inheritance sets more efficiently: we can now use indexes on the set members in inner-indexscan joins. (The generated plans could be improved further, but it'll take some executor changes.) This data structure will also support handling UNION ALL subqueries in the same way as inheritance sets, but that aspect of it isn't finished yet.
* Fix code that checks to see if an index can be considered to match the query'sTom Lane2006-01-291-2/+2
| | | | | | | | | requested sort order. It was assuming that build_index_pathkeys always generates a pathkey per index column, which was not true if implied equality deduction had determined that two index columns were effectively equated to each other. Simplest fix seems to be to install an option that causes build_index_pathkeys to support this behavior as well as the original one. Per report from Brian Hirt.
* Allow row comparisons to be used as indexscan qualifications.Tom Lane2006-01-251-2/+4
| | | | This completes the project to upgrade our handling of row comparisons.
* Teach planner how to rearrange join order for some classes of OUTER JOIN.Tom Lane2005-12-204-15/+11
| | | | | | Per my recent proposal. I ended up basing the implementation on the existing mechanism for enforcing valid join orders of IN joins --- the rules for valid outer-join orders are somewhat similar.
* Teach tid-scan code to make use of "ctid = ANY (array)" clauses, so thatTom Lane2005-11-262-4/+4
| | | | | | | "ctid IN (list)" will still work after we convert IN to ScalarArrayOpExpr. Make some minor efficiency improvements while at it, such as ensuring that multiple TIDs are fetched in physical heap order. And fix EXPLAIN so that it shows what's really going on for a TID scan.
* Teach planner and executor to handle ScalarArrayOpExpr as an indexableTom Lane2005-11-251-1/+10
| | | | | | | | | | | qualification when the underlying operator is indexable and useOr is true. That is, indexkey op ANY (ARRAY[...]) is effectively translated into an OR combination of one indexscan for each array element. This only works for bitmap index scans, of course, since regular indexscans no longer support OR'ing of scans. There are still some loose ends to clean up before changing 'x IN (list)' to translate as a ScalarArrayOpExpr; for instance predtest.c ought to be taught about it. But this gets the basic functionality in place.
* Restore the former RestrictInfo field valid_everywhere (but invert the flagTom Lane2005-11-141-1/+2
| | | | | | | | | | sense and rename to "outerjoin_delayed" to more clearly reflect what it means). I had decided that it was redundant in 8.1, but the folly of this is exposed by a bug report from Sebastian Böck. The place where it's needed is to prevent orindxpath.c from cherry-picking arms of an outer-join OR clause to form a relation restriction that isn't actually legal to push down to the relation scan level. There may be some legal cases that this forbids optimizing, but we'd need much closer analysis to determine it.
* Standard pgindent run for 8.1.Bruce Momjian2005-10-1511-49/+48
|
* Repair planning bug introduced in 7.4: outer-join ON clauses that referencedTom Lane2005-09-281-2/+3
| | | | | | only the inner-side relation would be considered as potential equijoin clauses, which is wrong because the condition doesn't necessarily hold above the point of the outer join. Per test case from Kevin Grittner (bug#1916).
* Change the division of labor between grouping_planner and query_plannerTom Lane2005-08-272-6/+4
| | | | | | | | | | | | | | so that the latter estimates the number of groups that grouping will produce. This is needed because it is primarily query_planner that makes the decision between fast-start and fast-finish plans, and in the original coding it was unable to make more than a crude rule-of-thumb choice when the query involved grouping. This revision helps us make saner choices for queries like SELECT ... GROUP BY ... LIMIT, as in a recent example from Mark Kirkwood. Also move the responsibility for canonicalizing sort_pathkeys and group_pathkeys into query_planner; this information has to be available anyway to support the first change, and doing it this way lets us get rid of compare_noncanonical_pathkeys entirely.
* enable_constraint_exclusion => constraint_exclusionBruce Momjian2005-08-221-2/+2
| | | | Also improve wording.
* Fix up LIMIT/OFFSET planning so that we cope with non-constant LIMITTom Lane2005-08-181-2/+3
| | | | | | | | | | or OFFSET clauses by using estimate_expression_value(). The main advantage of this is that if the expression is a Param and we have a value for the Param, we'll use that value rather than defaulting. Also, fix some thinkos in the logic for combining LIMIT/OFFSET with an externally supplied tuple fraction (this covers cases like EXISTS(...LIMIT...)). And make sure the results of all this are shown by EXPLAIN. Per a gripe from Merlin Moncure.
* Fix a bunch of bad interactions between partial indexes and the newTom Lane2005-07-282-4/+6
| | | | | | | | planning logic for bitmap indexscans. Partial indexes create corner cases in which a scan might be done with no explicit index qual conditions, and the code wasn't handling those cases nicely. Also be a little tenser about eliminating redundant clauses in the generated plan. Per report from Dmitry Karasik.
* Simple constraint exclusion. For now, only child tables of inheritanceTom Lane2005-07-233-3/+8
| | | | | scans are candidates for exclusion; this should be fixed eventually. Simon Riggs, with some help from Tom Lane.
* Teach planner about some cases where a restriction clause can beTom Lane2005-07-021-4/+2
| | | | | | | | | | | | | | | propagated inside an outer join. In particular, given LEFT JOIN ON (A = B) WHERE A = constant, we cannot conclude that B = constant at the top level (B might be null instead), but we can nonetheless put a restriction B = constant into the quals for B's relation, since no inner-side rows not meeting that condition can contribute to the final result. Similarly, given FULL JOIN USING (J) WHERE J = constant, we can't directly conclude that either input J variable = constant, but it's OK to push such quals into each input rel. Per recent gripe from Kim Bisgaard. Along the way, remove 'valid_everywhere' flag from RestrictInfo, as on closer analysis it was not being used for anything, and was defined backwards anyway.
* Separate predicate-testing code out of indxpath.c, making it a moduleTom Lane2005-06-102-2/+24
| | | | | in its own right. As proposed by Simon Riggs, but with some editorializing of my own.
* If a LIMIT is applied to a UNION ALL query, plan each UNION arm asTom Lane2005-06-101-2/+3
| | | | | | | if the limit were directly applied to it. This does not actually add a LIMIT plan node to the generated subqueries --- that would be useless overhead --- but it does cause the planner to prefer fast- start plans when the limit is small. After an idea from Phil Endecott.
* Simplify the planner's join clause management by storing join clausesTom Lane2005-06-092-6/+7
| | | | | | | | | | of a relation in a flat 'joininfo' list. The former arrangement grouped the join clauses according to the set of unjoined relids used in each; however, profiling on test cases involving lots of joins proves that that data structure is a net loss. It takes more time to group the join clauses together than is saved by avoiding duplicate tests later. It doesn't help any that there are usually not more than one or two clauses per group ...
* Remove planner's private fields from Query struct, and put them intoTom Lane2005-06-0512-112/+115
| | | | | | | | a new PlannerInfo struct, which is passed around instead of the bare Query in all the planning code. This commit is essentially just a code-beautification exercise, but it does open the door to making larger changes to the planner data structures without having to muck with the widely-known Query struct.
* Teach the planner to remove SubqueryScan nodes from the plan if theyTom Lane2005-05-222-5/+10
| | | | | | | | | aren't doing anything useful (ie, neither selection nor projection). Also, extend to SubqueryScan the hacks already in place to avoid unnecessary ExecProject calls when the result would just be the same tuple the subquery already delivered. This saves some overhead in UNION and other set operations, as well as avoiding overhead for unflatten-able subqueries. Per example from Sokolov Yura.
* Replace slightly klugy create_bitmap_restriction() function with aTom Lane2005-04-252-3/+5
| | | | | more efficient routine in restrictinfo.c (which can make use of make_restrictinfo_internal).
* Remove support for OR'd indexscans internal to a single IndexScan planTom Lane2005-04-253-10/+8
| | | | | | | | node, as this behavior is now better done as a bitmap OR indexscan. This allows considerable simplification in nodeIndexscan.c itself as well as several planner modules concerned with indexscan plan generation. Also we can improve the sharing of code between regular and bitmap indexscans, since they are now working with nigh-identical Plan nodes.
* First cut at planner support for bitmap index scans. Lots to do yet,Tom Lane2005-04-224-10/+15
| | | | | | | | but the code is basically working. Along the way, rewrite the entire approach to processing OR index conditions, and make it work in join cases for the first time ever. orindxpath.c is now basically obsolete, but I left it in for the time being to allow easy comparison testing against the old implementation.
* Rethink original decision to use AND/OR Expr nodes to represent bitmapTom Lane2005-04-212-5/+14
| | | | | | | logic operations during planning. Seems cleaner to create two new Path node types, instead --- this avoids duplication of cost-estimation code. Also, create an enable_bitmapscan GUC parameter to control use of bitmap plans.
* Install some slightly realistic cost estimation for bitmap index scans.Tom Lane2005-04-211-2/+2
|
* Create executor and planner-backend support for decoupled heap and indexTom Lane2005-04-192-2/+7
| | | | | | | | | scans, using in-memory tuple ID bitmaps as the intermediary. The planner frontend (path creation and cost estimation) is not there yet, so none of this code can be executed. I have tested it using some hacked planner code that is far too ugly to see the light of day, however. Committing now so that the bulk of the infrastructure changes go in before the tree drifts under me.
View Lorry Controller Status