From: Laurent Martelli <laurent.martelli@enercoop.org>
To: David Rowley <dgrowleyml@gmail.com>
Cc: Tom Lane <tgl@sss.pgh.pa.us>
Cc: pgsql-performance <pgsql-performance@postgresql.org>
Subject: Re: IS NOT NULL and LEFT JOIN
Date: Wed, 22 Oct 2014 08:29:18 +0200
Message-ID: <7fe0-54474e80-27-1379daa0@249371065> (raw)
In-Reply-To: <CAApHDvqM8jfeHA169V-TxAgpnZVaLnkuNd1t-TUX6=xJHAmvDw@mail.gmail.com>
List-Unsubscribe: <mailto:majordomo@postgresql.org?body=unsub%20pgsql-performance>
Le Mardi 21 Octobre 2014 10:44 CEST, David Rowley <dgrowleyml@gmail.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
--
Sent via pgsql-performance mailing list (pgsql-performance@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-performance
Reply instructions:
You may reply publicly to this message via plain-text email
using any one of the following methods:
* Reply to all the recipients using the --to and --cc options:
reply via email
To: pgsql-performance@postgresql.org
Cc: laurent.martelli@enercoop.org, dgrowleyml@gmail.com, tgl@sss.pgh.pa.us
Subject: Re: IS NOT NULL and LEFT JOIN
In-Reply-To: <7fe0-54474e80-27-1379daa0@249371065>
* Save the following mbox file, import it into your mail client,
and reply-to-all from there: mbox
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox