X-Original-To: pgsql-performance-postgresql.org@localhost.postgresql.org Received: from localhost (unknown [64.117.224.130]) by svr1.postgresql.org (Postfix) with ESMTP id D01B9D1C518 for ; Fri, 1 Aug 2003 15:10:47 +0000 (GMT) Received: from svr1.postgresql.org ([64.117.224.193]) by localhost (neptune.hub.org [64.117.224.130]) (amavisd-new, port 10024) with ESMTP id 48799-06 for ; Fri, 1 Aug 2003 12:09:31 -0300 (ADT) Received: from megazone.bigpanda.com (45.mufb.snfc.sffca01r18.dsl.att.net [12.103.249.45]) by svr1.postgresql.org (Postfix) with ESMTP id 6183FD1C4F1 for ; Fri, 1 Aug 2003 12:10:33 -0300 (ADT) Received: by megazone.bigpanda.com (Postfix, from userid 1001) id 94534D630; Fri, 1 Aug 2003 08:10:32 -0700 (PDT) Received: from localhost (localhost [127.0.0.1]) by megazone.bigpanda.com (Postfix) with ESMTP id 81F335C10; Fri, 1 Aug 2003 08:10:32 -0700 (PDT) Date: Fri, 1 Aug 2003 08:10:32 -0700 (PDT) From: Stephan Szabo To: Christopher Browne Cc: Subject: Re: Views With Unions In-Reply-To: <61665.64.229.210.126.1059740402.squirrel@look.libertyrms.com> Message-ID: <20030801075335.D51229-100000@megazone.bigpanda.com> MIME-Version: 1.0 Content-Type: TEXT/PLAIN; charset=US-ASCII X-Virus-Scanned: by amavisd-new at postgresql.org X-Archive-Number: 200308/4 X-Sequence-Number: 2821 On Fri, 1 Aug 2003, Christopher Browne wrote: > Stephan Szabo said: > > > > > > What version are you using? In 7.3 and up it should be willing to > > consider moving the clause down, unless there's something like a type > > mismatch (because in that case it may not be equivalent without a bunch > > more work on the clause). > > That was 7.2.4, although I had also tried it on 7.4 (yesterday's CVS). > > Which provides four findings: > > 1. On 7.2.4, adding additional type info just doesn't help, fitting with > the notion that, consistent with your comment, improvement wouldn't happen > earlier than 7.3. > > There's no help on 7.2 :-(, and the system I'm initially most interested > in using this on is still on 7.2. If you really wanted you could try going back and finding the diffs associated with this in the CVS history or committers archives and see if you can make equivalent changes to 7.2, but that's possibly going to be difficult. > 2. When I retried on 7.4, it _did_ find search paths based on Index Scan, > when I added in additional type information. So the optimization I was > wishing for _is_ there :-). In the longer term, that's very good news. > > 3. I'll have to test this out on 7.3.4, now, as I hadn't, and it sounds > as though that is an interesting case. > > 4. It's often necessary to expressly specify type information in queries > to get the optimizer to do the Right Thing. Especially for cases like this. It takes the safer route of not pushing things down when it's not sure if pushing down might change the semantics (for example if a union piece has a different type from the union output, simply pushing clauses down unchanged could change the results) Tom would probably be willing to relax conditions if it could be proven safe even for the wierd outlying cases with char and varchar and such.