Filter with "&&" (ash_elixir_and) slower than "and" in PostgreSQL

Ash Forum Ash Questions / Help
1

Is there documentation about using and instead of && in filters? I think I read that somewhere.

I found out that && is slower than and. I’m guessing because it doesn’t use the index.

# ~3400ms
# WHERE (ash_elixir_and((se0."path"::ltree @ '!history'), (se0."path"::ltree @ e0."id"::uuid::text::ltxtquery))))
filter expr(fragment("? @ '!history'", path) && fragment("? @ ?::text::ltxtquery", path, parent(id)))

# ~110ms
# WHERE ((se0."path"::ltree @ e0."id"::uuid::text::ltxtquery)) AND ((se0."path"::ltree @ '!history')))
filter expr(fragment("? @ '!history'", path) and fragment("? @ ?::text::ltxtquery", path, parent(id)))
2

&& and and do not have the same behaviour in elixir – and raises if any side is not a boolean, same with postgres. && on the other hand works with any data in elixir and the concept of falsey values. That needs to be mimiced in postgres, which is likely what ash_elixir_and does. Given this is not a built in operator in postgres, it’s expected to be less performant and harder to optimize.

2 Likes
3

Yep, exactly what @LostKobrakai said. We added && and || that do their elixir-ish counterparts so you could write things like expr(score && score + 1) or expr(identifier || first_name <> " " <> last_name) and get equivalent output from the data layer. You should prefer to use and whenever possible, but it sounds like maybe we need to make this clear in the expression docs.

EDIT: adding something now.

4

With that said, I actually think we have the type information necessary in some cases to automatically switch it to and and or when we know both sides of the operator are booleans that cannot return nil (this wouldn’t be one of those cases given the fragments). There be dragons though so probably not going to add it :slight_smile:

5

docs update commit: docs: explain difference between elixir-ish and boolean operators · ash-project/ash@021a33b · GitHub

2 Likes