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
