Re: SQL/MED estimated time of arrival?

On Thu, 4 Nov 2010 18:22:52 +0900
Itagaki Takahiro <itagaki(dot)takahiro(at)gmail(dot)com> wrote:
> On Thu, Nov 4, 2010 at 6:04 PM, Shigeru HANADA
> <hanada(at)metrosystems(dot)co(dot)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