Re: Terrible plan choice for view with distinct on clause

Adam Brusselback <adambrusselback@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