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:
--- [EMAIL PROTECTED] wrote: > You can roughly estimate time spent for just scaningthe 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 [EMAIL PROTECTED])