"JS Ubei" <jsubei@xxxxxxxx> writes: > Hi all, > > I need to improve a query like : > > SELECT id, min(the_date), max(the_date) FROM my_table GROUP BY id; > > Stupidly, I create a B-tree index on my_table(the_date), witch is logically > not used in my query, because it's not with a constant ? isn't it ? That's not so stupid, it would be possible for a database to make use of such an index for this query. But it's not one of the plans Postgres knows how to execute. I don't think you'll find anything much faster for this particular query. You could profile running these two (non-standard) queries: SELECT DISTINCT ON (id) id, the_date AS min_date FROM my_table ORDER BY id, the_date ASC SELECT DISTINCT ON (id) id, the_date AS max_date FROM my_table ORDER BY id, the_date DESC I think the first of these can actually use your index but the latter can't unless you create one for it specifically (which is not so easy -- it'll be easier in 8.3 though). Worse, I'm not really sure it'll be any faster than the query you already have. -- Gregory Stark EnterpriseDB http://www.enterprisedb.com ---------------------------(end of broadcast)--------------------------- TIP 5: don't forget to increase your free space map settings
