Re: postgres 9 query performance

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