Thread: Execution plan Question
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 nullcompanyid int4 not nullname 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 nullobjectid int8 not nullfirstname 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
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
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
Objectz wrote:> Oops .. Here they are>> ========================================================================> =====>>intranet=# explain analyze SELECT obj.companyid, obj.name,> obj.description, intranet-# cnt.firstname, cnt.lastnameintranet-# FROM> smb_contacts cnt JOIN shr_objects obj ON cnt.objectid = obj.objectid> intranet-# order by obj.companyidintranet-# limit 90;> NOTICE: QUERY PLAN:>> Limit (cost=44459.46..44459.46 rows=90 width=566) (actual> time=14426.92..14427.26rows=90 loops=1)> -> Sort (cost=44459.46..44459.46 rows=10101 width=566) (actual> time=14426.91..14427.05rows=91 loops=1)> -> Merge Join (cost=853.84..41938.61 rows=10101 width=566)> (actual time=123.25..14396.31rows=10101 loops=1)> -> Index Scan using shr_objects_pk on shr_objects obj> (cost=0.00..37386.55rows=1418686 width=544) (actual time=6.19..11769.85> rows=1418686 loops=1)> -> Sort (cost=853.84..853.84rows=10101 width=22)> (actual time=117.02..134.60 rows=10101 loops=1)> -> Seq Scanon 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.lastnameintranet-# 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)> -> IndexScan using objectid_fk on smb_contacts cnt> (cost=0.00..1869.48 rows=10101 width=22) (actual time=15.76..16.32> rows=91loops=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 querythe company index is not used> and also it had to go thru all records in shr_objects.> Can someone please tell me howis this happening and how to fix it. Well - it's not an "order by" problem, but combination of "order by and limit" Look at your execution plan without order by. Postgres thinks it has to result 10000 rows (cost 0.00..1869), but you have "limit 90" and it stops working after 90 rows. It doesn't have more than 200 rows to work. The case with order by is much more complicated. Postgres have to retrieve all 10000 rows , sort all of them and after all give you first 90 rows. In this case there are up to 1400000 rows to work. Try to rewrite your query to return less rows (for example 1000) before sorting/limiting them. Taking 90 of 1000000 rows will be allways a performance leak. Regards, Tomasz Myrta
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