Re: Proposal: Create index on foreign table

Robert Haas <robertmhaas(at)gmail(dot)com> writes:
> On Wed, Mar 21, 2012 at 4:47 AM, Etsuro Fujita
> <fujita(dot)etsuro(at)lab(dot)ntt(dot)co(dot)jp> wrote:
>> I did an investigation on DB2 a little bit. DB2 uses the CREATE INDEX
>> SPECIFICATION ONLY statement to define the properties of a remote index.
>>
>> CREATE INDEX index_name ON foreintable_name
>> (column_name) SPECIFICATION ONLY
>>
>> How about introducing this kind of option?

> I still don't think it's a good idea to introduce the concept of a
> PostgreSQL index that indexes data not stored in the database.

Well, above Etsuro-san is proposing the other case, ie a Postgres index
definition for an index *not* stored in the database. But frankly
I think both ideas are pretty bad. There's no reason to expect that
Postgres' model of an index will accurately describe the facilities
available in a remote server; and if it's not accurate, is it really
of any use for planning?

There might be reason to store information about remote indexes locally
(although nobody has yet proven that session-local caching isn't
sufficient). But there is very little reason to suppose that that
information will look the same for all different types of remote
servers, and I don't believe we know enough yet to design a common
representation. I think it'd be wise to let the different FDWs solve
this problem on their own for a couple of years, and then we can look
at whether they have common requirements or not.

regards, tom lane