Re: WIP Patch: Use sortedness of CSV foreign tables for query planning

On Tue, Aug 7, 2012 at 2:02 AM, Etsuro Fujita
<fujita(dot)etsuro(at)lab(dot)ntt(dot)co(dot)jp> wrote:
>> I think that optimizations like this are going to be essential for
>> things like pgsql_fdw (or other_rdms_fdw). Despite the thorny
>> semantic issues, we're just not going to be able to get around it.
>> There will even be people who want SELECT * FROM ft ORDER BY 1 to
>> order by the remote side's notion of ordering rather than ours,
>> despite the fact that the remote side has some insane-by-PG-standards
>> definition of ordering. People are going to find ways to do that kind
>> of thing whether we condone it or not, so we might as well start
>> thinking now about how we're going to live with it. But that doesn't
>> answer the question of whether or not we ought to support it for
>> file_fdw in particular, which seems like a more arguable point.
>
> For file_fdw, I feel inclined to simply implement file_fdw (1) to verify the key
> column is sorted in the specified way at the execution phase ie, at the (first)
> scan of a data file, only when pathkeys are set, and (2) to abort the
> transaction if it detects the data file is not sorted.

That seems like an even worse idea. People who want to access data
repeatedly should load it into tables.

Mind you, if you want to publish a version of file_fdw on PGXN that
does this, that's fine with me. But I don't think it belongs in core,
at least not without a lot more evidence that there is a real demand
for this than we have so far.

--
Robert Haas
EnterpriseDB: http://www.enterprisedb.com
The Enterprise PostgreSQL Company