Re: patch: SQL/MED(FDW) DDL

On 30.09.2010 04:30, Itagaki Takahiro wrote:
> On Wed, Sep 29, 2010 at 11:09 PM, Alvaro Herrera
> <alvherre(at)commandprompt(dot)com> wrote:
>> I'm not sure that it's a good idea to embed into the FDW API the set of
>> operations known to the executor. For example your proposal fails to
>> consider bitmap scans. Seems simpler and more general to hand the quals
>> over saying "I need to scan this relation with these quals", and have it
>> return an opaque iterable object;
>
> Agreed. If possible, we will avoid dedicated interfaces for seqscans and
> index scans. However, bitmap scan is difficult anyway because foreign tables
> might not have ctid columns. It's a challenging task to identify individual
> tuples in foreign tables. It will be also used for UPDATE and DELETE.
>
>> There doesn't to be much point in knowing the names of remote indexes
>> either (if it came to referencing them, better use OIDs)
>
> 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.

--
Heikki Linnakangas
EnterpriseDB http://www.enterprisedb.com