Postgres Pro
Downloads
Home   >   mailing lists

Thread: Operator COMMUTATOR - how does postgresql use this information

Operator COMMUTATOR - how does postgresql use this information

From
"Obe, Regina"
Date:
Does PostgreSQL use the COMMUTATOR property of an operator to determine if flip-flopped arguments can be collapsed.
 
I used to think it did until someone pointed it doesn't  - For example in the below
 
SELECT b.*
FROM boszip b INNER JOIN landparcels l
ON (b.the_geom && l.the_geom  AND l.the_geom && b.the_geom AND l.the_geom && b.the_geom )
WHERE l.gid = b.gid and b.gid = l.gid
limit 1
 
 
If I look at the query plan - I see the plan has reduced things down to
 
l.gid = b.gid  AND (b.the_geom && l.the_geom  AND l.the_geom && b.the_geom)
 
Why is (b.the_geom && l.the_geom  AND l.the_geom && b.the_geom)  not reduced down to just
 
b.the_geom && l.the_geom 
 
even though && is defined as the commutator of &&?
 
Thanks,
Regina
 
 

The substance of this message, including any attachments, may be confidential, legally privileged and/or exempt from disclosure pursuant to Massachusetts law. It is intended solely for the addressee. If you received this in error, please contact the sender and delete the material from any computer.

Help make the earth a greener place. If at all possible resist printing this email and join us in saving paper.

Re: Operator COMMUTATOR - how does postgresql use this information

From
Tom Lane
Date:
"Obe, Regina" <robe.dnd@cityofboston.gov> writes:
> Does PostgreSQL use the COMMUTATOR property of an operator to determine
> if flip-flopped arguments can be collapsed.

No.  In recent releases we don't even bother to look for simple
duplicate clauses (it's seldom worth the cycles), let alone clauses that
would be duplicates after some transformation or other.

> I used to think it did until someone pointed it doesn't  - For example
> in the below

> SELECT b.*
> FROM boszip b INNER JOIN landparcels l
> ON (b.the_geom && l.the_geom  AND l.the_geom && b.the_geom AND
> l.the_geom && b.the_geom )
> WHERE l.gid = b.gid and b.gid = l.gid
> limit 1

> If I look at the query plan - I see the plan has reduced things down to

> l.gid = b.gid  AND (b.the_geom && l.the_geom  AND l.the_geom &&
> b.the_geom)

8.3 will do that (prior releases will often fail to recognize the
redundancy) but it's an outgrowth of mergejoin equivalence-class
processing.  && isn't a mergejoinable equality operator so nothing much
happens to those clauses.

            regards, tom lane