Re: postgres 9 query performance

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(at)anarazel(dot)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
>