[teknomage1]

I'm not always the best at optimizing, but I think the reason your query slows down is that when you do any sort of join, it creates the cartesian product of both tables and then searchs the resulting millions of rows for valid ones.

You might be able to avoid this by doing using perl (EDIT: oops forgot to look at the poster ... )[Or Java] to do two queries (don't know if Postgres supports nested queries) to return only the trip_id number from trips table to get all the trips from between the two dates and then construct an ugly looking SQL query on the data table in the form of 'SELECT location_data FROM data WHERE Trip_id = ($result1 | $result 2 | ...)' so basically make your program logic layer perform a join without generating a huge temp table (which may have forced your machine to start swapping pages).

I'm sure other folks here know how to actually use the advanced features of Postgres to do all this inside the database.