From: Tom Lane <tgl@sss.pgh.pa.us>
To: bricklen <bricklen@gmail.com>
Cc: Erik van Zijst <erik.van.zijst@gmail.com>
Cc: pgsql-performance@postgresql.org list <pgsql-performance@postgresql.org>
Subject: Re: Suboptimal query plan when using expensive BCRYPT functions
Date: Sun, 23 Mar 2014 02:40:01 -0400
Message-ID: <8461.1395556801@sss.pgh.pa.us> (raw)
In-Reply-To: <CAGrpgQ8Z45DTKeOJb8VdEBKgRpYWk5yOarpF-gWsEO5VD9oZUw@mail.gmail.com>
References: <CA+69USsLsVb8U5=r7KYs9GYT4Rx9DtyaohsfHvLzVKS=fZNUEw@mail.gmail.com>
<CAGrpgQ_yi-kFq+02Nz2cORhK5o+X8phm3R_sPu7GwtUXUx02fQ@mail.gmail.com>
<CA+69USuvcKik9WSdCwQ_zck2WnN5tRK=A8PgfebDG+3MCeFz1w@mail.gmail.com>
<CAGrpgQ_Mg6HQDqbRmeVFSC3Y4z7F6uwX4Dws8p6a2AobOgnC-w@mail.gmail.com>
<CA+69USuaMfzAYa=YWpyJ-SRGLtrpBzCLVoZXRgh0yMvm4KvmeA@mail.gmail.com>
<CAGrpgQ8Z45DTKeOJb8VdEBKgRpYWk5yOarpF-gWsEO5VD9oZUw@mail.gmail.com>
List-Unsubscribe: <mailto:majordomo@postgresql.org?body=unsub%20pgsql-performance>
bricklen <bricklen@gmail.com> writes:
> Perhaps someone else will have some other ideas of what could be useful
> here.
Maybe I'm missing something ... but isn't the OP's query completely bogus?
SELECT DISTINCT u.*
FROM auth_user u
JOIN bb_userprofile p ON p.user_id = u.id
JOIN bb_identity i ON i.profile_id = p.id
WHERE
(
(
u.username ILIKE 'detkin'
OR
i.email ILIKE 'foo(at)example(dot)com'
)
AND
(
SUBSTRING(password FROM 8) = CRYPT(
'detkin', SUBSTRING(password FROM 8))
)
)
Granting that there are not chance collisions of password hashes (which
would surely be a bad thing if there were), success of the second AND arm
means that we are on user detkin's row of auth_user. Therefore the OR
business is entirely nonfunctional: if the password test passes, then
the u.username ILIKE 'detkin' clause succeeds a fortiori, while if the
password test fails, it hardly matters what i.email is, because the WHERE
clause as a whole fails. Ergo, the whole WHERE clause might as well just
be written "u.username = 'detkin'". If it were a RIGHT JOIN rather than
just a JOIN, this argument would fail ... but as written, the query
makes little sense.
I'll pass gently over the question of whether the password test as shown
could ever succeed at all.
I suppose we've been shown a lobotomized version of the real logic,
but it's hard to give advice in such situations.
regards, tom lane
--
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: tgl@sss.pgh.pa.us, bricklen@gmail.com, erik.van.zijst@gmail.com
Subject: Re: Suboptimal query plan when using expensive BCRYPT functions
In-Reply-To: <8461.1395556801@sss.pgh.pa.us>
* 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