Re: Terrible plan choice for view with distinct on clause

No ORM, just me.
Was somewhat similar to something I had seen done at an old job, but they
used SQL Server and that type of query worked fine there.

There were a couple business cases that had to be satisfied, which is why I
went the way I did:
The first was "allow products to be grouped together, and those groups be
placed in a hierarchy. All of the products in child groupings are valid for
the parent of the grouping. Products should be able to be added and removed
from this group at any point."
The next was "allow a user to add a product, or a group of products to a
contract and set pricing information, product is the most definitive and
overrides any groupings the product may be in, and the lowest level of the
grouping hierarchy should be used if the product is not directly on the
contract."
The last was "adding and removing products from a group should immediately
take effect to make those products valid or invalid on any contracts that
grouping is a part of."

Now I am not going to say I love this design, I actually am not a fan of it
at all. I just couldn't think of any other design pattern that would meet
those business requirements. I was hoping to create a view to make working
with the final result the rules specified above easy when you want to know
what pricing is valid for a specific product on a contract.

So that is the "why" at least.

On Thu, Dec 17, 2015 at 12:00 PM, Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)us> wrote:

> Adam Brusselback <adambrusselback(at)gmail(dot)com> writes:
> > The view I am having trouble with is able to push down it's where clause
> > when the id's are directly specified like so:
> > SELECT *
> > FROM contract_product cp
> > WHERE cp.contract_id = '16d6df05-d8a0-4ec9-ae39-f4d8e13da597'
> > AND cp.product_id = '00c117d7-6451-4842-b17b-baa44baa375f';
>
> > But the where clause or join conditions are not pushed down in these
> cases
> > (which is how I need to use the view):
> > SELECT *
> > FROM contract_product cp
> > WHERE EXISTS (
> > SELECT 1
> > WHERE cp.contract_id = '16d6df05-d8a0-4ec9-ae39-f4d8e13da597'
> > AND cp.product_id = '00c117d7-6451-4842-b17b-baa44baa375f'
> > );
>
> This plea for help would be more convincing if you could explain *why*
> you needed to do that. As is, it sure looks like "Doctor, it hurts when
> I do this". What about that construction isn't just silly?
>
> (And if you say "it's produced by an ORM I have no control over", I'm
> going to say "your ORM was evidently written by blithering idiots, and
> you should not have any faith in it".)
>
> Having said that, the reason nothing good happens is that
> convert_EXISTS_sublink_to_join() punts on subqueries that have an empty
> FROM clause, as well as some other corner cases that I did not care to
> analyze carefully at the time. Just looking at this example, it seems
> like if the SELECT list is trivial then we could simply replace the EXISTS
> clause in toto with the contents of the lower WHERE clause, thereby
> undoing the silliness of the query author. I don't think this could be
> handled directly in convert_EXISTS_sublink_to_join(), because it's defined
> to return a JoinExpr which would not apply in such a case. But possibly
> it could be dealt with in make_subplan() without too much overhead. I'm
> not feeling motivated to work on this myself, absent a more convincing
> explanation of why we should expend any effort to support this query
> pattern. But if anyone else is, have at it.
>
> regards, tom lane
>