Re: Execution plan Question

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