Re: SQL/MED estimated time of arrival?

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