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 878B353767
	for <pgsql-sql-postgresql.org@localhost.postgresql.org>;
	Tue,  3 May 2005 05:08:20 -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 88111-06
	for <pgsql-sql-postgresql.org@localhost.postgresql.org>;
	Tue,  3 May 2005 08:08:19 +0000 (GMT)
Received: from floppy.pyrenet.fr (floppy.pyrenet.fr [194.116.145.2])
	by svr1.postgresql.org (Postfix) with ESMTP id BEC8253488
	for <pgsql-sql@postgresql.org>; Tue,  3 May 2005 05:08:17 -0300 (ADT)
Received: by floppy.pyrenet.fr (Postfix, from userid 106)
	id 1275C30952; Tue,  3 May 2005 10:06:27 +0200 (MET DST)
From: Ezequiel Tolnay <mail@etolnay.com.ar>
X-Newsgroups: pgsql.sql
Subject: Re: Trimming the cost of ORDER BY in a simple query
Date: Tue, 03 May 2005 18:08:23 +1000
Organization: Hub.Org Networking Services
Lines: 26
Message-ID: <d57bhc$2nfr$1@news.hub.org>
References: <20050503043531.23516.qmail@web31106.mail.mud.yahoo.com>
	<1115104938.5853.25.camel@localhost.localdomain>
Mime-Version: 1.0
Content-Type: text/plain; charset=ISO-8859-1; format=flowed
Content-Transfer-Encoding: 8bit
X-Complaints-To: usenet@news.hub.org
User-Agent: Mozilla/5.0 (Windows; U; Windows NT 5.1; en-US;
	rv:1.7.6) Gecko/20050319
X-Accept-Language: en-au, en-gb, en-us, en, es-ar, es-es
In-Reply-To: <1115104938.5853.25.camel@localhost.localdomain>
To: pgsql-sql@postgresql.org
X-Virus-Scanned: by amavisd-new at hub.org
X-Spam-Status: No, hits=0.015 tagged_above=0 required=5 tests=AWL
X-Spam-Level: 
X-Archive-Number: 200505/63
X-Sequence-Number: 21376

Ragnar Hafstaš wrote:
> 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.
To make sure the index is used you could build the query using a 
subquery like this:

SELECT DISTINCT date_part('year',  add_date),  date_part('month', 
add_date),  date_part('day', add_date)
FROM (
  SELECT uu.add_date::date as add_date
  FROM user_url uu
  GROUP BY uu.user_id, uu.add_date
  HAVING uu.user_id=1
  ORDER BY uu.add_date::date DESC
  LIMIT 7
) x;

Perhaps a select distinct instead of the group by in the subquery would 
make use the index?

Cheers,

Ezequiel Tolnay