Top-level disjunctive causes sub-optimal query plan

[glebm]

Issue from a comment by @fatalmind:

Besides the row values, there is also a performance problem with the logic as shown in the README:

x0 OR 
y0 AND (x1 OR
        y1 AND (x2 OR
                y2 AND ...))

The problem is that this kind of where-clause cannot use and index on these columns, even if it exists. No matter which database.

See here: http://use-the-index-luke.com/sql/partial-results/fetch-next-page#sb-equivalent-logic

To use an index, you must not have an OR on the top level comparison.

Let me know if you have any questions.

-Markus Winand

[glebm]

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

[fatalmind]

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.

[glebm]

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

[glebm]

@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' ... 

[fatalmind]

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).

[glebm]

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