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 E6DB2D1D089 for ; Wed, 7 Apr 2004 23:07:02 -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 08383-09 for ; Wed, 7 Apr 2004 23:07:04 -0300 (ADT) Received: from sss.pgh.pa.us (sss.pgh.pa.us [66.207.139.130]) by svr1.postgresql.org (Postfix) with ESMTP id E312AD1D081 for ; Wed, 7 Apr 2004 23:06:59 -0300 (ADT) Received: from sss2.sss.pgh.pa.us (tgl@localhost [127.0.0.1]) by sss.pgh.pa.us (8.12.11/8.12.11) with ESMTP id i38271ve007200; Wed, 7 Apr 2004 22:07:02 -0400 (EDT) To: Sam Barnett-Cormack Cc: Hemapriya , pgsql-admin@postgresql.org Subject: Re: Query with Max, Order by is very slow....... In-reply-to: References: <20040407210354.79531.qmail@web21322.mail.yahoo.com> Comments: In-reply-to Sam Barnett-Cormack message dated "Wed, 07 Apr 2004 23:23:17 +0100" Date: Wed, 07 Apr 2004 22:07:01 -0400 Message-ID: <7199.1081390021@sss.pgh.pa.us> From: Tom Lane X-Virus-Scanned: by amavisd-new at postgresql.org X-Archive-Number: 200404/134 X-Sequence-Number: 13107 Sam Barnett-Cormack writes: > On Wed, 7 Apr 2004, Hemapriya wrote: >> Column | Type | Modifiers >> -----------+-----------------------------+----------- >> origindb | character(1) | not null >> uid | integer | not null >> ... >> Indexes: >> "request_pkey" primary key, btree (origindb, uid) >> >> select max(uid) from request where originDB=1; > You really want an index on origindb and uid - He's got one ;-). The real problem with this is the datatype mismatch is preventing use of the index. The query should be select max(uid) from request where originDB='1'; or else change the datatype of origindb to be integer. This query will still want to access all the rows with originDB='1', however. If there are a lot of those then you'll want to use the order by/limit hack. Correct application of the hack to this case goes like regression=# explain select uid from request where originDB='1' order by originDB desc, uid desc limit 1; QUERY PLAN -------------------------------------------------------------------------------------------- Limit (cost=0.00..3.41 rows=1 width=9) -> Index Scan Backward using request_pkey on request (cost=0.00..17.07 rows=5 width=9) Index Cond: (origindb = '1'::bpchar) (3 rows) If EXPLAIN doesn't show you a sort-free plan then you haven't gotten it right. regards, tom lane