Re: [PATCH] Push limit to sort through a subquery

On 18.08.2017 04:33, Robert Haas wrote:
>
> It seems like a somewhat ad-hoc approach; it supposes that we can take
> any query produced by deparseSelectStmtForRel() and stick a LIMIT
> clause onto the very end and all will be well. Maybe that's not a
> problematic assumption, not sure. The grammar happens to allow both
> FOR UPDATE LIMIT n and LIMIT n FOR UPDATE even though only the latter
> syntax is documented.
>
> --
> Robert Haas
> EnterpriseDB: http://www.enterprisedb.com
> The Enterprise PostgreSQL Company

I am not absolutely sure that it is possible to append any query which
can be constructed by postgres_fdw for foreign scan with "LIMIT n" clause.
But I also do not know example when it is not possible. As you have
mentioned, "FOR UPDATE LIMIT n" is currently recognized by Postgres.

Can you suggest how to implement limit push down to FDW in better way?
Move deparseSelectStmtForRel() from postgresGetForeignPlan to
postgresIterateForeignScan ?
It seems to be problematic because many information required by
deparseSelectStmtForRel is not available in postgresIterateForeignScan.
In principle, it is possible to somehow propagate it here. But from my
point of view it is not right approach...

IMHO there is some contradiction in Postgres optimizer that static
information about limit is not taken in account at the planning stage
and is actually used only during query execution,
when pass_down_bound() function is called to propagate knowledge about
limit down through plan nodes. Certainly I understand that it gives more
flexibility: we can use information from
previous steps of query execution which was not available at planning stage.

But pushing down limit at planning stage requires too much changes. And
the proposed patch is very small and non-invasive. And in principle, it
can be used not only postgres_fdw, but also in other FDW implementations
to push down information about LIMIT.

--
Konstantin Knizhnik
Postgres Professional: http://www.postgrespro.com
The Russian Postgres Company