--- [EMAIL PROTECTED] 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
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

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;
 Unique  (cost=117865.77..120574.48 rows=142
width=132) (actual time=63623.428..68269.111 rows=536
   ->  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])

Reply via email to