Re: patch: SQL/MED(FDW) DDL

On Thu, Sep 30, 2010 at 3:48 AM, Shigeru HANADA
<hanada@metrosystems.co.jp> wrote:
> On Thu, 30 Sep 2010 09:26:54 +0300
> Heikki Linnakangas <heikki.linnakangas@enterprisedb.com> wrote:
>> > FYI, HiRDB, that implements FDW routines, has CREATE FOREIGN INDEX.
>> > I think it is a little ugly and won't work in some cases -- for example,
>> > index organized tables -- but evidently it's a realistic solution.
>>
>> A long time ago I used DB2's federated database feature, which is at
>> least close to SQL/MED if not fully compatible. When you create a
>> "federated index" there. it's just a planner hint to the local database,
>> so that it knows how expensive it is to evaluate a qual remotely vs.
>> locally. It shouldn't matter what technology the remote index uses in
>> that case, as long as the cost model is roughly the same as a b-tree.
>>
>> I don't think we want to go down that path though, it's better to leave
>> the cost estimation altogether to the wrapper. It has much better
>> knowledge of expensive various quals are.
>>
>> However, the wrapper will likely need some local storage for indexes and
>> like to do the cost estimation. Or maybe it can just keep the
>> information in cache, loading it on first use from the remote database.
> How about having cost hints in generic option of the foreign table or
> its columns?  Generic options are storage for wrappers, not for
> PostgreSQL core modules.  Wrappers can use their own format to
> represent various information, and use the hints to estimate costs of
> a path.

I do think we're going to need some kind of local caching of relevant
information from the foreign side.  Really, I doubt that fdwoptions
are the place for that, though: that's data for the user to input, not
a place for the wrapper to scribble on internally.  The trick is that
there's a lot of stuff you might want to cache, and we don't really
know anything about what the format of it is - for example, you might
have foreign-side statistics that need to get cached locally, but they
needn't be in the same format we use for pg_statistic.  Perhaps part
of setting up an FDW should be creating tables with prespecified
definitions and passing the table names to CREATE FOREIGN DATA WRAPPER
as options.  Maybe we could even arrange to set up the dependencies
appropriately...

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