Inequality operators are not deduced.

Hello,

I found that the planner can decude equality operators,
but cannot decude inequality ones.
Are there any plans to improve handling of them?

I initialized tables as below.

CREATE TABLE T (i INTEGER PRIMARY KEY);
CREATE TABLE U (i INTEGER PRIMARY KEY);
INSERT INTO T SELECT generate_series(1, 10000);
INSERT INTO U SELECT generate_series(1, 100000);
ANALYZE;

The planner can add an implicit equality operator, so the folloing
two plans are exactly the same, regardless of the redundant 'U.i = 100'.

EXPLAIN SELECT * FROM T, U WHERE T.i = U.i AND T.i = 100;
EXPLAIN SELECT * FROM T, U WHERE T.i = U.i AND T.i = 100 AND U.i = 100;
QUERY PLAN
----------------------------------------------------------------------
Nested Loop (cost=0.00..16.56 rows=1 width=8)
-> Index Scan using t_pkey on t (cost=0.00..8.27 rows=1 width=4)
Index Cond: (i = 100)
-> Index Scan using u_pkey on u (cost=0.00..8.28 rows=1 width=4)
Index Cond: (i = 100)

However, it seems to be inapplicable for inequality operators. The plan
was improved after I added the deduce-able 'U.i = 100' in theory.

EXPLAIN SELECT * FROM T, U WHERE T.i = U.i AND T.i < 100;
QUERY PLAN
-----------------------------------------------------------------------------
Merge Join (cost=0.00..340.38 rows=100 width=8)
Merge Cond: (t.i = u.i)
-> Index Scan using t_pkey on t (cost=0.00..10.00 rows=100 width=4)
Index Cond: (i < 100)
-> Index Scan using u_pkey on u (cost=0.00..3048.26 rows=100000 width=4)

EXPLAIN SELECT * FROM T, U WHERE T.i = U.i AND T.i < 100 AND U.i < 100;
QUERY PLAN
-------------------------------------------------------------------------
Merge Join (cost=0.00..11.32 rows=1 width=8)
Merge Cond: (t.i = u.i)
-> Index Scan using t_pkey on t (cost=0.00..10.00 rows=100 width=4)
Index Cond: (i < 100)
-> Index Scan using u_pkey on u (cost=0.00..9.94 rows=96 width=4)
Index Cond: (i < 100)

Just for the record, if forcing nested loop joins, plans were the follows.

SET enable_mergejoin = off;
SET enable_hashjoin = off;
EXPLAIN SELECT * FROM T, U WHERE T.i = U.i AND T.i < 100;
QUERY PLAN
-------------------------------------------------------------------------
Nested Loop (cost=0.00..739.11 rows=100 width=8)
-> Index Scan using t_pkey on t (cost=0.00..10.00 rows=100 width=4)
Index Cond: (i < 100)
-> Index Scan using u_pkey on u (cost=0.00..7.28 rows=1 width=4)
Index Cond: (u.i = t.i)

EXPLAIN SELECT * FROM T, U WHERE T.i = U.i AND T.i < 100 AND U.i < 100;
QUERY PLAN
-----------------------------------------------------------------------
Nested Loop (cost=0.00..337.42 rows=1 width=8)
-> Index Scan using u_pkey on u (cost=0.00..9.94 rows=96 width=4)
Index Cond: (i < 100)
-> Index Scan using t_pkey on t (cost=0.00..3.40 rows=1 width=4)
Index Cond: ((t.i < 100) AND (t.i = u.i))

Regards,
---
ITAGAKI Takahiro
NTT Open Source Software Center

ITAGAKI Takahiro <itagaki(dot)takahiro(at)oss(dot)ntt(dot)co(dot)jp> writes:
> I found that the planner can decude equality operators,
> but cannot decude inequality ones.
> Are there any plans to improve handling of them?

Not particularly; it doesn't seem like something that comes up often
enough to be worth the work.

regards, tom lane

I wrote:
> However, it seems to be inapplicable for inequality operators. The plan
> was improved after I added the deduce-able 'U.i = 100' in theory.

Sorry, there was a miss. The correct is 'U.i < 100'.

>
> EXPLAIN SELECT * FROM T, U WHERE T.i = U.i AND T.i < 100;
> QUERY PLAN
> -----------------------------------------------------------------------------
> Merge Join (cost=0.00..340.38 rows=100 width=8)
> Merge Cond: (t.i = u.i)
> -> Index Scan using t_pkey on t (cost=0.00..10.00 rows=100 width=4)
> Index Cond: (i < 100)
> -> Index Scan using u_pkey on u (cost=0.00..3048.26 rows=100000 width=4)
>
> EXPLAIN SELECT * FROM T, U WHERE T.i = U.i AND T.i < 100 AND U.i < 100;
> QUERY PLAN
> -------------------------------------------------------------------------
> Merge Join (cost=0.00..11.32 rows=1 width=8)
> Merge Cond: (t.i = u.i)
> -> Index Scan using t_pkey on t (cost=0.00..10.00 rows=100 width=4)
> Index Cond: (i < 100)
> -> Index Scan using u_pkey on u (cost=0.00..9.94 rows=96 width=4)
> Index Cond: (i < 100)

Regards,
---
ITAGAKI Takahiro
NTT Open Source Software Center

Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)us> wrote:

> ITAGAKI Takahiro <itagaki(dot)takahiro(at)oss(dot)ntt(dot)co(dot)jp> writes:
> > I found that the planner can deduce equality operators,
> > but cannot deduce inequality ones.
> > Are there any plans to improve handling of them?
>
> Not particularly; it doesn't seem like something that comes up often
> enough to be worth the work.

Hmm... Then I'll add redundant conditions to where-clause manually
in such cases. However, it occured in an actual application ported
from another database to postgres. I'll try to improve the handling
and tell you if it will come good.

Regards,
---
ITAGAKI Takahiro
NTT Open Source Software Center