X-Original-To: pgsql-admin-postgresql.org@localhost.postgresql.org Received: from localhost (unknown [200.46.204.2]) by svr1.postgresql.org (Postfix) with ESMTP id 6A4F7D1D086 for ; Wed, 7 Apr 2004 19:21:43 -0300 (ADT) Received: from svr1.postgresql.org ([200.46.204.71]) by localhost (neptune.hub.org [200.46.204.2]) (amavisd-new, port 10024) with ESMTP id 39742-02 for ; Wed, 7 Apr 2004 19:21:43 -0300 (ADT) Received: from ptb-relay02.plus.net (ptb-relay02.plus.net [212.159.14.213]) by svr1.postgresql.org (Postfix) with ESMTP id 8EF45D1D068 for ; Wed, 7 Apr 2004 19:21:39 -0300 (ADT) Received: from [212.159.87.229] (helo=thelabyrinth.force9.co.uk) by ptb-relay02.plus.net with esmtp (Exim) id 1BBLQT-000Idx-7C; Wed, 07 Apr 2004 22:21:41 +0000 Received: from ruth.localdomain (ruth.localdomain [192.168.0.1]) by thelabyrinth.force9.co.uk (Postfix) with ESMTP id 9C36C30081; Wed, 7 Apr 2004 22:21:55 +0100 (BST) Date: Wed, 7 Apr 2004 23:23:17 +0100 (BST) From: Sam Barnett-Cormack X-X-Sender: sdb@localhost.localdomain To: Hemapriya Cc: pgsql-admin@postgresql.org Subject: Re: Query with Max, Order by is very slow....... In-Reply-To: <20040407210354.79531.qmail@web21322.mail.yahoo.com> Message-ID: References: <20040407210354.79531.qmail@web21322.mail.yahoo.com> MIME-Version: 1.0 Content-Type: TEXT/PLAIN; charset=US-ASCII X-Virus-Scanned: by amavisd-new at postgresql.org X-Archive-Number: 200404/130 X-Sequence-Number: 13103 On Wed, 7 Apr 2004, Hemapriya wrote: > Hi, > > we have table having 23 million rows. > This is the table structure. > Table Request: > > Column | Type | Modifiers > -----------+-----------------------------+----------- > origindb | character(1) | not null > uid | integer | not null > rtype | integer | > senderid | integer | > destaddr | character varying(15) | > opid | integer | > devmodel | integer | > ikind | integer | > itemid | character varying(10) | > tranfk | integer | > enteredon | timestamp without time zone | > status | integer | > accountid | integer | > Indexes: > "request_pkey" primary key, btree (origindb, uid) > > I do max Query like this > > select max(uid) from request where originDB=1; > > it took around 20 min to return the result.. Since > max, count functions do the full table scan, i tried > the workaround given.. > > select uid from request where originDB=1 order by uid > desc limit 1; > > this query runs forever.. i tried even without where > condition..no result.. You really want an index on origindb and uid - the order by ... desc limit 1 workaround is only quick if there's an index on the order by field, and and where clause is faster if it can use an index to speed up the query. I would say you might want an index on both of them together, a joint index. -- Sam Barnett-Cormack Software Developer | Student of Physics & Maths UK Mirror Service (http://www.mirror.ac.uk) | Lancaster University