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 5A9C13A55E1 for ; Mon, 24 Jan 2005 18:28:29 +0000 (GMT) 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 81501-08 for ; Mon, 24 Jan 2005 18:28:26 +0000 (GMT) Received: from lata.dp.ua (ad-z2-14.dp.ukrtel.net [213.179.230.244]) by svr1.postgresql.org (Postfix) with ESMTP id 8D4683A5595 for ; Mon, 24 Jan 2005 18:28:25 +0000 (GMT) Received: from [192.168.0.16] (helo=alexd) by lata.dp.ua with esmtp (Exim 3.22 #1) id 1Ct8wq-0008En-00 for pgsql-performance@postgresql.org; Mon, 24 Jan 2005 20:28:24 +0200 From: "Alexander Dolgin" To: Subject: 200 times slower then MSSQL?? Date: Mon, 24 Jan 2005 20:33:39 +0200 MIME-Version: 1.0 Content-Type: text/plain; charset="us-ascii" Content-Transfer-Encoding: 7bit X-Mailer: Microsoft Office Outlook, Build 11.0.5510 X-MimeOLE: Produced By Microsoft MimeOLE V6.00.2800.1106 Thread-Index: AcUCQzjymbv/RvhoQQupt/MgwPTorw== Message-Id: X-Virus-Scanned: by amavisd-new at hub.org X-Spam-Status: No, hits=0.05 tagged_above=0 required=5 tests=FORGED_RCVD_HELO X-Spam-Level: X-Archive-Number: 200501/471 X-Sequence-Number: 10161 Hi all, We are developing some application that works with DB over JDBC. We've used MSSQL before and trying to migrate to PostgreSQL now. Unfortunately problems with performance are found. MSSQL with default configuration looks like much faster then PostgreSQL on the same hardware (PostgreSQL8 rc5 was used). I've tried to increase work_mem significant (work_mem = 262144) but it doesn't help. Here is result of simple benchmark. I have table CREATE TABLE elt_tcli_messagelog ( connectionname varchar(64), msgseqnum int4, connectionmessageid int4, logtimestamp varchar(64), isfromcounterparty char(1), msgtype varchar(64), possdupflag char(1), isoutofsequence char(1), ordtrnid varchar(64), ordrqstid varchar(64), counterrequestid varchar(64), clordid varchar(64), origclordid varchar(64), execid varchar(64), exectranstype varchar(64), exectype varchar(64), ordstatus varchar(64), lastqty float8, orderqty float8, cumqty float8, leavesqty float8, sendercompid varchar(64), targetcompid varchar(64), tradeaccthrchy varchar(64), tradeacctid varchar(64), routedtransactiondestination varchar(64), originatingconnectionname varchar(64), originatingconnectionmsgid int4, instrument varchar(64), portfolio varchar(64), prevseqnum int4, "Message" text, nonmetadatafields text ) with about 8000 rows. For this table query: SELECT MAX(MsgSeqNum),MAX(LogTimestamp) FROM ELT_tcli_MessageLog WHERE LogTimestamp >= '0' AND IsFromCounterParty = 'Y' AND IsOutOfSequence = 'N' AND ConnectionName = 'DB_BENCHMARK' AND LogTimestamp IN (SELECT MAX(LogTimestamp) FROM ELT_tcli_MessageLog WHERE MsgSeqNum > 0 AND IsFromCounterParty = 'Y' AND IsOutOfSequence = 'N' AND ConnectionName = 'DB_BENCHMARK') takes about 1 second on MSSQL Server and 257 seconds on PostgreSQL one. Does anybody have idea about reasons of such results? Thanks, Alexander Dolgin.