We've hit a case where pass_down_bound() isn't pushing the row count limit
from limit into sort. The issue is that we're getting a subquery scan node
between the limit and the sort. The subquery is only doing column
projection and has no quals or SRFs so it should be safe to push the limit
into the sort.
The query that hit the problem can be simplified to:
SELECT * FROM (SELECT A,B FROM T ORDER BY C) LIMIT 5
(Yeah, the query's a little screwy in that the ORDER BY should really be
outside the subselect, but it came from a query generator, so that's a
different conversation.)
Proposed patch is attached.
- Doug
Salesforce
*** a/src/backend/executor/nodeLimit.c
--- b/src/backend/executor/nodeLimit.c
***************
*** 304,309 **** recompute_limits(LimitState *node)
--- 304,311 ----
* since the MergeAppend surely need read no more than that many tuples from
* any one input. We also have to be prepared to look through a Result,
* since the planner might stick one atop MergeAppend for projection purposes.
+ * We can also accept a subquery that has no quals or SRFs (that is, the
+ * subquery is just projecting columns) between the LIMIT and any of the above.
*
* This is a bit of a kluge, but we don't have any more-abstract way of
* communicating between the two nodes; and it doesn't seem worth trying
***************
*** 316,321 **** recompute_limits(LimitState *node)
--- 318,343 ----
static void
pass_down_bound(LimitState *node, PlanState *child_node)
{
+ /*
+ * If the child is a subquery that does no filtering (no predicates)
+ * and does not have any SRFs in the target list then we can potentially
+ * push the limit through the subquery.
+ */
+ if (IsA(child_node, SubqueryScanState))
+ {
+ SubqueryScanState *subqueryScanState = (SubqueryScanState *) child_node;
+
+ /*
+ * Non-empty predicates or an SRF means we cannot push down the limit.
+ */
+ if (subqueryScanState->ss.ps.qual != NULL ||
+ expression_returns_set((Node *) child_node->plan->targetlist))
+ return;
+
+ /* Use the child in the following checks */
+ child_node = subqueryScanState->subplan;
+ }
+
if (IsA(child_node, SortState))
{
SortState *sortState = (SortState *) child_node;
*** a/src/test/regress/expected/subselect.out
--- b/src/test/regress/expected/subselect.out
***************
*** 1041,1043 **** NOTICE: x = 9, y = 13
--- 1041,1077 ----
(3 rows)
drop function tattle(x int, y int);
+ -------------------------------------------------
+ --TEST LIMIT pushdown through subquery scan node
+ -------------------------------------------------
+ create table sq_limit (pk int primary key, c1 int, c2 int);
+ insert into sq_limit values
+ (1, 1, 1),
+ (2, 2, 2),
+ (3, 3, 3),
+ (4, 4, 4),
+ (5, 1, 1),
+ (6, 2, 2),
+ (7, 3, 3),
+ (8, 4, 4);
+ explain (analyze, summary off, timing off, costs off)
+ select * from (select pk,c2 from sq_limit order by c1,pk) as x limit 3;
+ QUERY PLAN
+ ----------------------------------------------------------------
+ Limit (actual rows=3 loops=1)
+ -> Subquery Scan on x (actual rows=3 loops=1)
+ -> Sort (actual rows=3 loops=1)
+ Sort Key: sq_limit.c1, sq_limit.pk
+ Sort Method: top-N heapsort Memory: 25kB
+ -> Seq Scan on sq_limit (actual rows=8 loops=1)
+ (6 rows)
+
+ select * from (select pk,c2 from sq_limit order by c1,pk) as x limit 3;
+ pk | c2
+ ----+----
+ 1 | 1
+ 5 | 1
+ 2 | 2
+ (3 rows)
+
+ drop table sq_limit;
*** a/src/test/regress/sql/subselect.sql
--- b/src/test/regress/sql/subselect.sql
***************
*** 540,542 **** select * from
--- 540,563 ----
where tattle(x, u);
drop function tattle(x int, y int);
+
+ -------------------------------------------------
+ --TEST LIMIT pushdown through subquery scan node
+ -------------------------------------------------
+ create table sq_limit (pk int primary key, c1 int, c2 int);
+ insert into sq_limit values
+ (1, 1, 1),
+ (2, 2, 2),
+ (3, 3, 3),
+ (4, 4, 4),
+ (5, 1, 1),
+ (6, 2, 2),
+ (7, 3, 3),
+ (8, 4, 4);
+
+ explain (analyze, summary off, timing off, costs off)
+ select * from (select pk,c2 from sq_limit order by c1,pk) as x limit 3;
+
+ select * from (select pk,c2 from sq_limit order by c1,pk) as x limit 3;
+
+ drop table sq_limit;
--
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers
