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
