Re: Proposal: Create index on foreign table

2012/3/16 Etsuro Fujita <fujita(dot)etsuro(at)lab(dot)ntt(dot)co(dot)jp>:
> I have a plan to support 'Create index on foreign table' for 9.3.  Here
> is my plan.

Very interesting idea, but...

> The index creation is supported for a flat file such as CSV and a remote
> table on a RDB e.g., Postgres using CREATE INDEX.

Why do you limit the target type to those two? How about web
services and non-relational databases? Some web services would
provide id-to-content mapping, and KVSs are obviously accessible by
key. IMHO CREATE INDEX for foreign tables should have general design,
not specific to some kind of FDWs.

> I'd like to build the index physical data file for a flat file using the
> index access method of regular tables (ie btree, hash, gin, gist, and
> spgist) based on the following transformation between the TID and the
> file offset to some data in the file:
>
>    block_number  = file_offset_to_some_data / BLCKSZ
>    offset_number = file_offset_to_some_data % BLCKSZ

Indeed these information would help searching data stored in local
files. But, again, it seems too specific to file-based FDWs. I'd
suggest separating basic general design and implementation by FDWs.
The design you shown here seems indexed-file_fdw to me...

Regards,
--
Shigeru HANADA