Phil Florent <philflorent@xxxxxxxxxxx> 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
