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

At 01:36 AM 11/16/2010, Shigeru HANADA wrote:<br /><blockquote cite="" class="cite" type="cite">Thanks for the
informationabout Informix VTI.  Because I'm not<br /> familiar to Informix, I might have missed your point.  Would you
mind<br/> telling me more about Informix VTI?</blockquote><br /><br /><blockquote cite="" class="cite" type="cite">On
Mon,15 Nov 2010 08:45:14 -0800<br /> Eric Davies <[email protected]> wrote:<br /> > With Informix VTI,
indexingis the same for native tables as for <br /> > virtual tables, except the interpretation of the 32 bit rowid
isleft <br /> > up to the developer. When you define the VTI class, you optionally <br /> > supply a method that
canfetch data based on a 32 bit rowid, and it's <br /> > the responsibility of your non-indexed scanning methods to
provide<br /> > rowids along with the row tuple.<br /><br /> ISTM that index on a VTI table could be inconsistent
whenoriginal<br /> (remote) data was changed in the way other than VTI.  Is it assumed<br /> that the data source is
neverupdated without VTI interface?</blockquote><br /> Yes, the data sources are assumed to updated only through the
VTIinterface.<br /> With our UFI product, the data sources are assumed to be unchanging files, you'd need to re-index
themif they changed.<br /><br /><br /><blockquote cite="" class="cite" type="cite">> Having local indexes can be
veryuseful if you have a user that <br /> > issues queries like:<br /> >     select count(*) from
some_external_tablewhere .... ;<br /> > With VTI, the "count" aggregate doesn't get pushed down, meaning that <br />
>without a local index, your scanning method has to return as many <br /> > tuples as match the where clause,
whichcan be very slow.<br /><br /> How can Informix server optimize such kind of query?  Counts the index<br /> tuple
whichmatch the WHERE clause? </blockquote><br /> That would be my assumption.<br /><br /><br /><blockquote cite=""
class="cite"type="cite"> If so, such optimization seems to<br /> be limited to "count" and wouldn't be able to be
usefulfor "max" or<br /> "sum".  Or, specialized index or VTI class is responsible to the<br />
optimization?</blockquote><br/> If there is an index on the column you want to sum/min/max, and your where clause
restrictsthe query to a particular set of rows based on that index, Informix can get the values for that column from
theindex (which it needed to scan anyhow) without looking at the table. This isn't particular to VTI, it's just a
cleveruse of indexes.<br /><br /> Here is a clipping from one of the Informix manuals on the topic: <dl><dd>The way
thatthe optimizer chooses to read a table is called an <i>access plan</i>. The simplest method to access a table is to
readit sequentially, which is called a <i>table scan</i>. The optimizer chooses a table scan when most of the table
mustbe read or the table does not have an index that is useful for the query.<a name="idx2646"></a><dd><a
name="idx2647"></a>Theoptimizer can also choose to access the table by an index. If the column in the index is the same
asa column in a filter of the query, the optimizer can use the index to retrieve only the rows that the query requires.
Theoptimizer can use a <i>key-only index scan</i><a name="idx2647"></a> if the columns requested are within one index
onthe table. The database server retrieves the needed data from the index and does not access the associated table.
<dd><aname="wq276"></a>Important: <dd><a name="idx2649"></a>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<a name="idx2649"></a>
clauseto change the column to a CHAR data type.<a name="idx2655"></a><dd>The optimizer compares the cost of each plan
todetermine the best one. The database server derives cost from estimates of the number of I/O operations required,
calculationsto produce the results, rows accessed, sorting, and so forth.<br /><br /><br /><blockquote cite=""
class="cite"type="cite"></blockquote></dl>> Local indexes also affords the opportunity of using specialized <br />
>indexes built into the database. My guess is that without some form <br /> > of rowids being passed back and
forth,you couldn't define <br /> > non-materialized views of virtual tables that could be indexed.<br /> > <br />
>That said, we implemented our own btree-like index that used the <br /> > pushed down predicates because
fetchingdata one row at a time wasn't <br /> > desirable with our design choices, and we wanted to support virtual
<br/> > tables with more than 4 billion rows.<br /><br /> I couldn't see the way to handle virtual table with more
than4<br /> billion rows with 32 bit rowids in local index.  Do you mean that your<br /> "btree-like index" searches
resultrows by predicates directly and<br /> skips getbyid()?<br /> Exactly. Our own "rowids" can be up to 64 bits but
arenever seen by Informix. As far as Informix is concerned, it's a regular table scan because the use of our indexes is
hidden.<br/><br /><br /><blockquote cite="" class="cite" type="cite">Regards,<br /> --<br /> Shigeru
Hanada</blockquote><br/><br /> Cheers,<br /> Eric.<br /><br /><p> ********************************************** <br />
EricDavies, M.Sc. <br /> Senior Programmer Analyst<br /> Barrodale Computing Services Ltd. <br /> 1095 McKenzie Ave.,
Suite418<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 />