On Fri, Jun 19, 2009 at 2:05 PM, Brian Cox<brian.cox@ca.com> wrote:
> David Rees [drees76@gmail.com] 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