Re: Index-only scans

On Wed, Jul 15, 2009 at 1:21 AM, Ron Mayer<rm_pg(at)cheapcomplexdevices(dot)com> wrote:
> Really?  I'd have thought that index is similar to materializing
> these views:
>  create view a_b as select aid,bid from manytomany order by aid,bid;
>  create view b_a as select bid,aid from manytomany order by bid,aid;
> Or perhaps
>  create view a_b as select aid,array_agg(bid) from manytomany group by aid;

How do any of these views help you answer a query like "select aid
from manytomany where bid in (subquery)"?

The last one could help you answer the dual of that but not without
rewriting the query quite heavily to use array operations. The first
two I'm puzzled how they're useful at all since unless you add indexes
to the materialized views they'll just contain a complete copy of the
original table -- the most they could help with is avoiding a sort but
they'll never be updatable so they'll rarely actually be usable.

--
greg
http://mit.edu/~gsstark/resume.pdf