pg.ddx.io  pgsql-performance@postgresql.org mailing list archive  
help / color / mirror / Atom feed
200 times slower then MSSQL??
5+ messages / 5 participants
[nested] [flat]

* 200 times slower then MSSQL??
@ 2005-01-24 18:33 Alexander Dolgin <alex@dolgin.dp.ua>
  2005-01-25 17:52 ` Re: 200 times slower then MSSQL?? Christopher Kings-Lynne <chriskl@familyhealth.com.au>
  2005-01-25 18:02 ` Re: 200 times slower then MSSQL?? Tom Lane <tgl@sss.pgh.pa.us>
  2005-01-25 18:09 ` Re: 200 times slower then MSSQL?? Kaloyan Iliev Iliev <news1@faith.digsys.bg>
  2005-01-26 21:10 ` Re: 200 times slower then MSSQL?? PFC <lists@boutiquenumerique.com>
  0 siblings, 4 replies; 5+ messages in thread

From: Alexander Dolgin @ 2005-01-24 18:33 UTC (permalink / raw)
  To: pgsql-performance

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.




^ permalink  raw  reply  [nested|flat] 5+ messages in thread

* Re: 200 times slower then MSSQL??
  2005-01-24 18:33 200 times slower then MSSQL?? Alexander Dolgin <alex@dolgin.dp.ua>
@ 2005-01-25 17:52 ` Christopher Kings-Lynne <chriskl@familyhealth.com.au>
  3 siblings, 0 replies; 5+ messages in thread

From: Christopher Kings-Lynne @ 2005-01-25 17:52 UTC (permalink / raw)
  To: Alexander Dolgin <alex@dolgin.dp.ua>; +Cc: pgsql-performance


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

1. Have you run vaccum analyze recently?
2. Reply with the output of EXPLAIN ANALYZE SELECT...

Chris



^ permalink  raw  reply  [nested|flat] 5+ messages in thread

* Re: 200 times slower then MSSQL??
  2005-01-24 18:33 200 times slower then MSSQL?? Alexander Dolgin <alex@dolgin.dp.ua>
@ 2005-01-25 18:02 ` Tom Lane <tgl@sss.pgh.pa.us>
  3 siblings, 0 replies; 5+ messages in thread

From: Tom Lane @ 2005-01-25 18:02 UTC (permalink / raw)
  To: Alexander Dolgin <alex@dolgin.dp.ua>; +Cc: pgsql-performance

"Alexander Dolgin" <alex@dolgin.dp.ua> writes:
> Does anybody have idea about reasons of such results?

Try converting the MAX() functions to queries that will use indexes.
See FAQ entry 4.7 "My queries are slow or don't make use of the
indexes. Why?"

			regards, tom lane



^ permalink  raw  reply  [nested|flat] 5+ messages in thread

* Re: 200 times slower then MSSQL??
  2005-01-24 18:33 200 times slower then MSSQL?? Alexander Dolgin <alex@dolgin.dp.ua>
@ 2005-01-25 18:09 ` Kaloyan Iliev Iliev <news1@faith.digsys.bg>
  3 siblings, 0 replies; 5+ messages in thread

From: Kaloyan Iliev Iliev @ 2005-01-25 18:09 UTC (permalink / raw)
  To: Alexander Dolgin <alex@dolgin.dp.ua>; pgsql-performance

Hi,
First it will be good if you supply some EXPLAIN ANALYZE results from 
your query.
Second, do you created the indexes which can be used with WHERE conditions.
And Third AFAK MAX doesn't use index. If you only need max then you can try:

ORDER BY .... DESC  and  LIMIT 1. But you can't use this if you want to 
select the two max values at once.
I am not an expert so if I am wrong, please someone to correct me.

Kaloyan

Alexander Dolgin wrote:

>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.
>
>
>---------------------------(end of broadcast)---------------------------
>TIP 1: subscribe and unsubscribe commands go to majordomo@postgresql.org
>
>
>  
>



^ permalink  raw  reply  [nested|flat] 5+ messages in thread

* Re: 200 times slower then MSSQL??
  2005-01-24 18:33 200 times slower then MSSQL?? Alexander Dolgin <alex@dolgin.dp.ua>
@ 2005-01-26 21:10 ` PFC <lists@boutiquenumerique.com>
  3 siblings, 0 replies; 5+ messages in thread

From: PFC @ 2005-01-26 21:10 UTC (permalink / raw)
  To: Alexander Dolgin <alex@dolgin.dp.ua>; pgsql-performance


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

	Can you explain (with words) what this query is supposed to return ? It  
is probably possible to write it in an entirely different way.
	Basically your problem is that max() in postgres does not use an index  
the way you think it should.
	"SELECT max(x) FROM t" should be written "SELECT x FROM t ORDER BY x DESC  
LIMIT 1" to use the index. Depending on additional Where conditions, you  
should add other columns to your index and also order-by clause.



^ permalink  raw  reply  [nested|flat] 5+ messages in thread


end of thread, other threads:[~2005-01-26 21:10 UTC | newest]

Thread overview: 5+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2005-01-24 18:33 200 times slower then MSSQL?? Alexander Dolgin <alex@dolgin.dp.ua>
2005-01-25 17:52 ` Christopher Kings-Lynne <chriskl@familyhealth.com.au>
2005-01-25 18:02 ` Tom Lane <tgl@sss.pgh.pa.us>
2005-01-25 18:09 ` Kaloyan Iliev Iliev <news1@faith.digsys.bg>
2005-01-26 21:10 ` PFC <lists@boutiquenumerique.com>

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