On Fri, Jun 19, 2009 at 2:05 PM, Brian Cox<brian.cox@xxxxxx> wrote: > David Rees [drees76@xxxxxxxxx] wrote: >> >> Along those lines, couldn't you just have the DB do the work? >> >> select max(ts_id), min(ts_id) from ... where ts_interval_start_time >= >> ... and ... >> >> Then you don't have to transfer 500k ids across the network... > > I guess you didn't read the entire thread: I started it because the query > you suggest took 15 mins to complete. I read the whole thing and just scanned through it again - I didn't see any queries where you put both the min and max into the same query, but perhaps I missed it. Then again - I don't quite see why your brute force method is any faster than using a min or max, either. It would be interesting to see the analyze output as apparently scanning on the ts_interval_start_time is a lot faster than scanning the pkey (even though Tom thought that it would not be much difference since either way you have to hit the heap a lot). My thought was that putting both the min and max into the query would encourage Pg to use the same index as the brute force method. If not, you could still put the ts_ids into a temporary table using your brute force query and use that to avoid the overhead transferring 500k ids over the network. -Dave -- Sent via pgsql-performance mailing list (pgsql-performance@xxxxxxxxxxxxxx) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-performance
