Re: factoring problem with view in 7.3.3 - Mailing list pgsql-performance

From Richard Huxton
Subject Re: factoring problem with view in 7.3.3
Date
Msg-id 200307231513.14574.dev@archonet.com
Whole thread Raw
In response to factoring problem with view in 7.3.3  (Rajesh Kumar Mallah <mallah@trade-india.com>)
List pgsql-performance
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

pgsql-performance by date:

Previous
From: "Shridhar Daithankar"
Date:
Subject: Re: [GENERAL] Performance hit of foreign key constraints?
Next
From: Jörg Schulz
Date:
Subject: different query plan for same select