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 <and...@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 > > I am honestly stumped if anybody can figure something sensible out of the > original formatting of the query... > > What happens if you change the > left join event.origin on event.id = origin.eventid > into > 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 >
