Re: IS NOT NULL and LEFT JOIN

Le Mardi 21 Octobre 2014 10:44 CEST, David Rowley <dgrowleyml(at)gmail(dot)com> a écrit:

> For what it's worth I'd say they are identical, at least, if you discount
> deferring foreign key constraints or also executing the query from within
> a volatile function which was called by a query which just updated the
> user_info table to break referential integrity.

I must say I had not thought of that.

> The presence of the foreign key on contract_contract.user_info which
> references user_user_info.id means that any non-null
> contract_contract.user_info record must reference a valid user_user_info
> record, therefore the join is not required to prove that a non nulled
> user_info contract records match a user info record, therefore the join to
> check it exists is pretty much pointless in just about all cases that
> you're likely to care about.
>
> Although, saying that I'm still a bit confused about the question. Are you
> asking if there's some way to get PostgreSQL to run the 1st query faster?
> Or are you asking if both queries are equivalent?

I was asking for a way to make it run faster. Given that it returns at most a few rows found by an index, I was thinking it could be made to run faster.

But I agree that the query is not well written (well generated by hibernate) considering the result I want.

Regards,
Laurent