Re: Equivalence Classes when using IN

David Rowley <david(dot)rowley(at)2ndquadrant(dot)com> writes:
> It's not all that clear what your view is doing here. Confusingly
> there's a Sort in the plan, yet nothing in the query asked for that,
> so I guess that the view must have an ORDER BY. If you get rid of that
> the planner would likely use an index on product (customer_id) to
> parameterise the nested loop, at least, it likely would, if you have
> one.

Yeah. The ORDER BY creates a partial optimization fence, preventing
any such plan from being considered.

> It's pretty bad practice to have ORDER BY in views. I kinda wish we
> didn't even allow it, but that ship sailed many years ago...

I think it's actually disallowed by the SQL spec (although so are
many other things we support). IMO it's a useful facility to have
for views that are meant for direct presentation to clients ---
but if you'd like joins to the view to be optimized, you don't
want an ORDER BY in there.

regards, tom lane