Re: Using the same condition twice

Hans-Jürgen Schönig <postgres@cybertec.at> writes:
> I came across a quite interesting issue I don't really understand but 
> maybe Tom will know.

Interesting.  We seem to recognize the fact that the extra clause is
redundant in nearly all places ... but not in indexscan plan generation.

I tried this simplified test case:

create table t_wert(werttypid int);
create table t_werttyp(id int);
create index idx_wert_werttypid on t_wert(werttypid);

explain select * from
t_wert JOIN t_werttyp ON (t_werttyp.id = t_wert.werttypid)
where t_werttyp.id = t_wert.werttypid;

explain select * from
t_wert JOIN t_werttyp ON (t_werttyp.id = t_wert.werttypid);

I got identical merge-join plans and row count estimates both ways.
I then turned off enable_mergejoin, and got identical hash-join plans
and row counts.  But with enable_hashjoin also off:

regression=# explain select * from
regression-# t_wert JOIN t_werttyp ON (t_werttyp.id = t_wert.werttypid)
regression-# where t_werttyp.id = t_wert.werttypid;                                       QUERY PLAN
-------------------------------------------------------------------------------------------Nested Loop
(cost=0.00..4858.02rows=5000 width=8)  ->  Seq Scan on t_werttyp  (cost=0.00..20.00 rows=1000 width=4)  ->  Index Scan
usingidx_wert_werttypid on t_wert  (cost=0.00..4.83 rows=1 width=4)        Index Cond: (("outer".id = t_wert.werttypid)
AND("outer".id = t_wert.werttypid))
 
(4 rows)

regression=# explain select * from
regression-# t_wert JOIN t_werttyp ON (t_werttyp.id = t_wert.werttypid);                                      QUERY
PLAN
----------------------------------------------------------------------------------------Nested Loop
(cost=0.00..17150.00rows=5000 width=8)  ->  Seq Scan on t_werttyp  (cost=0.00..20.00 rows=1000 width=4)  ->  Index Scan
usingidx_wert_werttypid on t_wert  (cost=0.00..17.07 rows=5 width=4)        Index Cond: ("outer".id =
t_wert.werttypid)
(4 rows)

Looks like a bug is lurking someplace ...
        regards, tom lane