X-Original-To: pgsql-performance-postgresql.org@localhost.postgresql.org Received: from localhost (unknown [64.117.224.130]) by svr1.postgresql.org (Postfix) with ESMTP id 0C0C0D1C4E3 for ; Wed, 23 Jul 2003 10:21:32 +0000 (GMT) Received: from svr1.postgresql.org ([64.117.224.193]) by localhost (neptune.hub.org [64.117.224.130]) (amavisd-new, port 10024) with ESMTP id 14986-09 for ; Wed, 23 Jul 2003 07:20:52 -0300 (ADT) Received: from rtlocal.trade-india.com (unknown [203.196.129.235]) by svr1.postgresql.org (Postfix) with SMTP id 41593D1C4D0 for ; Wed, 23 Jul 2003 07:21:27 -0300 (ADT) Received: (qmail 20148 invoked from network); 23 Jul 2003 10:30:08 -0000 Received: from unknown (HELO system67.trade-india-local.com) (unknown) by unknown with SMTP; 23 Jul 2003 10:30:08 -0000 From: Rajesh Kumar Mallah Organization: Infocom Network Limited To: pgsql-performance@postgresql.org Subject: factoring problem with view in 7.3.3 Date: Wed, 23 Jul 2003 15:51:48 +0530 User-Agent: KMail/1.5.1 MIME-Version: 1.0 Content-Type: text/plain; charset="us-ascii" Content-Transfer-Encoding: 7bit Content-Disposition: inline Message-Id: <200307231551.48444.mallah@trade-india.com> X-Virus-Scanned: by amavisd-new at postgresql.org X-Spam-Status: No, hits=0.0 tagged_above=0.0 required=5.0 tests=USER_AGENT_KMAIL X-Spam-Level: X-Archive-Number: 200307/389 X-Sequence-Number: 2682 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. Regds Mallah. 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; 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) tradein_clients=#