Re: Query performance
| От | S Arvind |
|---|---|
| Тема | Re: Query performance |
| Дата | |
| Msg-id | abf9211d0910120745x1fa5d986kf9326ff690ff95da@mail.gmail.com обсуждение исходный текст |
| Ответ на | Re: Query performance (Matthew Wakeling <matthew@flymine.org>) |
| Список | 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)
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@flymine.org>
On Mon, 12 Oct 2009, Grzegorz Jaśkiewicz wrote:For reference, here's the EXPLAIN: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).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.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)
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 ...
В списке pgsql-performance по дате отправления: