Re: Execution plan Question - Mailing list pgsql-sql

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