Re: factoring problem with view in 7.3.3

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