Simon Riggs wrote: <blockquote cite="mid:1194358117.4268.44.camel@ebony.site" type="cite"><pre wrap="">On Tue,
2007-11-06at 13:29 +0000, Heikki Linnakangas wrote: </pre><blockquote type="cite"><pre wrap="">And of course people
willstart adding columns to indexes, to make use
of index-only-scans, once we have the capability. </pre></blockquote><pre wrap="">Not too keen on that. Very
difficultto judge whether its worth the
benefit for creating lots of extra columns in indexes. Specifically,
this isn't going to speed up any existing application without additional
design work.
But seems like we have reasonable reason for them without that.
Do we know how much faster things might go if we do that? </pre></blockquote> Effectively - you get a materialized view
withlimitations (no joins or calculations), with rows in B-Tree order. Update speed would suffer, but I would expect
nearlyall random access queries to improve, and the fewer the columns included in the index, the less data that needs
tobe scanned to find the data you want.<br /><br /> I have some data that might benefit. For example, on one system I
synchronizedata from ACCPAC on MSSQL into PGSQL, then use only a subset of the columns in the ACCPAC tables in my PGSQL
queries.<br/><br /> I say might, because the ACCPAC data is so sprawled out that my "materialized view" does
significantcalculation calculating aggregates and fields with conditional values. The ACCPAC query based entirely on a
viewtakes over 1 second to run. The query on the "materialized view" row takes 0.01 seconds. Quite a difference. :-)<br
/><br/> Cheers,<br /> mark<br /><br /><pre class="moz-signature" cols="72">--
Mark Mielke <a class="moz-txt-link-rfc2396E" href="mailto:mark@mielke.cc"><mark@mielke.cc></a>
</pre>
