X-Original-To: pgsql-sql@postgresql.org Received: from smtp015.mail.yahoo.com (smtp015.mail.yahoo.com [216.136.173.59]) by postgresql.org (Postfix) with SMTP id BD49C476032 for ; Mon, 10 Mar 2003 10:41:10 -0500 (EST) Received: from unknown (HELO ?212.103.160.174?) (heshamgowaily@212.103.160.174 with plain) by smtp.mail.vip.sc5.yahoo.com with SMTP; 10 Mar 2003 15:41:14 -0000 Subject: Execution plan problem From: Hesham Gowaily To: pgsql-sql@postgresql.org Content-Type: text/plain Organization: Message-Id: <1047310853.3367.13.camel@EG1OPLLX107> Mime-Version: 1.0 X-Mailer: Ximian Evolution 1.2.2 Date: 10 Mar 2003 17:40:53 +0200 Content-Transfer-Encoding: 7bit X-Archive-Number: 200303/286 X-Sequence-Number: 12326 hi all, I have a strange problem and really wish to get some help in here. I have the following tables create table shr_objects( objectid int8 not null companyid int4 not null name varchar(255) description varchar(255) ) primary key : object id foreign key on companyid references shr_companies(companyid) Index on companyid Number of rows ~ 1,410,000 create table smb_contacts{ contactid int4 not null objectid int8 not null firstname varchar(255) lastname varchar(255) ) primary key : contactid foreign key on objectid references shr_objects9objectid) index on : objectid Number of rows ~ 10,000 I am trying to execute a query that joins the 2 tables on object id , it works fine but when i add an order clause the performance is degarded dramatically. I have included both quiries with their excution plan. ============================================================================= intranet=# explain analyze SELECT obj.companyid, obj.name, obj.description, intranet-# cnt.firstname, cnt.lastname intranet-# FROM smb_contacts cnt JOIN shr_objects obj ON cnt.objectid = obj.objectid intranet-# order by obj.companyid intranet-# limit 90; NOTICE: QUERY PLAN: Limit (cost=44459.46..44459.46 rows=90 width=566) (actual time=14426.92..14427.26 rows=90 loops=1) -> Sort (cost=44459.46..44459.46 rows=10101 width=566) (actual time=14426.91..14427.05 rows=91 loops=1) -> Merge Join (cost=853.84..41938.61 rows=10101 width=566) (actual time=123.25..14396.31 rows=10101 loops=1) -> Index Scan using shr_objects_pk on shr_objects obj (cost=0.00..37386.55 rows=1418686 width=544) (actual time=6.19..11769.85 rows=1418686 loops=1) -> Sort (cost=853.84..853.84 rows=10101 width=22) (actual time=117.02..134.60 rows=10101 loops=1) -> Seq Scan on smb_contacts cnt (cost=0.00..182.01 rows=10101 width=22) (actual time=0.03..27.14 rows=10101 loops=1) Total runtime: 14435.77 msec EXPLAIN ============================================================================== intranet=# intranet=# explain analyze SELECT obj.companyid, obj.name, obj.description, intranet-# cnt.firstname, cnt.lastname intranet-# FROM smb_contacts cnt JOIN shr_objects obj ON cnt.objectid = obj.objectid intranet-# limit 90; NOTICE: QUERY PLAN: Limit (cost=0.00..382.72 rows=90 width=566) (actual time=15.87..25.39 rows=90 loops=1) -> Merge Join (cost=0.00..42954.26 rows=10101 width=566) (actual time=15.86..25.08 rows=91 loops=1) -> Index Scan using objectid_fk on smb_contacts cnt (cost=0.00..1869.48 rows=10101 width=22) (actual time=15.76..16.32 rows=91 loops=1) -> Index Scan using shr_objects_pk on shr_objects obj (cost=0.00..37386.55 rows=1418686 width=544) (actual time=0.09..7.81 rows=193 loops=1) Total runtime: 25.60 msec EXPLAIN It is obvious that in the order by query the company index is not used and also it had to go thru all records in shr_objects. Can someone please tell me how is this happening and how to fix it. -- Hesham Gowaily