postgres 9 query performance

From: yazan suleiman <yazan(dot)suleiman(at)gmail(dot)com>
To: pgsql-performance(at)postgresql(dot)org
Subject: postgres 9 query performance
Date: 2011-01-28 17:30:19
Message-ID: AANLkTi=2peg+cbGLb1Tn2Bf+-rnq3YfOO_8JaOrJCP0X@mail.gmail.com
Views: Raw Message | Whole Thread | Download mbox | Resend email
Thread:
Lists: 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

Responses

Browse pgsql-performance by date

  From Date Subject
Next Message Scott Carey 2011-01-28 17:44:33 Re: How to best use 32 15k.7 300GB drives?
Previous Message Stephen Frost 2011-01-28 17:28:10 Re: How to best use 32 15k.7 300GB drives?