User-Agent: SOGoMail 2.2.3
cc: "Tom Lane" <tgl@sss.pgh.pa.us>,
 "pgsql-performance" <pgsql-performance@postgresql.org>
MIME-Version: 1.0
from: "Laurent Martelli" <laurent.martelli@enercoop.org>
Subject: Re: IS NOT NULL and LEFT JOIN
message-id: <7fe0-54474e80-27-1379daa0@249371065>
to: "David Rowley" <dgrowleyml@gmail.com>
content-type: text/plain; charset="utf-8"
date: Wed, 22 Oct 2014 08:29:18 +0200
in-reply-to: 
 <CAApHDvqM8jfeHA169V-TxAgpnZVaLnkuNd1t-TUX6=xJHAmvDw@mail.gmail.com>
content-transfer-encoding: quoted-printable
Precedence: bulk
Sender: pgsql-performance-owner@postgresql.org


Le Mardi 21 Octobre 2014 10:44 CEST, David Rowley <dgrowleyml@gmail.com> a =
=C3=A9crit:

> 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 faste=
r.

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

Regards,
Laurent


--=20
Sent via pgsql-performance mailing list (pgsql-performance@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-performance