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 ---------------------------(end of broadcast)--------------------------- TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED]
