Skip to content

Commit 1f6d515

Browse files
robertmhaasrobertmhaas
committed
Push limit through subqueries to underlying sort, where possible.
Douglas Doole, reviewed by Ashutosh Bapat and by me. Minor formatting change by me. Discussion: http://postgr.es/m/CADE5jYLuugnEEUsyW6Q_4mZFYTxHxaVCQmGAsF0yiY8ZDggi-w@mail.gmail.com
1 parent 79ccd7c commit 1f6d515

File tree

3 files changed

+124
-0
lines changed

3 files changed

+124
-0
lines changed

‎src/backend/executor/nodeLimit.c

+26
Original file line numberDiff line numberDiff line change
@@ -308,6 +308,9 @@ recompute_limits(LimitState *node)
308308
* since the MergeAppend surely need read no more than that many tuples from
309309
* any one input. We also have to be prepared to look through a Result,
310310
* since the planner might stick one atop MergeAppend for projection purposes.
311+
* We can also accept one or more levels of subqueries that have no quals or
312+
* SRFs (that is, each subquery is just projecting columns) between the LIMIT
313+
* and any of the above.
311314
*
312315
* This is a bit of a kluge, but we don't have any more-abstract way of
313316
* communicating between the two nodes; and it doesn't seem worth trying
@@ -320,6 +323,29 @@ recompute_limits(LimitState *node)
320323
static void
321324
pass_down_bound(LimitState *node, PlanState *child_node)
322325
{
326+
/*
327+
* If the child is a subquery that does no filtering (no predicates)
328+
* and does not have any SRFs in the target list then we can potentially
329+
* push the limit through the subquery. It is possible that we could have
330+
* multiple subqueries, so tunnel through them all.
331+
*/
332+
while (IsA(child_node, SubqueryScanState))
333+
{
334+
SubqueryScanState *subqueryScanState;
335+
336+
subqueryScanState = (SubqueryScanState *) child_node;
337+
338+
/*
339+
* Non-empty predicates or an SRF means we cannot push down the limit.
340+
*/
341+
if (subqueryScanState->ss.ps.qual != NULL ||
342+
expression_returns_set((Node *) child_node->plan->targetlist))
343+
return;
344+
345+
/* Use the child in the following checks */
346+
child_node = subqueryScanState->subplan;
347+
}
348+
323349
if (IsA(child_node, SortState))
324350
{
325351
SortState *sortState = (SortState *) child_node;

‎src/test/regress/expected/subselect.out

+52
Original file line numberDiff line numberDiff line change
@@ -1041,3 +1041,55 @@ NOTICE: x = 9, y = 13
10411041
(3 rows)
10421042

10431043
drop function tattle(x int, y int);
1044+
--
1045+
-- Test that LIMIT can be pushed to SORT through a subquery that just
1046+
-- projects columns
1047+
--
1048+
create table sq_limit (pk int primary key, c1 int, c2 int);
1049+
insert into sq_limit values
1050+
(1, 1, 1),
1051+
(2, 2, 2),
1052+
(3, 3, 3),
1053+
(4, 4, 4),
1054+
(5, 1, 1),
1055+
(6, 2, 2),
1056+
(7, 3, 3),
1057+
(8, 4, 4);
1058+
-- The explain contains data that may not be invariant, so
1059+
-- filter for just the interesting bits. The goal here is that
1060+
-- we should see three notices, in order:
1061+
-- NOTICE: Limit
1062+
-- NOTICE: Subquery
1063+
-- NOTICE: Top-N Sort
1064+
-- A missing step, or steps out of order means we have a problem.
1065+
do $$
1066+
declare x text;
1067+
begin
1068+
for x in
1069+
explain (analyze, summary off, timing off, costs off)
1070+
select * from (select pk,c2 from sq_limit order by c1,pk) as x limit 3
1071+
loop
1072+
if (left(ltrim(x), 5) = 'Limit') then
1073+
raise notice 'Limit';
1074+
end if;
1075+
if (left(ltrim(x), 12) = '-> Subquery') then
1076+
raise notice 'Subquery';
1077+
end if;
1078+
if (left(ltrim(x), 18) = 'Sort Method: top-N') then
1079+
raise notice 'Top-N Sort';
1080+
end if;
1081+
end loop;
1082+
end;
1083+
$$;
1084+
NOTICE: Limit
1085+
NOTICE: Subquery
1086+
NOTICE: Top-N Sort
1087+
select * from (select pk,c2 from sq_limit order by c1,pk) as x limit 3;
1088+
pk | c2
1089+
----+----
1090+
1 | 1
1091+
5 | 1
1092+
2 | 2
1093+
(3 rows)
1094+
1095+
drop table sq_limit;

‎src/test/regress/sql/subselect.sql

+46
Original file line numberDiff line numberDiff line change
@@ -540,3 +540,49 @@ select * from
540540
where tattle(x, u);
541541

542542
drop function tattle(x int, y int);
543+
544+
--
545+
-- Test that LIMIT can be pushed to SORT through a subquery that just
546+
-- projects columns
547+
--
548+
create table sq_limit (pk int primary key, c1 int, c2 int);
549+
insert into sq_limit values
550+
(1, 1, 1),
551+
(2, 2, 2),
552+
(3, 3, 3),
553+
(4, 4, 4),
554+
(5, 1, 1),
555+
(6, 2, 2),
556+
(7, 3, 3),
557+
(8, 4, 4);
558+
559+
-- The explain contains data that may not be invariant, so
560+
-- filter for just the interesting bits. The goal here is that
561+
-- we should see three notices, in order:
562+
-- NOTICE: Limit
563+
-- NOTICE: Subquery
564+
-- NOTICE: Top-N Sort
565+
-- A missing step, or steps out of order means we have a problem.
566+
do $$
567+
declare x text;
568+
begin
569+
for x in
570+
explain (analyze, summary off, timing off, costs off)
571+
select * from (select pk,c2 from sq_limit order by c1,pk) as x limit 3
572+
loop
573+
if (left(ltrim(x), 5) = 'Limit') then
574+
raise notice 'Limit';
575+
end if;
576+
if (left(ltrim(x), 12) = '-> Subquery') then
577+
raise notice 'Subquery';
578+
end if;
579+
if (left(ltrim(x), 18) = 'Sort Method: top-N') then
580+
raise notice 'Top-N Sort';
581+
end if;
582+
end loop;
583+
end;
584+
$$;
585+
586+
select * from (select pk,c2 from sq_limit order by c1,pk) as x limit 3;
587+
588+
drop table sq_limit;

0 commit comments

Comments
 (0)