Message-ID: <4CA42DAE.8000701@enterprisedb.com>
Date: Thu, 30 Sep 2010 09:26:54 +0300
From: Heikki Linnakangas <heikki.linnakangas@enterprisedb.com>
User-Agent: Mozilla/5.0 (X11; U; Linux x86_64; fi-FI;
	rv:1.9.1.12) Gecko/20100913 Icedove/3.0.7
MIME-Version: 1.0
To: Itagaki Takahiro <itagaki.takahiro@gmail.com>
CC: Alvaro Herrera <alvherre@commandprompt.com>,
	Robert Haas <robertmhaas@gmail.com>,
	SAKAMOTO Masahiko <sakamoto.masahiko@oss.ntt.co.jp>,
	Heikki Linnakangas <heikki.linnakangas@enterprisedb.com>,
	pgsql-hackers <pgsql-hackers@postgresql.org>
Subject: Re: patch: SQL/MED(FDW) DDL
References: <4C905DB0.6040302@oss.ntt.co.jp>
	<AANLkTim62ZKfiJyu7+J7+84rogr=-0eRdHjhgpHYuzMO@mail.gmail.com>
	<4C934741.4070102@oss.ntt.co.jp>
	<AANLkTi=e=3TxsByhxRZ01-VDyc9CEHmmGecexKkJdW5u@mail.gmail.com>
	<AANLkTikrLUzOQxC4CJYM4CDtPqmYUsWewB_63ORnqJ4O@mail.gmail.com>
	<AANLkTimdXSvpK_R=KAvJCEKMUn_G_X+_c5JF7Tr5GP5x@mail.gmail.com>
	<4C9C6B86.3040102@enterprisedb.com>
	<AANLkTimUT-mmgs-nez97=tw9SEhcijJBeQx7=+9VvB8B@mail.gmail.com>
	<AANLkTimcSrQk_bgAa0LSUxZzuWLJ39cmAnMsM6o99Kb0@mail.gmail.com>
	<4CA03EBA.4010203@oss.ntt.co.jp>
	<AANLkTinMHsjiQu_P8D5eRjc3mgWafUZtDV9qVE798NP+@mail.gmail.com>
	<1285768919-sup-3750@alvh.no-ip.org>
	<AANLkTi=dXAzCm8+R03gzkCd_8FJ+9njW_Gj6wHujx3r9@mail.gmail.com>
In-Reply-To: <AANLkTi=dXAzCm8+R03gzkCd_8FJ+9njW_Gj6wHujx3r9@mail.gmail.com>
Content-Type: text/plain; charset=UTF-8; format=flowed
Content-Transfer-Encoding: 7bit

On 30.09.2010 04:30, Itagaki Takahiro wrote:
> On Wed, Sep 29, 2010 at 11:09 PM, Alvaro Herrera
> <alvherre@commandprompt.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