Lookaside

Purpose

The purpose of LOOKASIDE is to inform planner existence of alternative scan/join path on particular tables. Once planner know these alternative scan/join paths, it can evaluate which is the cheapest path to run, in addition to the built-in logic.

It is primarily considered to redirect to materialized-view transparently on reference to the source relations. But not all, Simon introduced several potential use cases.

• KaiGai's work - https://wiki.postgresql.org/wiki/PGStrom
• http://www.postgresql.org/message-id/52C59858.9090500@garret.ru
• http://citusdata.github.io/cstore_fdw/
• University of Manchester - exploring GPUs as part of the AXLE project
• Barcelona SuperComputer Centre - exploring FPGAs, as part of the AXLE project
• Some other authors have also cited gains using GPU technology in databases

Syntax

CREATE LOOKASIDE <name> ON <target relation>
  TO <redirect relation>;

CREATE LOOKASIDE <name> ON <target relation>
  EXECUTE <path-generator function>;

FDW enhancement

• Replacement of Join/Aggregate by ForeignScan
  • Existing FDW mechanism does not support to replace Join node(s) by ForeignScan. Remote-join is a long-standing requirement for FDW, not only LOOKASIDE feature.
  • Also, Simon is planning to look at Aggregate replacement by ForeignScan.

• ForeignScan without foreign-table
  • Existing ForeignScan assumes it performs in front of a particular foreign table; that means a cataloged relation is required. It potentially makes a trouble if we try to replace Join (or something other) nodes without cataloged relation by ForeignScan. So, KaiGai think we need to eliminate these restriction around ForeignScan.
  • Simon proposed to define foreign-table for each regular table, using event trigger. However, Stephen does not like this idea, and KaiGai worried about how does it works on Join.

• MultiExec method support
  • Data exchange cost between exec-nodes is not ignorable. In case when two (or more) ForeignScan nodes are stacking, and their internal data format does not fit usual TupleTableSlot, it makes sense to exchange data as-is.
  • Existing MultiExec method in executor supports data exchange using custom data type on responsibility of parent and child node.
    • Stephen commented, asynchronous data exchange is also important, not only bulk data-exchange.

• ForeignScan with SubPlans
  • If an extension support its special logic to join relations, but don't want to support various method to scan relations, it is natural to leverage built-in scan logics (like SeqScan, ...).
  • KaiGai want ForeignScan to support to have SubPlans if FDW driver has capability.
  • Several core functions (that handles plan-tree recursively) needs to be exported to extensions, according to the experience in custom-plan works.

Requirement

Having an explicit linkage between data structures allows us to enhance an existing application by transaparently adding new structures, just as we already do with indexes. Specifically, that we allow more than one lookaside structure on any one table.

• Explicit definition that we are attaching an alternate path onto a table (conceptually similar to adding an index)

• Ability to check that the alternate path is viable (similar to the way we validate use of partial indexes prior to usage). Checks on columns(SELECT), rows(WHERE), aggregations(GROUP)
  • KaiGai agreed to its necessity, but doubt to support them in the first version.

• Ability to consider access cost for both normal table and alternate path (like an index) - this allows the alternate path to *not* be chosen when we are performing some operation that is sub-optimal (for whatever reason).

• There may be some need to define operator classes that are implemented via the alternate path

• allows the join of one or more tables to be replaced with a single lookaside

• LOOKASIDE on the writer commands (INSERT/UPDATE/DELETE).