Re: Query performance

On Mon, Oct 12, 2009 at 12:21 PM, S Arvind <arvindwill(at)gmail(dot)com> wrote:

> In the below query both table has less than 1 million data. Can u tell me
> the reason of this plan?
> why its takin extensive cost , seq scan and sorting?? wat is Materialize?
>
> select 1 from service_detail
> left join non_service_detail on non_service_detail_service_id =
> service_detail.service_detail_id
>
>
>
> Merge Left Join (cost=62451.86..67379.08 rows=286789 width=0)
> Merge Cond: (service_detail.service_detail_id =
> non_service_detail.non_service_detail_service_id)
> -> Sort (cost=18610.57..18923.27 rows=125077 width=8)
> Sort Key: service_detail.service_detail_id
> -> Seq Scan on service_detail (cost=0.00..6309.77 rows=125077
> width=8)
> -> Materialize (cost=43841.28..47426.15 rows=286789 width=8)
> -> Sort (cost=43841.28..44558.26 rows=286789 width=8)
> Sort Key: non_service_detail.non_service_detail_service_id
> -> Seq Scan on non_service_detail (cost=0.00..13920.89
> rows=286789 width=8)
>
> A) it is a left join, meaning - everything is pulled from left side,
B) there are no conditions, so ... ... everything is pulled again from left
side.

--
GJ