Skip to content

Commit 3b167a4

Browse files
tglsfdctglsfdc
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 actually add a LIMIT plan node to the generated subqueries --- that would be useless overhead --- but it does cause the planner to prefer fast- start plans when the limit is small. After an idea from Phil Endecott.
1 parent 39cee73 commit 3b167a4

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
static void preprocess_qual_conditions(PlannerInfo *root, Node *jtnode);
5959
static Plan *inheritance_planner(PlannerInfo *root, List *inheritlist);
6060
static Plan *grouping_planner(PlannerInfo *root, double tuple_fraction);
61+
static double adjust_tuple_fraction_for_limit(PlannerInfo *root,
62+
double tuple_fraction);
6163
static bool choose_hashed_grouping(PlannerInfo *root, double tuple_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-
double limit_fraction = 0.0;
787-
788-
if (IsA(parse->limitCount, Const))
789-
{
790-
Const *limitc = (Const *) parse->limitCount;
791-
int32 count = 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-
int32 offset;
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-
else if (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
return result_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+
static double
1169+
adjust_tuple_fraction_for_limit(PlannerInfo *root, double tuple_fraction)
1170+
{
1171+
Query *parse = root->parse;
1172+
double limit_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+
int32 count = 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+
int32 offset;
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+
else if (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+
return tuple_fraction;
1266+
}
1267+
12451268
/*
12461269
* choose_hashed_grouping - should we use hashed grouping?
12471270
*/

0 commit comments

Comments
 (0)