Rearranging simple where clauses

Michael Graham <mgraham@bloxx.com> writes:

I was playing around with some sql in postgres and got to wondering why
the optimiser can't figure out that rearranging some expressions can
result in massive improvements in the queue plan. For example id + 5 <
100 compared with id < 100 - 5.

Is it simply that no one has go around to doing it or is there some
deeper reasons? It's not really important I'm just curious.

Well, it'd require a very large amount of
type-specific/operator-specific knowledge, and it's not clear what would
drive the planner towards doing useful rearrangements rather than
counterproductive ones, and the number of real-world queries where it'd
actually help doesn't seem to be that large. I've seen one or two
complaints about that sort of thing, but it's way way down the list
of serious problems.

regards, tom lane

On Wed, 2011-05-04 at 10:49 -0400, Tom Lane wrote:

Well, it'd require a very large amount of
type-specific/operator-specific knowledge, and it's not clear what
would drive the planner towards doing useful rearrangements rather
than counterproductive ones, and the number of real-world queries
where it'd actually help doesn't seem to be that large. I've seen one
or two complaints about that sort of thing, but it's way way down the
list of serious problems.

I did suspect that the answer would be that the difficulty out ways the
benefit. But in terms of driving the planner don't we always want to be
looking to move all the constants to one side of the expression since
the planner seems to like those?

--
Michael Graham <mgraham@bloxx.com>

Michael Graham <mgraham@bloxx.com> writes:

I did suspect that the answer would be that the difficulty out ways the
benefit. But in terms of driving the planner don't we always want to be
looking to move all the constants to one side of the expression since
the planner seems to like those?

Well, you failed to show us any concrete examples of the cases you were
looking at, but no I don't think the planner necessarily likes "all the
constants on one side". Most likely the win cases are where one side of
a WHERE-condition operator exactly matches an index, so you'd need to be
looking for places where rearrangement could make that happen.

regards, tom lane

On Wed, 2011-05-04 at 11:49 -0400, Tom Lane wrote:

Well, you failed to show us any concrete examples of the cases you
were looking at, but no I don't think the planner necessarily likes
"all the constants on one side". Most likely the win cases are where
one side of a WHERE-condition operator exactly matches an index, so
you'd need to be looking for places where rearrangement could make
that happen.

The reason I never showed you any was because I don't have any I was
just curious. But yeah making one side match an index exactly is
probably the biggest win.

--
Michael Graham <mgraham@bloxx.com>

-----Original Message-----
From: Michael Graham [mailto:mgraham@bloxx.com]
Sent: Wednesday, May 04, 2011 11:59 AM
To: pgsql-general@postgresql.org
Subject: Re: Rearranging simple where clauses

On Wed, 2011-05-04 at 11:49 -0400, Tom Lane wrote:

Well, you failed to show us any concrete examples of the cases you
were looking at, but no I don't think the planner necessarily likes
"all the constants on one side". Most likely the win cases are where
one side of a WHERE-condition operator exactly matches an index, so
you'd need to be looking for places where rearrangement could make
that happen.

The reason I never showed you any was because I don't have any I was
just curious. But yeah making one side match an index exactly is
probably the biggest win.

<I.N.

I think, it'll be probably the "only" win, not the "biggest" - sometimes big, sometimes small.

But, what if there are more than one index based on the column in question? - Which one optimizer is supposed to satisfy by rearranging where clause?

Regards,
Igor Neyman