Horton

[PATCH] Push limit to sort through a subquery

Douglas Doole <dougdoole@gmail.com>

View thread View on postgr.es
From: Douglas Doole <dougdoole@gmail.com>
To: PostgreSQL Hackers <pgsql-hackers@postgresql.org>
Date: 2017-04-19T00:39:48Z
Lists: pgsql-hackers

Attachments

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

Commits

  1. Propagate sort instrumentation from workers back to leader.

  2. Push tuple limits through Gather and Gather Merge.

  3. Code review for pushing LIMIT through subqueries.

  4. Make new regression test case parallel-safe, and improve its output.

  5. Push limit through subqueries to underlying sort, where possible.

  6. Move targetlist SRF handling from expression evaluation to new executor node.