pg.ddx.io  pgsql-performance@postgresql.org mailing list archive  
help / color / mirror / Atom feed
From: Alexander Dolgin <alex@dolgin.dp.ua>
To: pgsql-performance@postgresql.org
Subject: 200 times slower then MSSQL??
Date: Mon, 24 Jan 2005 20:33:39 +0200
Message-ID: <E1Ct8wq-0008En-00@lata.dp.ua> (raw)

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.




view thread (5+ messages)  latest in thread

Message-ID: <E1Ct8wq-0008En-00@lata.dp.ua>
Permalink:  ../E1Ct8wq-0008En-00@lata.dp.ua/
Also on:    postgresql.org/message-id/E1Ct8wq-0008En-00@lata.dp.ua

 · 

reply

Reply instructions:

You may reply publicly to this message via plain-text email
using any one of the following methods:

* Reply to all the recipients using the --to and --cc options:
  reply via email

  To: pgsql-performance@postgresql.org
  Cc: alex@dolgin.dp.ua
  Subject: Re: 200 times slower then MSSQL??
  In-Reply-To: <E1Ct8wq-0008En-00@lata.dp.ua>

* Save the following mbox file, import it into your mail client,
  and reply-to-all from there: mbox

This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox