Movatterモバイル変換

[0]ホーム
URL:

Skip to content

Navigation Menu

Sign in
Appearance settings

Search code, repositories, users, issues, pull requests...

Provide feedback

We read every piece of feedback, and take your input very seriously.

Saved searches

Use saved searches to filter your results more quickly

 Sign up
Appearance settings

Commit8624ff7

Browse files
committed
Further selectivity-estimation work. Speed up eqsel()
(it should just call the given operator, not look up an = operator).Fix intltsel() so that all numeric data types are converted to doublebefore trying to estimate where the given comparison value is in theknown range of column values. intltsel() still needs work, or replacement,for non-numeric data types ... but for nonintegral numeric types itshould now be delivering reasonable estimates.
1 parentd35eebe commit8624ff7

File tree

1 file changed

+168
-100
lines changed

1 file changed

+168
-100
lines changed

‎src/backend/utils/adt/selfuncs.c

Lines changed: 168 additions & 100 deletions
Original file line numberDiff line numberDiff line change
@@ -10,7 +10,7 @@
1010
*
1111
*
1212
* IDENTIFICATION
13-
* $Header: /cvsroot/pgsql/src/backend/utils/adt/selfuncs.c,v 1.36 1999/08/01 04:54:22 tgl Exp $
13+
* $Header: /cvsroot/pgsql/src/backend/utils/adt/selfuncs.c,v 1.37 1999/08/02 02:05:41 tgl Exp $
1414
*
1515
*-------------------------------------------------------------------------
1616
*/
@@ -20,8 +20,10 @@
2020
#include"access/heapam.h"
2121
#include"catalog/catname.h"
2222
#include"catalog/pg_operator.h"
23+
#include"catalog/pg_proc.h"
2324
#include"catalog/pg_statistic.h"
2425
#include"catalog/pg_type.h"
26+
#include"parser/parse_func.h"
2527
#include"parser/parse_oper.h"
2628
#include"utils/builtins.h"
2729
#include"utils/lsyscache.h"
@@ -36,6 +38,8 @@
3638
/* default selectivity estimate for inequalities such as "A < b" */
3739
#defineDEFAULT_INEQ_SEL (1.0 / 3.0)
3840

41+
staticboolconvert_to_scale(Datumvalue,Oidtypid,
42+
double*scaleval);
3943
staticvoidgetattproperties(Oidrelid,AttrNumberattnum,
4044
Oid*typid,
4145
int*typlen,
@@ -53,6 +57,12 @@ static double getattdisbursion(Oid relid, AttrNumber attnum);
5357

5458
/*
5559
*eqsel- Selectivity of "=" for any data types.
60+
*
61+
* Note: this routine is also used to estimate selectivity for some
62+
* operators that are not "=" but have comparable selectivity behavior,
63+
* such as "~~" (text LIKE). Even for "=" we must keep in mind that
64+
* the left and right datatypes may differ, so the type of the given
65+
* constant "value" may be different from the type of the attribute.
5666
*/
5767
float64
5868
eqsel(Oidopid,
@@ -81,45 +91,38 @@ eqsel(Oid opid,
8191
getattproperties(relid,attno,
8292
&typid,&typlen,&typbyval,&typmod);
8393

94+
/* get stats for the attribute, if available */
8495
if (getattstatistics(relid,attno,typid,typmod,
8596
&nullfrac,&commonfrac,&commonval,
8697
NULL,NULL))
8798
{
8899
if (flag&SEL_CONSTANT)
89100
{
90-
/* Is the constant the same as the most common value? */
91-
HeapTupleoprtuple;
92-
Oidltype,
93-
rtype;
94-
Operatorfunc_operator;
95-
boolmostcommon= false;
96-
97-
/* get left and right datatypes of the operator */
98-
oprtuple=get_operator_tuple(opid);
99-
if (!HeapTupleIsValid(oprtuple))
100-
elog(ERROR,"eqsel: no tuple for operator %u",opid);
101-
ltype= ((Form_pg_operator)GETSTRUCT(oprtuple))->oprleft;
102-
rtype= ((Form_pg_operator)GETSTRUCT(oprtuple))->oprright;
103-
104-
/* and find appropriate equality operator (no, it ain't
105-
* necessarily opid itself...)
101+
/* Is the constant "=" to the column's most common value?
102+
* (Although the operator may not really be "=",
103+
* we will assume that seeing whether it returns TRUE
104+
* for the most common value is useful information.
105+
* If you don't like it, maybe you shouldn't be using
106+
* eqsel for your operator...)
106107
*/
107-
func_operator=oper("=",ltype,rtype, true);
108+
RegProcedureeqproc=get_opcode(opid);
109+
boolmostcommon;
108110

109-
if (func_operator!=NULL)
110-
{
111-
RegProcedureeqproc= ((Form_pg_operator)GETSTRUCT(func_operator))->oprcode;
112-
if (flag&SEL_RIGHT)/* given value on the right? */
113-
mostcommon= (bool)
114-
DatumGetUInt8(fmgr(eqproc,commonval,value));
115-
else
116-
mostcommon= (bool)
117-
DatumGetUInt8(fmgr(eqproc,value,commonval));
118-
}
111+
if (eqproc== (RegProcedure)NULL)
112+
elog(ERROR,"eqsel: no procedure for operator %u",
113+
opid);
114+
115+
/* be careful to apply operator right way 'round */
116+
if (flag&SEL_RIGHT)
117+
mostcommon= (bool)
118+
DatumGetUInt8(fmgr(eqproc,commonval,value));
119+
else
120+
mostcommon= (bool)
121+
DatumGetUInt8(fmgr(eqproc,value,commonval));
119122

120123
if (mostcommon)
121124
{
122-
/*Search isforthe most common value. We know the
125+
/*Constant is"=" tothe most common value. We know
123126
* selectivity exactly (or as exactly as VACUUM could
124127
* calculate it, anyway).
125128
*/
@@ -179,6 +182,10 @@ eqsel(Oid opid,
179182

180183
/*
181184
*neqsel- Selectivity of "!=" for any data types.
185+
*
186+
* This routine is also used for some operators that are not "!="
187+
* but have comparable selectivity behavior. See above comments
188+
* for eqsel().
182189
*/
183190
float64
184191
neqsel(Oidopid,
@@ -196,7 +203,11 @@ neqsel(Oid opid,
196203

197204
/*
198205
*intltsel- Selectivity of "<" (also "<=") for integers.
199-
* Should work for both longs and shorts.
206+
*
207+
* Actually, this works and is used for all numeric types, so it should
208+
* be renamed. In fact, it is also currently called for all manner of
209+
* non-numeric types, for which it is NOT very helpful. That needs
210+
* to be fixed.
200211
*/
201212
float64
202213
intltsel(Oidopid,
@@ -221,122 +232,108 @@ intltsel(Oid opid,
221232
int32typmod;
222233
Datumhival,
223234
loval;
224-
longval,
235+
doubleval,
225236
high,
226237
low,
227238
numerator,
228239
denominator;
229240

230-
/* get left and right datatypes of the operator */
241+
/* Get left and right datatypes of the operator so we know
242+
* what type the constant is.
243+
*/
231244
oprtuple=get_operator_tuple(opid);
232245
if (!HeapTupleIsValid(oprtuple))
233246
elog(ERROR,"intltsel: no tuple for operator %u",opid);
234247
ltype= ((Form_pg_operator)GETSTRUCT(oprtuple))->oprleft;
235248
rtype= ((Form_pg_operator)GETSTRUCT(oprtuple))->oprright;
236249

237-
/*
238-
* TEMPORARY HACK: this code is currently getting called for
239-
* a bunch of non-integral types. Give a default estimate if
240-
* either side is not pass-by-val. Need better solution.
241-
*/
242-
if (!get_typbyval(ltype)|| !get_typbyval(rtype))
250+
/* Convert the constant to a uniform comparison scale. */
251+
if (!convert_to_scale(value,
252+
((flag&SEL_RIGHT) ?rtype :ltype),
253+
&val))
243254
{
255+
/* Ideally we'd produce an error here, on the grounds that
256+
* the given operator shouldn't have intltsel registered as its
257+
* selectivity func unless we can deal with its operand types.
258+
* But currently, all manner of stuff is invoking intltsel,
259+
* so give a default estimate until that can be fixed.
260+
*/
244261
*result=DEFAULT_INEQ_SEL;
245262
returnresult;
246263
}
247264

248-
/* Deduce type of the constant, and convert to uniform "long" format.
249-
* Note that constant might well be a different type than attribute.
250-
* XXX this ought to use a type-specific "convert to double" op.
251-
*/
252-
typid= (flag&SEL_RIGHT) ?rtype :ltype;
253-
switch (get_typlen(typid))
254-
{
255-
case1:
256-
val= (long)DatumGetUInt8(value);
257-
break;
258-
case2:
259-
val= (long)DatumGetInt16(value);
260-
break;
261-
case4:
262-
val= (long)DatumGetInt32(value);
263-
break;
264-
default:
265-
elog(ERROR,"intltsel: unsupported type %u",typid);
266-
*result=DEFAULT_INEQ_SEL;
267-
returnresult;
268-
}
269-
270-
/* Now get info about the attribute */
265+
/* Now get info and stats about the attribute */
271266
getattproperties(relid,attno,
272267
&typid,&typlen,&typbyval,&typmod);
273268

274269
if (!getattstatistics(relid,attno,typid,typmod,
275270
NULL,NULL,NULL,
276271
&loval,&hival))
277272
{
273+
/* no stats available, so default result */
278274
*result=DEFAULT_INEQ_SEL;
279275
returnresult;
280276
}
281-
/*
282-
* Convert loval/hival to common "long int" representation.
283-
*/
284-
switch (typlen)
277+
278+
/* Convert the attribute's loval/hival to common scale. */
279+
if (!convert_to_scale(loval,typid,&low)||
280+
!convert_to_scale(hival,typid,&high))
281+
{
282+
/* See above comments... */
283+
if (!typbyval)
284+
{
285+
pfree(DatumGetPointer(hival));
286+
pfree(DatumGetPointer(loval));
287+
}
288+
289+
*result=DEFAULT_INEQ_SEL;
290+
returnresult;
291+
}
292+
293+
/* release temp storage if needed */
294+
if (!typbyval)
285295
{
286-
case1:
287-
low= (long)DatumGetUInt8(loval);
288-
high= (long)DatumGetUInt8(hival);
289-
break;
290-
case2:
291-
low= (long)DatumGetInt16(loval);
292-
high= (long)DatumGetInt16(hival);
293-
break;
294-
case4:
295-
low= (long)DatumGetInt32(loval);
296-
high= (long)DatumGetInt32(hival);
297-
break;
298-
default:
299-
elog(ERROR,"intltsel: unsupported type %u",typid);
300-
*result=DEFAULT_INEQ_SEL;
301-
returnresult;
296+
pfree(DatumGetPointer(hival));
297+
pfree(DatumGetPointer(loval));
302298
}
303-
if (val<low||val>high)
299+
300+
if (high <=low)
304301
{
305-
/* Ifgiven value is outsidethestatistical range,
306-
*assume we have out-of-date stats and return a default guess.
307-
*We could return a small or large value if we trusted the stats
308-
*more. XXX change this eventually.
302+
/* Ifwe trustedthestats fully, we could return a small or
303+
*large selec depending on which side of the single data point
304+
*the constant is on. But it seems better to assume that the
305+
*stats are out of date and return a default...
309306
*/
310307
*result=DEFAULT_INEQ_SEL;
308+
}
309+
elseif (val <=low||val >=high)
310+
{
311+
/* If given value is outside the statistical range, return a
312+
* small or large value; but not 0.0/1.0 since there is a chance
313+
* the stats are out of date.
314+
*/
315+
if (flag&SEL_RIGHT)
316+
*result= (val <=low) ?0.01 :0.99;
317+
else
318+
*result= (val <=low) ?0.99 :0.01;
311319
}
312320
else
313321
{
314322
denominator=high-low;
315-
if (denominator <=0)
316-
denominator=1;
317323
if (flag&SEL_RIGHT)
318324
numerator=val-low;
319325
else
320326
numerator=high-val;
321-
if (numerator <=0)/* never return a zero estimate! */
322-
numerator=1;
323-
if (numerator >=denominator)
324-
*result=1.0;
325-
else
326-
*result= (double)numerator / (double)denominator;
327-
}
328-
if (!typbyval)
329-
{
330-
pfree(DatumGetPointer(hival));
331-
pfree(DatumGetPointer(loval));
327+
*result=numerator /denominator;
332328
}
333329
}
334330
returnresult;
335331
}
336332

337333
/*
338334
*intgtsel- Selectivity of ">" (also ">=") for integers.
339-
* Should work for both longs and shorts.
335+
*
336+
* See above comments for intltsel.
340337
*/
341338
float64
342339
intgtsel(Oidopid,
@@ -439,6 +436,77 @@ intgtjoinsel(Oid opid,
439436
returnresult;
440437
}
441438

439+
/*
440+
* convert_to_scale
441+
* Convert a given value of the indicated type to the comparison
442+
* scale needed by intltsel(). Returns "true" if successful.
443+
*
444+
* All numeric datatypes are simply converted to their equivalent
445+
* "double" values.
446+
* Future extension: convert string-like types to some suitable scale.
447+
*/
448+
staticbool
449+
convert_to_scale(Datumvalue,Oidtypid,
450+
double*scaleval)
451+
{
452+
/* Fast-path conversions for some built-in types */
453+
switch (typid)
454+
{
455+
caseBOOLOID:
456+
*scaleval= (double)DatumGetUInt8(value);
457+
return true;
458+
caseINT2OID:
459+
*scaleval= (double)DatumGetInt16(value);
460+
return true;
461+
caseINT4OID:
462+
*scaleval= (double)DatumGetInt32(value);
463+
return true;
464+
//case INT8OID:
465+
466+
467+
caseFLOAT4OID:
468+
*scaleval= (double) (*DatumGetFloat32(value));
469+
return true;
470+
caseFLOAT8OID:
471+
*scaleval= (double) (*DatumGetFloat64(value));
472+
return true;
473+
//case NUMERICOID:
474+
475+
caseOIDOID:
476+
caseREGPROCOID:
477+
/* we can treat OIDs as integers... */
478+
*scaleval= (double)DatumGetObjectId(value);
479+
return true;
480+
default:
481+
{
482+
/* See whether there is a registered type-conversion function,
483+
* namely a procedure named "float8" with the right signature.
484+
*/
485+
Oidoid_array[MAXFARGS];
486+
HeapTupleftup;
487+
488+
MemSet(oid_array,0,MAXFARGS*sizeof(Oid));
489+
oid_array[0]=typid;
490+
ftup=SearchSysCacheTuple(PRONAME,
491+
PointerGetDatum("float8"),
492+
Int32GetDatum(1),
493+
PointerGetDatum(oid_array),
494+
0);
495+
if (HeapTupleIsValid(ftup)&&
496+
((Form_pg_proc)GETSTRUCT(ftup))->prorettype==FLOAT8OID)
497+
{
498+
RegProcedureconvertproc= (RegProcedure)ftup->t_data->t_oid;
499+
Datumconverted= (Datum)fmgr(convertproc,value);
500+
*scaleval= (double) (*DatumGetFloat64(converted));
501+
return true;
502+
}
503+
break;
504+
}
505+
}
506+
/* Don't know how to convert */
507+
return false;
508+
}
509+
442510
/*
443511
* getattproperties
444512
* Retrieve pg_attribute properties for an attribute,

0 commit comments

Comments
 (0)
[8]ページ先頭
©2009-2025 Movatter.jp