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

Commit0ee5a39

Browse files
committed
Apply a band-aid fix for the problem that 8.2 and up completely misestimate
the number of rows likely to be produced by a query such asSELECT * 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 thusit may produce a significant number of rows even if the t2.key table columncontains no nulls at all. 8.2 thinks the table column's null fraction isrelevant and thus may estimate no rows out, which results in terrible plansif there are more joins above this one. A proper fix for this will involvepassing much more information about the context of a clause to the selectivityestimator functions than we ever have. There's no time left to write such apatch 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 whenan IS NULL test is evaluated at an outer join, and just use a constantestimate instead --- I went with 0.5 for lack of a better idea. This won'tcatch every case but it will catch the typical ways of writing such queries,and it seems unlikely to make things worse for other queries.
1 parenta55eab8 commit0ee5a39

File tree

3 files changed

+23
-7
lines changed

3 files changed

+23
-7
lines changed

‎src/backend/optimizer/path/clausesel.c

Lines changed: 6 additions & 3 deletions
Original file line numberDiff line numberDiff line change
@@ -8,7 +8,7 @@
88
*
99
*
1010
* IDENTIFICATION
11-
* $PostgreSQL: pgsql/src/backend/optimizer/path/clausesel.c,v 1.86 2007/06/11 01:16:22 tgl Exp $
11+
* $PostgreSQL: pgsql/src/backend/optimizer/path/clausesel.c,v 1.87 2007/08/31 23:35:22 tgl Exp $
1212
*
1313
*-------------------------------------------------------------------------
1414
*/
@@ -219,7 +219,9 @@ clauselist_selectivity(PlannerInfo *root,
219219
s2=rqlist->hibound+rqlist->lobound-1.0;
220220

221221
/* Adjust for double-exclusion of NULLs */
222-
s2+=nulltestsel(root,IS_NULL,rqlist->var,varRelid);
222+
/* HACK: disable nulltestsel's special outer-join logic */
223+
s2+=nulltestsel(root,IS_NULL,rqlist->var,
224+
varRelid,JOIN_INNER);
223225

224226
/*
225227
* A zero or slightly negative s2 should be converted into a
@@ -702,7 +704,8 @@ clause_selectivity(PlannerInfo *root,
702704
s1=nulltestsel(root,
703705
((NullTest*)clause)->nulltesttype,
704706
(Node*) ((NullTest*)clause)->arg,
705-
varRelid);
707+
varRelid,
708+
jointype);
706709
}
707710
elseif (IsA(clause,BooleanTest))
708711
{

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

Lines changed: 15 additions & 2 deletions
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.235 2007/08/21 01:11:18 tgl Exp $
18+
* $PostgreSQL: pgsql/src/backend/utils/adt/selfuncs.c,v 1.236 2007/08/31 23:35:22 tgl Exp $
1919
*
2020
*-------------------------------------------------------------------------
2121
*/
@@ -1385,11 +1385,24 @@ booltestsel(PlannerInfo *root, BoolTestType booltesttype, Node *arg,
13851385
*/
13861386
Selectivity
13871387
nulltestsel(PlannerInfo*root,NullTestTypenulltesttype,
1388-
Node*arg,intvarRelid)
1388+
Node*arg,intvarRelid,JoinTypejointype)
13891389
{
13901390
VariableStatDatavardata;
13911391
doubleselec;
13921392

1393+
/*
1394+
* Special hack: an IS NULL test being applied at an outer join should not
1395+
* be taken at face value, since it's very likely being used to select the
1396+
* outer-side rows that don't have a match, and thus its selectivity has
1397+
* nothing whatever to do with the statistics of the original table
1398+
* column. We do not have nearly enough context here to determine its
1399+
* true selectivity, so for the moment punt and guess at 0.5. Eventually
1400+
* the planner should be made to provide enough info about the clause's
1401+
* context to let us do better.
1402+
*/
1403+
if (IS_OUTER_JOIN(jointype)&&nulltesttype==IS_NULL)
1404+
return (Selectivity)0.5;
1405+
13931406
examine_variable(root,arg,varRelid,&vardata);
13941407

13951408
if (HeapTupleIsValid(vardata.statsTuple))

‎src/include/utils/selfuncs.h

Lines changed: 2 additions & 2 deletions
Original file line numberDiff line numberDiff line change
@@ -8,7 +8,7 @@
88
* Portions Copyright (c) 1996-2007, 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.39 2007/01/22 20:00:40 tgl Exp $
11+
* $PostgreSQL: pgsql/src/include/utils/selfuncs.h,v 1.40 2007/08/31 23:35:22 tgl Exp $
1212
*
1313
*-------------------------------------------------------------------------
1414
*/
@@ -149,7 +149,7 @@ extern Datum icnlikejoinsel(PG_FUNCTION_ARGS);
149149
externSelectivitybooltestsel(PlannerInfo*root,BoolTestTypebooltesttype,
150150
Node*arg,intvarRelid,JoinTypejointype);
151151
externSelectivitynulltestsel(PlannerInfo*root,NullTestTypenulltesttype,
152-
Node*arg,intvarRelid);
152+
Node*arg,intvarRelid,JoinTypejointype);
153153
externSelectivityscalararraysel(PlannerInfo*root,
154154
ScalarArrayOpExpr*clause,
155155
boolis_join_clause,

0 commit comments

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