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 BD8BAD1C4E1 for ; Wed, 23 Jul 2003 15:43:24 +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 38387-03 for ; Wed, 23 Jul 2003 12:42:41 -0300 (ADT) Received: from sss.pgh.pa.us (unknown [192.204.191.242]) by svr1.postgresql.org (Postfix) with ESMTP id BF7E8D1C4DA for ; Wed, 23 Jul 2003 12:43:22 -0300 (ADT) Received: from sss2.sss.pgh.pa.us (tgl@localhost [127.0.0.1]) by sss.pgh.pa.us (8.12.9/8.12.9) with ESMTP id h6NFhMQ3013447; Wed, 23 Jul 2003 11:43:22 -0400 (EDT) To: Rajesh Kumar Mallah Cc: pgsql-performance@postgresql.org Subject: Re: factoring problem with view in 7.3.3 In-reply-to: <200307231551.48444.mallah@trade-india.com> References: <200307231551.48444.mallah@trade-india.com> Comments: In-reply-to Rajesh Kumar Mallah message dated "Wed, 23 Jul 2003 15:51:48 +0530" Date: Wed, 23 Jul 2003 11:43:21 -0400 Message-ID: <13446.1058975001@sss.pgh.pa.us> From: Tom Lane X-Virus-Scanned: by amavisd-new at postgresql.org X-Archive-Number: 200307/394 X-Sequence-Number: 2687 Rajesh Kumar Mallah writes: > I have a view which is a union of select of certain feilds from > indentical tables. The problem is when we query a column on > which index exists exists foreach of the tables does not use the > indexes. Hard to be certain since you didn't show us the table definitions, but I suspect the culprit is a datatype mismatch. Here are the comments for 7.3's subquery_is_pushdown_safe, which determines whether it's okay to push down a qualifier: * Conditions checked here: * * 1. If the subquery has a LIMIT clause or a DISTINCT ON clause, we must * not push down any quals, since that could change the set of rows * returned. (Actually, we could push down quals into a DISTINCT ON * subquery if they refer only to DISTINCT-ed output columns, but * checking that seems more work than it's worth. In any case, a * plain DISTINCT is safe to push down past.) * * 2. If the subquery has any functions returning sets in its target list, * we do not push down any quals, since the quals * might refer to those tlist items, which would mean we'd introduce * functions-returning-sets into the subquery's WHERE/HAVING quals. * (It'd be sufficient to not push down quals that refer to those * particular tlist items, but that's much clumsier to check.) * * 3. If the subquery contains EXCEPT or EXCEPT ALL set ops we cannot push * quals into it, because that would change the results. For subqueries * using UNION/UNION ALL/INTERSECT/INTERSECT ALL, we can push the quals * into each component query, so long as all the component queries share * identical output types. (That restriction could probably be relaxed, * but it would take much more code to include type coercion code into * the quals, and I'm also concerned about possible semantic gotchas.) 1 and 2 don't seem to apply to your problem, which leaves 3 ... (BTW, 7.4 has addressed all of the possible improvements noted in the parenthetical remarks here.) regards, tom lane