Adam Brusselback <adambrusselback@xxxxxxxxx> 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 -- Sent via pgsql-performance mailing list (pgsql-performance@xxxxxxxxxxxxxx) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-performance
