Re: Proposal: Create index on foreign table

Greg Stark <stark(at)mit(dot)edu> writes:
> On Wed, Mar 21, 2012 at 2:22 PM, Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)us> wrote:
>> 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?

> Surely this will ultimately be necessary though?

At the moment, whether a foreign table has indexes and when to use them
is strictly a private matter between the FDW and the remote server (if
any). The rest of the system doesn't have any need-to-know whatsoever,
and I don't foresee that it ever will, at least not without drastic
redesign of the FDW API.

What's at stake in the current discussion is whether it would be
advantageous to an FDW if we were to store some information about
remote indexes in the local catalogs. It would still be the FDW's
responsibility, and nobody else's, to make use of that information.
I can believe that we might eventually decide to do that; but I do not
think we have enough experience with different sorts of FDWs to define
a good solution today. And I think that most likely a good solution
will *not* conflate such remote-index information with local indexes.

So basically my reaction to Etsuro-san's proposal is "this is
premature". I think he should be hacking together some FDW-private
facilities for individual FDWs instead (with the full understanding
that these might be throwaway prototypes), and then looking for a
common abstraction after he's done a few of those.

regards, tom lane