Re: Suboptimal query plan when using expensive BCRYPT functions

bricklen <bricklen(at)gmail(dot)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