Re: Visibility map thoughts

Simon Riggs wrote:
> On Tue, 2007-11-06 at 13:29 +0000, Heikki Linnakangas wrote:
>
>> And of course people will start adding columns to indexes, to make use
>> of index-only-scans, once we have the capability.
>>
> Not too keen on that. Very difficult to 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?
>
Effectively - you get a materialized view with limitations (no joins or
calculations), with rows in B-Tree order. Update speed would suffer, but
I would expect nearly all random access queries to improve, and the
fewer the columns included in the index, the less data that needs to be
scanned to find the data you want.

I have some data that might benefit. For example, on one system I
synchronize data from ACCPAC on MSSQL into PGSQL, then use only a subset
of the columns in the ACCPAC tables in my PGSQL queries.

I say might, because the ACCPAC data is so sprawled out that my
"materialized view" does significant calculation calculating aggregates
and fields with conditional values. The ACCPAC query based entirely on a
view takes over 1 second to run. The query on the "materialized view"
row takes 0.01 seconds. Quite a difference. :-)

Cheers,
mark

--
Mark Mielke <mark(at)mielke(dot)cc>