Also, sorry if I wasn't clear. Those two example queries above that performed badly were not exact queries that I would use, they were just simple examples that performed identically to something like this (or the exists version of the same query):
SELECT cp.* FROM contract_product cp INNER JOIN claim_product clp ON cp.contract_id = clp.contract_id WHERE clp.claim_id = 'whatever'; On Thu, Dec 17, 2015 at 1:08 PM, Adam Brusselback <adambrusselb...@gmail.com > wrote: > 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 <t...@sss.pgh.pa.us> wrote: > >> Adam Brusselback <adambrusselb...@gmail.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 >> > >
