Thread: Rearranging simple where clauses
Hi, 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. Cheers, -- Michael Graham <mgraham@bloxx.com>
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 satisfyby rearranging where clause? Regards, Igor Neyman