Top-level disjunctive causes sub-optimal query plan about order_query HOT 6 CLOSED

Note that Oracle seems to be doing something else here: it plans a union for each top-level OR clause. Not sure if it does anything beyond this in our case. https://blogs.oracle.com/optimizer/entry/or_expansion_transformation

from order_query.

Yes, it may do that. Or not, that really depends on many factors. For the general case, in my experience, it is better to go for the "read too much" solution as shown in my article. Row-Values would be best of course, but that's only properly handled by PostgreSQL.

from order_query.

@fatalmind Released in v0.1.2, thank you :)

from order_query.

@fatalmind I wonder if the extra condition helps for enumerated order values, e.g.:

SELECT "issues".* FROM "issues"  WHERE 
  ("issues"."pinned" IN ('t','f') AND 
    ("issues"."pinned" = 't' OR
    "issues"."pinned" = 'f' AND ("issues"."priority" = 'high' ... 

from order_query.

No, because IN-Lists are disjunct conditions by themselves.

In that case I'd recommend just going for the simple way.

Generally: you need to have a so-called SARGable (search-argument-able) condition at the top-level. These are basically equals (=), ranges (>, <, but potentially also LIKEs without prefix-wildcard).

from order_query.

@fatalmind Thank you, order_query is now free of bugs that prevent index usage 🎱

from order_query.