X-Original-To: pgsql-sql-postgresql.org@localhost.postgresql.org Received: from localhost (unknown [200.46.204.144]) by svr1.postgresql.org (Postfix) with ESMTP id 0A2B5540CC for ; Tue, 3 May 2005 04:16:19 -0300 (ADT) 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 81878-06 for ; Tue, 3 May 2005 07:16:08 +0000 (GMT) Received: from quasar.skima.is (quasar.skima.is [212.30.200.205]) by svr1.postgresql.org (Postfix) with ESMTP id 4A81452A88 for ; Tue, 3 May 2005 04:16:07 -0300 (ADT) Received: from gnari ([85.220.28.44] [85.220.28.44]) by quasar.skima.is with ESMTP; Tue, 3 May 2005 07:15:52 Z Subject: Re: Trimming the cost of ORDER BY in a simple query From: Ragnar =?ISO-8859-1?Q?Hafsta=F0?= To: ogjunk-pgjedan@yahoo.com Cc: pgsql-sql@postgresql.org In-Reply-To: <20050503043531.23516.qmail@web31106.mail.mud.yahoo.com> References: <20050503043531.23516.qmail@web31106.mail.mud.yahoo.com> Content-Type: text/plain Date: Tue, 03 May 2005 07:22:18 +0000 Message-Id: <1115104938.5853.25.camel@localhost.localdomain> Mime-Version: 1.0 X-Mailer: Evolution 2.0.2 Content-Transfer-Encoding: 7bit X-Virus-Scanned: by amavisd-new at hub.org X-Spam-Status: No, hits=0 tagged_above=0 required=5 tests= X-Spam-Level: X-Archive-Number: 200505/29 X-Sequence-Number: 21342 On Mon, 2005-05-02 at 21:35 -0700, ogjunk-pgjedan@YAHOO.COM wrote: > Query (shows the last 7 dates): > > => SELECT DISTINCT date_part('year', uu.add_date), date_part('month', > uu.add_date), date_part('day', uu.add_date) FROM user_url uu WHERE > uu.user_id=1 ORDER BY date_part('year', uu.add_date) DESC, > date_part('month', uu.add_date) DESC, date_part('day', uu.add_date) > DESC LIMIT 7; > i assume add_date is a timestamp, because otherwise you could ORDER by add_date, and use an index on (desc, add_date): SELECT DISTINCT .... WHERE uu.user_id=1 ORDER BY uu.user_id DESC ,uu.add_date DESC LIMIT 7; this will not work for a timestamp add_date > QUERY PLAN: ... > Total runtime: 20.313 ms 20 ms does not sound like a big problem. if 20 ms is really too much for you, and add_date IS a timestamp, you might think about adding a date column , maintained by triggers of by your application, and add an index on (user_id, x) another possibility (total guess) is a functional index create index func_id_date on user_url(user_id,(add_date::date)); SELECT ... ORDER BY uu.user_id DESC ,uu.add_date::date DESC LIMIT 7; I have no idea if this will work, or if the planner will use such an index. gnari