Re: patch: SQL/MED(FDW) DDL

On Thu, 30 Sep 2010 09:26:54 +0300
Heikki Linnakangas <heikki(dot)linnakangas(at)enterprisedb(dot)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.

In addition, I think that the generic option of a server could be used
to store cost hints which depend on each server, such as network
transfer overhead for dbms wrappers, or disk I/O for file-wrappers.

Regards,
--
Shigeru Hanada