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(at)barrodale(dot)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(at)barrodale(dot)com
**********************************************