diff -Naur pgsql.virg/src/backend/optimizer/path/clausesel.c pgsql.dev/src/backend/optimizer/path/clausesel.c --- pgsql.virg/src/backend/optimizer/path/clausesel.c Thu Jun 21 04:18:11 2001 +++ pgsql.dev/src/backend/optimizer/path/clausesel.c Sun Jun 24 06:05:57 2001 @@ -24,7 +24,7 @@ #include "parser/parsetree.h" #include "utils/fmgroids.h" #include "utils/lsyscache.h" - +#include "utils/selfuncs.h" /* note that pg_type.h hardwires size of bool as 1 ... duplicate it */ #define MAKEBOOLCONST(val,isnull) \ @@ -409,6 +409,20 @@ } } } + else if (IsA(clause, NullTest)) + { + /* + * Use node specific selectivity calculation function + */ + s1 = nulltestsel(root, (NullTest *) clause, varRelid); + } + else if (IsA(clause, BooleanTest)) + { + /* + * Use node specific selectivity calculation function + */ + s1 = booltestsel(root, (BooleanTest *) clause, varRelid); + } else if (IsA(clause, Param)) { /* XXX any way to do better? */ @@ -516,6 +530,10 @@ ((RelabelType *) clause)->arg, varRelid); } + + #ifdef BOOLTESTDEBUG + elog(NOTICE, "clause_selectivity: s1 %f", s1); + #endif /* BOOLTESTDEBUG */ return s1; } diff -Naur pgsql.virg/src/backend/utils/adt/selfuncs.c pgsql.dev/src/backend/utils/adt/selfuncs.c --- pgsql.virg/src/backend/utils/adt/selfuncs.c Thu Jun 21 04:18:12 2001 +++ pgsql.dev/src/backend/utils/adt/selfuncs.c Sun Jun 24 06:04:30 2001 @@ -94,6 +94,7 @@ #include "utils/int8.h" #include "utils/lsyscache.h" #include "utils/syscache.h" +#include "utils/selfuncs.h" /* * Note: the default selectivity estimates are not chosen entirely at random. @@ -117,6 +118,10 @@ /* default number of distinct values in a table */ #define DEFAULT_NUM_DISTINCT 200 +/* default selectivity estimate for boolean and null test nodes */ +#define DEFAULT_UNK_SEL 0.005 +#define DEFAULT_NOT_UNK_SEL 1 - DEFAULT_UNK_SEL +#define DEFAULT_BOOL_SEL 0.5 static bool convert_to_scalar(Datum value, Oid valuetypid, double *scaledvalue, Datum lobound, Datum hibound, Oid boundstypid, @@ -931,6 +936,359 @@ result = patternsel(fcinfo, Pattern_Type_Like_IC); result = 1.0 - result; PG_RETURN_FLOAT8(result); +} + +/* + * booltestsel - Selectivity of BooleanTest Node. + */ +Selectivity +booltestsel(Query *root, BooleanTest *clause, int varRelid) +{ + Var *var; + Node *arg; + Oid relid; + HeapTuple statsTuple; + Datum *values; + int nvalues; + float4 *numbers; + int nnumbers; + double selec; + double defselec; + double freq_true; + double freq_false; + double freq_null; + + if (!IsA(clause, BooleanTest)) + elog(ERROR, "booltestsel: unexpected node type %d", + (int) clause->type); + + switch (clause->booltesttype) + { + case IS_UNKNOWN: + defselec = DEFAULT_UNK_SEL; + break; + case IS_NOT_UNKNOWN: + defselec = DEFAULT_NOT_UNK_SEL; + break; + case IS_TRUE: + case IS_NOT_FALSE: + case IS_FALSE: + case IS_NOT_TRUE: + defselec = DEFAULT_BOOL_SEL; + break; + default: + elog(ERROR, "booltestsel: unexpected booltesttype %d", + (int) clause->booltesttype); + return (Selectivity) 0; /* keep compiler quiet */ + } + + arg = (Node *) clause->arg; + + /* + * Ignore any binary-compatible relabeling + */ + if (IsA(arg, RelabelType)) + arg = ((RelabelType *) arg)->arg; + + if (IsA(arg, Var) && (varRelid == 0 || varRelid == ((Var *) arg)->varno)) + var = (Var *) arg; + else + { + /* + * punt + */ + switch (clause->booltesttype) + { + case IS_UNKNOWN: + case IS_NOT_UNKNOWN: + selec = defselec; + break; + case IS_TRUE: + case IS_NOT_FALSE: + selec = (double) clause_selectivity(root, (Node *) arg, varRelid); + break; + case IS_FALSE: + case IS_NOT_TRUE: + selec = 1.0 - (double) clause_selectivity(root, (Node *) arg, varRelid); + break; + default: + elog(ERROR, "booltestsel: unexpected booltesttype %d", + (int) clause->booltesttype); + selec = 0.0; /* Keep compiler quiet */ + } + return (Selectivity) selec; + } + + relid = getrelid(var->varno, root->rtable); + if (relid == InvalidOid) + return (Selectivity) defselec; + + /* get stats for the attribute, if available */ + statsTuple = SearchSysCache(STATRELATT, + ObjectIdGetDatum(relid), + Int16GetDatum(var->varattno), + 0, 0); + if (HeapTupleIsValid(statsTuple)) + { + Form_pg_statistic stats; + + stats = (Form_pg_statistic) GETSTRUCT(statsTuple); + freq_null = stats->stanullfrac; + + /* + * Var is being compared to a known non-null constant, + * i.e. true, false, or unknown + */ + if (get_attstatsslot(statsTuple, var->vartype, var->vartypmod, + STATISTIC_KIND_MCV, InvalidOid, + &values, &nvalues, + &numbers, &nnumbers)) + { + /* + * Get first MCV frequency and derive frequency for true. + */ + if (DatumGetBool(values[0])) + freq_true = numbers[0]; + else + freq_true = 1.0 - numbers[0] - stats->stanullfrac; + + /* + * Next derive freqency for false and set frequency for null. + * Then use these as appropriate to derive frequency for each case. + */ + freq_false = 1.0 - freq_true - stats->stanullfrac; + + switch (clause->booltesttype) + { + case IS_UNKNOWN: + /* + * Use freq_null directly. + */ + selec = freq_null; + break; + case IS_NOT_UNKNOWN: + /* + * Select not unknown (not null) values. + * Calculate from freq_null. + */ + selec = 1.0 - freq_null; + break; + case IS_TRUE: + /* + * Use freq_true directly. + */ + selec = freq_true; + break; + case IS_NOT_FALSE: + /* + * Select not false values. + * Calculate from freq_false. + */ + selec = 1 - freq_false; + break; + case IS_FALSE: + /* + * Use freq_false directly. + */ + selec = freq_false; + break; + case IS_NOT_TRUE: + /* + * Select not true values. + * Calculate from freq_true. + */ + selec = 1 - freq_true; + break; + default: + elog(ERROR, "booltestsel: unexpected booltesttype %d", + (int) clause->booltesttype); + /* + * Keep compiler quiet. + */ + selec = 0.0; + return (Selectivity) selec; + } + } + else + { + /* + * Prevent compiler complaints. + */ + values = NULL; + numbers = NULL; + nvalues = nnumbers = 0; + + /* + * No most-common-value info available. + * Still have null fraction information, + * so use it for is [not] unknown. + * Otherwise adjust for null fraction and + * assume an even split for boolean tests. + */ + + switch (clause->booltesttype) + { + case IS_UNKNOWN: + /* + * Use freq_null directly. + */ + selec = freq_null; + break; + case IS_NOT_UNKNOWN: + /* + * Select not unknown (not null) values. + * Calculate from freq_null. + */ + selec = 1.0 - freq_null; + break; + case IS_TRUE: + case IS_NOT_FALSE: + case IS_FALSE: + case IS_NOT_TRUE: + selec = (1.0 - freq_null) / 2; + break; + default: + elog(ERROR, "booltestsel: unexpected booltesttype %d", + (int) clause->booltesttype); + /* + * keep compiler quiet + */ + selec = 0.0; + return (Selectivity) selec; + } + } + + ReleaseSysCache(statsTuple); + } + else + { + /* + * No VACUUM ANALYZE stats available, so make a guess using + * the number of distinct values (2) and assuming they are + * equally common. (The guess is unlikely to be very good, + * but we do know a few special cases.) + */ + selec = defselec; + } + + /* result should be in range, but make sure... */ + if (selec < 0.0) + selec = 0.0; + else if (selec > 1.0) + selec = 1.0; + + return (Selectivity) selec; +} + +/* + * nulltestsel - Selectivity of NullTest Node. + */ +Selectivity +nulltestsel(Query *root, NullTest *clause, int varRelid) +{ + Var *var; + Node *arg; + Oid relid; + HeapTuple statsTuple; + double selec; + double defselec; + double freq_null; + + if (!IsA(clause, NullTest)) + elog(ERROR, "nulltestsel: unexpected node type %d", + (int) clause->type); + + switch (clause->nulltesttype) + { + case IS_NULL: + defselec = DEFAULT_UNK_SEL; + break; + case IS_NOT_NULL: + defselec = DEFAULT_NOT_UNK_SEL; + break; + default: + elog(ERROR, "nulltestsel: unexpected nulltesttype %d", + (int) clause->nulltesttype); + return (Selectivity) 0; /* keep compiler quiet */ + } + + arg = (Node *) clause->arg; + + /* + * Ignore any binary-compatible relabeling + */ + if (IsA(arg, RelabelType)) + arg = ((RelabelType *) arg)->arg; + + if (IsA(arg, Var) && (varRelid == 0 || varRelid == ((Var *) arg)->varno)) + var = (Var *) arg; + else + { + /* + * punt + */ + selec = defselec; + return (Selectivity) selec; + } + + relid = getrelid(var->varno, root->rtable); + if (relid == InvalidOid) + return (Selectivity) defselec; + + /* get stats for the attribute, if available */ + statsTuple = SearchSysCache(STATRELATT, + ObjectIdGetDatum(relid), + Int16GetDatum(var->varattno), + 0, 0); + if (HeapTupleIsValid(statsTuple)) + { + Form_pg_statistic stats; + + stats = (Form_pg_statistic) GETSTRUCT(statsTuple); + freq_null = stats->stanullfrac; + + switch (clause->nulltesttype) + { + case IS_NULL: + /* + * Use freq_null directly. + */ + selec = freq_null; + break; + case IS_NOT_NULL: + /* + * Select not unknown (not null) values. + * Calculate from freq_null. + */ + selec = 1.0 - freq_null; + break; + default: + elog(ERROR, "nulltestsel: unexpected nulltesttype %d", + (int) clause->nulltesttype); + /* + * Keep compiler quiet. + */ + selec = 0.0; + return (Selectivity) selec; + } + + ReleaseSysCache(statsTuple); + } + else + { + /* + * No VACUUM ANALYZE stats available, so make a guess + */ + selec = defselec; + } + + /* result should be in range, but make sure... */ + if (selec < 0.0) + selec = 0.0; + else if (selec > 1.0) + selec = 1.0; + + return (Selectivity) selec; } /* diff -Naur pgsql.virg/src/include/utils/selfuncs.h pgsql.dev/src/include/utils/selfuncs.h --- pgsql.virg/src/include/utils/selfuncs.h Thu Jan 1 00:00:00 1970 +++ pgsql.dev/src/include/utils/selfuncs.h Sun Jun 24 06:05:48 2001 @@ -0,0 +1,24 @@ +/*------------------------------------------------------------------------- + * + * selfuncs.h + * Selectivity functions and index cost estimation functions for + * standard operators and index access methods. + * + * Portions Copyright (c) 1996-2001, PostgreSQL Global Development Group + * Portions Copyright (c) 1994, Regents of the University of California + * + *------------------------------------------------------------------------- + */ +#ifndef SELFUNCS_H +#define SELFUNCS_H + + +#ifdef DEBUGBOOLTEST +#define BOOLTESTDEBUG +#endif /* DEBUGBOOLTEST */ + +Selectivity booltestsel(Query *root, BooleanTest *clause, int varRelid); +Selectivity nulltestsel(Query *root, NullTest *clause, int varRelid); + + +#endif /* SELFUNCS_H */