Inequality operators are not deduced. - Mailing list pgsql-general

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