From: Objectz <objectz@postmark.net>
To: pgsql-sql@postgresql.org
Subject: Re: Execution plan Question
Date: Tue, 11 Mar 2003 07:09:26 +0200
Message-ID: <000001c2e78c$67a26990$aea067d4@eg1opwxp107> (raw)
In-Reply-To: <3E6CC7C4.5050908@klaster.net>
Oops .. Here they are
========================================================================
=====
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.
Objectz wrote:
> 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.
>
> Regards,
> --ObjectZ Maker
Well, it looks like you didn't include neither queries nor there
execution plan. Don't forget to vacuum analyze before explain analyze
your queries.
Anyway, how many rows do you want to be sorted?
Regards,
Tomasz Myrta
---------------------------(end of broadcast)---------------------------
TIP 5: Have you checked our extensive FAQ?
http://www.postgresql.org/docs/faqs/FAQ.html
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: objectz@postmark.net
Subject: Re: Execution plan Question
In-Reply-To: <000001c2e78c$67a26990$aea067d4@eg1opwxp107>
* 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