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 89E8E3A57E2 for ; Wed, 26 Jan 2005 21:08:19 +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 57117-07 for ; Wed, 26 Jan 2005 21:08:16 +0000 (GMT) Received: from boutiquenumerique.com (boutiquenumerique.com [82.67.9.10]) by svr1.postgresql.org (Postfix) with ESMTP id 117EF3A57DB for ; Wed, 26 Jan 2005 21:08:16 +0000 (GMT) Received: (qmail 17684 invoked from network); 26 Jan 2005 22:08:32 +0100 Received: from unknown (HELO musicbox) (boutiquenumerique-lists@192.168.0.2) by boutiquenumerique.com with SMTP; 26 Jan 2005 22:08:32 +0100 To: "Alexander Dolgin" , pgsql-performance@postgresql.org Subject: Re: 200 times slower then MSSQL?? References: Message-ID: Date: Wed, 26 Jan 2005 22:10:19 +0100 From: PFC Organization: =?iso-8859-15?Q?La_Boutique_Num=E9rique?= Content-Type: text/plain; format=flowed; delsp=yes; charset=iso-8859-15 MIME-Version: 1.0 Content-Transfer-Encoding: 8bit In-Reply-To: User-Agent: Opera M2/7.54 (Linux, build 751) X-Virus-Scanned: by amavisd-new at hub.org X-Spam-Status: No, hits=0.107 tagged_above=0 required=5 tests=AWL, UPPERCASE_25_50 X-Spam-Level: X-Archive-Number: 200501/515 X-Sequence-Number: 10205 > 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.