And another thing which comes out as a little surprising to me - if I replace the *date_last_updated* condition with another one, say *doc.documenttype = 4*, the query finishes immediately. *documenttype* is an unindexed integer column.
The only index that matters here is the pkey on document. The problem is the failure to exit the nested loop once 1,000 translations have been gathered. Translation is related to document via key - hence the nested loop. A hashing-based plan would make use of the secondary indexes but likely would not be particularly useful in this query (contrary to my earlier speculation).
What's so special about that *date_last_updated* condition that makes it so slow to use? Is it because it involves the *date()* function call that it makes it difficult for the planner to guess the data distribution in the DOCUMENT table?
What happens if you pre-compute the date condition and hard code it?
