Re: Query Plan - Bitmap Index Scan and Views

On Aug 4, 2006, at 8:15 PM, Tom Lane wrote:

> Rusty Conover <rconover(at)infogears(dot)com> writes:
>> Is there any inherent benefit of using a the IN operator versus
>> joining a temporary table? Should they offer near equal performance?
>> It appears bitmap scan's aren't done when matching across a small
>> temporary table.
>
> I believe the problem you're facing is that existing PG releases
> don't know how to rearrange join order in the face of outer joins,
> and your view is full of outer joins. So the join against the temp
> table happens after forming the full output of the view, whereas you
> desperately need it to happen at the bottom of the join stack.
>
> CVS tip (8.2-to-be) has some ability to rearrange outer joins, and
> I'm interested to know whether it's smart enough to fix your problem.
> But you have not provided enough info to let someone else duplicate
> your test case. Would you be willing to download CVS or a recent
> nightly snapshot and see what it does with your problem?
>
> regards, tom lane

Absolutely, I'll attempt to run the test against the current CVS HEAD.

Do I need to pg_dump and restore from 8.1.4?

What other information would be helpful in the meantime?

Thanks,

Rusty
--
Rusty Conover
InfoGears Inc.
Web: http://www.infogears.com