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

Commit3b167a4

Browse files
committed
If a LIMIT is applied to a UNION ALL query, plan each UNION arm as
if the limit were directly applied to it. This does not actuallyadd a LIMIT plan node to the generated subqueries --- that would beuseless overhead --- but it does cause the planner to prefer fast-start plans when the limit is small. After an idea from Phil Endecott.
1 parent39cee73 commit3b167a4

File tree

3 files changed

+178
-118
lines changed

3 files changed

+178
-118
lines changed

‎src/backend/optimizer/plan/planner.c

Lines changed: 127 additions & 104 deletions
Original file line numberDiff line numberDiff line change
@@ -8,7 +8,7 @@
88
*
99
*
1010
* IDENTIFICATION
11-
* $PostgreSQL: pgsql/src/backend/optimizer/plan/planner.c,v 1.188 2005/06/05 22:32:56 tgl Exp $
11+
* $PostgreSQL: pgsql/src/backend/optimizer/plan/planner.c,v 1.189 2005/06/10 02:21:04 tgl Exp $
1212
*
1313
*-------------------------------------------------------------------------
1414
*/
@@ -58,6 +58,8 @@ static Node *preprocess_expression(PlannerInfo *root, Node *expr, int kind);
5858
staticvoidpreprocess_qual_conditions(PlannerInfo*root,Node*jtnode);
5959
staticPlan*inheritance_planner(PlannerInfo*root,List*inheritlist);
6060
staticPlan*grouping_planner(PlannerInfo*root,doubletuple_fraction);
61+
staticdoubleadjust_tuple_fraction_for_limit(PlannerInfo*root,
62+
doubletuple_fraction);
6163
staticboolchoose_hashed_grouping(PlannerInfo*root,doubletuple_fraction,
6264
Path*cheapest_path,Path*sorted_path,
6365
List*sort_pathkeys,List*group_pathkeys,
@@ -648,15 +650,30 @@ grouping_planner(PlannerInfo *root, double tuple_fraction)
648650
List*current_pathkeys;
649651
List*sort_pathkeys;
650652

653+
/* Tweak caller-supplied tuple_fraction if have LIMIT */
654+
if (parse->limitCount!=NULL)
655+
tuple_fraction=adjust_tuple_fraction_for_limit(root,tuple_fraction);
656+
651657
if (parse->setOperations)
652658
{
653659
List*set_sortclauses;
654660

661+
/*
662+
* If there's a top-level ORDER BY, assume we have to fetch all
663+
* the tuples. This might seem too simplistic given all the
664+
* hackery below to possibly avoid the sort ... but a nonzero
665+
* tuple_fraction is only of use to plan_set_operations() when
666+
* the setop is UNION ALL, and the result of UNION ALL is always
667+
* unsorted.
668+
*/
669+
if (parse->sortClause)
670+
tuple_fraction=0.0;
671+
655672
/*
656673
* Construct the plan for set operations. The result will not
657674
* need any work except perhaps a top-level sort and/or LIMIT.
658675
*/
659-
result_plan=plan_set_operations(root,
676+
result_plan=plan_set_operations(root,tuple_fraction,
660677
&set_sortclauses);
661678

662679
/*
@@ -769,108 +786,6 @@ grouping_planner(PlannerInfo *root, double tuple_fraction)
769786
else
770787
root->query_pathkeys=NIL;
771788

772-
/*
773-
* Adjust tuple_fraction if we see that we are going to apply
774-
* limiting/grouping/aggregation/etc. This is not overridable by
775-
* the caller, since it reflects plan actions that this routine
776-
* will certainly take, not assumptions about context.
777-
*/
778-
if (parse->limitCount!=NULL)
779-
{
780-
/*
781-
* A LIMIT clause limits the absolute number of tuples
782-
* returned. However, if it's not a constant LIMIT then we
783-
* have to punt; for lack of a better idea, assume 10% of the
784-
* plan's result is wanted.
785-
*/
786-
doublelimit_fraction=0.0;
787-
788-
if (IsA(parse->limitCount,Const))
789-
{
790-
Const*limitc= (Const*)parse->limitCount;
791-
int32count=DatumGetInt32(limitc->constvalue);
792-
793-
/*
794-
* A NULL-constant LIMIT represents "LIMIT ALL", which we
795-
* treat the same as no limit (ie, expect to retrieve all
796-
* the tuples).
797-
*/
798-
if (!limitc->constisnull&&count>0)
799-
{
800-
limit_fraction= (double)count;
801-
/* We must also consider the OFFSET, if present */
802-
if (parse->limitOffset!=NULL)
803-
{
804-
if (IsA(parse->limitOffset,Const))
805-
{
806-
int32offset;
807-
808-
limitc= (Const*)parse->limitOffset;
809-
offset=DatumGetInt32(limitc->constvalue);
810-
if (!limitc->constisnull&&offset>0)
811-
limit_fraction+= (double)offset;
812-
}
813-
else
814-
{
815-
/* OFFSET is an expression ... punt ... */
816-
limit_fraction=0.10;
817-
}
818-
}
819-
}
820-
}
821-
else
822-
{
823-
/* LIMIT is an expression ... punt ... */
824-
limit_fraction=0.10;
825-
}
826-
827-
if (limit_fraction>0.0)
828-
{
829-
/*
830-
* If we have absolute limits from both caller and LIMIT,
831-
* use the smaller value; if one is fractional and the
832-
* other absolute, treat the fraction as a fraction of the
833-
* absolute value; else we can multiply the two fractions
834-
* together.
835-
*/
836-
if (tuple_fraction >=1.0)
837-
{
838-
if (limit_fraction >=1.0)
839-
{
840-
/* both absolute */
841-
tuple_fraction=Min(tuple_fraction,limit_fraction);
842-
}
843-
else
844-
{
845-
/* caller absolute, limit fractional */
846-
tuple_fraction *=limit_fraction;
847-
if (tuple_fraction<1.0)
848-
tuple_fraction=1.0;
849-
}
850-
}
851-
elseif (tuple_fraction>0.0)
852-
{
853-
if (limit_fraction >=1.0)
854-
{
855-
/* caller fractional, limit absolute */
856-
tuple_fraction *=limit_fraction;
857-
if (tuple_fraction<1.0)
858-
tuple_fraction=1.0;
859-
}
860-
else
861-
{
862-
/* both fractional */
863-
tuple_fraction *=limit_fraction;
864-
}
865-
}
866-
else
867-
{
868-
/* no info from caller, just use limit */
869-
tuple_fraction=limit_fraction;
870-
}
871-
}
872-
}
873-
874789
/*
875790
* With grouping or aggregation, the tuple fraction to pass to
876791
* query_planner() may be different from what it is at top level.
@@ -1242,6 +1157,114 @@ grouping_planner(PlannerInfo *root, double tuple_fraction)
12421157
returnresult_plan;
12431158
}
12441159

1160+
/*
1161+
* adjust_tuple_fraction_for_limit - adjust tuple fraction for LIMIT
1162+
*
1163+
* If the query contains LIMIT, we adjust the caller-supplied tuple_fraction
1164+
* accordingly. This is not overridable by the caller, since it reflects plan
1165+
* actions that grouping_planner() will certainly take, not assumptions about
1166+
* context.
1167+
*/
1168+
staticdouble
1169+
adjust_tuple_fraction_for_limit(PlannerInfo*root,doubletuple_fraction)
1170+
{
1171+
Query*parse=root->parse;
1172+
doublelimit_fraction=0.0;
1173+
1174+
/* Should not be called unless LIMIT */
1175+
Assert(parse->limitCount!=NULL);
1176+
1177+
/*
1178+
* A LIMIT clause limits the absolute number of tuples returned. However,
1179+
* if it's not a constant LIMIT then we have to punt; for lack of a better
1180+
* idea, assume 10% of the plan's result is wanted.
1181+
*/
1182+
if (IsA(parse->limitCount,Const))
1183+
{
1184+
Const*limitc= (Const*)parse->limitCount;
1185+
int32count=DatumGetInt32(limitc->constvalue);
1186+
1187+
/*
1188+
* A NULL-constant LIMIT represents "LIMIT ALL", which we treat the
1189+
* same as no limit (ie, expect to retrieve all the tuples).
1190+
*/
1191+
if (!limitc->constisnull&&count>0)
1192+
{
1193+
limit_fraction= (double)count;
1194+
/* We must also consider the OFFSET, if present */
1195+
if (parse->limitOffset!=NULL)
1196+
{
1197+
if (IsA(parse->limitOffset,Const))
1198+
{
1199+
int32offset;
1200+
1201+
limitc= (Const*)parse->limitOffset;
1202+
offset=DatumGetInt32(limitc->constvalue);
1203+
if (!limitc->constisnull&&offset>0)
1204+
limit_fraction+= (double)offset;
1205+
}
1206+
else
1207+
{
1208+
/* OFFSET is an expression ... punt ... */
1209+
limit_fraction=0.10;
1210+
}
1211+
}
1212+
}
1213+
}
1214+
else
1215+
{
1216+
/* LIMIT is an expression ... punt ... */
1217+
limit_fraction=0.10;
1218+
}
1219+
1220+
if (limit_fraction>0.0)
1221+
{
1222+
/*
1223+
* If we have absolute limits from both caller and LIMIT, use the
1224+
* smaller value; if one is fractional and the other absolute,
1225+
* treat the fraction as a fraction of the absolute value;
1226+
* else we can multiply the two fractions together.
1227+
*/
1228+
if (tuple_fraction >=1.0)
1229+
{
1230+
if (limit_fraction >=1.0)
1231+
{
1232+
/* both absolute */
1233+
tuple_fraction=Min(tuple_fraction,limit_fraction);
1234+
}
1235+
else
1236+
{
1237+
/* caller absolute, limit fractional */
1238+
tuple_fraction *=limit_fraction;
1239+
if (tuple_fraction<1.0)
1240+
tuple_fraction=1.0;
1241+
}
1242+
}
1243+
elseif (tuple_fraction>0.0)
1244+
{
1245+
if (limit_fraction >=1.0)
1246+
{
1247+
/* caller fractional, limit absolute */
1248+
tuple_fraction *=limit_fraction;
1249+
if (tuple_fraction<1.0)
1250+
tuple_fraction=1.0;
1251+
}
1252+
else
1253+
{
1254+
/* both fractional */
1255+
tuple_fraction *=limit_fraction;
1256+
}
1257+
}
1258+
else
1259+
{
1260+
/* no info from caller, just use limit */
1261+
tuple_fraction=limit_fraction;
1262+
}
1263+
}
1264+
1265+
returntuple_fraction;
1266+
}
1267+
12451268
/*
12461269
* choose_hashed_grouping - should we use hashed grouping?
12471270
*/

0 commit comments

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