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

[Ezequiel Tolnay]

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
---------------------------(end of broadcast)---------------------------
TIP 2: you can get off all lists at once with the unregister command
   (send "unregister YourEmailAddressHere" to [EMAIL PROTECTED])