Re: SQL/MED estimated time of arrival?

With Informix VTI, indexing is the same for native tables as for 
virtual tables, except the interpretation of the 32 bit rowid is left 
up to the developer. When you define the VTI class, you optionally 
supply a method that can fetch data based on a 32 bit rowid, and it's 
the responsibility of your non-indexed scanning methods to provide 
rowids along with the row tuple.

Having local indexes can be very useful if you have a user that 
issues queries like:
    select count(*) from some_external_table where .... ;
With VTI, the "count" aggregate doesn't get pushed down, meaning that 
without a local index, your scanning method has to return as many 
tuples as match the where clause, which can be very slow.

Local indexes also affords the opportunity of using specialized 
indexes built into the database. My guess is that without some form 
of rowids being passed back and forth, you couldn't define 
non-materialized views of virtual tables that could be indexed.

That said, we implemented our own btree-like index that used the 
pushed down predicates because fetching data one row at a time wasn't 
desirable with our design choices, and we wanted to support virtual 
tables with more than 4 billion rows.

Eric
At 07:41 PM 11/14/2010, Shigeru HANADA wrote:
>On Fri, 12 Nov 2010 08:27:54 -0800
>Eric Davies <eric@barrodale.com> wrote:
> > Thank you for the time estimate and the interface discussion. It
> > sounds like the PostgreSQL SQL/MED code will be very useful when it
> > is done. Our product provides read-only access to files, so
> > updates/inserts/deletes aren't an issue for us.
> >
> > One thing that is not clear to me is indexing support. Will it be
> > possible to index a SQL/MED table as if it were a regular table?
>
>No, SQL/MED would not support indexing foreign tables, at least in
>first version.  Because it would be difficult to use common row id for
>various FDWs.  To support indexing foreign tables might need to change
>common structure of index tuple to be able to hold virtual row-id, not
>ItemPointerData.
>
>Instead, FDW can handle expressions which are parsed from WHERE clause
>and JOIN condition of original SQL, and use them to optimize scanning.
>For example, FDW for PostgreSQL pushes some conditions down to remote
>side to decrease result tuples to be transferred.  I hope this idea
>helps you.
>
> >                                                                  What
> > would be the equivalent of Informix's row ids?
>
>Answer to the second question would be "ItemPointerData".  It consists
>of a block number and an offset in the block, and consume 6 bytes for
>each tuple.  With this information, PostgreSQL can access to a data
>tuple directly.  Actual definition is:
>
>typedef struct ItemPointerData
>{
>     BlockIdData ip_blkid;
>     OffsetNumber ip_posid;
>} ItemPointer;
>
>Does Informix uses common row-id (AFAIK it's 4 bytes integer) for
>both of virtual tables and normal tables?
>
>Regards,
>--
>Shigeru Hanada

**********************************************
Eric Davies, M.Sc.
Senior Programmer Analyst
Barrodale Computing Services Ltd.
1095 McKenzie Ave., Suite 418
Victoria BC V8P 2L5
Canada

Tel: (250) 704-4428
Web: http://www.barrodale.com
Email: eric@barrodale.com
**********************************************