Re: SQL/MED estimated time of arrival?

Thanks for the additional information!

On Tue, 16 Nov 2010 09:31:43 -0800
Eric Davies <eric@barrodale.com> wrote:
> At 01:36 AM 11/16/2010, Shigeru HANADA wrote:
> >On Mon, 15 Nov 2010 08:45:14 -0800
> >Eric Davies <eric@barrodale.com> wrote:
> >ISTM that index on a VTI table could be inconsistent when original
> >(remote) data was changed in the way other than VTI.  Is it assumed
> >that the data source is never updated without VTI interface?
> 
> Yes, the data sources are assumed to updated only through the VTI interface.
> With our UFI product, the data sources are assumed to be unchanging 
> files, you'd need to re-index them if they changed.

ISTM that it would be difficult for SQL/MED to assume that external
data is unchangeable.  Also I'm not sure that index types which
PostgreSQL has currently are useful for external data.  So I think
that indexes for external data would be better to be encapsulated into
FDWs.

> >  If so, such optimization seems to
> >be limited to "count" and wouldn't be able to be useful for "max" or
> >"sum".  Or, specialized index or VTI class is responsible to the
> >optimization?
> 
> If there is an index on the column you want to sum/min/max, and your 
> where clause restricts the query to a particular set of rows based on 
> that index, Informix can get the values for that column from the 
> index (which it needed to scan anyhow) without looking at the table. 
> This isn't particular to VTI, it's just a clever use of indexes.
<snip>

The optimization in Informix is specific to "key-only scan" but not to
VTI, isn't it?  Then, it would be better to implement such kind of
optimization independently (separated from SQL/MED).  I found "Index
Only Scan" thread in pgsql-hackers, but I don't know its conclusion...

> >I couldn't see the way to handle virtual table with more than 4
> >billion rows with 32 bit rowids in local index.  Do you mean that your
> >"btree-like index" searches result rows by predicates directly and
> >skips getbyid()?
> 
> Exactly. Our own "rowids" can be up to 64 bits but are never seen by 
> Informix. As far as Informix is concerned, it's a regular table scan 
> because the use of our indexes is hidden.

The design of pushing down predicates which you've taken might be
similar to the one in the FDW for PostgreSQL.  FDW for PostgreSQL
generates WHERE clause from predicates and restrict the tuples to be
returned.  I think that you can port your VTI class to SQL/MED as a
FDW for indexed-files, without local index.

Regards,
--
Shigeru Hanada