On Fri, 3 Apr 2009, Tom Lane wrote:
Oh, hang on, I think I saw something in the docs about what conditions can
be used in a merge...
No, you got it right the first time. I was about to suggest that maybe
you could make it work by recasting the problem as equality on an
interval datatype, but the problem is that this is not equality but
"overlaps". And you can't cheat and call it equality, because it's
not transitive.
Well, according to
http://www.postgresql.org/docs/8.3/interactive/xoper-optimization.html#AEN41844
| So, both data types must be capable of being fully ordered, and the
| join operator must be one that can only succeed for pairs of values that
| fall at the "same place" in the sort order.
I don't actually believe that a standard merge join algorithm will work
with an intransitive join condition ...
A standard merge join should work absolutely fine, depending on how it's
implemented. If the implementation keeps a list of "current" right-hand
elements, and adds right-hand rows to the list when they compare "equal"
to the current left-hand element, and removes them from the list when they
compare "not equal" to the current left-hand element, then it would work
fine. If it does something else like rewinding the right-hand stream, or
throwing away the list when the current left-hand element is "not equal"
the previous left-hand element, (which would be fine for true equality)
then it will not work.
The description in the docs doesn't make it clear which way Postgres does
it.
Matthew
--
I have an inferiority complex. But it's not a very good one.
--
Sent via pgsql-performance mailing list (pgsql-performance@xxxxxxxxxxxxxx)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-performance
