You might post the results of "EXPLAIN ANALYZE <your SQL here>;" ... be sure to run it in a transaction if you want to be able roll it back. Perhaps try "EXPLAIN <your SQL>;" first as it is faster, but EXPLAIN ANALYZE shows what the planner is doing.
Here's something very very odd. Explain Analyze has now run, in about 4 minutes. (result below)
However, I'd be willing to swear that the last time I ran explain on this query about half an hour ago, the final 2 lines were sequential scans.
So, I've just terminated the real job (which uses this select for an update) after 77 minutes of fruitless cpu-hogging, and re-started it....
...This time, the same job ran through in 24 minutes. [This is running exactly the same transaction on exactly the same data!]
Richard
It looks like your statistics are way out of sync with the real data.
This shows that it thinks there will be 8,686 rows, but actually traverses 347,308.
Have you manually run a VACUUM on these tables? Preferrably a full one if you can. I notice that you appear ot have multiple sorts going on. Are all of those actually necessary for your output? Also consider using partial or multicolumn indexes where useful.
