Skip site navigation (1) Skip section navigation (2)

Re: Query performance

From: S Arvind <arvindwill(at)gmail(dot)com>
To: pgsql-performance(at)postgresql(dot)org
Subject: Re: Query performance
Date: 2009-10-12 14:45:47
Message-ID: abf9211d0910120745x1fa5d986kf9326ff690ff95da@mail.gmail.com (view raw or flat)
Thread:
Lists: pgsql-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 ...

In response to

pgsql-performance by date

Next:From: S ArvindDate: 2009-10-12 14:52:53
Subject: Re: Query performance
Previous:From: Merlin MoncureDate: 2009-10-12 14:25:18
Subject: Re: updating a row in a table with only one row

Privacy Policy | About PostgreSQL
Copyright © 1996-2014 The PostgreSQL Global Development Group