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 ;
> 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 

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

Note that you may get an error about an operator "=$" if you miss the spaces 
around the "=".


  Richard Huxton
  Archonet Ltd

---------------------------(end of broadcast)---------------------------
TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED]

Reply via email to