Re: Suboptimal query plan when using expensive BCRYPT functions

От: Tom Lane
Тема: Re: Suboptimal query plan when using expensive BCRYPT functions
Дата: ,
Msg-id: 8461.1395556801@sss.pgh.pa.us
(см: обсуждение, исходный текст)
Ответ на: Re: Suboptimal query plan when using expensive BCRYPT functions  (bricklen)
Ответы: Re: Suboptimal query plan when using expensive BCRYPT functions  (Erik van Zijst)
Список: pgsql-performance

Скрыть дерево обсуждения

Suboptimal query plan when using expensive BCRYPT functions  (Erik van Zijst, )
 Re: Suboptimal query plan when using expensive BCRYPT functions  (bricklen, )
  Re: Suboptimal query plan when using expensive BCRYPT functions  (Erik van Zijst, )
   Re: Suboptimal query plan when using expensive BCRYPT functions  (bricklen, )
    Re: Suboptimal query plan when using expensive BCRYPT functions  (Erik van Zijst, )
     Re: Suboptimal query plan when using expensive BCRYPT functions  (bricklen, )
      Re: Suboptimal query plan when using expensive BCRYPT functions  (Tom Lane, )
       Re: Suboptimal query plan when using expensive BCRYPT functions  (Erik van Zijst, )
 Re: Suboptimal query plan when using expensive BCRYPT functions  (Heikki Linnakangas, )
  Re: Suboptimal query plan when using expensive BCRYPT functions  (Erik van Zijst, )

bricklen <> 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



В списке pgsql-performance по дате сообщения:

От: Tom Lane
Дата:
Сообщение: Re: Suboptimal query plan when using expensive BCRYPT functions
От: Erik van Zijst
Дата:
Сообщение: Re: Suboptimal query plan when using expensive BCRYPT functions