Re: Suboptimal query plan when using expensive BCRYPT functions

On Sat, Mar 22, 2014 at 3:27 PM, Erik van Zijst <erik(dot)van(dot)zijst(at)gmail(dot)com>wrote:

> Yes, that works (it does at least on my small test database).
>
> However, these queries are generated by a parser that translates
> complex parse trees from a higher level DSL that doesn't lend itself
> well to logically isolating the crypt checks from the remaining
> conditions, as password checks might be present at arbitrary depths.
>
> For example:
>
> (
> active eq true
> AND
> (
> password eq "foo"
> OR
> password eq "bar"
> )
> )
> AND
> (
> username eq "erik"
> OR
> email contains "bar"
> )
>
> Currently the SQL generator translates each AST node into individual
> predicates that straightforwardly concatenate into a single SQL WHERE
> clause. For this to work, the individual nodes should compose well. I
> don't immediately see how the above query could be automatically
> translated into SQL when taking the WITH-AS approach.
>
> I could nonetheless take a stab at it, but life would certainly be
> easier if I could translate each component independently and leave
> optimization to the query planner.
>

How about encapsulating the revised query inside a db function? That
simplifies the query for your query generator to something like "select
x,y,z from your_func(p_user,p_email,p_crypt)"