Re: SQL/MED estimated time of arrival?

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