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.
---------------------------(end of broadcast)--------------------------- TIP 6: Have you searched our list archives?
http://archives.postgresql.org
