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

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