Received: from localhost (postgresql.org [64.49.215.8]) by postgresql.org (Postfix) with ESMTP id DFB8A4774F2 for ; Thu, 23 Jan 2003 16:32:10 -0500 (EST) Received: from sss.pgh.pa.us (unknown [192.204.191.242]) by postgresql.org (Postfix) with ESMTP id 13C684774E9 for ; Thu, 23 Jan 2003 14:54:13 -0500 (EST) Received: from sss2.sss.pgh.pa.us (tgl@localhost [127.0.0.1]) by sss.pgh.pa.us (8.12.6/8.12.6) with ESMTP id h0NJs95u027670; Thu, 23 Jan 2003 14:54:10 -0500 (EST) To: Stephan Szabo Cc: Tomasz Myrta , pgsql-sql@postgresql.org Subject: Re: To use a VIEW or not to use a View..... In-reply-to: <20030123095104.I13544-100000@megazone23.bigpanda.com> References: <20030123095104.I13544-100000@megazone23.bigpanda.com> Comments: In-reply-to Stephan Szabo message dated "Thu, 23 Jan 2003 10:21:06 -0800" Date: Thu, 23 Jan 2003 14:54:09 -0500 Message-ID: <27669.1043351649@sss.pgh.pa.us> From: Tom Lane X-Virus-Scanned: by AMaViS new-20020517 X-Archive-Number: 200301/371 X-Sequence-Number: 11236 Stephan Szabo writes: > On Thu, 23 Jan 2003, Tom Lane wrote: >> What I was thinking was that any time the code sees a "var = const" >> clause as part of a mergejoin equivalence set, we could mark all the >> "var = var" clauses in the same set as no-ops. For example, given >> WHERE a.f1 = b.f2 AND b.f2 = c.f3 AND c.f3 = 42 >> then after we finish deducing a.f1 = 42 and b.f2 = 42, there is no >> longer any value in either of the original clauses a.f1 = b.f2 and >> b.f2 = c.f3, nor in the other deduced clause a.f1 = c.f3. This would >> take a little bit of restructuring of generate_implied_equalities() and >> process_implied_equality(), but it doesn't seem too difficult to do. >> >> Thoughts? Are there any holes in that logic? > The main thing I can think of is being careful when the types are > different (like padding vs no padding in strings). This is a matter of being careful about marking cross-datatype operators as mergejoinable. We do not mark 'bpchar = text' as mergejoinable --- in fact we don't even have such an operator. AFAICS any pitfalls in those semantics come up already from the existing logic to treat mergejoinable equality as transitive for variables. Extending that transitivity to constants can't create problems that wouldn't exist anyway. For reference, these are the only cross-datatype mergejoinable operators as of CVS tip: regression=# select oid::regoperator,oprcode from pg_operator where oprlsortop!=0 and oprleft!=oprright; oid | oprcode --------------------------+----------- =(integer,bigint) | int48eq =(bigint,integer) | int84eq =(smallint,integer) | int24eq =(integer,smallint) | int42eq =(real,double precision) | float48eq =(double precision,real) | float84eq =(smallint,bigint) | int28eq =(bigint,smallint) | int82eq (8 rows) regards, tom lane