aboutsummaryrefslogtreecommitdiff
path: root/src/backend/utils/adt/selfuncs.c
Commit message (Collapse)AuthorAge
* Fix estimate_num_groups() to assume that GROUP BY expressions yielding booleanTom Lane2008-07-07
| | | | | | | | | results always contribute two groups, regardless of the expression contents. This is very substantially more accurate than the regular heuristic for certain boolean tests like "col IS NULL". Per gripe from Sam Mason. Back-patch to all supported releases, since the behavior of estimate_num_groups() hasn't changed all that much since 7.4.
* Second pass at improving LIKE/regex estimation in non-C locales. It turnsTom Lane2007-11-09
| | | | | | | | | | out that it's actually quite likely that a string that is an extension of the given prefix will sort as larger than the "greater" string our previous code created. To provide some defense against that, do the comparisons against a modified string instead of just the bare prefix. We tack on "Z", "z", "y", or "9", whichever is seen as largest in the current locale. Testing suggests that this is sufficient at least for cases involving ASCII data.
* Improve the performance of LIKE/regex estimation in non-C locales, by makingTom Lane2007-11-07
| | | | | | | | | | | | | | | | make_greater_string() try harder to generate a string that's actually greater than its input string. Before we just assumed that making a string that was memcmp-greater was enough, but it is easy to generate examples where this is not so when the locale is not C. Instead, loop until the relevant comparison function agrees that the generated string is greater than the input. Unfortunately this is probably not enough to guarantee that the generated string is greater than all extensions of the input, so we cannot relax the restriction to C locale for the LIKE/regex index optimization. But it should at least improve the odds of getting a useful selectivity estimate in prefix_selectivity(). Per example from Guillaume Smet. Backpatch to 8.1, mainly because that's what the complainant is using...
* Fix patternsel() and callers to do the right thing for NOT LIKE and the otherTom Lane2007-11-07
| | | | | | | | | | | | | | negated-match operators. patternsel had been using the supplied operator as though it were a positive-match operator, and thus obtaining a wrong result, which was even more wrong after the caller subtracted it from 1. Seems cleanest to give patternsel an explicit "negate" argument so that it knows what's going on. Also install the same factorization scheme for pattern join selectivity estimators; even though they are just stubs at the moment, this may keep someone from making the same type of mistake when they get filled out. Per report from Greg Mullane. Backpatch to 8.2 --- previous releases do not show the problem because patternsel() doesn't actually use the operator directly.
* Apply a band-aid fix for the problem that 8.2 and up completely misestimateTom Lane2007-08-31
| | | | | | | | | | | | | | | | | | the number of rows likely to be produced by a query such as SELECT * FROM t1 LEFT JOIN t2 USING (key) WHERE t2.key IS NULL; What this is doing is selecting for t1 rows with no match in t2, and thus it may produce a significant number of rows even if the t2.key table column contains no nulls at all. 8.2 thinks the table column's null fraction is relevant and thus may estimate no rows out, which results in terrible plans if there are more joins above this one. A proper fix for this will involve passing much more information about the context of a clause to the selectivity estimator functions than we ever have. There's no time left to write such a patch for 8.3, and it wouldn't be back-patchable into 8.2 anyway. Instead, put in an ad-hoc test to defeat the normal table-stats-based estimation when an IS NULL test is evaluated at an outer join, and just use a constant estimate instead --- I went with 0.5 for lack of a better idea. This won't catch every case but it will catch the typical ways of writing such queries, and it seems unlikely to make things worse for other queries.
* Check return code from strxfrm on Windows since it has aMagnus Hagander2007-05-05
| | | | | non-standard way of indicating errors, so we don't try to allocate INT_MAX bytes to store a result in.
* Dept of second thoughts: the IQ of estimate_array_length() needs to beTom Lane2007-01-28
| | | | | kept on par with that of scalararraysel(), else estimates that should track might not. Hence teach it about binary-compatible cases, too.
* Fix scalararraysel() to cope with binary-compatible cases, such as text[]Tom Lane2007-01-28
| | | | | | versus varchar[]. This oversight probably explains Ryan Holmes' recent complaint --- he was getting a generic selectivity estimate instead of anything intelligent.
* Fix regex_fixed_prefix() to cope reasonably well with regex patterns of theTom Lane2007-01-03
| | | | | | | | | | form '^(foo)$'. Before, these could never be optimized into indexscans. The recent changes to make psql and pg_dump generate such patterns (for \d commands and -t and related switches, respectively) therefore represented a big performance hit for people with large pg_class catalogs, as seen in recent gripe from Erik Jones. While at it, be more paranoid about case-sensitivity checking in multibyte encodings, and fix some other corner cases in which a regex might be interpreted too liberally.
* Fix some planner bugs exposed by reports from Arjen van der Meijden. TheseTom Lane2006-12-15
| | | | | | | | | | | | | | | | | | | | | | | | are all in new-in-8.2 logic associated with indexability of ScalarArrayOpExpr (IN-clauses) or amortization of indexscan costs across repeated indexscans on the inside of a nestloop. In particular: Fix some logic errors in the estimation for multiple scans induced by a ScalarArrayOpExpr indexqual. Include a small cost component in bitmap index scans to reflect the costs of manipulating the bitmap itself; this is mainly to prevent a bitmap scan from appearing to have the same cost as a plain indexscan for fetching a single tuple. Also add a per-index-scan-startup CPU cost component; while prior releases were clearly too pessimistic about the cost of repeated indexscans, the original 8.2 coding allowed the cost of an indexscan to effectively go to zero if repeated often enough, which is overly optimistic. Pay some attention to index correlation when estimating costs for a nestloop inner indexscan: this is significant when the plan fetches multiple heap tuples per iteration, since high correlation means those tuples are probably on the same or adjacent heap pages.
* pgindent run for 8.2.Bruce Momjian2006-10-04
|
* Change patternsel (LIKE/regex selectivity estimation) so that if thereTom Lane2006-09-20
| | | | | | | | | is a large enough histogram, it will use the number of matches in the histogram to derive a selectivity estimate, rather than the admittedly pretty bogus heuristics involving examining the pattern contents. I set 'large enough' at 100, but perhaps we should change that later. Also apply the same technique in contrib/ltree's <@ and @> estimator. Per discussion with Stefan Kaltenbrunner and Matteo Beccati.
* Improve usage of effective_cache_size parameter by assuming that all theTom Lane2006-09-19
| | | | | | | | | | | 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.
* Work around bug in strxfmt() but in MS VS2005.Bruce Momjian2006-07-26
| | | | William ZHANG
* Add a fudge factor to genericcostestimate() to prevent the planner fromTom Lane2006-07-24
| | | | | | | thinking that indexes of different sizes are equally attractive. Per gripe from Jim Nasby. (I remain unconvinced that there's such a problem in existing releases, but CVS HEAD definitely has got a problem because of its new count-only-leaf-pages approach to indexscan costing.)
* Remove 576 references of include files that were not needed.Bruce Momjian2006-07-14
|
* Fix oversight in planning for multiple indexscans driven byTom Lane2006-07-01
| | | | | | | | ScalarArrayOpExpr index quals: we were estimating the right total number of rows returned, but treating the index-access part of the cost as if a single scan were fetching that many consecutive index tuples. Actually we should treat it as a multiple indexscan, and if there are enough of 'em the Mackert-Lohman discount should kick in.
* Make the planner estimate costs for nestloop inner indexscans on the basisTom Lane2006-06-06
| | | | | | | | | | | | | | | | | | | | | 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.
* Add a GUC parameter seq_page_cost, and use that everywhere we formerlyTom Lane2006-06-05
| | | | | | | | 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.
* GIN: Generalized Inverted iNdex.Teodor Sigaev2006-05-02
| | | | text[], int4[], Tsearch2 support for GIN.
* Avoid assuming that statistics for a parent relation reflect the properties ofTom Lane2006-05-02
| | | | | | | | | | | | | the union of its child relations as well. This might have been a good idea when it was originally coded, but it's a fatally bad idea when inheritance is being used for partitioning. It's better to have no stats at all than completely misleading stats. Per report from Mark Liberman. The bug arguably exists all the way back, but I've only patched HEAD and 8.1 because we weren't particularly trying to support partitioning before 8.1. Eventually we ought to look at deriving union statistics instead of just punting, but for now the drop kick looks good.
* Generalize mcv_selectivity() to support both VAR OP CONST and CONST OP VARTom Lane2006-04-27
| | | | | | cases. This was not needed in the existing uses within selfuncs.c, but if we're gonna export it for general use, the extra generality seems helpful. Motivated by looking at ltree example.
* If we're going to expose VariableStatData for contrib modules to use,Tom Lane2006-04-27
| | | | then we should export a reasonable set of the supporting routines too.
* Move ltree parentsel() selectivity function into /contrib/ltree.Bruce Momjian2006-04-26
|
* Enhanced containment selectivity function for /contrib/ltreeBruce Momjian2006-04-26
| | | | Matteo Beccati
* Eliminate some no-longer-needed workarounds for palloc's old behaviorTom Lane2006-04-20
| | | | | | | | of rejecting palloc(0). Also, tweak like_selectivity() to avoid assuming the presented pattern is nonempty; although that assumption is valid, it doesn't really help much, and the new coding is more correct anyway since it properly handles redundant wildcards. In combination these changes should eliminate a Coverity warning noted by Martijn.
* Update copyright for 2006. Update scripts.Bruce Momjian2006-03-05
|
* Allow row comparisons to be used as indexscan qualifications.Tom Lane2006-01-25
| | | | This completes the project to upgrade our handling of row comparisons.
* Add selectivity-calculation code for RowCompareExpr nodes. Simplistic,Tom Lane2006-01-14
| | | | but a lot better than nothing at all ...
* Improve patternsel() by applying the operator itself to each valueTom Lane2006-01-10
| | | | | | | | | listed in the column's most-common-values statistics entry. This gives us an exact selectivity result for the portion of the column population represented by the MCV list, which can be a big leg up in accuracy if that's a large fraction of the population. The heuristics involving pattern contents and prefix are applied only to the part of the population not included in the MCV list.
* Teach planner and executor to handle ScalarArrayOpExpr as an indexableTom Lane2005-11-25
| | | | | | | | | | | 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.
* Re-run pgindent, fixing a problem where comment lines after a blankBruce Momjian2005-11-22
| | | | | | | | | comment line where output as too long, and update typedefs for /lib directory. Also fix case where identifiers were used as variable names in the backend, but as typedefs in ecpg (favor the backend for indenting). Backpatch to 8.1.X.
* R-tree is dead ... long live GiST.Tom Lane2005-11-07
|
* Standard pgindent run for 8.1.Bruce Momjian2005-10-15
|
* Document that get_attstatsslot/free_attstatsslot only need to be passedTom Lane2005-10-11
| | | | | | | | valid type information if they are asked to fetch the values part of a pg_statistic slot; these arguments are unneeded if fetching only the numbers part. Use this to save a catcache lookup in btcostestimate, which is looking like a bit of a hotspot in recent profiling. Not a big savings, but since it's essentially free, might as well do it.
* Clean up possibly-uninitialized-variable warnings reported by gcc 4.x.Tom Lane2005-09-24
|
* Suppress signed-vs-unsigned-char warnings.Tom Lane2005-09-24
|
* Remove unnecessary parentheses in assignments.Bruce Momjian2005-07-21
| | | | | Add spaces where needed. Reference time interval variables as tinterval.
* Add time/date macros for code clarity:Bruce Momjian2005-07-21
| | | | | | | #define DAYS_PER_YEAR 365.25 #define MONTHS_PER_YEAR 12 #define DAYS_PER_MONTH 30 #define HOURS_PER_DAY 24
* Add 'day' field to INTERVAL so 1 day interval can be distinguished fromBruce Momjian2005-07-20
| | | | | | | | | | | | | | | | 24 hours. This is very helpful for daylight savings time: select '2005-05-03 00:00:00 EST'::timestamp with time zone + '24 hours'; ?column? ---------------------- 2005-05-04 01:00:00-04 select '2005-05-03 00:00:00 EST'::timestamp with time zone + '1 day'; ?column? ---------------------- 2005-05-04 01:00:00-04 Michael Glaesemann
* Improve comments for AdjustIntervalForTypmod.Bruce Momjian2005-07-12
| | | | Blank line adjustments.
* Clean up the rather historically encumbered interface to now() andTom Lane2005-06-29
| | | | | | | | current time: provide a GetCurrentTimestamp() function that returns current time in the form of a TimestampTz, instead of separate time_t and microseconds fields. This is what all the callers really want anyway, and it eliminates low-level dependencies on AbsoluteTime, which is a deprecated datatype that will have to disappear eventually.
* Change the planner to allow indexscan qualification clauses to useTom Lane2005-06-13
| | | | | | | | | nonconsecutive columns of a multicolumn index, as per discussion around mid-May (pghackers thread "Best way to scan on-disk bitmaps"). This turns out to require only minimal changes in btree, and so far as I can see none at all in GiST. btcostestimate did need some work, but its original assumption that index selectivity == heap selectivity was quite bogus even before this.
* Separate predicate-testing code out of indxpath.c, making it a moduleTom Lane2005-06-10
| | | | | in its own right. As proposed by Simon Riggs, but with some editorializing of my own.
* Remove planner's private fields from Query struct, and put them intoTom Lane2005-06-05
| | | | | | | | 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.
* patternsel() was improperly stripping RelabelType from the derivedTom Lane2005-06-01
| | | | | | expressions it constructed, causing scalarineqsel to become confused if the underlying variable was of a domain type. Per report from Kevin Grittner.
* Remove support for OR'd indexscans internal to a single IndexScan planTom Lane2005-04-25
| | | | | | | | 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.
* Completion of project to use fixed OIDs for all system catalogs andTom Lane2005-04-14
| | | | | | | indexes. Replace all heap_openr and index_openr calls by heap_open and index_open. Remove runtime lookups of catalog OID numbers in various places. Remove relcache's support for looking up system catalogs by name. Bulky but mostly very boring patch ...
* Second try at making examine_variable and friends behave sanely inTom Lane2005-04-01
| | | | | | | cases with binary-compatible relabeling. My first try was implicitly assuming that all operators scalarineqsel is used for have binary- compatible datatypes on both sides ... which is very wrong of course. Per report from Michael Fuhr.
* First steps towards index scans with heap access decoupled from indexTom Lane2005-03-27
| | | | | | | | | | access: define new index access method functions 'amgetmulti' that can fetch multiple TIDs per call. (The functions exist but are totally untested as yet.) Since I was modifying pg_am anyway, remove the no-longer-needed 'rel' parameter from amcostestimate functions, and also remove the vestigial amowner column that was creating useless work for Alvaro's shared-object-dependencies project. Initdb forced due to changes in pg_am.
generated by cgit v1.2.3 (git 2.25.1) at 2025-08-15 00:42:38 +0000