From: Tomasz Myrta <jasiek@klaster.net>
To: Objectz <objectz@postmark.net>
Cc: pgsql-sql@postgresql.org
Subject: Re: Execution plan Question
Date: Tue, 11 Mar 2003 09:00:15 +0100
Message-ID: <3E6D978F.1080007@klaster.net> (raw)
References: <000001c2e78c$67a26990$aea067d4@eg1opwxp107>
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.
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
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: jasiek@klaster.net, objectz@postmark.net
Subject: Re: Execution plan Question
In-Reply-To: <3E6D978F.1080007@klaster.net>
* 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