Re: Trimming the cost of ORDER BY in a simple query

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
BYuu.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