X-Original-To: pgsql-performance-postgresql.org@localhost.postgresql.org Received: from localhost (unknown [200.46.204.144]) by svr1.postgresql.org (Postfix) with ESMTP id EC02CD1B751 for ; Fri, 18 Jun 2004 10:56:28 -0300 (ADT) Received: from svr1.postgresql.org ([200.46.204.71]) by localhost (av.hub.org [200.46.204.144]) (amavisd-new, port 10024) with ESMTP id 31833-05 for ; Fri, 18 Jun 2004 13:56:26 +0000 (GMT) Received: from anchor-post-32.mail.demon.net (anchor-post-32.mail.demon.net [194.217.242.90]) by svr1.postgresql.org (Postfix) with ESMTP id 0206BD1B4E5 for ; Fri, 18 Jun 2004 10:56:19 -0300 (ADT) Received: from mwynhau.demon.co.uk ([193.237.186.96] helo=mainbox.archonet.com) by anchor-post-32.mail.demon.net with esmtp (Exim 3.35 #1) id 1BbJqq-000Lxc-0W; Fri, 18 Jun 2004 14:56:16 +0100 Received: from [192.168.1.17] (client17.archonet.com [192.168.1.17]) by mainbox.archonet.com (Postfix) with ESMTP id 22FB316DD7; Fri, 18 Jun 2004 14:56:15 +0100 (BST) Message-ID: <40D2F47E.6060306@archonet.com> Date: Fri, 18 Jun 2004 14:56:14 +0100 From: Richard Huxton User-Agent: Mozilla Thunderbird 0.7 (X11/20040615) X-Accept-Language: en-us, en MIME-Version: 1.0 To: Gary Cowell Cc: pgsql-performance@postgresql.org Subject: Re: Major differences between oracle and postgres performance References: <20040618132554.72968.qmail@web25103.mail.ukl.yahoo.com> In-Reply-To: <20040618132554.72968.qmail@web25103.mail.ukl.yahoo.com> Content-Type: text/plain; charset=us-ascii; format=flowed Content-Transfer-Encoding: 7bit X-Virus-Scanned: by amavisd-new at hub.org X-Spam-Status: No, hits=0.0 tagged_above=0.0 required=5.0 tests= X-Spam-Level: X-Archive-Number: 200406/189 X-Sequence-Number: 7247 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. Which is basically the sorting time... > 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 Yep - the seq-scan takes 7214.903 ms, there's a huge setup time for the sort (63623.417) and it's not finished until 66127.641ms have elapsed. > > 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 Well, I'd probably up vacuum_mem, and check how much RAM is being used for disk cache - I'd guess it's more than 32MB (4000 * 8kb). You might want to up the shared_buffers, but that's going to depend on the load. Try increasing sort_mem temporarily, and see if that makes a difference: SET sort_mem = 64000; EXPLAIN ANALYSE ... The only thing I can think is that you're getting disk activity to get a sort that slow. I'd be expecting a hash-sort if PG thought it could fit the distinct values in memory. -- Richard Huxton Archonet Ltd