X-Original-To: pgsql-sql-postgresql.org@localhost.postgresql.org Received: from localhost (av.hub.org [200.46.204.144]) by postgresql.org (Postfix) with ESMTP id 734149DC821 for ; Fri, 3 Feb 2006 05:21:38 -0400 (AST) Received: from postgresql.org ([200.46.204.71]) by localhost (av.hub.org [200.46.204.144]) (amavisd-new, port 10024) with ESMTP id 51983-05 for ; Fri, 3 Feb 2006 05:21:39 -0400 (AST) Received: from svr4.postgresql.org (svr4.postgresql.org [66.98.251.159]) by postgresql.org (Postfix) with ESMTP id 3DAD39DC82D for ; Fri, 3 Feb 2006 05:21:36 -0400 (AST) Received: from mx1.hive.is (isaserver.ipf.is [85.197.192.10]) by svr4.postgresql.org (Postfix) with SMTP id A85735AF85D for ; Fri, 3 Feb 2006 09:21:37 +0000 (GMT) Received: (qmail 30799 invoked by uid 0); 3 Feb 2006 09:20:37 +0000 Received: from unknown (HELO ?192.168.1.34?) (85.197.216.186) by postur.hive.is with SMTP; 3 Feb 2006 09:20:37 +0000 Subject: Re: CREATE INDEX with order clause From: Ragnar To: Daniel Caune Cc: pgsql-sql@postgresql.org In-Reply-To: <1E293D3FF63A3740B10AD5AAD88535D20171B758@UBIMAIL1.ubisoft.org> References: <1E293D3FF63A3740B10AD5AAD88535D20171B758@UBIMAIL1.ubisoft.org> Content-Type: text/plain; charset=iso-8859-7 Date: Fri, 03 Feb 2006 09:27:11 +0000 Message-Id: <1138958832.18918.49.camel@localhost.localdomain> Mime-Version: 1.0 X-Mailer: Evolution 2.2.1.1 Content-Transfer-Encoding: 8bit X-Virus-Scanned: by amavisd-new at hub.org X-Spam-Status: No, score=0.415 required=5 tests=[AWL=-0.176, UPPERCASE_50_75=0.591] X-Spam-Score: 0.415 X-Spam-Level: X-Archive-Number: 200602/29 X-Sequence-Number: 24039 On Wed, 2006-02-01 at 10:46 -0500, Daniel Caune wrote: > Hi, > [snip need for reverse-sort operator class] > > SELECT GAME_CLIENT_VERSION > FROM GSLOG_EVENT > WHERE PLAYER_USERNAME = ? > AND EVENT_NAME = ? > AND EVENT_DATE_CREATED < ? > ORDER BY EVENT_DATE_CREATED DESC > LIMIT 1 > > > Actually, I¢m not sure that is useful; perhaps PostgreSQL handles > pretty well such query using an index such as: > CREATE INDEX IDX_GSLOG_EVENT_PLAYER_EVENT_TIME_DESC > ON GSLOG_EVENT(PLAYER_USERNAME, > EVENT_NAME, > EVENT_DATE_CREATED); > > > Any idea? does index work with: SELECT GAME_CLIENT_VERSION FROM GSLOG_EVENT WHERE PLAYER_USERNAME = ? AND EVENT_NAME = ? AND EVENT_DATE_CREATED < ? ORDER BY PLAYER_USERNAM DESC, EVENT_NAME DESC, EVENT_DATE_CREATED DESC LIMIT 1 gnari