Largely inconsistent query execution speed, involving psql_tmp

[Spiros Ioannou <sivann@xxxxxxxxxxxx>]

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;

                                          QUERY PLAN                                          

----------------------------------------------------------------------------------------------

 Hash Join  (cost=10111.78..422893652.69 rows=2958929695 width=103)

   Hash Cond: (me.measurement_source_id = mt.measurement_source_id)

   Join Filter: (me.measurement_time > mt.last_update_time)

   ->  Seq Scan on measurement_events me  (cost=0.00..234251772.85 rows=8876789085 width=103)

   ->  Hash  (cost=5733.57..5733.57 rows=350257 width=24)

         ->  Seq Scan on msrcs_timestamps mt  (cost=0.00..5733.57 rows=350257 width=24)

(6 rows)

We have tried so far fiddling with work_mem up to 512M - no difference. 

Any suggestions?

Thanks for any help,

-Spiros Ioannou

inaccess