diff options
| -rw-r--r-- | doc/src/sgml/catalogs.sgml | 8 | ||||
| -rw-r--r-- | src/backend/catalog/system_views.sql | 50 | ||||
| -rw-r--r-- | src/backend/tsearch/Makefile | 4 | ||||
| -rw-r--r-- | src/backend/tsearch/ts_typanalyze.c | 403 | ||||
| -rw-r--r-- | src/include/catalog/catversion.h | 4 | ||||
| -rw-r--r-- | src/include/catalog/pg_operator.h | 3 | ||||
| -rw-r--r-- | src/include/catalog/pg_proc.h | 5 | ||||
| -rw-r--r-- | src/include/catalog/pg_statistic.h | 17 | ||||
| -rw-r--r-- | src/include/catalog/pg_type.h | 4 | ||||
| -rw-r--r-- | src/include/tsearch/ts_type.h | 4 | ||||
| -rw-r--r-- | src/test/regress/expected/rules.out | 6 |
11 files changed, 467 insertions, 41 deletions
diff --git a/doc/src/sgml/catalogs.sgml b/doc/src/sgml/catalogs.sgml index de8e3456e15..76198b0f832 100644 --- a/doc/src/sgml/catalogs.sgml +++ b/doc/src/sgml/catalogs.sgml @@ -1,4 +1,4 @@ -<!-- $PostgreSQL: pgsql/doc/src/sgml/catalogs.sgml,v 2.167 2008/07/11 07:02:43 petere Exp $ --> +<!-- $PostgreSQL: pgsql/doc/src/sgml/catalogs.sgml,v 2.168 2008/07/14 00:51:45 tgl Exp $ --> <!-- Documentation of the system catalogs, directed toward PostgreSQL developers --> @@ -6516,6 +6516,8 @@ <entry> A list of the most common values in the column. (NULL if no values seem to be more common than any others.) + For some datatypes such as <type>tsvector</>, this is a list of + the most common element values rather than values of the type itself. </entry> </row> @@ -6524,10 +6526,10 @@ <entry><type>real[]</type></entry> <entry></entry> <entry> - A list of the frequencies of the most common values, + A list of the frequencies of the most common values or elements, i.e., number of occurrences of each divided by total number of rows. (NULL when <structfield>most_common_vals</structfield> is.) - </entry> + </entry> </row> <row> diff --git a/src/backend/catalog/system_views.sql b/src/backend/catalog/system_views.sql index d9ac50b429c..10082a6f4fc 100644 --- a/src/backend/catalog/system_views.sql +++ b/src/backend/catalog/system_views.sql @@ -3,7 +3,7 @@ * * Copyright (c) 1996-2008, PostgreSQL Global Development Group * - * $PostgreSQL: pgsql/src/backend/catalog/system_views.sql,v 1.52 2008/05/15 00:17:39 tgl Exp $ + * $PostgreSQL: pgsql/src/backend/catalog/system_views.sql,v 1.53 2008/07/14 00:51:45 tgl Exp $ */ CREATE VIEW pg_roles AS @@ -110,30 +110,30 @@ CREATE VIEW pg_stats AS stanullfrac AS null_frac, stawidth AS avg_width, stadistinct AS n_distinct, - CASE 1 - WHEN stakind1 THEN stavalues1 - WHEN stakind2 THEN stavalues2 - WHEN stakind3 THEN stavalues3 - WHEN stakind4 THEN stavalues4 - END AS most_common_vals, - CASE 1 - WHEN stakind1 THEN stanumbers1 - WHEN stakind2 THEN stanumbers2 - WHEN stakind3 THEN stanumbers3 - WHEN stakind4 THEN stanumbers4 - END AS most_common_freqs, - CASE 2 - WHEN stakind1 THEN stavalues1 - WHEN stakind2 THEN stavalues2 - WHEN stakind3 THEN stavalues3 - WHEN stakind4 THEN stavalues4 - END AS histogram_bounds, - CASE 3 - WHEN stakind1 THEN stanumbers1[1] - WHEN stakind2 THEN stanumbers2[1] - WHEN stakind3 THEN stanumbers3[1] - WHEN stakind4 THEN stanumbers4[1] - END AS correlation + CASE + WHEN stakind1 IN (1, 4) THEN stavalues1 + WHEN stakind2 IN (1, 4) THEN stavalues2 + WHEN stakind3 IN (1, 4) THEN stavalues3 + WHEN stakind4 IN (1, 4) THEN stavalues4 + END AS most_common_vals, + CASE + WHEN stakind1 IN (1, 4) THEN stanumbers1 + WHEN stakind2 IN (1, 4) THEN stanumbers2 + WHEN stakind3 IN (1, 4) THEN stanumbers3 + WHEN stakind4 IN (1, 4) THEN stanumbers4 + END AS most_common_freqs, + CASE + WHEN stakind1 = 2 THEN stavalues1 + WHEN stakind2 = 2 THEN stavalues2 + WHEN stakind3 = 2 THEN stavalues3 + WHEN stakind4 = 2 THEN stavalues4 + END AS histogram_bounds, + CASE + WHEN stakind1 = 3 THEN stanumbers1[1] + WHEN stakind2 = 3 THEN stanumbers2[1] + WHEN stakind3 = 3 THEN stanumbers3[1] + WHEN stakind4 = 3 THEN stanumbers4[1] + END AS correlation FROM pg_statistic s JOIN pg_class c ON (c.oid = s.starelid) JOIN pg_attribute a ON (c.oid = attrelid AND attnum = s.staattnum) LEFT JOIN pg_namespace n ON (n.oid = c.relnamespace) diff --git a/src/backend/tsearch/Makefile b/src/backend/tsearch/Makefile index 9ee7a7ea533..a2502172c8e 100644 --- a/src/backend/tsearch/Makefile +++ b/src/backend/tsearch/Makefile @@ -4,7 +4,7 @@ # # Copyright (c) 2006-2008, PostgreSQL Global Development Group # -# $PostgreSQL: pgsql/src/backend/tsearch/Makefile,v 1.6 2008/02/19 10:30:08 petere Exp $ +# $PostgreSQL: pgsql/src/backend/tsearch/Makefile,v 1.7 2008/07/14 00:51:45 tgl Exp $ # #------------------------------------------------------------------------- subdir = src/backend/tsearch @@ -19,7 +19,7 @@ DICTFILES=synonym_sample.syn thesaurus_sample.ths hunspell_sample.affix \ OBJS = ts_locale.o ts_parse.o wparser.o wparser_def.o dict.o \ dict_simple.o dict_synonym.o dict_thesaurus.o \ dict_ispell.o regis.o spell.o \ - to_tsany.o ts_utils.o + to_tsany.o ts_typanalyze.o ts_utils.o include $(top_srcdir)/src/backend/common.mk diff --git a/src/backend/tsearch/ts_typanalyze.c b/src/backend/tsearch/ts_typanalyze.c new file mode 100644 index 00000000000..d132cf75e63 --- /dev/null +++ b/src/backend/tsearch/ts_typanalyze.c @@ -0,0 +1,403 @@ +/*------------------------------------------------------------------------- + * + * ts_typanalyze.c + * functions for gathering statistics from tsvector columns + * + * Portions Copyright (c) 1996-2008, PostgreSQL Global Development Group + * + * + * IDENTIFICATION + * $PostgreSQL: pgsql/src/backend/tsearch/ts_typanalyze.c,v 1.1 2008/07/14 00:51:45 tgl Exp $ + * + *------------------------------------------------------------------------- + */ +#include "postgres.h" + +#include "access/hash.h" +#include "catalog/pg_operator.h" +#include "commands/vacuum.h" +#include "tsearch/ts_type.h" +#include "utils/builtins.h" +#include "utils/hsearch.h" + + +/* A hash key for lexemes */ +typedef struct +{ + char *lexeme; /* lexeme (not NULL terminated!) */ + int length; /* its length in bytes */ +} LexemeHashKey; + +/* A hash table entry for the Lossy Counting algorithm */ +typedef struct +{ + LexemeHashKey key; /* This is 'e' from the LC algorithm. */ + int frequency; /* This is 'f'. */ + int delta; /* And this is 'delta'. */ +} TrackItem; + +static void compute_tsvector_stats(VacAttrStats *stats, + AnalyzeAttrFetchFunc fetchfunc, + int samplerows, + double totalrows); +static void prune_lexemes_hashtable(HTAB *lexemes_tab, int b_current); +static uint32 lexeme_hash(const void *key, Size keysize); +static int lexeme_match(const void *key1, const void *key2, Size keysize); +static int trackitem_compare_desc(const void *e1, const void *e2); + + +/* + * ts_typanalyze -- a custom typanalyze function for tsvector columns + */ +Datum +ts_typanalyze(PG_FUNCTION_ARGS) +{ + VacAttrStats *stats = (VacAttrStats *) PG_GETARG_POINTER(0); + Form_pg_attribute attr = stats->attr; + + /* If the attstattarget column is negative, use the default value */ + /* NB: it is okay to scribble on stats->attr since it's a copy */ + if (attr->attstattarget < 0) + attr->attstattarget = default_statistics_target; + + stats->compute_stats = compute_tsvector_stats; + /* see comment about the choice of minrows from analyze.c */ + stats->minrows = 300 * attr->attstattarget; + + PG_RETURN_BOOL(true); +} + +/* + * compute_tsvector_stats() -- compute statistics for a tsvector column + * + * This functions computes statistics that are useful for determining @@ + * operations' selectivity, along with the fraction of non-null rows and + * average width. + * + * Instead of finding the most common values, as we do for most datatypes, + * we're looking for the most common lexemes. This is more useful, because + * there most probably won't be any two rows with the same tsvector and thus + * the notion of a MCV is a bit bogus with this datatype. With a list of the + * most common lexemes we can do a better job at figuring out @@ selectivity. + * + * For the same reasons we assume that tsvector columns are unique when + * determining the number of distinct values. + * + * The algorithm used is Lossy Counting, as proposed in the paper "Approximate + * frequency counts over data streams" by G. S. Manku and R. Motwani, in + * Proceedings of the 28th International Conference on Very Large Data Bases, + * Hong Kong, China, August 2002, section 4.2. The paper is available at + * http://www.vldb.org/conf/2002/S10P03.pdf + * + * The Lossy Counting (aka LC) algorithm goes like this: + * Let D be a set of triples (e, f, d), where e is an element value, f is + * that element's frequency (occurrence count) and d is the maximum error in + * f. We start with D empty and process the elements in batches of size + * w. (The batch size is also known as "bucket size".) Let the current batch + * number be b_current, starting with 1. For each element e we either + * increment its f count, if it's already in D, or insert a new triple into D + * with values (e, 1, b_current - 1). After processing each batch we prune D, + * by removing from it all elements with f + d <= b_current. Finally, we + * gather elements with largest f. The LC paper proves error bounds on f + * dependent on the batch size w, and shows that the required table size + * is no more than a few times w. + * + * We use a hashtable for the D structure and a bucket width of + * statistic_target * 100, where 100 is an arbitrarily chosen constant, meant + * to approximate the number of lexemes in a single tsvector. + */ +static void +compute_tsvector_stats(VacAttrStats *stats, + AnalyzeAttrFetchFunc fetchfunc, + int samplerows, + double totalrows) +{ + int num_mcelem; + int null_cnt = 0; + double total_width = 0; + /* This is D from the LC algorithm. */ + HTAB *lexemes_tab; + HASHCTL hash_ctl; + HASH_SEQ_STATUS scan_status; + /* This is the current bucket number from the LC algorithm */ + int b_current; + /* This is 'w' from the LC algorithm */ + int bucket_width; + int vector_no, + lexeme_no; + LexemeHashKey hash_key; + TrackItem *item; + + /* We want statistic_target * 100 lexemes in the MCELEM array */ + num_mcelem = stats->attr->attstattarget * 100; + + /* + * We set bucket width equal to the target number of result lexemes. + * This is probably about right but perhaps might need to be scaled + * up or down a bit? + */ + bucket_width = num_mcelem; + + /* + * Create the hashtable. It will be in local memory, so we don't need to + * worry about initial size too much. Also we don't need to pay any + * attention to locking and memory management. + */ + MemSet(&hash_ctl, 0, sizeof(hash_ctl)); + hash_ctl.keysize = sizeof(LexemeHashKey); + hash_ctl.entrysize = sizeof(TrackItem); + hash_ctl.hash = lexeme_hash; + hash_ctl.match = lexeme_match; + hash_ctl.hcxt = CurrentMemoryContext; + lexemes_tab = hash_create("Analyzed lexemes table", + bucket_width * 4, + &hash_ctl, + HASH_ELEM | HASH_FUNCTION | HASH_COMPARE | HASH_CONTEXT); + + /* Initialize counters. */ + b_current = 1; + lexeme_no = 1; + + /* Loop over the tsvectors. */ + for (vector_no = 0; vector_no < samplerows; vector_no++) + { + Datum value; + bool isnull; + TSVector vector; + WordEntry *curentryptr; + char *lexemesptr; + int j; + + vacuum_delay_point(); + + value = fetchfunc(stats, vector_no, &isnull); + + /* + * Check for null/nonnull. + */ + if (isnull) + { + null_cnt++; + continue; + } + + /* + * Add up widths for average-width calculation. Since it's a + * tsvector, we know it's varlena. As in the regular + * compute_minimal_stats function, we use the toasted width for this + * calculation. + */ + total_width += VARSIZE_ANY(DatumGetPointer(value)); + + /* + * Now detoast the tsvector if needed. + */ + vector = DatumGetTSVector(value); + + /* + * We loop through the lexemes in the tsvector and add them to our + * tracking hashtable. Note: the hashtable entries will point into + * the (detoasted) tsvector value, therefore we cannot free that + * storage until we're done. + */ + lexemesptr = STRPTR(vector); + curentryptr = ARRPTR(vector); + for (j = 0; j < vector->size; j++) + { + bool found; + + /* Construct a hash key */ + hash_key.lexeme = lexemesptr + curentryptr->pos; + hash_key.length = curentryptr->len; + + /* Lookup current lexeme in hashtable, adding it if new */ + item = (TrackItem *) hash_search(lexemes_tab, + (const void *) &hash_key, + HASH_ENTER, &found); + + if (found) + { + /* The lexeme is already on the tracking list */ + item->frequency++; + } + else + { + /* Initialize new tracking list element */ + item->frequency = 1; + item->delta = b_current - 1; + } + + /* We prune the D structure after processing each bucket */ + if (lexeme_no % bucket_width == 0) + { + prune_lexemes_hashtable(lexemes_tab, b_current); + b_current++; + } + + /* Advance to the next WordEntry in the tsvector */ + lexeme_no++; + curentryptr++; + } + } + + /* We can only compute real stats if we found some non-null values. */ + if (null_cnt < samplerows) + { + int nonnull_cnt = samplerows - null_cnt; + int i; + TrackItem **sort_table; + int track_len; + + stats->stats_valid = true; + /* Do the simple null-frac and average width stats */ + stats->stanullfrac = (double) null_cnt / (double) samplerows; + stats->stawidth = total_width / (double) nonnull_cnt; + + /* Assume it's a unique column (see notes above) */ + stats->stadistinct = -1.0; + + /* + * Determine the top-N lexemes by simply copying pointers from the + * hashtable into an array and applying qsort() + */ + track_len = hash_get_num_entries(lexemes_tab); + + sort_table = (TrackItem **) palloc(sizeof(TrackItem *) * track_len); + + hash_seq_init(&scan_status, lexemes_tab); + i = 0; + while ((item = (TrackItem *) hash_seq_search(&scan_status)) != NULL) + { + sort_table[i++] = item; + } + Assert(i == track_len); + + qsort(sort_table, track_len, sizeof(TrackItem *), + trackitem_compare_desc); + + /* Suppress any single-occurrence items */ + while (track_len > 0) + { + if (sort_table[track_len-1]->frequency > 1) + break; + track_len--; + } + + /* Determine the number of most common lexemes to be stored */ + if (num_mcelem > track_len) + num_mcelem = track_len; + + /* Generate MCELEM slot entry */ + if (num_mcelem > 0) + { + MemoryContext old_context; + Datum *mcelem_values; + float4 *mcelem_freqs; + + /* Must copy the target values into anl_context */ + old_context = MemoryContextSwitchTo(stats->anl_context); + mcelem_values = (Datum *) palloc(num_mcelem * sizeof(Datum)); + mcelem_freqs = (float4 *) palloc(num_mcelem * sizeof(float4)); + + for (i = 0; i < num_mcelem; i++) + { + TrackItem *item = sort_table[i]; + + mcelem_values[i] = + PointerGetDatum(cstring_to_text_with_len(item->key.lexeme, + item->key.length)); + mcelem_freqs[i] = (double) item->frequency / (double) nonnull_cnt; + } + MemoryContextSwitchTo(old_context); + + stats->stakind[0] = STATISTIC_KIND_MCELEM; + stats->staop[0] = TextEqualOperator; + stats->stanumbers[0] = mcelem_freqs; + stats->numnumbers[0] = num_mcelem; + stats->stavalues[0] = mcelem_values; + stats->numvalues[0] = num_mcelem; + /* We are storing text values */ + stats->statypid[0] = TEXTOID; + stats->statyplen[0] = -1; /* typlen, -1 for varlena */ + stats->statypbyval[0] = false; + stats->statypalign[0] = 'i'; + } + } + else + { + /* We found only nulls; assume the column is entirely null */ + stats->stats_valid = true; + stats->stanullfrac = 1.0; + stats->stawidth = 0; /* "unknown" */ + stats->stadistinct = 0.0; /* "unknown" */ + } + + /* + * We don't need to bother cleaning up any of our temporary palloc's. + * The hashtable should also go away, as it used a child memory context. + */ +} + +/* + * A function to prune the D structure from the Lossy Counting algorithm. + * Consult compute_tsvector_stats() for wider explanation. + */ +static void +prune_lexemes_hashtable(HTAB *lexemes_tab, int b_current) +{ + HASH_SEQ_STATUS scan_status; + TrackItem *item; + + hash_seq_init(&scan_status, lexemes_tab); + while ((item = (TrackItem *) hash_seq_search(&scan_status)) != NULL) + { + if (item->frequency + item->delta <= b_current) + { + if (hash_search(lexemes_tab, (const void *) &item->key, + HASH_REMOVE, NULL) == NULL) + elog(ERROR, "hash table corrupted"); + } + } +} + +/* + * Hash functions for lexemes. They are strings, but not NULL terminated, + * so we need a special hash function. + */ +static uint32 +lexeme_hash(const void *key, Size keysize) +{ + const LexemeHashKey *l = (const LexemeHashKey *) key; + + return DatumGetUInt32(hash_any((const unsigned char *) l->lexeme, + l->length)); +} + +/* + * Matching function for lexemes, to be used in hashtable lookups. + */ +static int +lexeme_match(const void *key1, const void *key2, Size keysize) +{ + const LexemeHashKey *d1 = (const LexemeHashKey *) key1; + const LexemeHashKey *d2 = (const LexemeHashKey *) key2; + + /* The lexemes need to have the same length, and be memcmp-equal */ + if (d1->length == d2->length && + memcmp(d1->lexeme, d2->lexeme, d1->length) == 0) + return 0; + else + return 1; +} + +/* + * qsort() comparator for TrackItems - LC style (descending sort) + */ +static int +trackitem_compare_desc(const void *e1, const void *e2) +{ + const TrackItem * const *t1 = (const TrackItem * const *) e1; + const TrackItem * const *t2 = (const TrackItem * const *) e2; + + return (*t2)->frequency - (*t1)->frequency; +} diff --git a/src/include/catalog/catversion.h b/src/include/catalog/catversion.h index d74a5eb5826..247833801c7 100644 --- a/src/include/catalog/catversion.h +++ b/src/include/catalog/catversion.h @@ -37,7 +37,7 @@ * Portions Copyright (c) 1996-2008, PostgreSQL Global Development Group * Portions Copyright (c) 1994, Regents of the University of California * - * $PostgreSQL: pgsql/src/include/catalog/catversion.h,v 1.466 2008/07/11 21:06:29 tgl Exp $ + * $PostgreSQL: pgsql/src/include/catalog/catversion.h,v 1.467 2008/07/14 00:51:45 tgl Exp $ * *------------------------------------------------------------------------- */ @@ -53,6 +53,6 @@ */ /* yyyymmddN */ -#define CATALOG_VERSION_NO 200807111 +#define CATALOG_VERSION_NO 200807131 #endif diff --git a/src/include/catalog/pg_operator.h b/src/include/catalog/pg_operator.h index 7bb2035b1bb..e0f5b48aa4c 100644 --- a/src/include/catalog/pg_operator.h +++ b/src/include/catalog/pg_operator.h @@ -8,7 +8,7 @@ * Portions Copyright (c) 1996-2008, PostgreSQL Global Development Group * Portions Copyright (c) 1994, Regents of the University of California * - * $PostgreSQL: pgsql/src/include/catalog/pg_operator.h,v 1.160 2008/06/17 19:10:56 tgl Exp $ + * $PostgreSQL: pgsql/src/include/catalog/pg_operator.h,v 1.161 2008/07/14 00:51:45 tgl Exp $ * * NOTES * the genbki.sh script reads this file and generates .bki @@ -105,6 +105,7 @@ DATA(insert OID = 95 ( "<" PGNSP PGUID b f f 21 21 16 520 524 int2lt scalar DATA(insert OID = 96 ( "=" PGNSP PGUID b t t 23 23 16 96 518 int4eq eqsel eqjoinsel )); DATA(insert OID = 97 ( "<" PGNSP PGUID b f f 23 23 16 521 525 int4lt scalarltsel scalarltjoinsel )); DATA(insert OID = 98 ( "=" PGNSP PGUID b t t 25 25 16 98 531 texteq eqsel eqjoinsel )); +#define TextEqualOperator 98 DATA(insert OID = 349 ( "||" PGNSP PGUID b f f 2277 2283 2277 0 0 array_append - - )); DATA(insert OID = 374 ( "||" PGNSP PGUID b f f 2283 2277 2277 0 0 array_prepend - - )); diff --git a/src/include/catalog/pg_proc.h b/src/include/catalog/pg_proc.h index 09700e8f43a..310f571290c 100644 --- a/src/include/catalog/pg_proc.h +++ b/src/include/catalog/pg_proc.h @@ -7,7 +7,7 @@ * Portions Copyright (c) 1996-2008, PostgreSQL Global Development Group * Portions Copyright (c) 1994, Regents of the University of California * - * $PostgreSQL: pgsql/src/include/catalog/pg_proc.h,v 1.504 2008/07/03 20:58:46 tgl Exp $ + * $PostgreSQL: pgsql/src/include/catalog/pg_proc.h,v 1.505 2008/07/14 00:51:45 tgl Exp $ * * NOTES * The script catalog/genbki.sh reads this file and generates .bki @@ -4313,6 +4313,9 @@ DESCR("GiST tsquery support"); DATA(insert OID = 3701 ( gtsquery_consistent PGNSP PGUID 12 1 0 f f t f i 5 16 "2281 2281 23 26 2281" _null_ _null_ _null_ gtsquery_consistent - _null_ _null_ )); DESCR("GiST tsquery support"); +DATA(insert OID = 3688 ( ts_typanalyze PGNSP PGUID 12 1 0 f f t f s 1 16 "2281" _null_ _null_ _null_ ts_typanalyze - _null_ _null_ )); +DESCR("tsvector typanalyze"); + DATA(insert OID = 3689 ( ts_stat PGNSP PGUID 12 10 10000 f f t t v 1 2249 "25" "{25,25,23,23}" "{i,o,o,o}" "{query,word,ndoc,nentry}" ts_stat1 - _null_ _null_ )); DESCR("statistics of tsvector column"); DATA(insert OID = 3690 ( ts_stat PGNSP PGUID 12 10 10000 f f t t v 2 2249 "25 25" "{25,25,25,23,23}" "{i,i,o,o,o}" "{query,weights,word,ndoc,nentry}" ts_stat2 - _null_ _null_ )); diff --git a/src/include/catalog/pg_statistic.h b/src/include/catalog/pg_statistic.h index d6c8c87b1c4..0ca66edd7f8 100644 --- a/src/include/catalog/pg_statistic.h +++ b/src/include/catalog/pg_statistic.h @@ -8,7 +8,7 @@ * Portions Copyright (c) 1996-2008, PostgreSQL Global Development Group * Portions Copyright (c) 1994, Regents of the University of California * - * $PostgreSQL: pgsql/src/include/catalog/pg_statistic.h,v 1.35 2008/03/27 03:57:34 tgl Exp $ + * $PostgreSQL: pgsql/src/include/catalog/pg_statistic.h,v 1.36 2008/07/14 00:51:45 tgl Exp $ * * NOTES * the genbki.sh script reads this file and generates .bki @@ -237,4 +237,19 @@ typedef FormData_pg_statistic *Form_pg_statistic; */ #define STATISTIC_KIND_CORRELATION 3 +/* + * A "most common elements" slot is similar to a "most common values" slot, + * except that it stores the most common non-null *elements* of the column + * values. This is useful when the column datatype is an array or some other + * type with identifiable elements (for instance, tsvector). staop contains + * the equality operator appropriate to the element type. stavalues contains + * the most common element values, and stanumbers their frequencies, with the + * same rules as for MCV slots. + * + * Note: in current usage for tsvector columns, the stavalues elements are of + * type text, even though their representation within tsvector is not + * exactly text. + */ +#define STATISTIC_KIND_MCELEM 4 + #endif /* PG_STATISTIC_H */ diff --git a/src/include/catalog/pg_type.h b/src/include/catalog/pg_type.h index 82798a7951e..f170a4c6311 100644 --- a/src/include/catalog/pg_type.h +++ b/src/include/catalog/pg_type.h @@ -8,7 +8,7 @@ * Portions Copyright (c) 1996-2008, PostgreSQL Global Development Group * Portions Copyright (c) 1994, Regents of the University of California * - * $PostgreSQL: pgsql/src/include/catalog/pg_type.h,v 1.196 2008/06/24 17:58:27 tgl Exp $ + * $PostgreSQL: pgsql/src/include/catalog/pg_type.h,v 1.197 2008/07/14 00:51:45 tgl Exp $ * * NOTES * the genbki.sh script reads this file and generates .bki @@ -543,7 +543,7 @@ DESCR("UUID datatype"); DATA(insert OID = 2951 ( _uuid PGNSP PGUID -1 f b t \054 0 2950 0 array_in array_out array_recv array_send - - - i x f 0 -1 0 _null_ _null_ )); /* text search */ -DATA(insert OID = 3614 ( tsvector PGNSP PGUID -1 f b t \054 0 0 3643 tsvectorin tsvectorout tsvectorrecv tsvectorsend - - - i x f 0 -1 0 _null_ _null_ )); +DATA(insert OID = 3614 ( tsvector PGNSP PGUID -1 f b t \054 0 0 3643 tsvectorin tsvectorout tsvectorrecv tsvectorsend - - ts_typanalyze i x f 0 -1 0 _null_ _null_ )); DESCR("text representation for text search"); #define TSVECTOROID 3614 DATA(insert OID = 3642 ( gtsvector PGNSP PGUID -1 f b t \054 0 0 3644 gtsvectorin gtsvectorout - - - - - i p f 0 -1 0 _null_ _null_ )); diff --git a/src/include/tsearch/ts_type.h b/src/include/tsearch/ts_type.h index 067114f92e4..a546d015b58 100644 --- a/src/include/tsearch/ts_type.h +++ b/src/include/tsearch/ts_type.h @@ -5,7 +5,7 @@ * * Copyright (c) 1998-2008, PostgreSQL Global Development Group * - * $PostgreSQL: pgsql/src/include/tsearch/ts_type.h,v 1.12 2008/06/10 08:55:50 heikki Exp $ + * $PostgreSQL: pgsql/src/include/tsearch/ts_type.h,v 1.13 2008/07/14 00:51:45 tgl Exp $ * *------------------------------------------------------------------------- */ @@ -153,6 +153,8 @@ extern Datum ts_rankcd_wtt(PG_FUNCTION_ARGS); extern Datum ts_rankcd_ttf(PG_FUNCTION_ARGS); extern Datum ts_rankcd_wttf(PG_FUNCTION_ARGS); +extern Datum ts_typanalyze(PG_FUNCTION_ARGS); + /* * TSQuery diff --git a/src/test/regress/expected/rules.out b/src/test/regress/expected/rules.out index 99f240f1f01..ecef0e96acf 100644 --- a/src/test/regress/expected/rules.out +++ b/src/test/regress/expected/rules.out @@ -1276,8 +1276,8 @@ drop table cchild; -- Check that ruleutils are working -- SELECT viewname, definition FROM pg_views WHERE schemaname <> 'information_schema' ORDER BY viewname; - viewname | definition ---------------------------+--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------- + viewname | definition +--------------------------+--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------- iexit | SELECT ih.name, ih.thepath, interpt_pp(ih.thepath, r.thepath) AS exit FROM ihighway ih, ramp r WHERE (ih.thepath ## r.thepath); pg_cursors | SELECT c.name, c.statement, c.is_holdable, c.is_binary, c.is_scrollable, c.creation_time FROM pg_cursor() c(name text, statement text, is_holdable boolean, is_binary boolean, is_scrollable boolean, creation_time timestamp with time zone); pg_group | SELECT pg_authid.rolname AS groname, pg_authid.oid AS grosysid, ARRAY(SELECT pg_auth_members.member FROM pg_auth_members WHERE (pg_auth_members.roleid = pg_authid.oid)) AS grolist FROM pg_authid WHERE (NOT pg_authid.rolcanlogin); @@ -1308,7 +1308,7 @@ SELECT viewname, definition FROM pg_views WHERE schemaname <> 'information_schem pg_statio_user_indexes | SELECT pg_statio_all_indexes.relid, pg_statio_all_indexes.indexrelid, pg_statio_all_indexes.schemaname, pg_statio_all_indexes.relname, pg_statio_all_indexes.indexrelname, pg_statio_all_indexes.idx_blks_read, pg_statio_all_indexes.idx_blks_hit FROM pg_statio_all_indexes WHERE ((pg_statio_all_indexes.schemaname <> ALL (ARRAY['pg_catalog'::name, 'information_schema'::name])) AND (pg_statio_all_indexes.schemaname !~ '^pg_toast'::text)); pg_statio_user_sequences | SELECT pg_statio_all_sequences.relid, pg_statio_all_sequences.schemaname, pg_statio_all_sequences.relname, pg_statio_all_sequences.blks_read, pg_statio_all_sequences.blks_hit FROM pg_statio_all_sequences WHERE ((pg_statio_all_sequences.schemaname <> ALL (ARRAY['pg_catalog'::name, 'information_schema'::name])) AND (pg_statio_all_sequences.schemaname !~ '^pg_toast'::text)); pg_statio_user_tables | SELECT pg_statio_all_tables.relid, pg_statio_all_tables.schemaname, pg_statio_all_tables.relname, pg_statio_all_tables.heap_blks_read, pg_statio_all_tables.heap_blks_hit, pg_statio_all_tables.idx_blks_read, pg_statio_all_tables.idx_blks_hit, pg_statio_all_tables.toast_blks_read, pg_statio_all_tables.toast_blks_hit, pg_statio_all_tables.tidx_blks_read, pg_statio_all_tables.tidx_blks_hit FROM pg_statio_all_tables WHERE ((pg_statio_all_tables.schemaname <> ALL (ARRAY['pg_catalog'::name, 'information_schema'::name])) AND (pg_statio_all_tables.schemaname !~ '^pg_toast'::text)); - pg_stats | SELECT n.nspname AS schemaname, c.relname AS tablename, a.attname, s.stanullfrac AS null_frac, s.stawidth AS avg_width, s.stadistinct AS n_distinct, CASE 1 WHEN s.stakind1 THEN s.stavalues1 WHEN s.stakind2 THEN s.stavalues2 WHEN s.stakind3 THEN s.stavalues3 WHEN s.stakind4 THEN s.stavalues4 ELSE NULL::anyarray END AS most_common_vals, CASE 1 WHEN s.stakind1 THEN s.stanumbers1 WHEN s.stakind2 THEN s.stanumbers2 WHEN s.stakind3 THEN s.stanumbers3 WHEN s.stakind4 THEN s.stanumbers4 ELSE NULL::real[] END AS most_common_freqs, CASE 2 WHEN s.stakind1 THEN s.stavalues1 WHEN s.stakind2 THEN s.stavalues2 WHEN s.stakind3 THEN s.stavalues3 WHEN s.stakind4 THEN s.stavalues4 ELSE NULL::anyarray END AS histogram_bounds, CASE 3 WHEN s.stakind1 THEN s.stanumbers1[1] WHEN s.stakind2 THEN s.stanumbers2[1] WHEN s.stakind3 THEN s.stanumbers3[1] WHEN s.stakind4 THEN s.stanumbers4[1] ELSE NULL::real END AS correlation FROM (((pg_statistic s JOIN pg_class c ON ((c.oid = s.starelid))) JOIN pg_attribute a ON (((c.oid = a.attrelid) AND (a.attnum = s.staattnum)))) LEFT JOIN pg_namespace n ON ((n.oid = c.relnamespace))) WHERE has_table_privilege(c.oid, 'select'::text); + pg_stats | SELECT n.nspname AS schemaname, c.relname AS tablename, a.attname, s.stanullfrac AS null_frac, s.stawidth AS avg_width, s.stadistinct AS n_distinct, CASE WHEN (s.stakind1 = ANY (ARRAY[1, 4])) THEN s.stavalues1 WHEN (s.stakind2 = ANY (ARRAY[1, 4])) THEN s.stavalues2 WHEN (s.stakind3 = ANY (ARRAY[1, 4])) THEN s.stavalues3 WHEN (s.stakind4 = ANY (ARRAY[1, 4])) THEN s.stavalues4 ELSE NULL::anyarray END AS most_common_vals, CASE WHEN (s.stakind1 = ANY (ARRAY[1, 4])) THEN s.stanumbers1 WHEN (s.stakind2 = ANY (ARRAY[1, 4])) THEN s.stanumbers2 WHEN (s.stakind3 = ANY (ARRAY[1, 4])) THEN s.stanumbers3 WHEN (s.stakind4 = ANY (ARRAY[1, 4])) THEN s.stanumbers4 ELSE NULL::real[] END AS most_common_freqs, CASE WHEN (s.stakind1 = 2) THEN s.stavalues1 WHEN (s.stakind2 = 2) THEN s.stavalues2 WHEN (s.stakind3 = 2) THEN s.stavalues3 WHEN (s.stakind4 = 2) THEN s.stavalues4 ELSE NULL::anyarray END AS histogram_bounds, CASE WHEN (s.stakind1 = 3) THEN s.stanumbers1[1] WHEN (s.stakind2 = 3) THEN s.stanumbers2[1] WHEN (s.stakind3 = 3) THEN s.stanumbers3[1] WHEN (s.stakind4 = 3) THEN s.stanumbers4[1] ELSE NULL::real END AS correlation FROM (((pg_statistic s JOIN pg_class c ON ((c.oid = s.starelid))) JOIN pg_attribute a ON (((c.oid = a.attrelid) AND (a.attnum = s.staattnum)))) LEFT JOIN pg_namespace n ON ((n.oid = c.relnamespace))) WHERE has_table_privilege(c.oid, 'select'::text); pg_tables | SELECT n.nspname AS schemaname, c.relname AS tablename, pg_get_userbyid(c.relowner) AS tableowner, t.spcname AS tablespace, c.relhasindex AS hasindexes, c.relhasrules AS hasrules, (c.reltriggers > 0) AS hastriggers FROM ((pg_class c LEFT JOIN pg_namespace n ON ((n.oid = c.relnamespace))) LEFT JOIN pg_tablespace t ON ((t.oid = c.reltablespace))) WHERE (c.relkind = 'r'::"char"); pg_timezone_abbrevs | SELECT pg_timezone_abbrevs.abbrev, pg_timezone_abbrevs.utc_offset, pg_timezone_abbrevs.is_dst FROM pg_timezone_abbrevs() pg_timezone_abbrevs(abbrev, utc_offset, is_dst); pg_timezone_names | SELECT pg_timezone_names.name, pg_timezone_names.abbrev, pg_timezone_names.utc_offset, pg_timezone_names.is_dst FROM pg_timezone_names() pg_timezone_names(name, abbrev, utc_offset, is_dst); |