Re: [PERFORM] postgres 9 query performance

2011-02-01 Thread Ross J. Reedstrom
On Sun, Jan 30, 2011 at 05:18:15PM -0500, Tom Lane wrote: Andres Freund and...@anarazel.de writes: 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

Re: [PERFORM] postgres 9 query performance

2011-01-30 Thread Tom Lane
Andres Freund and...@anarazel.de writes: 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

Re: [PERFORM] postgres 9 query performance

2011-01-29 Thread yazan suleiman
They have the same indexes, foreign keys are indexed in addition to the search values like magnitude. Distinct does nothing to speed up the query. If I remove the select in the where clause the time goes down to 98 ms: select DISTINCT EVENT.ID, ORIGIN.ID AS ORIGINID,EVENT.PREFERRED_ORIGIN_ID AS

Re: [PERFORM] postgres 9 query performance

2011-01-29 Thread yazan suleiman
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,

[PERFORM] postgres 9 query performance

2011-01-28 Thread yazan suleiman
I am evaluating postgres 9 to migrate away from Oracle. The following query runs too slow, also please find the explain plan: explain analyze select DISTINCT EVENT.ID, ORIGIN.ID AS ORIGINID,EVENT.PREFERRED_ORIGIN_ID AS

Re: [PERFORM] postgres 9 query performance

2011-01-28 Thread Scott Marlowe
On Fri, Jan 28, 2011 at 10:30 AM, yazan suleiman yazan.sulei...@gmail.com wrote: I am evaluating postgres 9 to migrate away from Oracle.  The following query runs too slow, also please find the explain plan: explain analyze

Re: [PERFORM] postgres 9 query performance

2011-01-28 Thread Kenneth Marshall
On Fri, Jan 28, 2011 at 09:30:19AM -0800, 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: explain analyze select DISTINCT

Re: [PERFORM] postgres 9 query performance

2011-01-28 Thread Andres Freund
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