Re: Query performance

[Date Prev][Date Next][Thread Prev][Thread Next][Date Index][Thread Index]

 



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@xxxxxxxxxxx>
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 ...


[Postgresql General]     [Postgresql PHP]     [PHP Users]     [PHP Home]     [PHP on Windows]     [Kernel Newbies]     [PHP Classes]     [PHP Books]     [PHP Databases]     [Yosemite]

  Powered by Linux