SQL/MED estimated time of arrival?

On Wed, 03 Nov 2010 13:32:18 -0700
Eric Davies <eric@barrodale.com> wrote:

On Informix, we were able to take advantage of the VTI (Virtual Table
Interface) feature to support "table" scans and indexing. (See
http://www.ibm.com/developerworks/data/zones/informix/library/techarticle/db_vti.html
.) Do you have any idea of how long it will be before SQL/MED on
PostgreSQL will be available, and perhaps how similar it will be to
Informix VTI?

SQL/MED is now under discussion/development for PostgreSQL 9.1, and
9.1 would be released one year after 9.0, maybe around Sep 2011? For
detail of release schedule, please see the development plan of
PostgreSQL 9.1.

http://wiki.postgresql.org/wiki/PostgreSQL_9.1_Development_Plan

I looked into VTI documents you've pointed. ISTM that VTI and SQL/MED
would have a lot of common ideas, and most of VTI items would be able
to be mapped to one of SQL/MED items, except features about updating
data and indexing.

For example:

* PRIMARY ACCESS_METHOD -> HANDLER of FOREIGN DATA WRAPPER
* am_scancost() -> FdwRoutine.EstimateCosts()
* am_open() -> FdwRoutine.Open()
* am_beginscan() -> first call of FdwRoutine.Iterate()?
* am_getnext() -> FdwRoutine.Iterate()
* am_rescan() -> FdwRoutine.ReOpen()
* am_close() -> FdwRoutine.Close()
* Table descriptor -> Relation, Form_pg_class
* Qual descriptor -> PlanState.qual

I hope the summary of SQL/MED described in wiki page helps you.

http://wiki.postgresql.org/wiki/SQL/MED

Any comments and questions are welcome.

Regards,
--
Shigeru Hanada

On Thu, Nov 4, 2010 at 6:04 PM, Shigeru HANADA
<hanada@metrosystems.co.jp> wrote:

For example:
* PRIMARY ACCESS_METHOD -> HANDLER of FOREIGN DATA WRAPPER
* am_scancost()         -> FdwRoutine.EstimateCosts()
* am_open()             -> FdwRoutine.Open()
* am_beginscan()        -> first call of FdwRoutine.Iterate()?

It might be good to have a separated "beginscan" method if we use
asynchronous scans in multiple foreign servers in one query
because multiple foreign servers can run their queries in parallel.
(Imagine that pushing-down aggregate function into each foreign server.)
I think it is different from "open" because it is called
before query execution, for example by EXPLAIN.

* am_getnext()          -> FdwRoutine.Iterate()
* am_rescan()           -> FdwRoutine.ReOpen()
* am_close()            -> FdwRoutine.Close()
* Table descriptor      -> Relation, Form_pg_class
* Qual descriptor       -> PlanState.qual

Do you think you have all counterpart methods for VTI AMs?
If so, it's a good news ;-) We could support foreign table
features as same level as Informix.

--
Itagaki Takahiro

On Thu, 4 Nov 2010 18:22:52 +0900
Itagaki Takahiro <itagaki.takahiro@gmail.com> wrote:

On Thu, Nov 4, 2010 at 6:04 PM, Shigeru HANADA
<hanada@metrosystems.co.jp> wrote:

For example:
* PRIMARY ACCESS_METHOD -> HANDLER of FOREIGN DATA WRAPPER
* am_scancost()         -> FdwRoutine.EstimateCosts()
* am_open()             -> FdwRoutine.Open()
* am_beginscan()        -> first call of FdwRoutine.Iterate()?

It might be good to have a separated "beginscan" method if we use
asynchronous scans in multiple foreign servers in one query
because multiple foreign servers can run their queries in parallel.
(Imagine that pushing-down aggregate function into each foreign server.)

You mean that separated beginscan (FdwRoutine.BeginScan?) starts
asynchronous query and returns immediately, and FdwRoutine.Iterate
returns result of that query?

Pushing aggregate down to foreign server would be efficient, but need
another hook which can create one ForeignScan node which have "Agg +
ForeignScan" functionality. Same optimization would be able to apply
for Sort and Limit. Such optimization should be done in optimizer
with estimated costs? Or FDW's hook function may change plan tree
which was created by planner?

I think it is different from "open" because it is called
before query execution, for example by EXPLAIN.

Right, I've misunderstood.

VTI programmer's guide says that am_open is called before processing
SQL to initialize input or output, and called for not only SELECT but
also other queries using a virtual table such as INSERT and DROP TABLE.
The am_open would have no counterpart in SQL/MED.

Do you think you have all counterpart methods for VTI AMs?
If so, it's a good news ;-) We could support foreign table
features as same level as Informix.

Not all, but most of them for read-only access.

VTI supports updating external data and various management tasks via
SQL, but SQL/MED supports (at least in standard) only read access.
The full set of ACCESS_METHOD functions are:

am_create CREATE FOREIGN TABLE
am_drop DROP TABLE

am_stats gather statistics (ANALYZE)
am_check verify data structure and index consistency

am_open initialize access to a virtual table
(might connect to external server)
am_close finalize access to a virtual table

am_scancost estimate cost of a scan
am_beginscan initialize scan
am_getbyid get a tuple by row-id
am_getnext get next tuple(s)
am_rescan reset state of scanning
am_endscan finalize scan

am_insert insert a tuple and return row-id
am_update update a tuple by row-id
am_delete delete a tuple by row-id
am_truncate truncate table

VTI might be similar to storage engine of MySQL or heap-am of PG,
rather than SQL/MED of PG.

Like FOREIGN INDEX of HiRDB, Informix has Virtual Index Interface, and
am_getbyid is used to get a tuple by row-id. I'll research more about
VTI and VII for revising design of SQL/MED.

Regards,
--
Shigeru Hanada

On Fri, Nov 5, 2010 at 4:00 PM, Shigeru HANADA
<hanada@metrosystems.co.jp> wrote:

* am_beginscan()        -> first call of FdwRoutine.Iterate()?

It might be good to have a separated "beginscan" method if we use
asynchronous scans in multiple foreign servers in one query

You mean that separated beginscan (FdwRoutine.BeginScan?) starts
asynchronous query and returns immediately, and FdwRoutine.Iterate
returns result of that query?

Yes. Each BeginScan() in the executor node tree will be called at
the beginning of executor's run. The callback should not block
the caller. OTOH, Iterate() are called at the first time tuples
in the node are required.

PL/Proxy has a similar functionality with RUN ON ALL to start queries
in parallel. So, I think it's a infrastructure commonly required.

--
Itagaki Takahiro

On Fri, 5 Nov 2010 16:27:49 +0900
Itagaki Takahiro <itagaki.takahiro@gmail.com> wrote:

On Fri, Nov 5, 2010 at 4:00 PM, Shigeru HANADA
<hanada@metrosystems.co.jp> wrote:

* am_beginscan()        -> first call of FdwRoutine.Iterate()?

It might be good to have a separated "beginscan" method if we use
asynchronous scans in multiple foreign servers in one query

You mean that separated beginscan (FdwRoutine.BeginScan?) starts
asynchronous query and returns immediately, and FdwRoutine.Iterate
returns result of that query?

Yes. Each BeginScan() in the executor node tree will be called at
the beginning of executor's run. The callback should not block
the caller. OTOH, Iterate() are called at the first time tuples
in the node are required.

Thanks, now I see your point. Current FdwRoutine has no appropriate
function because Open is called from ExecutorStart which is used by
EXPLAIN too.

But then we have mismatch between executor node interface and FDW
interface about BeginScan. Should we add new function such as
ExecBeginNode and call ExecBeginXXX for each plan node?

New Query Processing Control Flow would be:
# based on README of executor directory

CreateQueryDesc

ExecutorStart
CreateExecutorState
creates per-query context
switch to per-query context to run ExecInitNode
ExecInitNode --- recursively scans plan tree
CreateExprContext
creates per-tuple context
ExecInitExpr

ExecutorRun
ExecBeginNode(new) --- recursively scans plan tree
call ExecBeginXXXS for each plan node
ExecProcNode --- recursively called in per-query context
ExecEvalExpr --- called in per-tuple context
ResetExprContext --- to free memory

ExecutorEnd
ExecEndNode --- recursively releases resources
FreeExecutorState
frees per-query context and child contexts

FreeQueryDesc

PL/Proxy has a similar functionality with RUN ON ALL to start queries
in parallel. So, I think it's a infrastructure commonly required.

I noticed the lack of consideration about cache invalidation from
reading PL/Proxy source, thanks for your mention about PL/Proxy. :-)

Regards,
--
Shigeru Hanada

Shigeru HANADA <hanada@metrosystems.co.jp> writes:

Thanks, now I see your point. Current FdwRoutine has no appropriate
function because Open is called from ExecutorStart which is used by
EXPLAIN too.

But then we have mismatch between executor node interface and FDW
interface about BeginScan. Should we add new function such as
ExecBeginNode and call ExecBeginXXX for each plan node?

That seems like a massive amount of new code, and wasted cycles during
every query startup, to fix a very small problem.

There's a flag EXEC_FLAG_EXPLAIN_ONLY that tells node Init functions
whether the query is going to be run "for real" or only EXPLAINed.
Use that to decide whether to do any real work.

regards, tom lane

2010/11/5 Shigeru HANADA <hanada@metrosystems.co.jp>:

On Fri, 5 Nov 2010 16:27:49 +0900
Itagaki Takahiro <itagaki.takahiro@gmail.com> wrote:

PL/Proxy has a similar functionality with RUN ON ALL to start queries
in parallel. So, I think it's a infrastructure commonly required.

I noticed the lack of consideration about cache invalidation from
reading PL/Proxy source, thanks for your mention about PL/Proxy. :-)

And if we really make this async query come true, I suggest designing
resource (i.e. remote connection) management very carefully. When the
executor fails in the middle of its execution, it possibly fails to
release its own resource; close() in ExecutorEnd() will never be
called. As far as I know files and memory are released automatically
in the current mechanism, but MED APIs will use their own resources
other than them.

Regards,

--
Hitoshi Harada

Hitoshi Harada <umi.tanuki@gmail.com> writes:

And if we really make this async query come true, I suggest designing
resource (i.e. remote connection) management very carefully. When the
executor fails in the middle of its execution, it possibly fails to
release its own resource; close() in ExecutorEnd() will never be
called. As far as I know files and memory are released automatically
in the current mechanism, but MED APIs will use their own resources
other than them.

The way to fix that is for the FDW to hook into the ResourceOwner
mechanism (via RegisterResourceReleaseCallback). Then it can track
and clean up things it knows about just as "automatically" as anything
else is.

Of course, if you lose your network connection to the remote DB,
you have to assume it will clean up of its own accord.

regards, tom lane

On Fri, 05 Nov 2010 10:43:45 -0400
Tom Lane <tgl@sss.pgh.pa.us> wrote:

Shigeru HANADA <hanada@metrosystems.co.jp> writes:

Thanks, now I see your point. Current FdwRoutine has no appropriate
function because Open is called from ExecutorStart which is used by
EXPLAIN too.

But then we have mismatch between executor node interface and FDW
interface about BeginScan. Should we add new function such as
ExecBeginNode and call ExecBeginXXX for each plan node?

That seems like a massive amount of new code, and wasted cycles during
every query startup, to fix a very small problem.

Agreed.

There's a flag EXEC_FLAG_EXPLAIN_ONLY that tells node Init functions
whether the query is going to be run "for real" or only EXPLAINed.
Use that to decide whether to do any real work.

I missed that flag. That flag would make ExecInitForeignScan be able
to skip calling FdwRoutine.BeginScan when the query was an EXPLAIN
without ANALYZE. Thanks for the suggestion.

Regards,
--
Shigeru Hanada

On Sat, 6 Nov 2010 16:04:37 +0900
Hitoshi Harada <umi.tanuki@gmail.com> wrote:

2010/11/5 Shigeru HANADA <hanada@metrosystems.co.jp>:

On Fri, 5 Nov 2010 16:27:49 +0900
Itagaki Takahiro <itagaki.takahiro@gmail.com> wrote:

PL/Proxy has a similar functionality with RUN ON ALL to start queries
in parallel. So, I think it's a infrastructure commonly required.

I noticed the lack of consideration about cache invalidation from
reading PL/Proxy source, thanks for your mention about PL/Proxy. :-)

And if we really make this async query come true, I suggest designing
resource (i.e. remote connection) management very carefully. When the
executor fails in the middle of its execution, it possibly fails to
release its own resource; close() in ExecutorEnd() will never be
called. As far as I know files and memory are released automatically
in the current mechanism, but MED APIs will use their own resources
other than them.

Yes, managegement of FDW's resources is very important issue. Curren
FdwRoutine includes ConnectServer and FreeFSConnection, but they might
not be enough to manage FDW's resources by backend in common way.
Because connection is not only resource FDW use. Possible resources
are:

- Files (Virtual File descriptor would help to manage)
- Database connections (might be cached)
- Server-side cursors (would be released with DB connection?)
- Heap memory (for instance, libpq uses malloc)

For example, if postgresql_fdw uses server-side cursor to retreive
result tuples, it would be required to CLOSE cursors at the end of
transaction. Closing cursor at the end of session wouldn't be good
idea because clients might pool and reuse connections.

How about removing them, ConnectServer and FreeFSConnection, from
FdwRoutine and leaving the responsibility of resource management to
each FDW? Each FDW would have to use mechanism such as Virtual File
and ResourceOwner to manage resources properly, though.

Regards,
--
Shigeru Hanada

On Fri, 5 Nov 2010 16:27:49 +0900
Itagaki Takahiro <itagaki.takahiro@gmail.com> wrote:

On Fri, Nov 5, 2010 at 4:00 PM, Shigeru HANADA
<hanada@metrosystems.co.jp> wrote:

* am_beginscan()        -> first call of FdwRoutine.Iterate()?

It might be good to have a separated "beginscan" method if we use
asynchronous scans in multiple foreign servers in one query

You mean that separated beginscan (FdwRoutine.BeginScan?) starts
asynchronous query and returns immediately, and FdwRoutine.Iterate
returns result of that query?

Yes. Each BeginScan() in the executor node tree will be called at
the beginning of executor's run. The callback should not block
the caller. OTOH, Iterate() are called at the first time tuples
in the node are required.

Please find attached WIP patch for BeginScan. Postgresql_fdw has been
changed to use server-side cursor for sample. It's DECLAREd with HOLD
option to avoid transaction management, though.

Other changes since 20101025 patch are:

- Some document fixes.
- Don't call ConnectServer from ExecInitForeignScan. Instead,
postgresql_fdw calls it from pgOpen(). This change is only trial
and would be removed later.
- Add "schema" column to output of \det psql command.
- New \dE psql command shows list of foreign tables in \d format.
- \d+ <foreign table> psql command shows per-column options.

If the changes (at least adding BeginScan) are OK, I'll clean the
patch up and post it soon.

Regards,
--
Shigeru Hanada

Shigeru HANADA <hanada@metrosystems.co.jp> writes:

How about removing them, ConnectServer and FreeFSConnection, from
FdwRoutine and leaving the responsibility of resource management to
each FDW? Each FDW would have to use mechanism such as Virtual File
and ResourceOwner to manage resources properly, though.

For the most part, we expect that ResourceOwners only do something
useful during error cleanup. That is, you *should* have a
close-connection type of function that is expected to be called during
normal query shutdown. The ResourceOwner hooks will operate to
compensate for the lack of this call in an error recovery case.
The reason for doing things that way is so that we can check for
unintentional resource leakage in the non-error code paths.

regards, tom lane

On Mon, 08 Nov 2010 10:31:22 -0500
Tom Lane <tgl@sss.pgh.pa.us> wrote:

Shigeru HANADA <hanada@metrosystems.co.jp> writes:

How about removing them, ConnectServer and FreeFSConnection, from
FdwRoutine and leaving the responsibility of resource management to
each FDW? Each FDW would have to use mechanism such as Virtual File
and ResourceOwner to manage resources properly, though.

For the most part, we expect that ResourceOwners only do something
useful during error cleanup. That is, you *should* have a
close-connection type of function that is expected to be called during
normal query shutdown. The ResourceOwner hooks will operate to
compensate for the lack of this call in an error recovery case.
The reason for doing things that way is so that we can check for
unintentional resource leakage in the non-error code paths.

I fixed postgresql_fdw to use RegisterResourceReleaseCallback() to
close all connections in error cases including user interrupt. But
I'm not sure if I used the mechanism correctly because all I could
find about the API was only few documents, README of resowner and
function comments. I tested the codes in cases below and confirmed
that all connections have been closed.

- remote query error with wrong relation name
- user interrupt, Ctrl+C on psql during long query

Of course, in non-error case, all connections are closed via normal
query shutdown path.

During fixing connection cleanup, I've removed connection pooling
from postgresql_fdw to make resource management simple. Now
postgresql_fdw uses one connection for all of ForeignScan nodes in a
local query, but doesn't keep the connection alive beyond queries.
Originally, sharing connection is intended to execute multiple remote
query in a transaction for consistency.

I think external tools such as pgpool or pgbouncer would be better to
pool connections. Is it reasonable?

Also, ExecInitForeignScan() was fixed to call ConnectServer() and
BeginScan() only when the EXEC_FLAG_EXPLAIN_ONLY is not set.

Regards,
--
Shigeru Hanada

Hi Gentlemen,

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? What
would be the equivalent of Informix's row ids?

Eric.

**********************************************
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
**********************************************

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

On Mon, Nov 15, 2010 at 12:41, Shigeru HANADA <hanada@metrosystems.co.jp> wrote:

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.

I think the reason is the SQL standard never mention about indexes.
It is not a specific issue for SQL/MED.

 To support indexing foreign tables might need to change
common structure of index tuple to be able to hold virtual row-id, not
ItemPointerData.

I'm not sure we actually need foreign indexes because the query text
sent to another server is same whether the foreign table has indexes.
Of course, foreign indexes might be useful to calculate costs to scan
foreign tables, but the cost also comes from non-index conditions.

I think foreign table and foreign index are a model for row-based
databases, including postgres. But other DBs might have different
cost models. So, it would be better to encapsulate such operations in FDW.

--
Itagaki Takahiro

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
**********************************************

Thanks for the information about Informix VTI. Because I'm not
familiar to Informix, I might have missed your point. Would you mind
telling me more about Informix VTI?

On Mon, 15 Nov 2010 08:45:14 -0800
Eric Davies <eric@barrodale.com> wrote:

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.

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?

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.

How can Informix server optimize such kind of query? Counts the index
tuple which match the WHERE clause? 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?

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.

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()?

Regards,
--
Shigeru Hanada

At 01:36 AM 11/16/2010, Shigeru HANADA wrote:

Thanks for the information about Informix VTI. Because I'm not
familiar to Informix, I might have missed your point. Would you mind
telling me more about Informix VTI?

On Mon, 15 Nov 2010 08:45:14 -0800
Eric Davies <eric@barrodale.com> wrote:

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.

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.

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.

How can Informix server optimize such kind of query? Counts the index
tuple which match the WHERE clause?

That would be my assumption.

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.

Here is a clipping from one of the Informix manuals on the topic:
The way that the optimizer chooses to read a table is called an
access plan. The simplest method to access a table is to read it
sequentially, which is called a table scan. The optimizer chooses a
table scan when most of the table must be read or the table does not
have an index that is useful for the query.
The optimizer can also choose to access the table by an index. If the
column in the index is the same as a column in a filter of the query,
the optimizer can use the index to retrieve only the rows that the
query requires. The optimizer can use a key-only index scan if the
columns requested are within one index on the table. The database
server retrieves the needed data from the index and does not access
the associated table.
Important:
The optimizer does not choose a key-only scan for a VARCHAR column.
If you want to take advantage of key-only scans, use the ALTER TABLE
with the MODFIY clause to change the column to a CHAR data type.
The optimizer compares the cost of each plan to determine the best
one. The database server derives cost from estimates of the number of
I/O operations required, calculations to produce the results, rows
accessed, sorting, and so forth.

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.

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.

Regards,
--
Shigeru Hanada

Cheers,
Eric.

**********************************************
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
**********************************************