While executing the following query through psql :
SELECT me.* FROM measurement_events me JOIN msrcs_timestamps mt ON me.measurement_source_id=mt.measurement_source_id WHERE measurement_time > last_update_time
there are two behaviors observed by postgresql (8.4):
1) Either the query performs lots of reads on the database and completes in about 4 hours (that is the normal-expected behavior)
2) Either the query starts filling-up pgsql_tmp and this causes large write I/O on the server, and the query never actually completes on a reasonable time (we stop it after 10h).
For some strange reason, behaviour 2 is always observed when running psql through a bash script, while behavior 1 is only observed while running psql interactively from command line (but not always).
explain:
# explain SELECT me.* FROM measurement_events me JOIN msrcs_timestamps mt ON me.measurement_source_id=mt.measurement_source_id WHERE measurement_time > last_update_time;