Re: join to view over custom aggregate seems like it should be faster

On 4/10/07, Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)us> wrote:
> "Merlin Moncure" <mmoncure(at)gmail(dot)com> writes:
> > For some reason, I can't get the index to be used on the table sitting
> > under a view during a join, even though it should be, or at least it
> > seems....
>
> Nope, that's not going to work, because the aggregate keeps the subquery
> from being flattened into the upper query, which is what would have to
> happen for a nestloop-with-inner-indexscan join to be considered.
> AFAICS you've got to structure it so that the aggregation happens above
> the join.

right, i see that it's actually the 'group by' that does it:

select a, b from foo join (select a, b from bar group by a,b) q using (a,b);

is enough to keep it from using the index on a,b from bar. thats too bad...

merlin