Skip site navigation (1) Skip section navigation (2)

factoring problem with view in 7.3.3

From: Rajesh Kumar Mallah <mallah(at)trade-india(dot)com>
To: pgsql-performance(at)postgresql(dot)org
Subject: factoring problem with view in 7.3.3
Date: 2003-07-23 10:21:48
Message-ID: (view raw or flat)
Lists: pgsql-performance
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

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=# explain analyze select rfi_id      from 
sent_enquiry_eyp_iid_ip_cat1 where sender_uid = 34866;

                                                                    QUERY PLAN
 Subquery Scan sent_enquiry_eyp_iid_ip_cat1  (cost=173347.05..182139.66 
rows=58617 width=55) (actual time=57514.58..62462.15 rows=73 loops=1)
   Filter: (sender_uid = 34866)
   ->  Unique  (cost=173347.05..182139.66 rows=58617 width=55) (actual 
time=57514.54..61598.82 rows=586230 loops=1)
         ->  Sort  (cost=173347.05..174812.49 rows=586174 width=55) (actual 
time=57514.54..58472.01 rows=586231 loops=1)
               Sort Key: rfi_id, sender_uid, receiver_uid, subject, generated
               ->  Append  (cost=0.00..90563.74 rows=586174 width=55) (actual 
time=13.17..50500.95 rows=586231 loops=1)
                     ->  Subquery Scan "*SELECT* 1"  (cost=0.00..57800.63 
rows=369463 width=42) (actual time=13.17..30405.33 rows=369536 loops=1)
                           ->  Seq Scan on eyp_rfi  (cost=0.00..57800.63 
rows=369463 width=42) (actual time=13.14..28230.00 rows=369536 loops=1)
                     ->  Subquery Scan "*SELECT* 2"  (cost=0.00..7317.11 
rows=45811 width=47) (actual time=0.04..534.89 rows=45811 loops=1)
                           ->  Seq Scan on iid_rfi  (cost=0.00..7317.11 
rows=45811 width=47) (actual time=0.03..359.88 rows=45811 loops=1)
                     ->  Subquery Scan "*SELECT* 3"  (cost=0.00..22335.44 
rows=145244 width=42) (actual time=0.08..17815.66 rows=145251 loops=1)
                           ->  Seq Scan on ip_rfi  (cost=0.00..22335.44 
rows=145244 width=42) (actual time=0.05..16949.03 rows=145251 loops=1)
                     ->  Subquery Scan "*SELECT* 4"  (cost=0.00..3110.56 
rows=25656 width=55) (actual time=0.07..469.60 rows=25633 loops=1)
                           ->  Seq Scan on catalog_rfi  (cost=0.00..3110.56 
rows=25656 width=55) (actual time=0.06..380.64 rows=25633 loops=1)
 Total runtime: 62504.24 msec
(15 rows)

tradein_clients=# explain analyze select rfi_id      from eyp_rfi where 
sender_uid = 34866;
                                                           QUERY PLAN
 Index Scan using eyp_sender_uid_idx on eyp_rfi  (cost=0.00..376.11 rows=117 
width=4) (actual time=9.88..69.10 rows=12 loops=1)
   Index Cond: (sender_uid = 34866)
 Total runtime: 69.17 msec
(3 rows)



pgsql-performance by date

Next:From: Richard HuxtonDate: 2003-07-23 14:13:14
Subject: Re: factoring problem with view in 7.3.3
Previous:From: Shridhar DaithankarDate: 2003-07-23 09:54:01
Subject: Re: Performance hit of foreign key constraints?

Privacy Policy | About PostgreSQL
Copyright © 1996-2014 The PostgreSQL Global Development Group