From: Andres Freund <andres@anarazel.de>
To: pgsql-performance@postgresql.org
Cc: yazan suleiman <yazan.suleiman@gmail.com>
Subject: Re: postgres 9 query performance
Date: Fri, 28 Jan 2011 22:19:29 +0100
Message-ID: <201101282219.29777.andres@anarazel.de> (raw)
In-Reply-To: <AANLkTi=2peg+cbGLb1Tn2Bf+-rnq3YfOO_8JaOrJCP0X@mail.gmail.com>
References: <AANLkTi=2peg+cbGLb1Tn2Bf+-rnq3YfOO_8JaOrJCP0X@mail.gmail.com>
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
Reply instructions:
You may reply publicly to this message via plain-text email
using any one of the following methods:
* Reply to all the recipients using the --to and --cc options:
reply via email
To: pgsql-performance@postgresql.org
Cc: andres@anarazel.de, yazan.suleiman@gmail.com
Subject: Re: postgres 9 query performance
In-Reply-To: <201101282219.29777.andres@anarazel.de>
* Save the following mbox file, import it into your mail client,
and reply-to-all from there: mbox
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox