From: Andres Freund <andres@anarazel.de>
To: pgsql-performance@postgresql.org
Subject: Re: postgres 9 query performance
Date: Fri, 28 Jan 2011 22:19:29 +0100
User-Agent: KMail/1.13.5 (Linux/2.6.38-rc2-andres-00244-gbffb276; KDE/4.6.0;
	x86_64; ; )
Cc: yazan suleiman <yazan.suleiman@gmail.com>
References: <AANLkTi=2peg+cbGLb1Tn2Bf+-rnq3YfOO_8JaOrJCP0X@mail.gmail.com>
In-Reply-To: <AANLkTi=2peg+cbGLb1Tn2Bf+-rnq3YfOO_8JaOrJCP0X@mail.gmail.com>
MIME-Version: 1.0
Content-Type: Text/Plain;
  charset="iso-8859-15"
Content-Transfer-Encoding: 7bit
Message-Id: <201101282219.29777.andres@anarazel.de>

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