Re: Query performance

Sorry guys, i sent the required plan....

QUERY
PLAN
----------------------------------------------------------------------------------------------------------------------------------------------
Merge Left Join (cost=62422.81..67345.85 rows=286487 width=0) (actual
time=1459.355..2538.538 rows=325998 loops=1)
Merge Cond: (service_detail.service_detail_id =
non_service_detail.non_service_detail_service_id)
-> Sort (cost=*18617*.60..18930.47 rows=125146 width=8) (actual
time=425.115..560.807 rows=125146 loops=1)
Sort Key: service_detail.service_detail_id
Sort Method: external merge Disk: 2912kB
-> Seq Scan on service_detail (cost=0.00..6310.46 rows=125146
width=8) (actual time=0.056..114.925 rows=125146 loops=1)
-> Materialize (cost=43805.21..47386.30 rows=286487 width=8) (actual
time=1034.220..1617.313 rows=286491 loops=1)
-> Sort (cost=*43805*.21..44521.43 rows=286487 width=8) (actual
time=1034.204..1337.708 rows=286491 loops=1)
Sort Key: non_service_detail.non_service_detail_service_id
Sort Method: external merge Disk: 6720kB
-> Seq Scan on non_service_detail (cost=0.00..13917.87
rows=286487 width=8) (actual time=0.063..248.950 rows=286491 loops=1)
Total runtime: 2650.763 ms
(12 rows)

2009/10/12 Matthew Wakeling <matthew(at)flymine(dot)org>

> On Mon, 12 Oct 2009, Grzegorz Jaśkiewicz wrote:
>
>> try setting work_mem to higher value. As postgresql will fallback to disc
>> sorting if the
>> content doesn't fit in work_mem, which it probably doesn't (8.4+ show the
>> memory usage
>> for sorting, which your explain doesn't have).
>>
>
> For reference, here's the EXPLAIN:
>
> Merge Left Join (cost=62451.86..67379.08 rows=286789 width=0)
>> Merge Cond: (a.id = b.id)
>> -> Sort (cost=18610.57..18923.27 rows=125077 width=8)
>> Sort Key: a.id
>> -> Seq Scan on a (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: b.id
>> -> Seq Scan on b (cost=0.00..13920.89 rows=286789 width=8)
>>
>
> This is an EXPLAIN, not an EXPLAIN ANALYSE. If it was an EXPLAIN ANALYSE,
> it would show how much memory was used, and whether it was a disc sort or an
> in-memory sort. As it is only an EXPLAIN, the query hasn't actually been
> run, and we have no information about whether the sort would be performed on
> disc or not.
>
> Matthew
>
> --
> Hi! You have reached 555-0129. None of us are here to answer the phone and
> the cat doesn't have opposing thumbs, so his messages are illegible. Please
> leave your name and message after the beep ...