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@hnit.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@postgresql.org)
