OK, that did it. Time is now 315 ms. I am so exited working with postgres. I really apologize for the format, my first time posting on the list. That does not justify it though. Really thanks.
On Fri, Jan 28, 2011 at 1:19 PM, Andres Freund
<andres@anarazel.de> wrote:
On Friday, January 28, 2011 06:30:19 PM yazan suleiman wrote:
> I am evaluating postgres 9 to migrate away from Oracle. The following
> query runs too slow, also please find the explain plan:
First:
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
original formatting of the query...
What happens if you change the
left join event.origin on
event.id = origin.eventid
join event.origin on
event.id = origin.eventid
The EXISTS() requires that origin is not null anyway. (Not sure why the
planner doesn't recognize that though).
Andres
