Re: Strange (and good) side effect of partitioning ?

Phil Florent <philflorent(at)hotmail(dot)com> writes:
> I read that on Jonathan Lewis' blog :
> (I believe that there may be some RDBMS which will treat (e.g.) "X between 20 and 10" as being identical to "X between 10 and 20" )

FWIW, I hope not, because the SQL spec is perfectly clear that it's
not supposed to work like that. As rob stone noted nearby, you're
supposed to say BETWEEN SYMMETRIC if you want that behavior.
"X between 20 and 10" should always be false (well, unless it's
null because X is null). But I think that's not really what
your question is.

> I am puzzled. PostgreSQL seems NOT to treat X between 20 and 10" as being identical to "X between 10 and 20" but it's complicated.

There's no specific mechanism in Postgres that would cause "X between 20
and 10" to be reduced to constant-false (and I kind of think it would
be a waste of effort to add one). So that's why in simple cases you
get a plan like

> Seq Scan on t1a (cost=0.00..1167.00 rows=1 width=33) (actual time=6.553..6.553 rows=0 loops=1)
> Filter: ((rn >= 20) AND (rn <= 10))

I think that the other cases you show work as they do because the
code for excluding irrelevant range-based partitions is able to
conclude that no partition need be scanned. That is, the
constant-false-one-time-filter plan arises when we have no
partitions remaining to scan, not because the plan for any one
partition would have looked different from what's above.

regards, tom lane