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

Commitb3e4aef

Browse files
committed
Enhanced containment selectivity function for /contrib/ltree
Matteo Beccati
1 parent0df32e3 commitb3e4aef

File tree

5 files changed

+190
-7
lines changed

5 files changed

+190
-7
lines changed

‎contrib/ltree/ltree.sql.in‎

Lines changed: 2 additions & 2 deletions
Original file line numberDiff line numberDiff line change
@@ -230,7 +230,7 @@ CREATE OPERATOR @> (
230230
RIGHTARG = ltree,
231231
PROCEDURE = ltree_isparent,
232232
COMMUTATOR = '<@',
233-
RESTRICT =contsel,
233+
RESTRICT =parentsel,
234234
JOIN = contjoinsel
235235
);
236236

@@ -248,7 +248,7 @@ CREATE OPERATOR <@ (
248248
RIGHTARG = ltree,
249249
PROCEDURE = ltree_risparent,
250250
COMMUTATOR = '@>',
251-
RESTRICT =contsel,
251+
RESTRICT =parentsel,
252252
JOIN = contjoinsel
253253
);
254254

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

Lines changed: 2 additions & 2 deletions
Original file line numberDiff line numberDiff line change
@@ -9,7 +9,7 @@
99
*
1010
*
1111
* IDENTIFICATION
12-
* $PostgreSQL: pgsql/src/backend/utils/adt/geo_selfuncs.c,v 1.27 2006/03/05 15:58:42 momjian Exp $
12+
* $PostgreSQL: pgsql/src/backend/utils/adt/geo_selfuncs.c,v 1.28 2006/04/26 18:28:29 momjian Exp $
1313
*
1414
*XXX These are totally bogus. Perhaps someone will make them do
1515
*something reasonable, someday.
@@ -20,7 +20,6 @@
2020

2121
#include"utils/geo_decls.h"
2222

23-
2423
/*
2524
*Selectivity functions for geometric operators.These are bogus -- unless
2625
*we know the actual key distribution in the index, we can't make a good
@@ -93,3 +92,4 @@ contjoinsel(PG_FUNCTION_ARGS)
9392
{
9493
PG_RETURN_FLOAT8(0.001);
9594
}
95+

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

Lines changed: 180 additions & 1 deletion
Original file line numberDiff line numberDiff line change
@@ -15,7 +15,7 @@
1515
*
1616
*
1717
* IDENTIFICATION
18-
* $PostgreSQL: pgsql/src/backend/utils/adt/selfuncs.c,v 1.199 2006/04/20 17:50:18 tgl Exp $
18+
* $PostgreSQL: pgsql/src/backend/utils/adt/selfuncs.c,v 1.200 2006/04/26 18:28:29 momjian Exp $
1919
*
2020
*-------------------------------------------------------------------------
2121
*/
@@ -4852,3 +4852,182 @@ gistcostestimate(PG_FUNCTION_ARGS)
48524852

48534853
PG_RETURN_VOID();
48544854
}
4855+
4856+
4857+
#defineDEFAULT_PARENT_SEL 0.001
4858+
4859+
/*
4860+
*parentsel - Selectivity of parent relationship for ltree data types.
4861+
*/
4862+
Datum
4863+
parentsel(PG_FUNCTION_ARGS)
4864+
{
4865+
PlannerInfo*root= (PlannerInfo*)PG_GETARG_POINTER(0);
4866+
Oidoperator=PG_GETARG_OID(1);
4867+
List*args= (List*)PG_GETARG_POINTER(2);
4868+
intvarRelid=PG_GETARG_INT32(3);
4869+
VariableStatDatavardata;
4870+
Node*other;
4871+
boolvaronleft;
4872+
Datum*values;
4873+
intnvalues;
4874+
float4*numbers;
4875+
intnnumbers;
4876+
doubleselec=0.0;
4877+
4878+
/*
4879+
* If expression is not variable <@ something or something <@ variable,
4880+
* then punt and return a default estimate.
4881+
*/
4882+
if (!get_restriction_variable(root,args,varRelid,
4883+
&vardata,&other,&varonleft))
4884+
PG_RETURN_FLOAT8(DEFAULT_PARENT_SEL);
4885+
4886+
/*
4887+
* If the something is a NULL constant, assume operator is strict and
4888+
* return zero, ie, operator will never return TRUE.
4889+
*/
4890+
if (IsA(other,Const)&&
4891+
((Const*)other)->constisnull)
4892+
{
4893+
ReleaseVariableStats(vardata);
4894+
PG_RETURN_FLOAT8(0.0);
4895+
}
4896+
4897+
if (HeapTupleIsValid(vardata.statsTuple))
4898+
{
4899+
Form_pg_statisticstats;
4900+
doublemcvsum=0.0;
4901+
doublemcvsel=0.0;
4902+
doublehissel=0.0;
4903+
4904+
stats= (Form_pg_statistic)GETSTRUCT(vardata.statsTuple);
4905+
4906+
if (IsA(other,Const))
4907+
{
4908+
/* Variable is being compared to a known non-null constant */
4909+
Datumconstval= ((Const*)other)->constvalue;
4910+
boolmatch= false;
4911+
inti;
4912+
4913+
/*
4914+
* Is the constant "<@" to any of the column's most common values?
4915+
*/
4916+
if (get_attstatsslot(vardata.statsTuple,
4917+
vardata.atttype,vardata.atttypmod,
4918+
STATISTIC_KIND_MCV,InvalidOid,
4919+
&values,&nvalues,
4920+
&numbers,&nnumbers))
4921+
{
4922+
FmgrInfocontproc;
4923+
4924+
fmgr_info(get_opcode(operator),&contproc);
4925+
4926+
for (i=0;i<nvalues;i++)
4927+
{
4928+
/* be careful to apply operator right way 'round */
4929+
if (varonleft)
4930+
match=DatumGetBool(FunctionCall2(&contproc,
4931+
values[i],
4932+
constval));
4933+
else
4934+
match=DatumGetBool(FunctionCall2(&contproc,
4935+
constval,
4936+
values[i]));
4937+
4938+
/* calculate total selectivity of all most-common-values */
4939+
mcvsum+=numbers[i];
4940+
4941+
/* calculate selectivity of matching most-common-values */
4942+
if (match)
4943+
mcvsel+=numbers[i];
4944+
}
4945+
}
4946+
else
4947+
{
4948+
/* no most-common-values info available */
4949+
values=NULL;
4950+
numbers=NULL;
4951+
i=nvalues=nnumbers=0;
4952+
}
4953+
4954+
free_attstatsslot(vardata.atttype,values,nvalues,NULL,0);
4955+
4956+
/*
4957+
* Is the constant "<@" to any of the column's histogram values?
4958+
*/
4959+
if (get_attstatsslot(vardata.statsTuple,
4960+
vardata.atttype,vardata.atttypmod,
4961+
STATISTIC_KIND_HISTOGRAM,InvalidOid,
4962+
&values,&nvalues,
4963+
NULL,NULL))
4964+
{
4965+
FmgrInfocontproc;
4966+
4967+
fmgr_info(get_opcode(operator),&contproc);
4968+
4969+
for (i=0;i<nvalues;i++)
4970+
{
4971+
/* be careful to apply operator right way 'round */
4972+
if (varonleft)
4973+
match=DatumGetBool(FunctionCall2(&contproc,
4974+
values[i],
4975+
constval));
4976+
else
4977+
match=DatumGetBool(FunctionCall2(&contproc,
4978+
constval,
4979+
values[i]));
4980+
/* count matching histogram values */
4981+
if (match)
4982+
hissel++;
4983+
}
4984+
4985+
if (hissel>0.0)
4986+
{
4987+
/*
4988+
* some matching values found inside histogram, divide
4989+
* matching entries number by total histogram entries to
4990+
* get the histogram related selectivity
4991+
*/
4992+
hissel /=nvalues;
4993+
}
4994+
}
4995+
else
4996+
{
4997+
/* no histogram info available */
4998+
values=NULL;
4999+
i=nvalues=0;
5000+
}
5001+
5002+
free_attstatsslot(vardata.atttype,values,nvalues,
5003+
NULL,0);
5004+
5005+
5006+
/*
5007+
* calculate selectivity based on MCV and histogram result
5008+
* histogram selectivity needs to be scaled down if there are any
5009+
* most-common-values
5010+
*/
5011+
selec=mcvsel+hissel* (1.0-mcvsum);
5012+
5013+
/*
5014+
* don't return 0.0 selectivity unless all table values are inside
5015+
* mcv
5016+
*/
5017+
if (selec==0.0&&mcvsum!=1.0)
5018+
selec=DEFAULT_PARENT_SEL;
5019+
}
5020+
else
5021+
selec=DEFAULT_PARENT_SEL;
5022+
}
5023+
else
5024+
selec=DEFAULT_PARENT_SEL;
5025+
5026+
ReleaseVariableStats(vardata);
5027+
5028+
/* result should be in range, but make sure... */
5029+
CLAMP_PROBABILITY(selec);
5030+
5031+
PG_RETURN_FLOAT8((float8)selec);
5032+
}
5033+

‎src/include/catalog/pg_proc.h‎

Lines changed: 3 additions & 1 deletion
Original file line numberDiff line numberDiff line change
@@ -7,7 +7,7 @@
77
* Portions Copyright (c) 1996-2006, PostgreSQL Global Development Group
88
* Portions Copyright (c) 1994, Regents of the University of California
99
*
10-
* $PostgreSQL: pgsql/src/include/catalog/pg_proc.h,v 1.406 2006/04/25 00:25:20 momjian Exp $
10+
* $PostgreSQL: pgsql/src/include/catalog/pg_proc.h,v 1.407 2006/04/26 18:28:30 momjian Exp $
1111
*
1212
* NOTES
1313
* The script catalog/genbki.sh reads this file and generates .bki
@@ -3812,6 +3812,8 @@ DATA(insert OID = 2591 ( gist_circle_consistent PGNSP PGUID 12 f f t f i 3 16 "
38123812
DESCR("GiST support");
38133813
DATA(insertOID=2592 (gist_circle_compressPGNSPPGUID12fftfi12281"2281"_null__null__null_gist_circle_compress-_null_ ));
38143814
DESCR("GiST support");
3815+
DATA(insertOID=2599 (parentselPGNSPPGUID12fftfs4701"2281 26 2281 23"_null__null__null_parentsel-_null_ ));
3816+
DESCR("enhanced restriction selectivity for ltree isparent comparison operators");
38153817

38163818

38173819
/*

‎src/include/utils/selfuncs.h‎

Lines changed: 3 additions & 1 deletion
Original file line numberDiff line numberDiff line change
@@ -8,7 +8,7 @@
88
* Portions Copyright (c) 1996-2006, PostgreSQL Global Development Group
99
* Portions Copyright (c) 1994, Regents of the University of California
1010
*
11-
* $PostgreSQL: pgsql/src/include/utils/selfuncs.h,v 1.28 2006/03/05 15:59:07 momjian Exp $
11+
* $PostgreSQL: pgsql/src/include/utils/selfuncs.h,v 1.29 2006/04/26 18:28:34 momjian Exp $
1212
*
1313
*-------------------------------------------------------------------------
1414
*/
@@ -134,4 +134,6 @@ extern Datum btcostestimate(PG_FUNCTION_ARGS);
134134
externDatumhashcostestimate(PG_FUNCTION_ARGS);
135135
externDatumgistcostestimate(PG_FUNCTION_ARGS);
136136

137+
externDatumparentsel(PG_FUNCTION_ARGS);
138+
137139
#endif/* SELFUNCS_H */

0 commit comments

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