Re: 200 times slower then MSSQL?? - Mailing list 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.