Re: Major differences between oracle and postgres performance - what can I do ?

--- 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