Re: postgres 9 query performance

[yazan suleiman <yazan.suleiman@xxxxxxxxx>]

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@xxxxxxxxxxx> 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