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