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 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 magnitude.magnitude>=7.2 order by ORIGIN.TIME desc,MAGNITUDE.MAGNITUDE desc,EVENT.ID,EVENT.PREFERRED_ORIGIN_ID,ORIGIN.ID The new query returns 4000 rows, so the result is still big. I am not sure if I am answering your question, but I don't have access to generate ddl from Oracle. Thanks for the reply. On Fri, Jan 28, 2011 at 12:50 PM, Kenneth Marshall <k...@rice.edu> wrote: > 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 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 > > > > > > "Unique (cost=740549.86..741151.42 rows=15039 width=80) (actual > > time=17791.557..17799.092 rows=5517 loops=1)" > > " -> Sort (cost=740549.86..740587.45 rows=15039 width=80) (actual > > time=17791.556..17792.220 rows=5517 loops=1)" > > " Sort Key: origin."time", event.magnitude.magnitude, event.id, > > event.preferred_origin_id, origin.id, event.contributor, > origin.latitude, > > origin.longitude, origin.depth, origin.evtype, origin.catalog, > > origin.author, origin.contributor, event.magnitude.id, > event.magnitude.type" > > " Sort Method: quicksort Memory: 968kB" > > " -> Nested Loop Left Join (cost=34642.50..739506.42 rows=15039 > > width=80) (actual time=6.927..17769.788 rows=5517 loops=1)" > > " -> Hash Semi Join (cost=34642.50..723750.23 rows=14382 > > width=62) (actual time=6.912..17744.858 rows=2246 loops=1)" > > " Hash Cond: (origin.id = event.magnitude.origin_id)" > > " -> Merge Left Join (cost=0.00..641544.72 > rows=6133105 > > width=62) (actual time=0.036..16221.008 rows=6133105 loops=1)" > > " Merge Cond: (event.id = origin.eventid)" > > " -> Index Scan using event_key_index on event > > (cost=0.00..163046.53 rows=3272228 width=12) (actual > time=0.017..1243.616 > > rows=3276192 loops=1)" > > " -> Index Scan using origin_fk_index on origin > > (cost=0.00..393653.81 rows=6133105 width=54) (actual > time=0.013..3033.657 > > rows=6133105 loops=1)" > > " -> Hash (cost=34462.73..34462.73 rows=14382 > width=4) > > (actual time=6.668..6.668 rows=3198 loops=1)" > > " Buckets: 2048 Batches: 1 Memory Usage: > 113kB" > > " -> Bitmap Heap Scan on magnitude > > (cost=324.65..34462.73 rows=14382 width=4) (actual time=1.682..5.414 > > rows=3198 loops=1)" > > " Recheck Cond: (magnitude >= 7.2)" > > " -> Bitmap Index Scan on mag_index > > (cost=0.00..321.05 rows=14382 width=0) (actual time=1.331..1.331 > rows=3198 > > loops=1)" > > " Index Cond: (magnitude >= 7.2)" > > " -> Index Scan using mag_fkey_index on magnitude > > (cost=0.00..1.06 rows=3 width=22) (actual time=0.007..0.009 rows=2 > > loops=2246)" > > " Index Cond: (origin.id = > event.magnitude.origin_id)" > > "Total runtime: 17799.669 ms" > > **************************************************************** > > > > This query runs in Oracle in 1 second while takes 16 seconds in postgres, > > The difference tells me that I am doing something wrong somewhere. This > is > > a new installation on a local Mac machine with 12G of RAM. > > > > I have: > > effective_cache_size=4096MB > > shared_buffer=2048MB > > work_mem=100MB > > It sounds like the queries are not doing the same thing. What is > the schema/index definition for Oracle versus PostgreSQL? > > Ken >
