Re: Execution plan Question - Mailing list pgsql-sql

i was able to fix the problem but i still dont know the reason. I
discovered the column objectid in table smb_contacts was int4 and not
int8. obviously all values were less then max(int4) dso there was no
problems in that. However I still need to know why the order by trigger
this to happen. I am joining between the 2 tables on the objectid which
of different datatypes in this case (int8 and int4) but without the
order by clause it was pretty fast.

Now after i changed the objectid cilumn in smb_contacts the performance
is as quick as before.

here is the execution plan after i ran the same query (with order
caluse) after changing datatypes

Limit  (cost=52044.46..52044.46 rows=90 width=1970) (actual
time=395.81..396.16 rows=90 loops=1) ->  Sort  (cost=52044.46..52044.46 rows=10101 width=1970) (actual
time=395.80..395.96 rows=91 loops=1)       ->  Nested Loop  (cost=0.00..30752.64 rows=10101 width=1970)
(actual time=0.11..360.99 rows=10104 loops=1)             ->  Seq Scan on smb_contacts cnt  (cost=0.00..187.01
rows=10101 width=1398) (actual time=0.01..48.57 rows=10104 loops=1)             ->  Index Scan using shr_objects_pk on
shr_objectsobj 
 
(cost=0.00..3.01 rows=1 width=572) (actual time=0.02..0.02 rows=1
loops=10104)
Total runtime: 438.96 msec

EXPLAIN


i need to know what happened in here

On Tue, 2003-03-11 at 07:09, Objectz wrote:
> 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
> 
> 
> ---------------------------(end of broadcast)---------------------------
> TIP 5: Have you checked our extensive FAQ?
> 
> http://www.postgresql.org/docs/faqs/FAQ.html