postgres 9 query performance

Поиск
Список
Период
Сортировка
От yazan suleiman
Тема postgres 9 query performance
Дата
Msg-id AANLkTi=2peg+cbGLb1Tn2Bf+-rnq3YfOO_8JaOrJCP0X@mail.gmail.com
обсуждение исходный текст
Ответы Re: postgres 9 query performance
Re: postgres 9 query performance
Re: postgres 9 query performance
Список pgsql-performance
I am evaluating postgres 9 to migrate away from Oracle.  The following query runs too slow, also please find the explain plan:

****************************************************************
explain analyze select DISTINCT EVENT.ID, ORIGIN.ID AS ORIGINID,EVENT.PREFERRED_ORIGIN_ID AS PREFERRED_ORIGIN, 
EVENT.CONTRIBUTOR, ORIGIN.TIME, ORIGIN.LATITUDE, ORIGIN.LONGITUDE, ORIGIN.DEPTH,ORIGIN.EVTYPE, 
ORIGIN.CATALOG, ORIGIN.AUTHOR OAUTHOR, ORIGIN.CONTRIBUTOR OCONTRIBUTOR,MAGNITUDE.ID AS MAGID,
MAGNITUDE.MAGNITUDE,MAGNITUDE.TYPE AS MAGTYPE 
from event.event left join event.origin on event.id=origin.eventid left join event.magnitude on origin.id=event.magnitude.origin_id 
WHERE EXISTS(select origin_id from event.magnitude where  magnitude.magnitude>=7.2 and origin.id=origin_id) 
order by ORIGIN.TIME desc,MAGNITUDE.MAGNITUDE desc,EVENT.ID,EVENT.PREFERRED_ORIGIN_ID,ORIGIN.ID


"Unique  (cost=740549.86..741151.42 rows=15039 width=80) (actual time=17791.557..17799.092 rows=5517 loops=1)"
"  ->  Sort  (cost=740549.86..740587.45 rows=15039 width=80) (actual time=17791.556..17792.220 rows=5517 loops=1)"
"        Sort Key: origin."time", event.magnitude.magnitude, event.id, event.preferred_origin_id, origin.id, event.contributor, origin.latitude, origin.longitude, origin.depth, origin.evtype, origin.catalog, origin.author, origin.contributor, event.magnitude.id, event.magnitude.type"
"        Sort Method:  quicksort  Memory: 968kB"
"        ->  Nested Loop Left Join  (cost=34642.50..739506.42 rows=15039 width=80) (actual time=6.927..17769.788 rows=5517 loops=1)"
"              ->  Hash Semi Join  (cost=34642.50..723750.23 rows=14382 width=62) (actual time=6.912..17744.858 rows=2246 loops=1)"
"                    Hash Cond: (origin.id = event.magnitude.origin_id)"
"                    ->  Merge Left Join  (cost=0.00..641544.72 rows=6133105 width=62) (actual time=0.036..16221.008 rows=6133105 loops=1)"
"                          Merge Cond: (event.id = origin.eventid)"
"                          ->  Index Scan using event_key_index on event  (cost=0.00..163046.53 rows=3272228 width=12) (actual time=0.017..1243.616 rows=3276192 loops=1)"
"                          ->  Index Scan using origin_fk_index on origin  (cost=0.00..393653.81 rows=6133105 width=54) (actual time=0.013..3033.657 rows=6133105 loops=1)"
"                    ->  Hash  (cost=34462.73..34462.73 rows=14382 width=4) (actual time=6.668..6.668 rows=3198 loops=1)"
"                          Buckets: 2048  Batches: 1  Memory Usage: 113kB"
"                          ->  Bitmap Heap Scan on magnitude  (cost=324.65..34462.73 rows=14382 width=4) (actual time=1.682..5.414 rows=3198 loops=1)"
"                                Recheck Cond: (magnitude >= 7.2)"
"                                ->  Bitmap Index Scan on mag_index  (cost=0.00..321.05 rows=14382 width=0) (actual time=1.331..1.331 rows=3198 loops=1)"
"                                      Index Cond: (magnitude >= 7.2)"
"              ->  Index Scan using mag_fkey_index on magnitude  (cost=0.00..1.06 rows=3 width=22) (actual time=0.007..0.009 rows=2 loops=2246)"
"                    Index Cond: (origin.id = event.magnitude.origin_id)"
"Total runtime: 17799.669 ms"
****************************************************************

This query runs in Oracle in 1 second while takes 16 seconds in postgres, The difference tells me that I am doing something wrong somewhere.  This is a new installation on a local Mac machine with 12G of RAM.

I have:
effective_cache_size=4096MB 
shared_buffer=2048MB
work_mem=100MB

В списке pgsql-performance по дате отправления:

Предыдущее
От: Scott Marlowe
Дата:
Сообщение: Re: How to best use 32 15k.7 300GB drives?
Следующее
От: Scott Marlowe
Дата:
Сообщение: Re: postgres 9 query performance