Re: [GENERAL] Row value expression much faster than equivalent OR clauses

On Wed, Dec 28, 2016 at 10:58 AM, Steven Grimm <sgrimm@thesegovia.com> wrote:

> WHERE ((e.timeStamp > '2016-12-19T20:34:22.315Z')
>         OR (e.timeStamp = '2016-12-19T20:34:22.315Z'
>             AND e.sequenceNumber > 0)
>         OR (e.timeStamp = '2016-12-19T20:34:22.315Z'
>             AND e.sequenceNumber = 0
>             AND e.aggregateIdentifier >
> 'dev:642e1953-2562-4768-80d9-0c3af9b0ff84'))

> This uses the index on the three columns it's using for ordering of events,
> but (if I'm reading the explain output correctly) does a full scan of the
> index.

> I played around with it a little and one thing I tried was to restructure
> the WHERE clause using a row value expression that's semantically equivalent
> to the original.

> WHERE (e.timeStamp, e.sequenceNumber, e.aggregateIdentifier) >
>         ('2016-11-19T20:34:22.315Z', 0, 'dev:642e1953-2562-4768-80d9-0c3af9b0ff84')

> This ends up being a LOT faster:

Yup.

> I wonder if the query planner could recognize that the two queries are
> equivalent and choose the second plan for the OR-clause version, or at least
> use the index more efficiently.

Theoretically it could, but that would add significant time to
planning for a large number of queries, with no benefit to those
who explicitly write the query in the faster (and more concise!)
fashion.

You could come a lot closer to the performance of the row value
expression technique by using the logical equivalent of your
original query that puts AND at the higher level and OR at the
lower level.  (Having OR at the top is generally inefficient.)

WHERE (e.timeStamp >= '2016-12-19T20:34:22.315Z'
  AND (e.timeStamp >  '2016-12-19T20:34:22.315Z'
     OR (e.sequenceNumber >= 0
    AND (e.sequenceNumber >  0
       OR (e.aggregateIdentifier >
'dev:642e1953-2562-4768-80d9-0c3af9b0ff84')))))

--
Kevin Grittner
EDB: http://www.enterprisedb.com
The Enterprise PostgreSQL Company