Re: Major differences between oracle and postgres performance

Hi ,
I have similare problem and found that the problem is by pg sort.
It is extremly slow by me.

Also in my case I tryed to migrate one db from oracle to pg .

To solve this problem I dinamicaly set sort_mem to some big value.
In this case the sort is working into RAM and is relative fast.
You can try this and remember sort mem is per sort, not per connection.

In my migration I found the only advantage for oracle is the very good sort.

regards,
ivan.

Gary Cowell wrote:

>--- lnd(at)hnit(dot)is wrote: > You can roughly estimate time
>spent for just scaning
>
>
>>the table using
>>something like this:
>>
>> select sum(version) from ... where version is not
>>null
>>
>> and just
>>
>> select sum(version) from ...
>>
>>The results would be interesting to compare.
>>
>>
>
>To answer (I hope) everyones questions at once:
>
>1) Oracle and postmaster were not running at the same
>time
>2) The queries were run once, to cache as much as
>possible then run again to get the timing
>
>3) Distinct vs. no distinct (i.e. sort performance).
>
>select length(version) from vers where version is not
>null;
>
>Time: 9748.174 ms
>
>select distinct(version) from vers where version is
>not null;
>
>Time: 67988.972 ms
>
>So about an extra 60 seconds with the distinct on.
>
>Here is the explain analyze output from psql:
>
># explain analyze select distinct version from vers
>where version is not null;
>
> QUERY PLAN
>-----------------------------------------------------------------------------------------------------------------------------------
> Unique (cost=117865.77..120574.48 rows=142
>width=132) (actual time=63623.428..68269.111 rows=536
>loops=1)
> -> Sort (cost=117865.77..119220.13 rows=541741
>width=132) (actual time=63623.417..66127.641
>rows=541741 loops=1)
> Sort Key: "version"
> -> Seq Scan on vers (cost=0.00..21367.41
>rows=541741 width=132) (actual time=0.218..7214.903
>rows=541741 loops=1)
> Filter: ("version" IS NOT NULL)
> Total runtime: 68324.215 ms
>(6 rows)
>
>Time: 68326.062 ms
>
>
>And the non-default .conf parameters:
>
>tcpip_socket = true
>max_connections = 100
>password_encryption = true
>shared_buffers = 2000
>sort_mem = 16384
>vacuum_mem = 8192
>effective_cache_size = 4000
>syslog = 2
>
>postgresql version is 7.4.3
>compiled with GCC 3.3.2 on sun4u architecture.
>
>
>
>
>
>
>
>
>___________________________________________________________ALL-NEW Yahoo! Messenger - sooooo many all-new ways to express yourself http://uk.messenger.yahoo.com
>
>---------------------------(end of broadcast)---------------------------
>TIP 2: you can get off all lists at once with the unregister command
> (send "unregister YourEmailAddressHere" to majordomo(at)postgresql(dot)org)
>
>
>
>