From: | Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)us> |
---|---|
To: | hs(at)cybertec(dot)at |
Cc: | pgsql-hackers(at)postgresql(dot)org |
Subject: | Re: Using the same condition twice |
Date: | 2002-10-24 14:23:29 |
Message-ID: | 11660.1035469409@sss.pgh.pa.us |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Thread: | |
Lists: | pgsql-hackers |
=?ISO-8859-1?Q?Hans-J=FCrgen_Sch=F6nig?= <postgres(at)cybertec(dot)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.02 rows=5000 width=8)
-> Seq Scan on t_werttyp (cost=0.00..20.00 rows=1000 width=4)
-> Index Scan using idx_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.00 rows=5000 width=8)
-> Seq Scan on t_werttyp (cost=0.00..20.00 rows=1000 width=4)
-> Index Scan using idx_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
From | Date | Subject | |
---|---|---|---|
Next Message | Robert Treat | 2002-10-24 14:42:00 | Re: Hot Backup |
Previous Message | Tom Lane | 2002-10-24 13:42:46 | Re: Simulating a SELECT..FOR UPDATE to LOCK and SELECT statement |