Re: SQL/MED estimated time of arrival? - Mailing list pgsql-hackers

With Informix VTI, indexing is the same for native tables as for virtual tables, except the interpretation of the 32
bitrowid is left up to the developer. When you define the VTI class, you optionally supply a method that can fetch data
basedon a 32 bit rowid, and it's the responsibility of your non-indexed scanning methods to provide rowids along with
therow tuple. <br /><br /> Having local indexes can be very useful if you have a user that issues queries like:<br />
  select count(*) from some_external_table where .... ;<br /> With VTI, the "count" aggregate doesn't get pushed down,
meaningthat without a local index, your scanning method has to return as many tuples as match the where clause, which
canbe very slow.<br /><br /> 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
viewsof virtual tables that could be indexed.<br /><br /> That said, we implemented our own btree-like index that used
thepushed down predicates because fetching data one row at a time wasn't desirable with our design choices, and we
wantedto support virtual tables with more than 4 billion rows.<br /><br /> Eric<br /> At 07:41 PM 11/14/2010, Shigeru
HANADAwrote:<br /><blockquote cite="" class="cite" type="cite">On Fri, 12 Nov 2010 08:27:54 -0800<br /> Eric Davies
<[email protected]>wrote:<br /> > Thank you for the time estimate and the interface discussion. It <br />
>sounds like the PostgreSQL SQL/MED code will be very useful when it <br /> > is done. Our product provides
read-onlyaccess to files, so <br /> > updates/inserts/deletes aren't an issue for us.<br /> > <br /> > One
thingthat is not clear to me is indexing support. Will it be <br /> > possible to index a SQL/MED table as if it
werea regular table?<br /><br /> No, SQL/MED would not support indexing foreign tables, at least in<br /> first
version. Because it would be difficult to use common row id for<br /> various FDWs.  To support indexing foreign tables
mightneed to change<br /> common structure of index tuple to be able to hold virtual row-id, not<br />
ItemPointerData.<br/><br /> Instead, FDW can handle expressions which are parsed from WHERE clause<br /> and JOIN
conditionof original SQL, and use them to optimize scanning. <br /> For example, FDW for PostgreSQL pushes some
conditionsdown to remote<br /> side to decrease result tuples to be transferred.  I hope this idea<br /> helps you.<br
/><br/> >                                                                  What <br /> > would be the equivalent
ofInformix's row ids?<br /><br /> Answer to the second question would be "ItemPointerData".  It consists<br /> of a
blocknumber and an offset in the block, and consume 6 bytes for<br /> each tuple.  With this information, PostgreSQL
canaccess to a data<br /> tuple directly.  Actual definition is:<br /><br /> typedef struct ItemPointerData<br /> {<br
/>    BlockIdData ip_blkid;<br />     OffsetNumber ip_posid;<br /> } ItemPointer;<br /><br /> Does Informix uses common
row-id(AFAIK it's 4 bytes integer) for<br /> both of virtual tables and normal tables?<br /><br /> Regards,<br /> --<br
/>Shigeru Hanada</blockquote><p> ********************************************** <br /> Eric Davies, M.Sc. <br /> Senior
ProgrammerAnalyst<br /> Barrodale Computing Services Ltd. <br /> 1095 McKenzie Ave., Suite 418<br /> Victoria BC V8P
2L5<br/> Canada<br /><br /> Tel: (250) 704-4428<br /> Web: <a eudora="autourl" href="http://www.barrodale.com/"> <font
color="#0000FF"><u>http://www.barrodale.com</u></font></a><br/> Email: [email protected] <br />
**********************************************<br/><br /><br />