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 88BD5D1BAF7 for ; Wed, 7 Apr 2004 18:03:57 -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 08469-04 for ; Wed, 7 Apr 2004 18:03:57 -0300 (ADT) Received: from web21322.mail.yahoo.com (web21322.mail.yahoo.com [216.136.175.208]) by svr1.postgresql.org (Postfix) with SMTP id CEF29D1B9EB for ; Wed, 7 Apr 2004 18:03:53 -0300 (ADT) Message-ID: <20040407210354.79531.qmail@web21322.mail.yahoo.com> Received: from [65.115.48.30] by web21322.mail.yahoo.com via HTTP; Wed, 07 Apr 2004 14:03:54 PDT Date: Wed, 7 Apr 2004 14:03:54 -0700 (PDT) From: Hemapriya Subject: Query with Max, Order by is very slow....... To: pgsql-admin@postgresql.org MIME-Version: 1.0 Content-Type: text/plain; charset=us-ascii X-Virus-Scanned: by amavisd-new at postgresql.org X-Archive-Number: 200404/124 X-Sequence-Number: 13097 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.. I'm not able to figure out what could be the reason.. can anybody help? Thanks in Advance Regards Priya __________________________________ Do you Yahoo!? Yahoo! Small Business $15K Web Design Giveaway http://promotions.yahoo.com/design_giveaway/