Re: Terrible plan choice for view with distinct on clause

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 <adambrusselback(at)gmail(dot)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 <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
>>
>
>