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 BA249D1C90F for ; Wed, 23 Jul 2003 14:13:20 +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 27823-04 for ; Wed, 23 Jul 2003 11:12:38 -0300 (ADT) Received: from anchor-post-39.mail.demon.net (anchor-post-39.mail.demon.net [194.217.242.80]) by svr1.postgresql.org (Postfix) with ESMTP id 00117D1C517 for ; Wed, 23 Jul 2003 11:13:18 -0300 (ADT) Received: from mwynhau.demon.co.uk ([193.237.186.96] helo=mainbox.archonet.com) by anchor-post-39.mail.demon.net with esmtp (Exim 3.36 #2) id 19fKMo-0007QC-0U; Wed, 23 Jul 2003 15:13:18 +0100 Received: from localhost (localhost.localdomain [127.0.0.1]) by mainbox.archonet.com (Postfix) with ESMTP id BBE6516C86; Wed, 23 Jul 2003 15:13:16 +0100 (BST) Received: from client.archonet.com (client.archonet.com [192.168.1.16]) by mainbox.archonet.com (Postfix) with ESMTP id 2300116B07; Wed, 23 Jul 2003 15:13:16 +0100 (BST) From: Richard Huxton To: Rajesh Kumar Mallah , pgsql-performance@postgresql.org Subject: Re: factoring problem with view in 7.3.3 Date: Wed, 23 Jul 2003 15:13:14 +0100 User-Agent: KMail/1.5 References: <200307231551.48444.mallah@trade-india.com> In-Reply-To: <200307231551.48444.mallah@trade-india.com> MIME-Version: 1.0 Content-Type: text/plain; charset="iso-8859-1" Content-Transfer-Encoding: 7bit Content-Disposition: inline Message-Id: <200307231513.14574.dev@archonet.com> X-Virus-Scanned: by AMaViS snapshot-20020531 X-Virus-Scanned: by amavisd-new at postgresql.org X-Archive-Number: 200307/391 X-Sequence-Number: 2684 On Wednesday 23 July 2003 11:21, Rajesh Kumar Mallah wrote: > Hi , > > 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. > > > But when we query individual tables it uses indexes. > > tradein_clients=# create view sent_enquiry_eyp_iid_ip_cat1 as > select rfi_id,sender_uid,receiver_uid,subject,generated from eyp_rfi UNION > select rfi_id,sender_uid,receiver_uid,subject,generated from iid_rfi UNION > select rfi_id,sender_uid,receiver_uid,subject,generated from ip_rfi UNION > select rfi_id,sender_uid,receiver_uid,subject,generated from catalog_rfi ; > > CREATE VIEW > tradein_clients=# > tradein_clients=# explain analyze select rfi_id from > sent_enquiry_eyp_iid_ip_cat1 where sender_uid = 34866; [snip query plan showing full selects being done and then filtering on the outputs] I do remember some talk about issues with pushing where clauses down into unions on a view (sorry - can't remember when - maybe check the archives). Actually, I thought work had been done on that for 7.3.3, but it might have been 7.4 If you generally do that particular query (checking agains sender_uid) then the simplest solution is to build an SQL query to push the comparison down for you: CREATE my_function(int4) RETURNS SETOF my_type AS ' SELECT ... FROM eyp_rfi WHERE sender_uid = $1 UNION ...etc... ' LANGUAGE 'SQL'; Note that you may get an error about an operator "=$" if you miss the spaces around the "=". HTH -- Richard Huxton Archonet Ltd