From: Hesham Gowaily <heshamgowaily@yahoo.com>
To: pgsql-sql@postgresql.org
Subject: Execution plan problem
Date: 10 Mar 2003 17:40:53 +0200
Message-ID: <1047310853.3367.13.camel@EG1OPLLX107> (raw)
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 <heshamgowaily@yahoo.com>
Reply instructions:
You may reply publicly to this message via plain-text email
using any one of the following methods:
* Reply to all the recipients using the --to and --cc options:
reply via email
To: pgsql-sql@postgresql.org
Cc: heshamgowaily@yahoo.com
Subject: Re: Execution plan problem
In-Reply-To: <1047310853.3367.13.camel@EG1OPLLX107>
* Save the following mbox file, import it into your mail client,
and reply-to-all from there: mbox
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox